如何动态将任意嵌套JSON转换为CSV或DataFrame且列表按行展开

以下代码可实现任意嵌套JSON结构转换为DataFrame，自动将列表按行展开，非列表公共字段复制到每一行，嵌套层级用.作为列名分隔符：

import pandas as pd
from pandas import json_normalize

def nested_json_to_df(data, sep='.'):
    # 扁平化非列表字段，同时识别所有列表类型字段
    def flatten_helper(x, parent_key=''):
        base_fields = {}
        list_fields = {}
        if isinstance(x, dict):
            for k, v in x.items():
                current_key = f"{parent_key}{sep}{k}" if parent_key else k
                if isinstance(v, list):
                    list_fields[current_key] = v
                elif isinstance(v, dict):
                    sub_base, sub_list = flatten_helper(v, current_key)
                    base_fields.update(sub_base)
                    list_fields.update(sub_list)
                else:
                    base_fields[current_key] = v
        return base_fields, list_fields

    # 处理根节点为列表的场景
    if isinstance(data, list):
        result_dfs = []
        for item in data:
            base, lists = flatten_helper(item)
            if not lists:
                result_dfs.append(pd.DataFrame([base]))
                continue
            # 取第一个识别到的列表按行展开，多列表场景可自行调整优先级逻辑
            list_key, list_val = next(iter(lists.items()))
            base_df = pd.DataFrame([base]*len(list_val))
            list_df = json_normalize(list_val, sep=sep).add_prefix(f"{list_key}{sep}")
            result_dfs.append(pd.concat([base_df.reset_index(drop=True), list_df.reset_index(drop=True)], axis=1))
        return pd.concat(result_dfs, ignore_index=True)
    else:
        base, lists = flatten_helper(data)
        if not lists:
            return pd.DataFrame([base])
        list_key, list_val = next(iter(lists.items()))
        base_df = pd.DataFrame([base]*len(list_val))
        list_df = json_normalize(list_val, sep=sep).add_prefix(f"{list_key}{sep}")
        return pd.concat([base_df.reset_index(drop=True), list_df.reset_index(drop=True)], axis=1)

使用示例 #

测试输入1 #

input1 = {"menu": {
    "header": "SVG Viewer",
    "items": [
        {"id": "Open"},
        {"id": "OpenNew", "label": "Open New"},
        None,
        {"id": "ZoomIn", "label": "Zoom In"},
        {"id": "ZoomOut", "label": "Zoom Out"},
        {"id": "OriginalView", "label": "Original View"},
        None,
        {"id": "Quality"},
        {"id": "Pause"},
        {"id": "Mute"},
        None,
        {"id": "Find", "label": "Find..."},
        {"id": "FindAgain", "label": "Find Again"},
        {"id": "Copy"},
        {"id": "CopyAgain", "label": "Copy Again"},
        {"id": "CopySVG", "label": "Copy SVG"},
        {"id": "ViewSVG", "label": "View SVG"},
        {"id": "ViewSource", "label": "View Source"},
        {"id": "SaveAs", "label": "Save As"},
        None,
        {"id": "Help"},
        {"id": "About", "label": "About Adobe CVG Viewer..."}
    ]
}}
df1 = nested_json_to_df(input1)
# 导出为CSV执行：df1.to_csv("output1.csv", index=False, encoding="utf-8-sig")

输出会保留menu.header公共列，menu.items的每个元素对应一行，每个元素的属性拆分为menu.items.id、menu.items.label列。

测试输入2 #

input2 = {"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}
df2 = nested_json_to_df(input2)

输出会保留menu.id、menu.value公共列，menu.popup.menuitem的每个元素对应一行，属性拆分为对应子列。

说明 #

• 空的列表元素会自动填充空值到对应列
• 支持根节点为列表的JSON结构转换
• 存在多个并列列表时，默认展开第一个识别到的列表，可根据业务需求调整列表选择逻辑
• 导出CSV可直接调用pandas自带的to_csv方法，编码建议用utf-8-sig避免中文乱码

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