How to read nested objects in a json file as dataframe?

Question

I have a .json file with content:

{"success":true,"code":"SUCCESS","data":{"from":1514745000000,"to":1522175400000,"transactionData":[{"name":"Recharge & bill payments","paymentInstruments":[{"type":"TOTAL","count":4200,"amount":1845307.4673655091}]},{"name":"Peer-to-peer payments","paymentInstruments":[{"type":"TOTAL","count":1871,"amount":1.2138655299749982E7}]},{"name":"Merchant payments","paymentInstruments":[{"type":"TOTAL","count":298,"amount":452507.168646613}]},{"name":"Financial Services","paymentInstruments":[{"type":"TOTAL","count":33,"amount":10601.419933464953}]},{"name":"Others","paymentInstruments":[{"type":"TOTAL","count":256,"amount":184689.8662902223}]}]},"responseTimestamp":1630501487199}

I want to convert it into a pandas data frame. But when I apply:

a = pd.read_json('/1.json')

I get output like this:

How can I get it in the correct pandas DataFrame format?

How exactly, in which format do you want the data to be, the dataframe is read in same format as the data is present, can you post the desired table please — Himanshu Poddar
– Himanshu Poddar, Commented Jul 16, 2022 at 8:14
I want data in of transactionData which is in form of list in a dataframe — Akash Kumar
– Akash Kumar, Commented Jul 16, 2022 at 8:17

Himanshu Poddar · Accepted Answer · 2022-07-16 09:53:49Z

1

Since you want to read data key in your dictionary. You can load the json as dictionary in memory and then use pandas to convert the same to a dataframe.

As discussed in comments : This is the expected output

Solution :

Here we are reading the json data first, then converting the data >>> transaction key to a pandas dataframe.

The above gives us a dataframe containing list values using pandas.DataFrame.explode for paymentInstruments. We need to unravel it such that we transform each element of a list-like to a row, replicating index values.

Once the values in lists are replicated we can convert the dictionries obtained to columns using pandas.Series.apply or pandas.json_normalize

Reading Data

import json

f = open(r'/1.json')
data = json.load(f)
                           
df = pd.DataFrame.from_dict(data['data']['transactionData'])

Pre-processing and generating Data

df = df.explode('paymentInstruments')
df = pd.concat([df, df.pop('paymentInstruments').apply(pd.Series)], axis = 1)

Output

This gives us the expected output

Alternatively :

Here is json_normalize solution

df = df.explode('paymentInstruments')
df = pd.concat([df, pd.json_normalize(df.pop('paymentInstruments'))], axis = 1)

This gives us :

edited Jul 16, 2022 at 9:53

answered Jul 16, 2022 at 8:40

Himanshu Poddar

7,90116 gold badges60 silver badges120 bronze badges

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

6 Comments

Akash Kumar Over a year ago

I think there is some error in syntax in 3rd line there are 2 closing brackets..

Himanshu Poddar Over a year ago

updated the answer, did it work for you?

Akash Kumar Over a year ago

I am getting : imgur.com/96z6WOs This error

Himanshu Poddar Over a year ago

sorry missed a line, please try this

Akash Kumar Over a year ago

Thanks, it worked. It was before you added the line.

|

Collectives™ on Stack Overflow

How to read nested objects in a json file as dataframe?

1 Answer 1

Solution :

Reading Data

Pre-processing and generating Data

Output

Alternatively :

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Solution :

Reading Data

Pre-processing and generating Data

Output

Alternatively :

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related