如何使用Python将嵌套JSON转换为带分组子行的CSV文件

需求说明 #

将多层嵌套结构的JSON转换为CSV格式，嵌套的列表/字典生成对应子行，相同层级的重复字段位置保留空字符串。

示例JSON数据 #

{
    "id": "1",
    "name": "HIGHLEVEL",
    "description": "HLD",
    "item": {
        "id": "11",
        "description": "description"
    },
    "packages": [{
            "id": "1",
            "label": "Package 1",
            "products": [{
                    "id": "1",
                    "price": 5
                }, {
                    "id": "2",
                    "price": 3
                }
            ]
        }, {
            "id": "2",
            "label": "Package 3",
            "products": [{
                    "id": "1",
                    "price": 5
                }, {
                    "id": "2",
                    "price": 3
                }
            ]
        }
    ]
}

原处理方案问题 #

直接使用pandas.json_normalize默认参数仅能展开一层字典，无法展开嵌套数组生成子行，数组字段会保留为JSON字符串格式。
原测试代码：

import pandas as pd
df = pd.json_normalize(data)

预期输出格式 #

"id","name","description","item__id","item__description","packages__id","packages__label","packages__products__id","packages__products__price"
"1","HIGHLEVEL","HLD","11","description","1","Package 1","1","5"
"","","","","","","","2","3"
"","","","","","2","Package 3","1","5"
"","","","","","","","2","3"

可用Python脚本 #

import pandas as pd

def nested_json_to_csv(data, output_path):
    # 展开所有嵌套层级生成全量明细行
    df = pd.json_normalize(
        data,
        record_path=['packages', 'products'],
        meta=[
            'id', 'name', 'description',
            ['item', 'id'],
            ['item', 'description'],
            ['packages', 'id'],
            ['packages', 'label']
        ],
        sep='__'
    )
    
    # 调整列顺序与命名匹配预期格式
    df = df.rename(columns={
        'id': 'packages__products__id',
        'price': 'packages__products__price'
    })
    df = df[[
        'id', 'name', 'description',
        'item__id', 'item__description',
        'packages__id', 'packages__label',
        'packages__products__id', 'packages__products__price'
    ]]
    
    # 顶层字段仅保留第一行值，其余行置空
    top_cols = ['id', 'name', 'description', 'item__id', 'item__description']
    df.loc[1:, top_cols] = ''
    
    # 包层级字段每个包仅保留第一行值，同包其余行置空
    pkg_cols = ['packages__id', 'packages__label']
    df.loc[df.groupby('packages__id').cumcount() > 0, pkg_cols] = ''
    
    # 导出CSV，所有字段自动加双引号
    df.to_csv(output_path, index=False, quoting=1, encoding='utf-8')

# 调用示例
if __name__ == '__main__':
    # 此处替换为你的JSON数据
    data = {
        "id": "1",
        "name": "HIGHLEVEL",
        "description": "HLD",
        "item": {
            "id": "11",
            "description": "description"
        },
        "packages": [{
                "id": "1",
                "label": "Package 1",
                "products": [{
                        "id": "1",
                        "price": 5
                    }, {
                        "id": "2",
                        "price": 3
                    }
                ]
            }, {
                "id": "2",
                "label": "Package 3",
                "products": [{
                        "id": "1",
                        "price": 5
                    }, {
                        "id": "2",
                        "price": 3
                    }
                ]
            }
        ]
    }
    nested_json_to_csv(data, 'output.csv')

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