Create a pandas dataframe from a nested json file

Question

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!

mozway · Accepted Answer · 2022-10-19 09:31:00Z

4

One option assuming d the input variable:

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

output:

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

answered Oct 19, 2022 at 9:31

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ynjxsjmh · Accepted Answer · 2022-10-19 09:31:52Z

3

Assume your json file is loaded into dictionary as data

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

print(df)

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

answered Oct 19, 2022 at 9:31

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

2 Comments

mozway Over a year ago

NB. using orient="index" in the DataFramefrom_dict method should be preferred to transposition, this is more efficient and avoids messing up with the dtypes (imagine usd was a string, coffee would become an object)

Ynjxsjmh Over a year ago

@mozway You are right, always learn something new from you :) and good selecting tips rather than drop.

Gonçalo Peres · Accepted Answer · 2022-10-19 09:30:53Z

Assuming that the json is stored in the variable json

json = {
    "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
            }
        }
    }
}

The following will do the work

df = pd.DataFrame(json['data']['prizes']).T.drop('usd', axis=1)

[Out]:

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

Pradhumn charde · Accepted Answer · 2022-10-19 11:58:35Z

0

dic=json.load(path)
df=pd.DataFrame(dic)
print(type(df.loc['prizes']))
md=pd.DataFrame(df.loc['prizes'][0])
md=md.transpose()
del md['usd']
print(md)

answered Oct 19, 2022 at 11:58

Pradhumn charde

11 bronze badge

Collectives™ on Stack Overflow

Create a pandas dataframe from a nested json file

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related