如何将含重复字段的API返回JSON解析为指定结构的Pandas DataFrame

问题背景 #

我正在解析API返回的JSON数据，需要将其转换为指定结构的Pandas DataFrame，但当前解析方式无法得到预期结果。

API返回的JSON结构示例 #

{
  "result": {
    "data": [],
    "totals": [
      0
    ]
  },
  "timestamp": "2021-11-25 15:19:21"
}

实际获取的response数据 #

{
   "result":{
      "data":[
         {
            "dimensions":[
               {
                  "id":"2023-01-10",
                  "name":""
               },
               {
                  "id":"123",
                  "name":"good3"
               }
            ],
            "metrics":[
               10,
               20,
               30,
               40
            ]
         },
         {
            "dimensions":[
               {
                  "id":"2023-01-10",
                  "name":""
               },
               {
                  "id":"234",
                  "name":"good2"
               }
            ],
            "metrics":[
               1,
               2,
               3,
               4
            ]
         }
      ],
      "totals":[
         11,
         22,
         33,
         44
      ]
   },
   "timestamp":"2023-02-07 12:58:40"
}

当前代码及结果 #

我不需要timestamp和totals字段，仅提取data部分，执行以下代码：

...
response_ = requests.post(url, headers=head, data=body)
datas = response_.json()
datas_ = datas['result']['data']
df1 = pd.json_normalize(datas_)

得到的DataFrame：

期望的DataFrame结构 #

尝试df1 = pd.json_normalize(datas_, 'dimensions')时，所有id和name会被解析到同一列中，需要分步解决。

分步解决方法 #

步骤1：提取并拆分dimensions字段 #

首先将每个dimensions列表中的两个字典分别展开为独立列，通过apply提取指定位置的字典，再用pd.json_normalize展开并重命名：

# 提取data部分
datas_ = datas['result']['data']
df = pd.json_normalize(datas_)

# 拆分dimensions第一个元素为id_和name_
dim0 = pd.json_normalize(df['dimensions'].apply(lambda x: x[0])).rename(columns={'id': 'id_', 'name': 'name_'})
# 拆分dimensions第二个元素为id和name
dim1 = pd.json_normalize(df['dimensions'].apply(lambda x: x[1]))

步骤2：拆分metrics字段为独立列 #

将metrics列表中的每个元素拆分为单独的metric1到metric4列：

metrics_df = pd.DataFrame(df['metrics'].tolist(), columns=['metric1', 'metric2', 'metric3', 'metric4'])

步骤3：合并所有DataFrame #

将拆分后的维度列和指标列按列合并，得到最终结构：

final_df = pd.concat([dim0, dim1, metrics_df], axis=1)

完整代码 #

import pandas as pd
import requests

# 请求API获取数据
response_ = requests.post(url, headers=head, data=body)
datas = response_.json()
datas_ = datas['result']['data']

# 初始规范化
df = pd.json_normalize(datas_)

# 处理dimensions字段
dim0 = pd.json_normalize(df['dimensions'].apply(lambda x: x[0])).rename(columns={'id': 'id_', 'name': 'name_'})
dim1 = pd.json_normalize(df['dimensions'].apply(lambda x: x[1]))

# 处理metrics字段
metrics_df = pd.DataFrame(df['metrics'].tolist(), columns=['metric1', 'metric2', 'metric3', 'metric4'])

# 合并得到最终DataFrame
final_df = pd.concat([dim0, dim1, metrics_df], axis=1)

执行后即可得到符合预期结构的DataFrame。

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