如何用Python将嵌套JSON转换为Excel/CSV文件

我有一个嵌套结构的JSON数据，想要将其转换为Excel（.xlsx）或CSV格式的表格，但因结构问题遇到困难。我尝试了以下pandas代码但未成功：

# Load the JSON data into a pandas DataFrame
df = pd.read_json(response_data, typ='series')

# Convert the DataFrame to a flattened dictionary
flat_dict = pd.json_normalize(df.to_dict())

# Export the flattened dictionary to an Excel file
flat_dict.to_excel('output.xlsx', index=False)

提供的JSON数据如下：

{
    "A": [
        {
            "Price": 200,
            "category": 620,
            "service": {
                "id": 15,
                "name": "KAL",
                "description": "Description",
                "Validity": null,
                "order": 0,
                "services": [
                    {
                        "id": 100,
                        "financeable": true,
                        "benefit": {
                            "id": 235,
                            "name": "ZSX",
                            "Priced": null
                        },
                        "Execution": null,
                        "serviceId": 112,
                        "label": "Colab"
                    }
                ],
                "selection": false
            },
            "creditTO": {
                "id": 0,
                "duration": 6,
                "Type": "standard",
                "Tax": 51,
                "total": 400,
                "promotion": false
            }
        }
    ],
        "B": [
        {
            "Price": 200,
            "category": 620,
            "service": {
                "id": 15,
                "name": "BTX",
                "description": "Description",
                "Validity": null,
                "order": 0,
                "services": [
                    {
                        "id": 100,
                        "financeable": true,
                        "benefit": {
                            "id": 235,
                            "name": "ZSX",
                            "Priced": null
                        },
                        "Execution": null,
                        "serviceId": 112,
                        "label": "Colab"
                    }
                ],
                "selection": false
            },
            "creditTO": {
                "id": 0,
                "duration": 9,
                "Type": "standard",
                "Tax": 51,
                "total": 400,
                "promotion": false
            }
        }
    ],
        "C": [
        {
            "Price": 600,
            "category": 620,
            "service": {
                "id": 15,
                "name": "FLS",
                "description": "Description",
                "Validity": null,
                "order": 0,
                "services": [
                    {
                        "id": 100,
                        "financeable": true,
                        "benefit": {
                            "id": 235,
                            "name": "ZSX",
                            "Priced": null
                        },
                        "Execution": null,
                        "serviceId": 112,
                        "label": "Colab"
                    }
                ],
                "selection": false
            },
            "creditTO": {
                "id": 0,
                "duration": 12,
                "Type": "standard",
                "Tax": 51,
                "total": 400,
                "promotion": false
            }
        }
    ],
        "D": [
        {
            "Price": 705,
            "category": 620,
            "service": {
                "id": 15,
                "name": "TRW",
                "description": "Description",
                "Validity": null,
                "order": 0,
                "services": [
                    {
                        "id": 100,
                        "financeable": true,
                        "benefit": {
                            "id": 235,
                            "name": "ZSX",
                            "Priced": null
                        },
                        "Execution": null,
                        "serviceId": 112,
                        "label": "Colab"
                    }
                ],
                "selection": false
            },
            "creditTO": {
                "id": 0,
                "duration": 18,
                "Type": "standard",
                "Tax": 67,
                "total": 245,
                "promotion": false
            }
        }
    ]
}

理想输出表格需包含顶层键（A/B/C/D）作为产品标识列，同时将嵌套的service、creditTO、benefit等字段展开为独立列，每行对应一个产品的完整信息。

原代码的问题在于未处理顶层的键值结构，且没有将嵌套数组中的单个对象正确提取。以下是基于pandas的正确实现步骤：

1. 遍历JSON的顶层键，提取每个键对应的对象数据，并添加产品标识列
2. 使用pd.json_normalize扁平化嵌套结构，支持多层嵌套展开
3. 合并所有处理后的数据集，导出为Excel或CSV

完整代码如下：

import pandas as pd
import json

# 解析JSON数据（如果response_data是字符串格式）
# response_data 是你的原始JSON字符串
# data = json.loads(response_data)

# 如果已经是字典格式，直接使用
data = {
    # 放入你的JSON字典内容
}

# 初始化空列表存储每个处理后的DataFrame
dfs = []

# 遍历顶层键（A/B/C/D）
for product_key, items in data.items():
    # 取出数组中的单个对象（每个产品对应一个对象）
    item = items[0]
    # 添加产品标识列
    item['Product'] = product_key
    # 扁平化嵌套结构，自动展开多层嵌套字段
    normalized_df = pd.json_normalize(item)
    dfs.append(normalized_df)

# 合并所有DataFrame
final_df = pd.concat(dfs, ignore_index=True)

# 导出到Excel
final_df.to_excel('output.xlsx', index=False)
# 或者导出到CSV
# final_df.to_csv('output.csv', index=False, encoding='utf-8')

代码说明 #

• 遍历顶层键时，每个键对应的值是包含单个对象的数组，直接取items[0]提取核心数据
• 添加Product列用于标识每个条目对应的顶层键（A/B/C/D）
• pd.json_normalize会自动将嵌套字段展开为点分隔的列名，例如service.name、creditTO.duration、service.services.0.benefit.name等，完美匹配嵌套结构的扁平化需求
• 合并后的DataFrame包含所有字段，可直接导出为Excel或CSV格式

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