KeyError: "Key 'Record_Path' not found. If specifying a record_path, all elements of data should have the path."

Doug Sack 0
Viewed 16

I am trying to organize a json response from a URL into a panda dataframe but I am having issues getting at the nested data.

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

I am trying to create a table of all the driverIds, their Position and their lap time. However, whenever I use a record_path for example df = pd.json_normalize(data, record_path, meta) I get a Key Error. What am I missing?

The json data looks like this at the URL:

{
  "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"
                }
              ]
            }
          ]
        }
      ]
    }
  }
}
pandas json python
1 Answers

Try to construct dataframe without .json_normalize:

import requests
import pandas as pd


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

r = requests.get(url=url)

df = pd.DataFrame(
    r.json()["MRData"]["RaceTable"]["Races"][0]["Laps"][0]["Timings"]
)
print(df)

Prints:

              driverId position      time
0               alonso        1  1:34.494
1               vettel        2  1:35.274
2               webber        3  1:36.329
3             hamilton        4  1:36.991
4               petrov        5  1:38.084
5   michael_schumacher        6  1:38.633
6              rosberg        7  1:39.139
7                massa        8  1:39.979
8                buemi        9  1:40.611
9               button       10  1:40.998
10               perez       11  1:41.433
11         alguersuari       12  1:41.876
12           maldonado       13  1:42.255
13               resta       14  1:42.808
14              trulli       15  1:43.553
15          kovalainen       16  1:44.276
16            heidfeld       17  1:45.164
17               sutil       18  1:46.107
18              liuzzi       19  1:46.737
19         barrichello       20  1:47.077
20               glock       21  1:47.556
21         karthikeyan       22  1:48.183
22            ambrosio       23  1:48.573
23           kobayashi       24  1:57.590
Andrej Kesely0
Related