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))