How to convert to API response with multiple levels to dataframe in python

user_8888469 0
Viewed 258

I have response from API call as shown below

(data = json.loads(response.text)

{'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 convert this into proper dataframe. I tried below code its creating the columns are properly. I want each element of the API response as seperate column. For eg. 'position': {'lat': 47.464699, 'lon': 8.54917} should be created dedicated column for lat and Lon seperately.

df =pd.json_normalize(data)
df1= pd.DataFrame(df1)

Loop Locations and get values and convert to dataframe

location
SIN
ZRH

import requests

headers = {
    'accept': 'application/json',
    'Authorization': 'Api-Key',
    'Content-Type': 'application/json',
}

response = requests.get('https://risk.dev.logindex.com/il/risk/location/SIN', headers=headers)
pandas dataframe python-3.x python json
1 Answers

You can try json_normalize in loop with append for list of DataFrames and then join them by concat:

import requests

headers = {
    'accept': 'application/json',
    'Authorization': 'APIkey',
    'Content-Type': 'application/json',
}

dfs = []
locations = ['SIN','ZRH']
for loc in locations:
    response = requests.get(f'https://risk.dev.logindex.com/il/risk/location/{loc}', headers=headers)
    data = json.loads(response.text)
    df = pd.json_normalize(data, 'items', 'totalItems') 
    
    df1 = pd.concat([pd.DataFrame(x) for x in df.pop('raw.identifiers')], keys=df.index).add_prefix('raw.identifiers.')
    df2 = pd.concat([pd.DataFrame(x) for x in df.pop('raw.relationships')], keys=df.index).add_prefix('raw.relationships.')
    
    df3 = df.join(df1.join(df2).reset_index(level=1, drop=True))
    dfs.append(df3)

df = pd.concat(dfs, ignore_index=True)

print (df)
                     start                      end     source  \
0  2020-05-11T00:00:00.000  2020-05-18T00:00:00.000  geoeditor   
1  2020-11-22T00:00:00.000  2020-11-29T00:00:00.000  geoeditor   
2  2021-03-21T00:00:00.000  2021-03-31T00:00:00.000  geoeditor   

                                     title  \
0     9349576 - Wrecked/stranded (aground)   
1  9349576 - Collision (involving vessels)   
2                          test 25.03.2021   

                                                body severity category  \
0  Ran aground waters off Batam Indonesia, 11 May...     None     None   
1  Vessel was involved in collision with Tina I o...     None     None   
2                                   test description     None     None   

  relatedEntities relevant location.code  location.position.lat  \
0            None     None          None               1.350190   
1            None     None          None               1.350190   
2            None     None          None              47.464699   

   location.position.lon location.country_code  raw.active    raw.id  \
0             103.994003                  None        True  73459846   
1             103.994003                  None        True  73478162   
2               8.549170                  None        True  82482150   

     raw.layerId raw.locationType                                 raw.name  \
0  disruption_il            POINT     9349576 - Wrecked/stranded (aground)   
1  disruption_il            POINT  9349576 - Collision (involving vessels)   
2  disruption_il            POINT                             New Location   

            raw.changed raw.groupId                     raw.properties.title  \
0  2021-03-02T11:31:13Z        None     9349576 - Wrecked/stranded (aground)   
1  2021-03-04T21:01:12Z        None  9349576 - Collision (involving vessels)   
2  2021-03-25T20:49:51Z        None                          test 25.03.2021   

      raw.properties.source raw.properties.to_date raw.properties.relevant  \
0  disruption_vessel_issues             2020-05-18                    true   
1  disruption_vessel_issues             2020-11-29                    true   
2     disruption_news_event             2021-03-31                    true   

  raw.properties.from_date                         raw.properties.description  \
0               2020-05-11  Ran aground waters off Batam Indonesia, 11 May...   
1               2020-11-22  Vessel was involved in collision with Tina I o...   
2               2021-03-21                                   test description   

  totalItems raw.identifiers.name                 raw.identifiers.value  \
0          2               ref_id       disruption_vessel_issues:299346   
1          2               ref_id       disruption_vessel_issues:307022   
2          1               ref_id  9ded7375-bea2-4466-96a9-fd5c42f9a562   

  raw.relationships.referenceIdentifierValue raw.relationships.relationshipId  \
0            disruption_vessel_issues:299346                event_source_link   
1            disruption_vessel_issues:307022                event_source_link   
2                                        ZRH            event_impacts_airport   

  raw.relationships.referenceLayerId raw.relationships.referenceIdentifierName  
0           disruption_kn_disruption                                    ref_id  
1           disruption_kn_disruption                                    ref_id  
2                     airport_status                                 iata_code
user_29010020
Related