如何将嵌套记录转换为指定格式的Pandas多层索引DataFrame

场景1：列表形式的嵌套数据 #

原始数据：

data = [
        {"name": "Jack", "last_name": "Black", "sizes": {"shoes": 43, "waist": 48, "chest":52}},
        {"name": "Mario", "last_name": "Green", "sizes": {"shoes": 42, "waist": 53, "chest":63}}
]

需求是生成带有如下多层列索引的DataFrame：

name  last_name sizes
name  last_name shoes waist chest
Jack  Black      43     48    52
Mario Green      42     53    63

pd.json_normalize会将嵌套字段展开为sizes.shoes这类单层列名，不符合需求。可以通过拆分拼接+构造多层索引实现：

import pandas as pd

data = [
        {"name": "Jack", "last_name": "Black", "sizes": {"shoes": 43, "waist": 48, "chest":52}},
        {"name": "Mario", "last_name": "Green", "sizes": {"shoes": 42, "waist": 53, "chest":63}}
]

# 拆分顶层字段与嵌套的sizes字段
top_cols = pd.DataFrame(data)[["name", "last_name"]]
sizes_cols = pd.DataFrame([item["sizes"] for item in data])

# 拼接两部分数据
combined = pd.concat([top_cols, sizes_cols], axis=1)

# 构造多层列索引
combined.columns = pd.MultiIndex.from_tuples(
    [("name", "name"), ("last_name", "last_name"), ("sizes", "shoes"), ("sizes", "waist"), ("sizes", "chest")]
)

print(combined)

场景2：字典形式的嵌套数据（带指定行索引） #

原始数据：

data = {
        12345: {"name": "Jack", "last_name": "Black", "sizes": {"shoes": 43, "waist": 48, "chest":52}},
        78910: {"name": "Mario", "last_name": "Green", "sizes": {"shoes": 42, "waist": 53, "chest":63}}
}

需求是生成带有字典key作为行索引、同时具备指定多层列索引的DataFrame：

name  last_name sizes
      name  last_name shoes waist chest
12345 Jack  Black      43     48    52
78910 Mario Green      42     53    63

实现代码：

import pandas as pd

data = {
        12345: {"name": "Jack", "last_name": "Black", "sizes": {"shoes": 43, "waist": 48, "chest":52}},
        78910: {"name": "Mario", "last_name": "Green", "sizes": {"shoes": 42, "waist": 53, "chest":63}}
}

# 从字典创建DataFrame，保留原key为行索引
df = pd.DataFrame.from_dict(data, orient="index")

# 展开嵌套的sizes字段
sizes_cols = pd.DataFrame(df["sizes"].tolist(), index=df.index)

# 拼接顶层字段与展开后的sizes字段
combined = pd.concat([df[["name", "last_name"]], sizes_cols], axis=1)

# 构造多层列索引
combined.columns = pd.MultiIndex.from_tuples(
    [("name", "name"), ("last_name", "last_name"), ("sizes", "shoes"), ("sizes", "waist"), ("sizes", "chest")]
)

print(combined)

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