Pandas Dataframe到JSON层次结构

Question

我已经详尽地审查/尝试了与此挑战相对应的所有其他问题的实现，但尚未找到解决方案。在

问题：如何将员工和主管对转换为用于D3可视化的分层JSON结构？有未知数量的级别，所以它必须是动态的。在

我有一个包含五列的数据帧（是的，我意识到这不是办公室的实际层次结构）：

  Employee_FN Employee_LN Supervisor_FN Supervisor_LN  Level
0     Michael       Scott          None          None      0
1         Jim     Halpert       Michael         Scott      1
2      Dwight     Schrute       Michael         Scott      1
3     Stanley      Hudson           Jim       Halpert      2
4         Pam     Beasley           Jim       Halpert      2
5        Ryan      Howard           Pam       Beasley      3
6       Kelly      Kapoor          Ryan        Howard      4
7    Meredith      Palmer          Ryan        Howard      4 

所需输出快照：

^{pr2}$

当前状态：

j = (df.groupby(['Level','Employee_FN','Employee_LN'], as_index=False)
             .apply(lambda x: x[['Level','Employee_FN','Employee_LN']].to_dict('r'))
             .reset_index()
             .rename(columns={0:'Reports'})
             .to_json(orient='records'))

print(json.dumps(json.loads(j), indent=2, sort_keys=True))

电流输出：

[
  {
    "Employee_FN": "Michael",
    "Employee_LN": "Scott",
    "Level": 0,
    "Reports": [
      {
        "Employee_FN": "Michael",
        "Employee_LN": "Scott",
        "Level": 0
      }
    ]
  },
  {
    "Employee_FN": "Dwight",
    "Employee_LN": "Schrute",
    "Level": 1,
    "Reports": [
      {
        "Employee_FN": "Dwight",
        "Employee_LN": "Schrute",
        "Level": 1
      }
    ]
  },
  {
    "Employee_FN": "Jim",
    "Employee_LN": "Halpert",
    "Level": 1,
    "Reports": [
      {
        "Employee_FN": "Jim",
        "Employee_LN": "Halpert",
        "Level": 1
      }
    ]
  },
  {
    "Employee_FN": "Pam",
    "Employee_LN": "Beasley",
    "Level": 2,
    "Reports": [
      {
        "Employee_FN": "Pam",
        "Employee_LN": "Beasley",
        "Level": 2
      }
    ]
  },
  {
    "Employee_FN": "Stanley",
    "Employee_LN": "Hudson",
    "Level": 2,
    "Reports": [
      {
        "Employee_FN": "Stanley",
        "Employee_LN": "Hudson",
        "Level": 2
      }
    ]
  },
  {
    "Employee_FN": "Ryan",
    "Employee_LN": "Howard",
    "Level": 3,
    "Reports": [
      {
        "Employee_FN": "Ryan",
        "Employee_LN": "Howard",
        "Level": 3
      }
    ]
  },
  {
    "Employee_FN": "Kelly",
    "Employee_LN": "Kapoor",
    "Level": 4,
    "Reports": [
      {
        "Employee_FN": "Kelly",
        "Employee_LN": "Kapoor",
        "Level": 4
      }
    ]
  },
  {
    "Employee_FN": "Meredith",
    "Employee_LN": "Palmer",
    "Level": 4,
    "Reports": [
      {
        "Employee_FN": "Meredith",
        "Employee_LN": "Palmer",
        "Level": 4
      }
    ]
  }
]

问题：

1. 每个人都只有自己小时候
2. 整个JSON结构似乎都在dict中—我相信它必须用{}括起来才能阅读

我尝试在各种配置中切换groupby和lambda元素，以达到所需的输出。任何和所有的洞察力将不胜感激！谢谢您！在

更新：

我把代码块改为：

j = (df.groupby(['Level','Supervisor_FN','Supervisor_LN'], as_index=False)
             .apply(lambda x: x[['Level','Employee_FN','Employee_LN']].to_dict('r'))
             .reset_index()
             .rename(columns={0:'Reports'})
             .rename(columns={'Supervisor_FN':'Employee_FN'})
             .rename(columns={'Supervisor_LN':'Employee_LN'})
             .to_json(orient='records'))

print(json.dumps(json.loads(j), indent=2, sort_keys=True))

新的输出是：

[
  {
    "Employee_FN": "Michael",
    "Employee_LN": "Scott",
    "Level": 1,
    "Reports": [
      {
        "Employee_FN": "Jim",
        "Employee_LN": "Halpert",
        "Level": 1
      },
      {
        "Employee_FN": "Dwight",
        "Employee_LN": "Schrute",
        "Level": 1
      }
    ]
  },
  {
    "Employee_FN": "Jim",
    "Employee_LN": "Halpert",
    "Level": 2,
    "Reports": [
      {
        "Employee_FN": "Stanley",
        "Employee_LN": "Hudson",
        "Level": 2
      },
      {
        "Employee_FN": "Pam",
        "Employee_LN": "Beasley",
        "Level": 2
      }
    ]
  },
  {
    "Employee_FN": "Pam",
    "Employee_LN": "Beasley",
    "Level": 3,
    "Reports": [
      {
        "Employee_FN": "Ryan",
        "Employee_LN": "Howard",
        "Level": 3
      }
    ]
  },
  {
    "Employee_FN": "Ryan",
    "Employee_LN": "Howard",
    "Level": 4,
    "Reports": [
      {
        "Employee_FN": "Kelly",
        "Employee_LN": "Kapoor",
        "Level": 4
      },
      {
        "Employee_FN": "Meredith",
        "Employee_LN": "Palmer",
        "Level": 4
      }
    ]
  }
]

问题：

1. Level匹配基础员工和主管的基础员工
2. 筑巢的深度只有一层

Answer 1

这种类型的问题并不特别适合Pandas；您所追求的数据结构是递归的，而不是表格式的。在

这里有一个可能的解决方案。在

from operator import itemgetter

employee_key = itemgetter('Employee_FN', 'Employee_LN')
supervisor_key = itemgetter('Supervisor_FN', 'Supervisor_LN')

def subset(dict_, keys):
    return {k: dict_[k] for k in keys}

# store employee references
cache = {}

# iterate over employees sorted by level, so supervisors are cached before reports
for row in df.sort_values('Level').to_dict('records'):

    # look up employee/supervisor references
    employee = cache.setdefault(employee_key(row), subset(row, keys=('Employee_FN', 'Employee_LN', 'Level')))
    supervisor = cache.get(supervisor_key(row), {})

    # link reports to employee
    supervisor.setdefault('Reports', []).append(employee)

# grab only top-level employees
[rec for key, rec in cache.iteritems() if rec['Level'] == 0]

^{pr2}$