使用Pandas将多层级Dict/JSON转换为CSV的问题求助

示例多层级JSON数据 #

sample_response = [
    {
        "id": 1,
        "order_no": "ORD001",
        "person": {
            "name": "张三",
            "age": 30,
            "contact": {
                "phone": "13800138000",
                "email": "zhangsan@example.com"
            },
            "addresses": [
                {"type": "home", "detail": "北京市朝阳区XX小区"},
                {"type": "work", "detail": "北京市海淀区XX大厦"}
            ]
        },
        "items": [
            {"product": "手机", "price": 5999, "quantity": 1},
            {"product": "耳机", "price": 299, "quantity": 2}
        ]
    },
    {
        "id": 2,
        "order_no": "ORD002",
        "person": {
            "name": "李四",
            "age": 25,
            "contact": {
                "phone": "13900139000",
                "email": "lisi@example.com"
            },
            "addresses": [
                {"type": "home", "detail": "上海市浦东新区XX公寓"}
            ]
        },
        "items": [
            {"product": "平板", "price": 3999, "quantity": 1}
        ]
    }
]

原无效代码示例（模拟场景） #

import pandas as pd

def expand_col(df, col):
    expanded = df[col].apply(pd.Series)
    return pd.concat([df.drop(col, axis=1), expanded], axis=1)

df = pd.DataFrame(sample_response)
df_expanded = expand_col(df, "person")
# 嵌套列表（如addresses、items）无法被展开，转CSV后仍保留列表格式，不符合需求
df_expanded.to_csv("output.csv", index=False)

可行解决方案 #

1. 用pandas.json_normalize处理嵌套字典 #

json_normalize可直接展开单层嵌套字典，通过参数控制字段命名规则：

import pandas as pd

# 展开person下的所有嵌套字典，用下划线连接层级字段
df_main = pd.json_normalize(sample_response, sep="_")
# 此时addresses、items仍为列表类型，需进一步处理

2. 展开嵌套列表字段（多对多关联场景） #

如果需要将列表中的每个元素拆分为独立行，同时保留主数据关联，结合explode和json_normalize：

处理addresses列表

# 先得到展开字典后的主数据
df_main = pd.json_normalize(sample_response, sep="_")
# 将addresses列表拆分为多行
df_addresses = df_main.explode("person_addresses", ignore_index=True)
# 展开拆分后的addresses字典
df_addresses = pd.concat(
    [
        df_addresses.drop("person_addresses", axis=1),
        pd.json_normalize(df_addresses["person_addresses"], sep="_")
    ],
    axis=1
)
# 转CSV
df_addresses.to_csv("addresses_expanded.csv", index=False)

处理items列表（逻辑一致）

df_items = df_main.explode("items", ignore_index=True)
df_items = pd.concat(
    [
        df_items.drop("items", axis=1),
        pd.json_normalize(df_items["items"], sep="_")
    ],
    axis=1
)
df_items.to_csv("items_expanded.csv", index=False)

3. 递归展开所有嵌套结构（通用扁平化方案） #

针对层级复杂、不确定的嵌套数据，用递归函数完全展开所有字段：

import pandas as pd

def flatten_json(nested_json, sep="_"):
    out = {}
    def flatten(x, name=""):
        if isinstance(x, dict):
            for k, v in x.items():
                flatten(v, name + k + sep)
        elif isinstance(x, list):
            for i, v in enumerate(x):
                flatten(v, name + str(i) + sep)
        else:
            out[name[:-1]] = x
    flatten(nested_json)
    return out

# 对每个数据项递归扁平化
flattened_data = [flatten_json(item) for item in sample_response]
df_flattened = pd.DataFrame(flattened_data)
# 转CSV
df_flattened.to_csv("full_flattened_output.csv", index=False)

注意事项 #

• 嵌套列表展开后会生成多行，需根据业务需求确认是否保留多对多关联关系
• json_normalize的sep参数可自定义字段分隔符，避免命名冲突
• 完全扁平化会生成大量字段，可在转CSV前筛选需要的列

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