Convert JSON response from request into Pandas DataFrame

Question

I want to iterate over a dataframe by rows and use cell values to pull data from an api and assign the response to a new column. I got the response and everything works but i need to convert the json response into a dataframe column so i wrote a function similar to this

def get():
  response=requests.get(url + id)
  return response.json()

and applied this function to every row

d['res'] = d.apply(lambda row: get())

The problem that i got a json format in the column. how can i extract what i need from the reponse and put it in columns.

    {  'code': 'OK',
      'items': [{'start': '2021-03-21T00:00:00.000',
       'end': '2021-03-31T00:00:00.000',
       'location': {'code': None,
        'position': {'lat': 47.464699, 'lon': 8.54917},
        'country_code': None},
       'source': 'geoeditor',
       'title': 'test 25.03.2021',
       'body': 'test description',
       'severity': None,
       'category': None,
       'relatedEntities': None,
       'relevant': None,
       'raw': {'active': True,
        'id': 82482150,
        'layerId': 'disruption_il',
        'locationType': 'POINT',
        'name': 'New Location',
        'changed': '2021-03-25T20:49:51Z',
        'groupId': None,
        'identifiers': [{'name': 'ref_id',
          'value': '9ded7375-bea2-4466-96a9-fd5c42f9a562'}],
        'properties': {'title': 'test 25.03.2021',
         'source': 'disruption_news_event',
         'to_date': '2021-03-31',
         'relevant': 'true',
         'from_date': '2021-03-21',
         'description': 'test description'},
        'relationships': [{'referenceIdentifierValue': 'ZRH',
          'relationshipId': 'event_impacts_airport',
          'referenceLayerId': 'airport_status',
          'referenceIdentifierName': 'iata_code'}]}}],
     'totalItems': 1,

 'errors': []}

how can i extract data from items and put it on columns e.g:

col 1 = start
col 2 = end
col n = country_code etc...

Answer 1

Did you try json_normalize method?

There is an example of using:

import json
# load data using Python JSON module
with open('data/nested_mix.json','r') as f:
    data = json.loads(f.read())
    
# Normalizing data
df = pd.json_normalize(data, record_path =['students'])

I found that in this article: https://towardsdatascience.com/how-to-convert-json-into-a-pandas-dataframe-100b2ae1e0d8

Generally, there are some examples of transforming json data to pandas data frame, so maybe that article would be helpful for you.