将Pandas Dataframe转换为自定义嵌套JSON [英] Convert Pandas Dataframe to Custom Nested JSON

问题描述

我必须将Pandas Dataframe转换为嵌套的json.我尝试使用to_json，但是它将整个数据帧转换为键值对，不确定如何转换这样的嵌套json.任何帮助表示赞赏.

I have to convert a Pandas Dataframe to nested json. I tried using to_json but it converts the entire dataframe as key-value pair, Not sure how to convert a nested json like this. Any help much appreciated.

我的数据框:

df=pd.DataFrame({'id':[1,2,3,4,5,6,7],'name':['walmart','walmart dc','walmart supercenter','wal','walmart 5603','walmart#5603'
                                         ,'Sams walmart'],'Cluster_id':[123,123,123,123,123,123,123],
             'Cluster_name':['walmart','walmart','walmart','walmart','walmart','walmart','walmart'],'House_num':[123,456,789
                                                                                                                ,654,321,102,945]
            ,'Street':['Main Street','Main Street','Main Street','Main Street','Main Street','Main Street','Main Street'],
             'Cluster_Street':['Main Street','Main Street','Main Street','Main Street','Main Street','Main Street','Main Street'],
             'Cluster_House_Num':[456,456,456,456,456,456,456]
            })

输出JSON:

{
'cluster_id':123
'cluster_name':'walmart'
'address':{
    'House_num':456
    'Street': 'Main Street'
}
'records':[{
    'id':1
    'name':'walmart'
    'address':{
    'House_num':123
    'Street': 'Main Street'
}
},{
    'id':2
    'name':'walmart dc'
    'address':{
    'House_num':456
    'Street': 'Main Street'
}
},{
    'id':3
    'name':'walmart supercenter'
    'address':{
    'House_num':789
    'Street': 'Main Street'
}
},{
    'id':4
    'name':'wal'
    'address':{
    'House_num':654
    'Street': 'Main Street'
}
},{
    'id':5
    'name':'walmart 5603'
    'address':{
    'House_num':321
    'Street': 'Main Street'
}
},{
    'id':6
    'name':'walmart#5603'
    'address':{
    'House_num':102
    'Street': 'Main Street'
}
},{
    'id':7
    'name':'Sams walmart'
    'address':{
    'House_num':945
    'Street': 'Main Street'
}
}]

}

推荐答案

import pandas as pd

df=pd.DataFrame({'id':[1,2,3,4,5,6,7],'name':['walmart','walmart dc','walmart supercenter','wal','walmart 5603','walmart#5603'
                                             ,'Sams walmart'],'cluster_id':[123,123,123,123,123,123,123],
                 'cluster_name':['walmart','walmart','walmart','walmart','walmart','walmart','walmart'],'House_num':[456,456,456
                                                                                                                    ,456,456,456,456]
                ,'Street':['Main Street','Main Street','Main Street','Main Street','Main Street','Main Street','Main Street']
                })

df_cluster = df.groupby('cluster_id')
for cluster_id, group in df_cluster:

    records = []
    for row, data in group.iterrows():
        rec_dict = {'id':data[4],
                  'name':data[3],
                  'address':{
                      'House_num':data[0],
                      'Street': data[1]
                      }
                  }
        records.append(rec_dict)

    out_dict = {'cluster_id':cluster_id,'records':records}
    print (out_dict)

这篇关于将Pandas Dataframe转换为自定义嵌套JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！