CODEWITHSUNDEEP
pythonjsonpandasdataframe
Anthony_A
Anthony_A

Reputation: 11

Creating a Dataframe from nested JSON API output

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
}

}

Upvotes: 1

Views: 184

Answers (2)

Ferris
Ferris

Reputation: 5601

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.

Upvotes: 1

watfe
watfe

Reputation: 107

try

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

Upvotes: 0

Related Questions