Home > Mobile >  Reading a json object as a single row pandas df
Reading a json object as a single row pandas df

Time:07-16

I have a json object of the following format

{
  "ABC": 123,
  "DATE": "2020-01-01",
  "AMOUNT": 100,
  "IDENTIFIER": 12345
}

I want to read this into pandas as a single row df. So the output should look like

ABC    DATE        AMOUNT    IDENTIFIER
123   2020-01-01   100        12345

For now, I am OK if all the data is read in as strings, I can change the type in the df. I have tried df=pd.read_json('file.json', orient='index') but this results in a df which I have to pivot (and renaming columns seems to be a challenge as well as the column names are rangeindex). Any thoughts on a more cleaner way to do this? Thanks!

CodePudding user response:

I am not sure if I understand you well as I understood you need to convert JSON object like this

{
  "ABC": 123,
  "DATE": "2020-01-01",
  "AMOUNT": 100,
  "IDENTIFIER": 12345
} 

to dataframe, you can read the file and then pass the records to the pd.DataFrame like this

import json
data = open('file.json').read()
df=pd.json_normalize(json.loads(data))

CodePudding user response:

The safer way of doing this is using a with statement:

import json
import pandas as pd

with open('file.json') as f:
    df = pd.json_normalize(json.load(f))
  • Related