Create a pandas dataframe from a nested json file-CodePudding

I have the following json file:

{
    "data": {
        "start_date": "2022-10-01",
        "end_date": "2022-10-04",
        "cur": "EUR",
        "prizes": {
            "2022-10-01": {
                "coffee": 0.1448939471560284,
                "usd": 1
            },
            "2022-10-02": {
                "coffee": 0.14487923291390148,
                "usd":1
            },
            "2022-10-03": {
                "coffee": 0.1454857922753868,
                "usd": 1
            }
        }
    }
}

I want to create a dataframe which looks like this, (so without the usd column):

              coffee  
2022-10-01  0.144894  
2022-10-02  0.144879  
2022-10-03  0.145486

This is what I tried:

path = r'C:\Users\Geo\Desktop\json_files\coffee.json'

df = pd.read_json(path)
df = pd.DataFrame(df['data']['prizes']['2022-10-01']['coffee']).T
print(df)

This is what I received:

   raise ValueError("DataFrame constructor not properly called!")
ValueError: DataFrame constructor not properly called!

CodePudding user response：

df = pd.DataFrame(json['data']['prizes']).T

[Out]:

              coffee  usd
2022-10-01  0.144894  1.0
2022-10-02  0.144879  1.0
2022-10-03  0.145486  1.0

CodePudding user response：

One option:

df = pd.DataFrame.from_dict(d['data']['prizes'], orient='index').drop(columns='usd', errors='ignore')