从 pandas 为组织结构图创建嵌套的JSON [英] Create Nested JSON from Pandas for Org Chart

问题描述

我正在尝试从分层DataFrame(python 3.5)创建一个嵌套的JSON对象，以馈入JavaScript以呈现组织结构图.我实质上是在尝试创建在此问题的答案中找到的结构:

I'm trying to create a nested JSON object from a hierarchical DataFrame (python 3.5) to feed into JavaScript to render an Org Chart. I'm essentially trying to create the structure found in the answer of this question: Organization chart - tree, online, dynamic, collapsible, pictures - in D3

示例数据框:

df = pd.DataFrame({\
'Manager_Name':['Mike' ,'Jon', 'Susan' ,'Susan' ,'Joe'],\
'Manager_Title':['Level1' ,'Level2'  ,'Level3' ,"Level3", 'Level4'],\
'Employee_Name':['Jon' ,'Susan' ,'Josh' ,'Joe' ,'Jimmy'],\
'Employee_Title':["Level2" ,"Level3" ,"Level4" ,"Level4" ,"Level5"]})

所需的输出将是:

"Name": "Mike"
"Title": "Level1"
"Employees": [{
        "Name": "Jon"
        "Title": "Level2"
        "Employees": [{
               "Name": "Susan"
               "Title": "Level3"
               "Employees": [{
               ...
               ...
               ...
               }]
      }]
}]

我知道这不是代码生成服务，但是我尝试应用其他类似相关的答案，但似乎无法在此处应用这些答案.我也没有和字典合作那么多(我更像是R人)，所以这个问题可能有些笨拙.我花了更多的时间在这里，但我确定这里的人可以在几分钟内完成此操作.

I know this isn't a code generating service but I've tried applying other similarly related answers and can't seem to apply those answers here. I also haven't worked with dictionaries that much (I'm more of an R person) so there's probably some noobishness to this question. I've more time than I should on this yet I'm sure someone here can do this in a few minutes.

其他问题:

• pandas groupby到嵌套的json
• 使用以下方法创建嵌套的Json结构来自Json的Python中的多个键值

• pandas groupby to nested json
• Creating nested Json structure with multiple key values in Python from Json
• How to build a JSON file with nested records from a flat data table?

提前谢谢！

推荐答案

考虑按级别过滤数据帧，并使用pandas将dfs转换为字典名称"和"标题"列连接起来.

Consider filtering out dataframe by Level and converting dfs to dictionary with pandas to_dict() which are continually rolled into one list across levels. Below defined function walks from last level to first to roll up individual Employee Levels dictionaries. But first you should concatenate Manager and Employee Name and Title columns.

import json
import pandas as pd

cdf = pd.concat([df[['Manager_Name', 'Manager_Title']].\
            rename(index=str, columns={'Manager_Name':'Name', 'Manager_Title':'Title'}),
            df[['Employee_Name', 'Employee_Title']].\
            rename(index=str, columns={'Employee_Name':'Name', 'Employee_Title':'Title'})])

cdf = cdf.drop_duplicates().reset_index(drop=True)
print(cdf)
#     Name   Title
# 0   Mike  Level1
# 1    Jon  Level2
# 2  Susan  Level3
# 3    Joe  Level4
# 4   Josh  Level4
# 5  Jimmy  Level5

def jsondict():
    inner = ['']
    for i in ['Level5', 'Level4', 'Level3', 'Level2']:            
        if i == 'Level5':
            inner[0] = cdf[cdf['Title']==i].to_dict(orient='records')            
        else:
            tmp = cdf[cdf['Title']==i].copy().reset_index(drop=True)            
            if len(tmp) == 1:
                tmp['Employees'] = [inner[0]]
            else:
                for d in range(0,len(tmp)):
                    tmp.ix[d, 'Employees'] = [inner[0]]                                
            lvltemp = tmp.to_dict(orient='records')
            inner[0] = lvltemp            
    return(inner)

jsondf = cdf[cdf['Title']=='Level1'].copy()
jsondf['Employees'] = jsondict()    
jsondata = jsondf.to_json(orient='records')

输出

[{"Name":"Mike","Title":"Level1","Employees":
[{"Name":"Jon","Title":"Level2","Employees":
[{"Name":"Susan","Title":"Level3","Employees":
[{"Name":"Joe","Title":"Level4","Employees":
[{"Name":"Jimmy","Title":"Level5"}]},
{"Name":"Josh","Title":"Level4","Employees":
[[{"Name":"Jimmy","Title":"Level5"}]]}]}]}]}]

或精美印刷

[
  {
    "Name": "Mike",
    "Title": "Level1",
    "Employees": [
      {
        "Name": "Jon",
        "Title": "Level2",
        "Employees": [
          {
            "Name": "Susan",
            "Title": "Level3",
            "Employees": [
              {
                "Name": "Joe",
                "Title": "Level4",
                "Employees": [
                  {
                    "Name": "Jimmy",
                    "Title": "Level5"
                  }
                ]
              },
              {
                "Name": "Josh",
                "Title": "Level4",
                "Employees": [
                  [
                    {
                      "Name": "Jimmy",
                      "Title": "Level5"
                    }
                  ]
                ]
              }
            ]
          }
        ]
      }
    ]
  }
]

这篇关于从 pandas 为组织结构图创建嵌套的JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！