Python Flatten Multiply Nested Dictionary JSON with Pandas

Question

I am working with a JSON response that is formatted like a many-nested dictionary below:

{u'addresses': [],
 u'application_ids': [20855193],
 u'applications': [{u'answers': [{u'answer': u'Indeed ',
                                  u'question': u'How did you hear?'}],
                    u'applied_at': u'2015-10-29T22:19:04.925Z',
                    u'candidate_id': 9999999,
                    u'credited_to': None,
                    u'current_stage': {u'id': 9999999,
                                       u'name': u'Application Review'},
                    u'id': 9999999,
                    u'jobs': [{u'id': 9999999,u'name': u'ENGINEER'}],
                    u'last_activity_at': u'2015-10-29T22:19:04.767Z',
                    u'prospect': False,
                    u'rejected_at': None,
                    u'rejection_details': None,
                    u'rejection_reason': None,
                    u'source': {u'id': 7, u'public_name': u'Indeed'},
                    u'status': u'active'}],
 u'attachments': [{u'filename': u'Jason_Bourne.pdf',
                   u'type': u'resume',
                   u'url': u'https://resumeURL'}],
 u'company': None,
 u'coordinator': {u'employee_id': None,
                  u'id': 9999999,
                  u'name': u'Batman_Robin'},
 u'email_addresses': [{u'type': u'personal',
                       u'value': u'[email protected]'}],
 u'first_name': u'Jason',
 u'id': 9999999,
 u'last_activity': u'2015-10-29T22:19:04.767Z',
 u'last_name': u'Bourne',
 u'website_addresses': []}

I am trying to flatten the JSON into a table and have found the following example on the pandas documentation:

http://pandas.pydata.org/pandas-docs/version/0.17.0/generated/pandas.io.json.json_normalize.html

From what I understand, the "record_path" parameter specifies the path of the lowest-level record you are interested in. The "record_path" parameter can only be a string, or list of strings. But, to call the 'answers' records in my data above, I have to specify strings and indexes as follows;

answer = data['applications'][0]['answers']['answer']
question = data['applications'][0]['answers']['question']

How can I enter the record paths above as a parameter to the json_normalize function?

Thanks!

jezrael · Accepted Answer · 2016-12-20 09:04:03Z

3

I think you can use as record_path nested list:

from pandas.io.json import json_normalize    
df = json_normalize(d, ['applications', ['answers']])
print (df)
    answer           question
0  Indeed   How did you hear?

answered Dec 20, 2016 at 9:04

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrew Neeld Over a year ago

Now I am trying to do the same for the 'name" under "current_stage'. when I type df = json_normalize(d, ['applications', ['current_stage','name']]) I get each letter of "Application Review" in its own row?

jezrael Over a year ago

You can use df = json_normalize(d['applications'][0]['current_stage']), I try many ways and unfortunately without success. main problem is [] are missing in {u'id': 9999999, u'name': u'Application Review'} like [{u'id': 9999999, u'name': u'Application Review'}]

Collectives™ on Stack Overflow

Python Flatten Multiply Nested Dictionary JSON with Pandas

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related