How unpack nested JSON with many columns from dictionary object into pandas?

Question

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'...

Have your code thrown any errors? If not, I think you could get more feedback if you tell what is your expected output and how your actual outcomes differ from it. — Daniel Ruiz
– Daniel Ruiz, Commented Mar 10, 2018 at 20:15

Reck · Accepted Answer · 2018-03-10 20:38:26Z

1

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

edited Mar 10, 2018 at 20:38

answered Mar 10, 2018 at 20:25

Reck

1,43612 silver badges20 bronze badges

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

2 Comments

RustyShackleford Over a year ago

Thank you for the comment. Your code got me much closer, but I am still seeing nested json fields. See edit for what your code got me.

Reck Over a year ago

Whats the type of contactid_staging (Assuming a dict). Whats the content? Could you try adding it the question.

Collectives™ on Stack Overflow

How unpack nested JSON with many columns from dictionary object into pandas?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related