如何将含嵌套数组的Pandas生成JSON转换为扁平化DataFrame

步骤说明 #

要将包含嵌套数组的DataFrame转换为目标扁平化结构，核心是拆分嵌套数组后展开字典字段，具体操作如下：

测试用JSON示例 #

先准备测试数据（保存为students.json）：

[
  {
    "student_id": 1,
    "name": "Alice",
    "created_at": "2023-01-01",
    "languages": [
      {"language_id": 101, "language_name": "English", "optin_at": "2023-01-02"},
      {"language_id": 102, "language_name": "Spanish", "optin_at": "2023-01-03"}
    ]
  },
  {
    "student_id": 2,
    "name": "Bob",
    "created_at": "2023-02-01",
    "languages": [
      {"language_id": 103, "language_name": "French", "optin_at": "2023-02-02"}
    ]
  }
]

完整代码实现 #

import pandas as pd
from pandas import json_normalize

# 加载JSON数据
df = pd.read_json('students.json')

# 拆分嵌套的languages数组，每行对应一个语言条目
df_exploded = df.explode('languages', ignore_index=True)

# 展开languages字段中的字典为独立列
languages_flat = json_normalize(df_exploded['languages'])

# 合并必要字段并整理顺序
flat_df = pd.concat([df_exploded[['student_id']], languages_flat], axis=1)
flat_df = flat_df[['student_id', 'language_id', 'language_name', 'optin_at']]

# 查看结果
print(flat_df)

输出结果 #

注意事项 #

• 如果JSON文件是每行一个JSON对象的格式，加载时需要添加lines=True参数：pd.read_json('students.json', lines=True)
• 若languages列存在空数组或None值，explode后会生成空行，可通过dropna(subset=['languages'])提前过滤

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