python - 将熊猫多索引系列转换为 Json python
问题描述
嗨,我有两个类似于下面的熊猫系列
盈亏
Product Name Price
Company A Orange 3000
Company B Apple 2000
Grapes 1000
税
Product Name Price
Company A Orange 100
Company B Apple 100
Grapes 10
我想把pandas系列转换成下面的JSON格式
{'PnL':{'Company A':{'productName':'Orange','price':3000},
'Company B':[{'productName':'Apple','price':2000},
{'productName':'Grapes','price':1000}]
},
'Tax':{'Company A':{'productName':'Orange','price':100},
'Company B':[{'productName':'Apple','price':100},
{'productName':'Grapes','price':10}]
}
}
我尝试使用下面的代码
convertedJson = json.dumps([{'company': k[0], 'productName':k[1],'price': v} for k,v in df.items()])
但我无法形成我想要生成的 JSON。感谢您的帮助
解决方案
您可以将concat
for joinDaatFrame
一起使用,然后groupby
使用 withto_dict
获得预期的输出:
df = pd.concat([s1, s2], keys=('PnL','Tax')).reset_index()
df.columns = ['type','company','productName','price']
print (df)
type company productName price
0 PnL Company A Orange 3000
1 PnL Company B Apple 2000
2 PnL Company B Grapes 1000
3 Tax Company A Orange 3000
4 Tax Company B Apple 2000
5 Tax Company B Grapes 1000
d = (df.groupby(['type','company'])['productName','price']
.apply(lambda x: x.to_dict('r'))
.reset_index(name='data')
.groupby('type')['company','data']
.apply(lambda x: x.set_index('company')['data'].to_dict())
.to_json()
)
print (d)
{
"PnL": {
"Company A": [{
"productName": "Orange",
"price": 3000
}],
"Company B": [{
"productName": "Apple",
"price": 2000
}, {
"productName": "Grapes",
"price": 1000
}]
},
"Tax": {
"Company A": [{
"productName": "Orange",
"price": 3000
}],
"Company B": [{
"productName": "Apple",
"price": 2000
}, {
"productName": "Grapes",
"price": 1000
}]
}
}