如何在包含嵌套列表列表的 json 上使用 json_normalize

我正在用 Python 处理一个复杂的 JSON 字符串，并且在将数据放入 Pandas 数据帧时遇到问题。示例数据、当前输出和预期输出全部如下。我正在尝试使用 json_normalize，但它没有完全标准化，留下了几列包含 JSON 列表。我的最终目标是将其插入到 SQL DB 中，但是 json 字符串由于其长度而被截断，因此我无法使用我最初的计划使用 OPENJSON() 在 SQL 中执行转换。

我的 json_normalize 看起来像下面这样。使用 max_level=10 适用于我为此资源进行的所有其他 API 调用（除此特定表外）。我也尝试过按列列出它，但我的尝试失败了。

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

incomingString 看起来像下面的 json。这是虚拟数据，只是为了澄清。

{
    "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
                }
            ]
        }
    ]
}

当我运行代码时，我得到如下所示的输出。

描述

Any and all help is appreciated.

由于 JSON 的结构，无法使用 json_normalize 获得所需的精确输出。但是，我们可以通过几个步骤来实现想要的结果：

import pandas as pd
import json

incomingString = '''
{"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
            }
        ]
    }
]}
'''
incomingJson = json.loads(incomingString)

# 1. 为 "fields" 中的每个元素创建单独的数据帧。
dfs = []
for field in incomingJson['fields']:
    df = pd.DataFrame(field)
    df = df.explode('choices').explode('other_choices').reset_index(drop=True)
    dfs.append(df)

# 2. 合并创建的数据帧。
final_df = pd.concat(dfs, ignore_index=True)

# 3. 使用 "choices" 和 "other_choices" 中的字典创建新列。
final_df['choices.id'] = final_df['choices'].apply(lambda x: x.get('id') if isinstance(x, dict) else None)
final_df['choices.name'] = final_df['choices'].apply(lambda x: x.get('name') if isinstance(x, dict) else None)
final_df['other_choices.id'] = final_df['other_choices'].apply(lambda x: x.get('id') if isinstance(x, dict) else None)

# 4. 删除原始的 "choices" 和 "other_choices" 列。
final_df = final_df.drop(['choices', 'other_choices'], axis=1)

print(final_df)

这将输出以下数据帧：

       id description  choices.id choices.name  other_choices.id
0  12345    Location         1.0      Seattle               NaN
1  12345    Location         2.0    Nashville               NaN
2  67372        Name       101.0         John               NaN
3  67372        Name       102.0         Mary               NaN
4  39265   Department       143.0          IT            9473.0
5  39265   Department       226.0          HR            9473.0
6  39265   Department       394.0         ERG            9473.0

此数据帧现在具有需要的结构，可以更轻松地将其插入到 SQL 数据库中。

说明：

1. 我们遍历 "fields" 列表中的每个元素，并为每个元素创建一个单独的数据帧。
2. 我们使用 explode 函数将 "choices" 和 "other_choices" 列中的列表项扩展为单独的行。
3. 我们使用 concat 函数将所有创建的数据帧合并为一个。
4. 我们使用 apply 函数和 lambda 表达式从 "choices" 和 "other_choices" 列中的字典中提取 "id" 和 "name" 值，并将它们存储在新列中。
5. 最后，我们删除原始的 "choices" 和 "other_choices" 列。

这是一种将嵌套 JSON 数据转换为 Pandas 数据帧的更灵活的方法，可以根据需要调整它。