Python如何将复杂JSON/Dict转换为带层级列名的单行DataFrame

你可以通过自定义递归扁平化函数完成转换，适配任意深度的嵌套字典、数组结构，具体实现代码如下：

import pandas as pd

def flatten_json(nested_json, parent_key='', sep='.'):
    items = []
    for k, v in nested_json.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):
            # 处理数组，索引从1开始匹配示例命名规则
            for idx, item in enumerate(v, start=1):
                if isinstance(item, dict):
                    items.extend(flatten_json(item, f"{new_key}{idx}", sep=sep).items())
                else:
                    items.append((f"{new_key}{idx}", item))
        else:
            # 基础类型字段直接添加
            items.append((new_key, v))
    return dict(items)

# 示例输入JSON
input_json = {
   "fname":"Mickey",
   "lname":"Mouse",
   "Id":"12345",
   "education":[
      {
         "school":"acme University",
         "degree":"Doctor of Philosophy (PhD)"
      },
      {
         "school":"super university",
         "degree":"Master of Science (MS)"
      }
   ],
   "location":"New York, NY",
   "experience":[
      {
         "description":"I Work Hard",
         "title":"Manager",
         "work":"Hogwarts"
      },
      {
         "description":"I work Harder.",
         "title":"Senior Manager",
         "work":"hundred acre wood"
      }
   ],
   "startTime":4352,
   "endTime":234234
}

# 扁平化后生成单行DataFrame
flat_dict = flatten_json(input_json)
df = pd.DataFrame([flat_dict])

运行后输出的DataFrame完全匹配需求，会自动生成education.school1、education.degree1、education.school2、experience.title1等符合父子层级关系的列名。

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