使用pd.json_normalize处理嵌套JSON转DataFrame遇KeyError问题求助

尝试将URL返回的JSON响应转换为Pandas DataFrame时，在提取嵌套数据环节出错。

现有代码 #

import requests
import json
import numpy as np
from pandas import json_normalize 

series = 'f1'
season = 2022
ssnround = '1'
laps = 3
url = "http://ergast.com/api/f1/2011/5/laps/1.json"
record_path = ['Races']
meta = ['driverId', 'position', 'time']

r = requests.get(url = url)

data = json.loads(r.content)

df = pd.json_normalize(data)
df

目标是生成包含所有driverId、车手位置（position）和单圈用时（time）的表格，但调用df = pd.json_normalize(data, record_path, meta)时触发KeyError：Key 'Record_Path' not found. If specifying a record_path, all elements of data should have the path.，问题出在哪里？

返回的JSON结构 #

{
  "MRData": {
    "xmlns": "http://ergast.com/mrd/1.5",
    "series": "f1",
    "url": "http://ergast.com/api/f1/2011/5/laps/1.json",
    "limit": "30",
    "offset": "0",
    "total": "24",
    "RaceTable": {
      "season": "2011",
      "round": "5",
      "Races": [
        {
          "season": "2011",
          "round": "5",
          "url": "http://en.wikipedia.org/wiki/2011_Spanish_Grand_Prix",
          "raceName": "Spanish Grand Prix",
          "Circuit": {
            "circuitId": "catalunya",
            "url": "http://en.wikipedia.org/wiki/Circuit_de_Barcelona-Catalunya",
            "circuitName": "Circuit de Barcelona-Catalunya",
            "Location": {
              "lat": "41.57",
              "long": "2.26111",
              "locality": "Montmeló",
              "country": "Spain"
            }
          },
          "date": "2011-05-22",
          "time": "12:00:00Z",
          "Laps": [
            {
              "number": "1",
              "Timings": [
                {
                  "driverId": "alonso",
                  "position": "1",
                  "time": "1:34.494"
                },
                {"driverId": "vettel","position": "2","time": "1:35.274"},
                {"driverId": "webber","position": "3","time": "1:36.329"},
                {"driverId": "hamilton","position": "4","time": "1:36.991"},
                {"driverId": "petrov","position": "5","time": "1:38.084"},
                {"driverId": "michael_schumacher","position": "6","time": "1:38.633"},
                {"driverId": "rosberg","position": "7","time": "1:39.139"},
                {"driverId": "massa","position": "8","time": "1:39.979"},
                {"driverId": "buemi","position": "9","time": "1:40.611"},
                {"driverId": "button","position": "10","time": "1:40.998"},
                {"driverId": "perez","position": "11","time": "1:41.433"},
                {"driverId": "alguersuari","position": "12","time": "1:41.876"},
                {"driverId": "maldonado","position": "13","time": "1:42.255"},
                {"driverId": "resta","position": "14","time": "1:42.808"},
                {"driverId": "trulli","position": "15","time": "1:43.553"},
                {"driverId": "kovalainen","position": "16","time": "1:44.276"},
                {"driverId": "heidfeld","position": "17","time": "1:45.164"},
                {"driverId": "sutil","position": "18","time": "1:46.107"},
                {"driverId": "liuzzi","position": "19","time": "1:46.737"},
                {"driverId": "barrichello","position": "20","time": "1:47.077"},
                {"driverId": "glock","position": "21","time": "1:47.556"},
                {"driverId": "karthikeyan","position": "22","time": "1:48.183"},
                {"driverId": "ambrosio","position": "23","time": "1:48.573"},
                {"driverId": "kobayashi","position": "24","time": "1:57.590"}
              ]
            }
          ]
        }
      ]
    }
  }
}

问题根源 #

1. 路径定位错误：传入json_normalize的data是完整JSON对象，但Races字段不在顶层，而是嵌套在MRData -> RaceTable下；同时你要提取的目标字段（driverId/position/time）实际在Races -> Laps -> Timings数组中，并非Races的直接子元素。
2. 参数用法混淆：meta参数用于保留父层级的额外字段，而非指定要提取的目标字段，你完全搞反了这个参数的用途。

修正代码 #

基础版本（仅提取目标字段）

import requests
import pandas as pd
from pandas import json_normalize

url = "http://ergast.com/api/f1/2011/5/laps/1.json"

r = requests.get(url=url)
data = r.json()  # 直接用r.json()简化操作，无需手动解析

# 指定完整嵌套路径到Timings数组
df = json_normalize(
    data,
    record_path=['MRData', 'RaceTable', 'Races', 'Laps', 'Timings']
)

print(df)

运行后会得到包含driverId、position、time三列的DataFrame，每条车手单圈数据对应一行。

进阶版本（保留父级字段）

如果需要同时显示赛季、轮次、圈数等信息，可通过meta参数添加父层级字段，并自定义列名：

df = json_normalize(
    data,
    record_path=['MRData', 'RaceTable', 'Races', 'Laps', 'Timings'],
    meta=[
        ['MRData', 'RaceTable', 'season'],
        ['MRData', 'RaceTable', 'round'],
        ['MRData', 'RaceTable', 'Races', 'Laps', 'number']
    ]
)

# 重命名列名提升可读性
df.rename(columns={
    'MRData.RaceTable.season': '赛季',
    'MRData.RaceTable.round': '轮次',
    'MRData.RaceTable.Races.Laps.number': '圈数'
}, inplace=True)

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