如何展开多层嵌套JSON并转换为DataFrame？

问题场景 #

需将包含多层嵌套字典与列表的JSON完全展平并转换为DataFrame，示例JSON结构如下：

{
 "Result": [
   {
     "OptionalColumns": {
       "optionalColumnName": "Joe Blogs"
     },
     "fieldOne": "some string",
     "fieldtwo": "some more string",
     "fieldthree": "even more string",
     "secondList": [
       {
         "secondListFieldOne": "value",
         "secondListFieldTwo": 0,
         "secondListFieldThree": true
       },
       {
         "secondListFieldOne": "value",
         "secondListFieldTwo": 0,
         "secondListFieldThree": true
       }
     ],
     "anotherField": "string value",
     "thirdList": [
       {
         "thirdListFieldOne": "string",
         "thirdListFieldTwo": "string"
       }
     ],
     "someNumberValue": 1
   },
   {
     "OptionalColumns": {
       "optionalColumnName": "Joe Blogs"
     },
     "fieldOne": "some string",
     "fieldtwo": "some more string",
     "fieldthree": "even more string",
     "secondList": [
       {
         "secondListFieldOne": "value",
         "secondListFieldTwo": 0,
         "secondListFieldThree": true
       },
       {
         "secondListFieldOne": "value",
         "secondListFieldTwo": 0,
         "secondListFieldThree": true
       }
     ],
     "anotherField": "string value",
     "thirdList": [
       {
         "thirdListFieldOne": "string",
         "thirdListFieldTwo": "string"
       }
      ],
      "someNumberValue": 1
    }
  ],
  "Message": null,
  "Errors": []
}

现有代码仅能处理单层/双层嵌套，无法完全展平多层列表嵌套：

import json
import pandas as pd

with open('data/my_file.json','r') as f:
    json_data = json.loads(f.read())

df_unnested_list = pd.json_normalize(json_data, 'Result')

参考的字典列表展开写法：

pd.json_normalize(data, "field", ["fieldTwo", "nestFieldOne"])

解决方案 #

方法1：分步展开指定嵌套列表 #

针对JSON中明确的多个嵌套列表字段（secondList、thirdList），可通过多次调用pd.json_normalize分别展开，同时保留关联的元数据字段：

1. 读取JSON数据

import json
import pandas as pd

with open('data/my_file.json','r') as f:
    json_data = json.load(f)

2. 展开secondList并保留元数据

df_second = pd.json_normalize(
    json_data['Result'],
    record_path='secondList',  # 指定要展开的列表字段
    meta=[
        'fieldOne', 'fieldtwo', 'fieldthree', 'anotherField', 'someNumberValue',
        ['OptionalColumns', 'optionalColumnName']  # 展平嵌套字典字段
    ]
)

3. 展开thirdList并保留元数据

df_third = pd.json_normalize(
    json_data['Result'],
    record_path='thirdList',
    meta=[
        'fieldOne', 'fieldtwo', 'fieldthree', 'anotherField', 'someNumberValue',
        ['OptionalColumns', 'optionalColumnName']
    ]
)

4. 合并结果（可选）
   如果需要将两个列表的展平结果整合，可添加标识列后合并：

df_second['source_list'] = 'secondList'
df_third['source_list'] = 'thirdList'
final_df = pd.concat([df_second, df_third], ignore_index=True)

方法2：通用递归展平（支持任意层级嵌套） #

如果JSON嵌套层级复杂且不固定，可使用递归函数遍历所有嵌套结构，自动转换为扁平键值对：

def flatten_json(obj, parent_key='', sep='_'):
    items = []
    for k, v in obj.items():
        new_key = f"{parent_key}{sep}{k}" if parent_key else k
        if isinstance(v, dict):
            items.extend(flatten_json(v, new_key, sep=sep).items())
        elif isinstance(v, list):
            for i, item in enumerate(v):
                items.extend(flatten_json(item, f"{new_key}_{i}", sep=sep).items())
        else:
            items.append((new_key, v))
    return dict(items)

# 处理每个Result条目并转换为DataFrame
flattened_data = [flatten_json(item) for item in json_data['Result']]
final_df = pd.DataFrame(flattened_data)

该方法会将所有嵌套路径转换为扁平列名（如secondList_0_secondListFieldOne），无需提前指定嵌套字段。

内容的提问来源于stack exchange，提问作者CartHack-3421