嵌套JSON转Pandas DataFrame：如何生成指定结构表格？

问题背景 #

现有如下嵌套JSON数据：

{
    "count": 5175,
    "text_indicator_to_statistics": [
        {
            "text_indicator": {
                "type": "URL",
                "value": "some text"
            },
            "statistics": [
                {
                    "date": "2023-04-15",
                    "field": "CLICKS",
                    "value": 7.0
                },
                {
                    "date": "2023-04-15",
                    "field": "POSITION",
                    "value": 4.0
                },
                {
                    "date": "2023-04-15",
                    "field": "IMPRESSIONS",
                    "value": 8595.0
                },
                {
                    "date": "2023-04-15",
                    "field": "CTR",
                    "value": 0.0
                }
            ]
        }
    ]
}

需要转换为如下结构的DataFrame：

其中key取自text_indicator.value，CLICKS、POSITION等列对应statistics中field的取值。之前尝试用pd.json_normalize分别提取两层数据，但得到的两个DataFrame无法直接合并，需要更合理的处理方式。

解决方案 #

可以通过遍历每个指标条目，将对应的统计数据转成宽表后，再与key和日期合并，具体代码如下：

import pandas as pd

# 假设r是请求响应对象，先获取JSON数据
data = r.json()

# 初始化空列表存储处理后的结果
processed_data = []

# 遍历每个text_indicator条目
for item in data['text_indicator_to_statistics']:
    key = item['text_indicator']['value']
    # 将statistics转为DataFrame
    stats_df = pd.DataFrame(item['statistics'])
    # 把field转成列，value作为对应值，同时保留date
    pivoted = stats_df.pivot(index='date', columns='field', values='value').reset_index()
    # 添加key列
    pivoted['key'] = key
    # 调整列顺序，把key放在最前面
    pivoted = pivoted[['key', 'date'] + [col for col in pivoted.columns if col not in ['key', 'date']]]
    processed_data.append(pivoted)

# 合并所有处理后的结果
final_df = pd.concat(processed_data, ignore_index=True)

# 如果只需要指定列（比如CLICKS、POSITION），可以筛选
final_df = final_df[['key', 'date', 'CLICKS', 'POSITION']]

print(final_df)

代码说明 #

1. 遍历条目：逐个处理text_indicator_to_statistics中的每个对象，提取唯一标识key。
2. 统计数据转宽表：用pivot方法将statistics中的长格式数据（每个field一行）转为宽格式（每个field一列），同时保留日期字段。
3. 合并key与调整列顺序：给转好的宽表添加key列，并调整列顺序符合需求。
4. 合并结果：将所有处理后的小DataFrame合并成最终结果，按需筛选指定列即可。

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