如何从pandas DataFrame生成n级分层JSON? [英] How to generate n-level hierarchical JSON from pandas DataFrame?
问题描述
是否有一种有效的方法来创建层次化JSON(深度为n级),其中父值是键而不是变量标签?即:
Is there an efficient way to create hierarchical JSON (n-levels deep) where the parent values are the keys and not the variable label? i.e:
{"2017-12-31":
{"Junior":
{"Electronics":
{"A":
{"sales": 0.440755
}
},
{"B":
{"sales": -3.230951
}
}
}, ...etc...
}, ...etc...
}, ...etc...
1.我的测试DataFrame:
colIndex=pd.MultiIndex.from_product([['New York','Paris'],
['Electronics','Household'],
['A','B','C'],
['Junior','Senior']],
names=['City','Department','Team','Job Role'])
rowIndex=pd.date_range('25-12-2017',periods=12,freq='D')
df1=pd.DataFrame(np.random.randn(12, 24), index=rowIndex, columns=colIndex)
df1.index.name='Date'
df2=df1.resample('M').sum()
df3=df2.stack(level=0).groupby('Date').sum()
2.我正在进行的转换,因为它似乎是从以下位置构建JSON的最合乎逻辑的结构:
2. Transformation I'm making as it seems to be the most logical structure to build the JSON from:
df4=df3.stack(level=[0,1,2]).reset_index() \
.set_index(['Date','Job Role','Department','Team']) \
.sort_index()
3.我到目前为止的尝试
我遇到了这个非常有帮助的SO问题,该问题使用如下代码使用代码解决了一层嵌套的问题:
I came across this very helpful SO question which solves the problem for one level of nesting using code along the lines of:
j =(df.groupby(['ID','Location','Country','Latitude','Longitude'],as_index=False) \
.apply(lambda x: x[['timestamp','tide']].to_dict('r'))\
.reset_index()\
.rename(columns={0:'Tide-Data'})\
.to_json(orient='records'))
...但是我找不到找到嵌套.groupby()
的方法:
...but I can't find a way to get nested .groupby()
s working:
j=(df.groupby('date', as_index=True).apply(
lambda x: x.groupby('Job Role', as_index=True).apply(
lambda x: x.groupby('Department', as_index=True).apply(
lambda x: x.groupby('Team', as_index=True).to_dict()))) \
.reset_index().rename(columns={0:'sales'}).to_json(orient='records'))
推荐答案
您可以使用itertuples生成嵌套的dict
,然后转储到json
.为此,您需要将日期时间戳更改为string
You can use itertuples to generate a nested dict
, and then dump to json
. To do this, you need to change the date timestamp to string
df4=df3.stack(level=[0,1,2]).reset_index()
df4['Date'] = df4['Date'].dt.strftime('%Y-%m-%d')
df4 = df4.set_index(['Date','Job Role','Department','Team']) \
.sort_index()
创建嵌套字典
def nested_dict():
return collections.defaultdict(nested_dict)
result = nested_dict()
使用itertuples
填充
for row in df4.itertuples():
result[row.Index[0]][row.Index[1]][row.Index[2]][row.Index[3]]['sales'] = row._1
# print(row)
,然后使用json
模块将其转储.
and then use the json
module to dump it.
import json
json.dumps(result)
'{"2017-12-31":{初级":{电子":{"A":{销售":-0.3947134370101142},"B":{销售":-0.9873530754403204}, "C":{销售":-1.1182598058984508},家庭":{"A":{销售":-1.1211850078098677},"B":{销售":2.0330914483907847},"C":{销售":3.94762379718749}}},高级":{电子":{"A":{销售":1.4528493451404196},"B":{销售":-2.3277322345261005},"C":{销售:-2.8040263791743922}},"家庭:{" A:{"销售:3.0972591929279663}," B:{"销售:9.884565742502392}," C:{"销售:2.9359830722457576}}}} ,"2018-01-31":{初级":{电子":{"A":{销售":-1.3580300149125217},"B":{销售":1.414665000013205},"C":{ 销售":-1.432795129108244}},家庭":{"A":{销售":2.7783259569115346},"B":{销售":2.717700275321333},"C":{销售":1.4358377416259644}} },"Senior":{"Electronics":{"A":{"sales":2.8981726774941485},"B":{"sales":12.022897003654117},"C":{"sales":0.01776855733076088}},家庭":{"A":{销售":-3.3421637766130 92},"B":{"sales":-5.283208386572307},"C":{"sales":2.942580121975619}}}}}''
'{"2017-12-31": {"Junior": {"Electronics": {"A": {"sales": -0.3947134370101142}, "B": {"sales": -0.9873530754403204}, "C": {"sales": -1.1182598058984508}}, "Household": {"A": {"sales": -1.1211850078098677}, "B": {"sales": 2.0330914483907847}, "C": {"sales": 3.94762379718749}}}, "Senior": {"Electronics": {"A": {"sales": 1.4528493451404196}, "B": {"sales": -2.3277322345261005}, "C": {"sales": -2.8040263791743922}}, "Household": {"A": {"sales": 3.0972591929279663}, "B": {"sales": 9.884565742502392}, "C": {"sales": 2.9359830722457576}}}}, "2018-01-31": {"Junior": {"Electronics": {"A": {"sales": -1.3580300149125217}, "B": {"sales": 1.414665000013205}, "C": {"sales": -1.432795129108244}}, "Household": {"A": {"sales": 2.7783259569115346}, "B": {"sales": 2.717700275321333}, "C": {"sales": 1.4358377416259644}}}, "Senior": {"Electronics": {"A": {"sales": 2.8981726774941485}, "B": {"sales": 12.022897003654117}, "C": {"sales": 0.01776855733076088}}, "Household": {"A": {"sales": -3.342163776613092}, "B": {"sales": -5.283208386572307}, "C": {"sales": 2.942580121975619}}}}}'
这篇关于如何从pandas DataFrame生成n级分层JSON?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!