如何使用json_normalize并转置轴，实现键为列值为内容

问题场景 #

处理API返回的JSON数据时，使用pd.json_normalize得到的是每行对应一个键值对的长表（包含key、label、value列），需要将key作为列名，value作为对应列的值，转换成宽表格式。

原始代码及输出 #

原始代码：

import pandas as pd
my_json = [
    {
        "total": "null",
        "items": [
            {
                "key": "time",
                "label": "Time",
                "value": "2022-12-13T23:59:59.939-07:00"
            },
            {
                "key": "agentNotes",
                "label": "Agent Notes",
                "value": "null"
            },
            {
                "key": "blindTransferToAgent",
                "label": "Blind Transfer To Agent",
                "value": "0"
            }]},
  {"total": "null",
        "items": [
            {
                "key": "time",
                "label": "Time",
                "value": "2022-12-13T23:59:59.939-07:00"
            },
            {
                "key": "agentNotes",
                "label": "Agent Notes",
                "value": "null"
            },
            {
                "key": "blindTransferToAgent",
                "label": "Blind Transfer To Agent",
                "value": "0"
            }
        ]}]
df = pd.json_normalize(my_json, ["items"])
print(df)

当前输出（长表）：

key  ...                          value
0             time  ...  2022-12-13T23:59:59.939-07:00
1       agentNotes  ...                           null
2  blindTransferToAgent  ...                              0
[3 rows x 3 columns]

期望输出（宽表）：

time agentNotes blindTransferToAgent
0  2022-12-13T23:59:59.939-07:00       null                    0
1  2022-12-13T23:59:59.939-07:00       null                    0

解决方案 #

直接使用json_normalize无法直接生成宽表，需先将每个items列表转换为以key为键、value为值的字典，再生成DataFrame：

import pandas as pd

my_json = [
    {
        "total": "null",
        "items": [
            {"key": "time", "label": "Time", "value": "2022-12-13T23:59:59.939-07:00"},
            {"key": "agentNotes", "label": "Agent Notes", "value": "null"},
            {"key": "blindTransferToAgent", "label": "Blind Transfer To Agent", "value": "0"}
        ]
    },
    {
        "total": "null",
        "items": [
            {"key": "time", "label": "Time", "value": "2022-12-13T23:59:59.939-07:00"},
            {"key": "agentNotes", "label": "Agent Notes", "value": "null"},
            {"key": "blindTransferToAgent", "label": "Blind Transfer To Agent", "value": "0"}
        ]
    }
]

# 遍历每个JSON对象，将items转换为字典
processed_data = []
for obj in my_json:
    item_dict = {item["key"]: item["value"] for item in obj["items"]}
    # 如需保留total字段，可添加以下行
    # item_dict["total"] = obj["total"]
    processed_data.append(item_dict)

# 生成宽表DataFrame
df = pd.DataFrame(processed_data)
print(df)

输出结果 #

time agentNotes blindTransferToAgent
0  2022-12-13T23:59:59.939-07:00       null                    0
1  2022-12-13T23:59:59.939-07:00       null                    0

备选方法：透视表转换 #

如果已经通过json_normalize得到了长表，也可以用透视表转换：

import pandas as pd

my_json = [
    # 同原始数据
]

df = pd.json_normalize(my_json, ["items"], meta=["total"])
# 为每个原始JSON对象添加分组标识
df["group"] = df.index // len(my_json[0]["items"])
# 透视生成宽表
wide_df = df.pivot(index="group", columns="key", values="value").reset_index(drop=True)
# 如需保留total字段，可合并数据
# wide_df["total"] = df["total"].iloc[::len(my_json[0]["items"])].values
print(wide_df)

该方法适合已生成长表的场景，但直接处理数据结构的方式效率更高。

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