如何用json_normalize处理含嵌套列表的JSON生成Pandas DataFrame

问题 #

处理复杂JSON字符串转换为Pandas DataFrame时，使用pd.json_normalize无法完全归一化，choices和other_choices列仍保留JSON列表格式。由于JSON字符串过长无法通过SQL的OPENJSON转换，需在Python中将数据完全扁平化为包含choices.id、choices.name、other_choices.id等字段的DataFrame，以便插入SQL数据库。

现有代码 #

import json
import pandas as pd

incomingJson = json.loads(incomingString)
incomingDataFrame = pd.json_normalize(incomingJson, 'fields', max_level=10)

示例数据 #

{
    "fields": [
        {
            "id": 12345,
            "description": "Location",
            "choices": [
                {
                    "id": 1,
                    "name": "Seattle"
                },
                {
                    "id": 2,
                    "name": "Nashville"
                }
            ],
            "other_choices": []
        },
        {
            "id": 67372,
            "description": "Name",
            "choices": [
                {
                    "id": 101,
                    "name": "John"
                },
                {
                    "id": 102,
                    "name": "Mary"
                }
            ],
            "other_choices": []
        },
        {
            "id": 39265,
            "description": "Department",
            "choices": [
                {
                    "id": 143,
                    "name": "IT"
                },
                {
                    "id": 226,
                    "name": "HR"
                },
                {
                    "id": 394,
                    "name": "ERG"
                }
            ],
            "other_choices": [
                {
                    "id": 9473
                }
            ]
        }
    ]
}

当前输出问题 #

DataFrame中choices和other_choices列仍为JSON列表，未展开为扁平字段。

期望输出 #

生成完全扁平的DataFrame，包含id、description、choices.id、choices.name、other_choices.id等字段，每个choices和other_choices条目对应一行数据。

解决方案 #

要实现完全扁平的DataFrame，需先将嵌套列表字段拆分展开，再对展开后的嵌套JSON对象进行归一化，最后合并所有字段。

完整代码 #

import json
import pandas as pd

# 加载JSON数据
incomingString = '''[你的JSON字符串内容]'''
incomingJson = json.loads(incomingString)

# 1. 初始归一化，提取顶层字段
df = pd.json_normalize(incomingJson, 'fields')

# 2. 处理choices列：展开列表并归一化嵌套内容
df_exploded_choices = df.explode('choices', ignore_index=True)
choices_normalized = pd.json_normalize(df_exploded_choices['choices']).add_prefix('choices.')
df_choices = pd.concat([df_exploded_choices.drop('choices', axis=1), choices_normalized], axis=1)

# 3. 处理other_choices列：同理展开并归一化
df_exploded_other = df_choices.explode('other_choices', ignore_index=True)
other_normalized = pd.json_normalize(df_exploded_other['other_choices']).add_prefix('other_choices.')
final_df = pd.concat([df_exploded_other.drop('other_choices', axis=1), other_normalized], axis=1)

# 可选：替换NaN为默认值（如空字符串或0）
# final_df = final_df.fillna({'other_choices.id': 0})

print(final_df)

最终输出 #

关键说明 #

• explode方法将嵌套列表拆分为多行，确保每个子条目对应独立行数据
• json_normalize配合add_prefix处理嵌套JSON，避免字段名冲突
• 空列表展开后生成的NaN可根据业务需求用fillna替换为默认值

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