How to normalize a JSON file using Pandas?-CodePudding

I have the following JSON file:

{"data":
    {"success":true,
    "timeseries":true,
    "start_date":"2022-10-01",
    "end_date":"2022-10-04",
    "base":"EUR",
    "rates":
        {"2022-10-01":{"NG":0.1448939471560284},
        "2022-10-02":{"NG":0.14487923291390148},
        "2022-10-03":{"NG":0.1454857922753868},
        "2022-10-04":{"NG":0.1507352356663182}},
    "unit":"per MMBtu"}}

Im trying to normalize this JSON into a pandas dataframe. This is what I tried:

import pandas as pd
import json

with open(r'C:\Users\Gio\Desktop\tools\python\acrodata.json','r') as f:
    data = json.loads(f.read())

df_flatten = pd.json_normalize(data, record_path =['data']['rates'], meta=['success', 'timeseries', 'start_date','end_date','base'])
print(df_flatten)

I receive the following error:

TypeError: list indices must be integers or slices, not str

CodePudding user response：

Try:

df = pd.DataFrame(data["data"])
df = pd.concat([df, df.pop("rates").apply(pd.Series, dtype=str)], axis=1)
print(df)

Prints:

            success  timeseries  start_date    end_date base       unit                   NG
2022-10-01     True        True  2022-10-01  2022-10-04  EUR  per MMBtu   0.1448939471560284
2022-10-02     True        True  2022-10-01  2022-10-04  EUR  per MMBtu  0.14487923291390148
2022-10-03     True        True  2022-10-01  2022-10-04  EUR  per MMBtu   0.1454857922753868
2022-10-04     True        True  2022-10-01  2022-10-04  EUR  per MMBtu   0.1507352356663182