CODEWITHSUNDEEP
pythonjsonpandas
Aditya sharma
Aditya sharma

Reputation: 153

convert json file to data frame in python

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

Upvotes: 1

Views: 123

Answers (1)

kederrac
kederrac

Reputation: 17322

you may use:

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

output:

enter image description here

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

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

enter image description here

Upvotes: 2

Related Questions