flat one-line json to pandas datafame

user_7106865 0
Viewed 21

trying to read a very simple and flat json into pandas dataframe. This one is an example, real one has more than 200 variables.

    {"ranking":"11.1","real_name":"Robert","aggregate":"3","thirdparty":"none", "notes":""}

import pandas as pd

df = pd.read_json("data.json", typ='series')

I get:

ranking         11.1
real_name     Robert
aggregate          3
thirdparty      none
notes               
dtype: object

tried to coerce to dataframe as I need it for further processing with pandas:

pd.DataFrame(df)


0
ranking 11.1
real_name   Robert
aggregate   3
thirdparty  none
notes

but expected dataframe should be:

ranking   real_name   aggregate   thirdparty    notes
11.1       Robert      3           none

What step I am missing here to get a tabular dataframe even with only one line?

pandas json
1 Answers

Use pd.json_normalize:

import pandas as pd
import json

with open('data.json') as fp:
    d = json.load(fp)
    df = pd.json_normalize(d)

Output

>>> df
  ranking real_name aggregate thirdparty notes
0    11.1    Robert         3       none
user_152399510
Related