如何从嵌套JSON文件创建指定格式的Pandas DataFrame？

原始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
            }
        }
    }
}

期望生成的DataFrame #

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

错误尝试代码及报错 #

错误代码 #

path = r'C:\Users\Geo\Desktop\json_files\coffee.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!

正确实现方法 #

方法一：直接解析嵌套结构构造DataFrame #

读取JSON后提取prizes数据，转置后筛选目标列：

import pandas as pd

path = r'C:\Users\Geo\Desktop\json_files\coffee.json'
# 读取JSON为Series格式
data = pd.read_json(path, typ='series')
# 提取prizes字典转为DataFrame并转置，保留coffee列
df = pd.DataFrame(data['data']['prizes']).T[['coffee']]
# 保留6位小数
df = df.round(6)
print(df)

方法二：原生json模块配合字典推导 #

先解析JSON，提取目标数据构造字典后转DataFrame：

import pandas as pd
import json

path = r'C:\Users\Geo\Desktop\json_files\coffee.json'
with open(path, 'r') as f:
    json_data = json.load(f)
# 提取每个日期对应的coffee值
coffee_data = {date: info['coffee'] for date, info in json_data['data']['prizes'].items()}
# 从字典生成DataFrame，设置索引为日期
df = pd.DataFrame.from_dict(coffee_data, orient='index', columns=['coffee'])
df = df.round(6)
print(df)

方法三：利用pd.json_normalize简化解析 #

通过归一化工具处理嵌套JSON：

import pandas as pd
import json

path = r'C:\Users\Geo\Desktop\json_files\coffee.json'
with open(path, 'r') as f:
    json_data = json.load(f)
# 归一化嵌套的prizes数据并转置
df = pd.json_normalize(json_data['data']['prizes']).T
# 设置列名并移除索引名称
df.columns = ['coffee']
df.index.name = None
df = df.round(6)
print(df)

内容的提问来源于stack exchange，提问作者Erling_Haaland