从嵌套的json文件创建pandas数据帧

Question

我有以下json文件：

{
"data": {
"start_date": "2022-10-01",
"end_date": "2022-10-04",
"cur": "EUR",
"prizes": {
"2022-10-01": {
"coffee": 0.1448939471560284,
"usd": 1
},
"2022-10-02": {
"coffee": 0.14487923291390148,
"usd":1
},
"2022-10-03": {
"coffee": 0.1454857922753868,
"usd": 1
}
}
}
}

我想创建一个看起来像这样的数据帧(所以没有usd列(：

coffee  
2022-10-01  0.144894  
2022-10-02  0.144879  
2022-10-03  0.145486 

这是我尝试过的：

path = r'C:UsersGeoDesktopjson_filescoffee.json'
df = pd.read_json(path)
df = pd.DataFrame(df['data']['prizes']['2022-10-01']['coffee']).T
print(df)

这是我收到的：

raise ValueError("DataFrame constructor not properly called!")
ValueError: DataFrame constructor not properly called!

Answer 1

假设d为输入变量的一个选项：

df = (pd.DataFrame
.from_dict(d['data']['prizes'], orient='index')
.drop(columns='usd', errors='ignore')  # or [['coffee']]
)

输出：

coffee
2022-10-01  0.144894
2022-10-02  0.144879
2022-10-03  0.145486

Answer 2

假设您的json文件作为data加载到字典中

df = pd.DataFrame(data['data']['prizes']).drop('usd').T

print(df)
coffee
2022-10-01  0.144894
2022-10-02  0.144879
2022-10-03  0.145486

Answer 3

假设json存储在变量json中

json = {
"data": {
"start_date": "2022-10-01",
"end_date": "2022-10-04",
"cur": "EUR",
"prizes": {
"2022-10-01": {
"coffee": 0.1448939471560284,
"usd": 1
},
"2022-10-02": {
"coffee": 0.14487923291390148,
"usd":1
},
"2022-10-03": {
"coffee": 0.1454857922753868,
"usd": 1
}
}
}
}

以下将完成的工作

df = pd.DataFrame(json['data']['prizes']).T.drop('usd', axis=1)
[Out]:
coffee
2022-10-01  0.144894
2022-10-02  0.144879
2022-10-03  0.145486

Answer 4

dic=json.load(path)
df=pd.DataFrame(dic)
print(type(df.loc['prizes']))
md=pd.DataFrame(df.loc['prizes'][0])
md=md.transpose()
del md['usd']
print(md)