RustyShackleford
RustyShackleford

Reputation: 3667

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

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

Answers (1)

Reck
Reck

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

Related Questions