convert json file to data frame in python

Question

I am new to json file handling. I have a below json file

{"loanAccount":{"openAccount":[{"accountNumber":"986985874","accountOpenDate":"2020-02-045-11:00","accountCode":"NA","relationship":"Main account","loanTerm":"120"}]}}

I want to convert this into dataframe. I am using the below code :

import pandas as pd
from pandas.io.json import json_normalize

data1 = pd.read_json (r'./DLResponse1.json',lines=True)
df = pd.DataFrame.from_dict(data1, orient='columns')

This is giving me the below output :

index   loanAccount
0       {'openAccount': [{'accountNumber': '986985874', 'accountOpenDate': '2020-02-045-11:00', 'accountCode': 'NA', 'relationship': 'Main account', 'loanTerm': '120'}]}}

However I want to extract in the below format :

loanAccount  openAccount  accountNumber  accountOpenDate    accountCode  relationship  loanTerm
                          986985874      2020-02-045-11:00   NA          Main account  120

there is a problem with your json file

Aven Desta
– Aven Desta

2020-04-01 19:04:22 +00:00
Commented Apr 1, 2020 at 19:04 — Aven Desta
– Aven Desta, Commented Apr 1, 2020 at 19:04

kederrac · Accepted Answer · 2020-04-01 21:14:18Z

2

you may use:

# s is your json, you can read from file
pd.DataFrame(s["loanAccount"]["openAccount"])

output:

if you want also to have the other json keys as columns you may use:

pd.DataFrame([{"loanAccount": '', "openAccount": '', **s["loanAccount"]["openAccount"][0]}])

edited Apr 1, 2020 at 21:14

answered Apr 1, 2020 at 19:03

kederrac

17.4k6 gold badges36 silver badges58 bronze badges

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

5 Comments

Aditya sharma Over a year ago

I tried your code but unfortunately I am not getting the expected output. Getting error KeyError: 'openAccount'. I first loaded my json file which I mentioned in my query and then I used your code.However I tried this x =pd.DataFrame([{"loanAccount": '', **data1["loanAccount"][0]}]) but this also seems not working for me. I am new to json file handling so could not able to resolve this error.

kederrac Over a year ago

maybe you have a diffrent json than the one from question ? can you check again ?

kederrac Over a year ago

you should read the json like : with open('data.json') as f: data = json.load(f)

Aditya sharma Over a year ago

I used this command to read json file data1 = pd.read_json (r'./DLResponse1.json',lines=True) and DLResponse1 file contains exactly what I have mentioned in my above json data.

Aditya sharma Over a year ago

with open('data.json') as f: data = json.load(f) with this reading method, it works for me.. thanks a lot

Collectives™ on Stack Overflow

convert json file to data frame in python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related