从嵌套的json中提取键和值并放入DataFrame中

Question

我有一个json对象，看起来像这样:

{
"2022-06-05":{
"revenue":"287.05",
"returns_amount":"0.00",
"date":"2022-06-05"
},
"2022-06-06":{
"revenue":"229.76",
"returns_amount":"0.00",
"date":"2022-06-06"
},
"2022-06-07":{
"revenue":"0.00",
"returns_amount":"0.00",
"date":"2022-06-07"
}
}

是否有可能提取所有日期(键)及其各自的"收入"?值，并将它们放入如下所示的数据框架中:

date              revenue
2022-06-05        287.05
2022-06-06        229.76
2022-06-07        0.00

Answer 1

您可以使用read_json读取json字符串，然后删除索引日期值和returns_amount列。假设json在jstr中:

pd.read_json(jstr, orient='index').reset_index(drop=True).drop('returns_amount',axis=1)

输出:

revenue       date
0   287.05 2022-06-05
1   229.76 2022-06-06
2     0.00 2022-06-07

或者，将json转换为字典，然后对其进行过滤，使数据帧:

dic = json.loads(jstr)
pd.DataFrame([ { 'date' : d['date'], 'revenue': d['revenue'] } for d in dic.values() ])

输出:

date revenue
0  2022-06-05  287.05
1  2022-06-06  229.76
2  2022-06-07    0.00

Answer 2

不如这样写:

import pandas as pd
import json
data = []   # list to hold data
file_name = "file.json" # JSON file
with open(file_name) as f:
doc = json.load(f)
for key in doc:
data.append(doc[key])   # just need object, not outer date "key"
df = pd.DataFrame(data=data, columns=["date", "revenue"])
print(df)