Python中如何将嵌套字典转换为指定格式的DataFrame

问题描述 #

给定如下嵌套字典结构的scores数据：

scores = [{"Student":"Adam","Subjects":[{"Name":"Math","Score":85},{"Name":"Science","Score":90}]},
     {"Student":"Bec","Subjects":[{"Name":"Math","Score":70},{"Name":"English","Score":100}]}]

直接使用pd.DataFrame(scores)转换会得到不符合预期的结果，希望将其转换为如下格式的DataFrame：

Student   Subject.Name   Subject.Score
 Adam         Math            85
 Adam         Science         90
 Bec          Math            70
 Bec          English         100

解决方案 #

方法一：使用pd.json_normalize（推荐） #

这是处理嵌套字典结构最简洁的方式，通过指定参数展开嵌套列表并保留外层字段：

import pandas as pd

scores = [{"Student":"Adam","Subjects":[{"Name":"Math","Score":85},{"Name":"Science","Score":90}]},
     {"Student":"Bec","Subjects":[{"Name":"Math","Score":70},{"Name":"English","Score":100}]}]

# 直接通过json_normalize展开嵌套结构
df = pd.json_normalize(
    scores,
    record_path='Subjects',  # 指定要展开的嵌套列表字段
    meta='Student',          # 指定要保留的外层字段
    sep='.'                  # 指定嵌套字段的分隔符，匹配目标列名格式
)

# 调整列顺序与命名，完全匹配需求格式
df = df[['Student', 'Name', 'Score']].rename(columns={'Name': 'Subject.Name', 'Score': 'Subject.Score'})
print(df)

方法二：手动循环展开 #

如果需要更直观的逻辑展示，可以通过循环遍历构造数据列表，再转换为DataFrame：

import pandas as pd

scores = [{"Student":"Adam","Subjects":[{"Name":"Math","Score":85},{"Name":"Science","Score":90}]},
     {"Student":"Bec","Subjects":[{"Name":"Math","Score":70},{"Name":"English","Score":100}]}]

data_list = []
# 遍历每个学生及其科目，构造扁平化数据
for student_info in scores:
    student_name = student_info['Student']
    for subject in student_info['Subjects']:
        data_list.append({
            'Student': student_name,
            'Subject.Name': subject['Name'],
            'Subject.Score': subject['Score']
        })

df = pd.DataFrame(data_list)
print(df)

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