Transforming nested JSON coming from Api into dataframe with Python

Question

This is Json coming from Api put into list:

final = [{
"email":"[email protected]"
"createdAt":"2019-10-14T15:22:35.188-05:00" 
"statistics":{
      "clicked":[
         0:{
            "campaignId":415
            "links":[
               0:{
                  "count":1
                  "eventTime":"2019-10-17T17:29:16.551-05:00"
                 }
            ]
         }
         1:{...}
         2:{...}
         3:{...}
      ]
...
}]

I want to make a Dataframe with this, the desired output is:

count campaignId   eventTime                    email                       createdAt    status
0   1        415  2019-10-17    [email protected]   2019-10-14T15:22:35.188-05:00   clicked
1   1        415  2020-06-15    [email protected]   2019-10-14T15:22:35.188-05:00   clicked
2   1        415  2020-06-15    [email protected]   2019-10-14T15:22:35.188-05:00   clicked
3   1        415  2020-06-15    [email protected]   2019-10-14T15:22:35.188-05:00   clicked
4   2        415  2020-10-14    [email protected]   2019-10-14T15:22:35.188-05:00   clicked
5   1        415  2020-10-14    [email protected]   2019-10-14T15:22:35.188-05:00   clicked

What I have tried is this:

import json
import pandas as pd

df3 = pd.json_normalize(final,['statistics','clicked','links'],['email','createdAt'],errors='ignore')
df3['status'] = 'clicked'

But I only getting this. I try to get campaignId in dataframe, any help would be appreciated.

 count                      eventTime                   email                       createdAt    status
0   1   2019-10-17T17:29:16.551-05:00   [email protected]   2019-10-14T15:22:35.188-05:00   clicked
1   1   2020-06-15T18:27:33.179-05:00   [email protected]   2019-10-14T15:22:35.188-05:00   clicked
2   1   2020-06-15T18:21:32.942-05:00   [email protected]   2019-10-14T15:22:35.188-05:00   clicked
3   1   2020-06-15T18:22:46.963-05:00   [email protected]   2019-10-14T15:22:35.188-05:00   clicked
4   2   2020-10-14T18:23:18.949-05:00   [email protected]   2019-10-14T15:22:35.188-05:00   clicked
5   1   2020-10-14T18:25:42.373-05:00   [email protected]   2019-10-14T15:22:35.188-05:00   clicked

Juan Carlos Bellido Arenas · Accepted Answer · 2020-11-22 17:44:28Z

1

Solved!

df3 = pd.json_normalize(final,['statistics','clicked'],['email','createdAt'],errors='ignore')
df3 = df3.explode('links')
df3 = pd.concat([df3 ,df3['links'].apply(pd.Series)],axis=1)

edited Nov 22, 2020 at 17:44

answered Nov 22, 2020 at 17:04

Juan Carlos Bellido Arenas

1171 silver badge12 bronze badges

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

Comments

trigonom · Accepted Answer · 2020-11-21 20:41:13Z

0

you want to change eventTime to date?

import datetime as dt
df3['eventTime'] =  pd.to_datetime(df['eventTime'])
df['eventTime'] = df['eventTime'].dt.date

answered Nov 21, 2020 at 20:41

trigonom

5284 silver badges9 bronze badges

1 Comment

Juan Carlos Bellido Arenas Over a year ago

So sorry, what I try to get is campaignId in dataframe

Collectives™ on Stack Overflow

Transforming nested JSON coming from Api into dataframe with Python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related