Reputation: 3667
I have been struggling with consuming a nested JSON API response into a pandas dataframe.
The nested JSON has many columns, example;
{
"id": "196",
"status": "ACTIVE",
"fax": "318-978-7575",
"addresses": [
{
"id":"2",
"line1": "47 Shawmut Ave.",
"line2": "Suite 404",
"city": "Boston",
"address_type": "BUSINESS",
"state":"Massachusetts",
"state_code": "MA",
"country_code": "us",
"postal_code": "02158",
"sub_postal_code": ""
}
],
"notes": [],
"confirmed": false,
"lists": [
{
"id": "1",
"status": "ACTIVE"
}
],
"source": "API",
"email_addresses": [
{
"id":"1",
"status": "ACTIVE",
"confirm_status": "NO_CONFIRMATION_REQUIRED",
"opt_in_source": "ACTION_BY_VISITOR",
"opt_in_date": "2013-01-23T13:48:44.108Z",
"opt_out_date": "1969-12-31T19:00:00.000Z",
"email_address": "[email protected]"
}
],
"prefix_name": "Mr.",
"first_name": "Ronald",
"last_name": "Martone",
"job_title": "Systems Analyst 3",
"company_name": "System Optimzations",
"home_phone": "617-555-1212",
"work_phone": "318-978-8896",
"cell_phone": "448-989-3182",
"custom_fields": [],
"source_details": "Server Flow App"
}
None of the code I am trying is unpacking this into columns I can view.
Here is code I am trying:
contactid_api_call = requests.get('URL', headers = headers)
contactid_staging = json.loads(contactid_api_call.text)
from this point none of my code for trying to unpack this works. As an example I have been trying:
pd.DataFrame.from_dict(contactid_staging.items(),orient='index')
def dict_to_df(d):
d=pd.DataFrame(d.items())
d.set_index(0, inplace=True)
return d
edit:
this line of code got me:
pd.DataFrame(dict((k, pd.Series(v)) for k, v in contactid_staging.items()))
result:
meta results
pagination {} NaN
0 NaN {'id': '1005840546', 'status': 'ACTIVE', 'fax'...
Upvotes: 0
Views: 464
Reputation: 1436
This may help you,
pd.DataFrame(dict((k, pd.Series(v)) for k, v in contactid_staging.items()))
The result I got testing it locally (reading a json file and not by request):
addresses cell_phone \
0 {'state_code': 'MA', 'line2': 'Suite 404', 'st... 448-989-3182
company_name confirmed custom_fields \
0 System Optimzations False NaN
email_addresses fax first_name \
0 {'opt_in_source': 'ACTION_BY_VISITOR', 'email_... 318-978-7575 Ronald
home_phone id job_title last_name \
0 617-555-1212 196 Systems Analyst 3 Martone
lists notes prefix_name source source_details \
0 {'status': 'ACTIVE', 'id': '1'} NaN Mr. API Server Flow App
status work_phone
0 ACTIVE 318-978-8896
Upvotes: 1