Creating a Dataframe from nested JSON API output

Question

I am using the Qualtrics API to pull some data for work. The results I have been receiving comes back in a JSON format and I would like to transform the data into a dataframe. I'm working inside a jupyter notebook within Alteryx. I plan to export the dataframe in Alteryx to do work elsewhere..all I need to do is get it into shape. I receive the same response as the example I have posted below from Qualtrics website. Does anyone know how I can take the fields under the nested "elements" section and create a dataframe? I would like to make a dataframe of the contact fields I receive back.

I have tried the following:

jdata = json.loads(response.text)
df = pd.DataFrame(jdata)
print(df)

But I am getting a dataframe of the entire json response.

Example Response:

{
"meta": {
    "httpStatus": "200 - OK",
    "requestId": "7de14d38-f5ed-49d0-9ff0-773e12b896b8"
},
"result": {
    "elements": [
        {
            "contactId": "CID_123456",
            "email": "[email protected]",
            "extRef": "1234567",
            "firstName": "James",
            "language": "en",
            "lastName": "Smith",
            "phone": "8005552000",
            "unsubscribed": false
        },
        {
            "contactId": "CID_3456789",
            "email": "[email protected]",
            "extRef": "12345678",
            "firstName": "John",
            "language": "en",
            "lastName": "Smith",
            "phone": "8005551212",
            "unsubscribed": true
        }
    ],
    "nextPage": null
}

}

You'd need to select the $.result field out of the initial resonse JSON if you want the nested data — OneCricketeer
– OneCricketeer, Commented Dec 9, 2020 at 3:11

Ferris · Accepted Answer · 2020-12-09 05:16:10Z

1

jdata = json.loads(response.text)
df = pd.json_normalize(jdata, record_path=['result', 'elements'])

In fact, if jdata is a list of dict, this method is also available.

answered Dec 9, 2020 at 5:16

Ferris

5,6611 gold badge18 silver badges27 bronze badges

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

Comments

watfe · Accepted Answer · 2020-12-09 04:05:10Z

0

try

jdata = json.loads(response.text)
elements= jdata['result']['elements']
df = pd.DataFrame(elements)
print(df)

answered Dec 9, 2020 at 4:05

watfe

1073 bronze badges

Collectives™ on Stack Overflow

Creating a Dataframe from nested JSON API output

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related