如何用Python Pandas从JSON嵌套字典生成指定格式交叉表

步骤1：加载JSON数据 #

如果JSON存储在本地文件中，先读取并加载：

import json
import pandas as pd

with open('stats.json', 'r') as f:
    data = json.load(f)

步骤2：整理嵌套数据为目标结构 #

直接从嵌套字典中提取对应字段，构造符合交叉表格式的字典：

response = data['response']

stats_dict = {
    'played': {
        'Home': response['fixtures']['played']['home'],
        'Away': response['fixtures']['played']['away'],
        'Tot': response['fixtures']['played']['total']
    },
    'wins': {
        'Home': response['fixtures']['wins']['home'],
        'Away': response['fixtures']['wins']['away'],
        'Tot': response['fixtures']['wins']['total']
    },
    'draws': {
        'Home': response['fixtures']['draws']['home'],
        'Away': response['fixtures']['draws']['away'],
        'Tot': response['fixtures']['draws']['total']
    },
    'loss': {
        'Home': response['fixtures']['loses']['home'],
        'Away': response['fixtures']['loses']['away'],
        'Tot': response['fixtures']['loses']['total']
    },
    'goals_for': {
        'Home': response['goals']['for']['total']['home'],
        'Away': response['goals']['for']['total']['away'],
        'Tot': response['goals']['for']['total']['total']
    },
    'goals_against': {
        'Home': response['goals']['against']['total']['home'],
        'Away': response['goals']['against']['total']['away'],
        'Tot': response['goals']['against']['total']['total']
    },
    'avg_goal_for': {
        'Home': float(response['goals']['for']['average']['home']),
        'Away': float(response['goals']['for']['average']['away']),
        'Tot': float(response['goals']['for']['average']['total'])
    },
    'avg_goal_against': {
        'Home': float(response['goals']['against']['average']['home']),
        'Away': float(response['goals']['against']['average']['away']),
        'Tot': float(response['goals']['against']['average']['total'])
    }
}

步骤3：转换为DataFrame并调整格式 #

将构造好的字典转为DataFrame，同时调整索引名称以匹配目标表格：

df = pd.DataFrame.from_dict(stats_dict, orient='index')
df.index.name = None  # 移除索引列的表头

# 查看结果
print(df)

运行后输出的DataFrame与需求的交叉表完全一致：

Home  Away   Tot
played            19    19    38
wins              10     8    18
draws              7     5    12
loss               2     6     8
goals_for         40    26    66
goals_against     17    19    36
avg_goal_for      2.1   1.4   1.7
avg_goal_against  0.9   1.0   0.9

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