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)
Advertisement
Answer
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