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

输入JSON结构 #

{    
    "data": {        
        "success": true,        
        "timeseries": true,        
        "start_date": "2022-10-01",        
        "end_date": "2022-10-04",        
        "base": "EUR",        
        "rates": {            
            "2022-10-01": {                
                "NG": 0.1448939471560284            
            },            
            "2022-10-02": {                
                "NG": 0.14487923291390148            
            },            
            "2022-10-03": {                
                "NG": 0.1454857922753868            
            },            
            "2022-10-04": {                
                "NG": 0.1507352356663182            
            }        
        },        
        "unit": "per MMBtu"    
    }
}

期望的DataFrame格式 #

Date        NG        base 
2022-10-01  0.144894  EUR
2022-10-02  0.144879  EUR
2022-10-03  0.145486  EUR
2022-10-04  0.150735  EUR

尝试的代码及错误输出 #

尝试代码 #

import json
import pandas as pd

with open(r'C:\Users\EH\Desktop\tools\json_files\blue_file.json','r') as f:    
    data = json.loads(f.read())

df1 = pd.DataFrame(data['data']['rates'])
df1 = df1.T
df2 = pd.DataFrame(data['data'])
df2 = df2.base

merge = [df1, df2]
df3 = pd.concat(merge)

print(df3)

错误输出 #

NG    0
2022-10-01  0.144894  NaN
2022-10-02  0.144879  NaN
2022-10-03  0.145486  NaN
2022-10-04  0.150735  NaN
2022-10-01       NaN  EUR
2022-10-02       NaN  EUR
2022-10-03       NaN  EUR
2022-10-04       NaN  EUR

错误原因 #

你用pd.concat(merge)是按行方向拼接两个对象，导致原本的df1（含NG列）和df2（含base值的Series）被上下堆叠，两者没有共同列，所以交叉位置出现NaN。另外df2 = pd.DataFrame(data['data'])生成的DataFrame中，base列只有1个值，后续拼接时自动对齐索引重复填充，这种方式完全没必要——因为base是全局固定值，不需要单独生成DataFrame。

正确解决方案 #

直接处理rates生成基础DataFrame，然后添加固定值的base列，最后重置索引为Date列即可：

import json
import pandas as pd

with open(r'C:\Users\EH\Desktop\tools\json_files\blue_file.json','r') as f:    
    data = json.loads(f.read())

# 从rates生成DataFrame并转置，日期转为索引
df = pd.DataFrame(data['data']['rates']).T
# 添加base列，值为全局固定的EUR
df['base'] = data['data']['base']
# 重置索引，把日期转为显式列并命名为Date
df = df.reset_index().rename(columns={'index': 'Date'})
# 调整列顺序，匹配期望格式
df = df[['Date', 'NG', 'base']]
# 可选：保留NG列6位小数
df['NG'] = df['NG'].round(6)

print(df)

输出结果 #

Date        NG base
0  2022-10-01  0.144894  EUR
1  2022-10-02  0.144879  EUR
2  2022-10-03  0.145486  EUR
3  2022-10-04  0.150735  EUR

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