[FIXED] How to read .json file in dataframe?

Issue

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?

Solution

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.

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

import json

f = open(r'/1.json')
data = json.load(f)
                              
df = pd.DataFrame.from_dict(data['data']['transactionData'])
df = df.explode('paymentInstruments')
df = pd.concat([df, df['paymentInstruments'].apply(pd.Series)], axis = 1)

This gives us the expected output :

                       name  ...        amount
0  Recharge & bill payments  ...  1.845307e+06
1     Peer-to-peer payments  ...  1.213866e+07
2         Merchant payments  ...  4.525072e+05
3        Financial Services  ...  1.060142e+04
4                    Others  ...  1.846899e+05

[5 rows x 5 columns]

Answered By - Himanshu Poddar