Python : nested json to dataframe

Question

I'm trying with python to convert a nested json content into a dataframe :

    {
       "end_date":"2023-02-02-00:00",
       "price":{
          "2023-01-30":{
             "CHFEUR":{
                "close":0.99612,
                "high":0.99939,
                "low":0.99408,
                "open":0.99925
             },
             "CHFUSD":{
                "close":1.08098,
                "high":1.08884,
                "low":1.08041,
                "open":1.08579
             },
             "EURUSD":{
                "close":1.08518,
                "high":1.0914,
                "low":1.08393,
                "open":1.08609
             }
          },
          "2023-01-31":{
             "CHFEUR":{
                "close":1.00489,
                "high":1.00532,
                "low":0.99497,
                "open":0.99684
             },
             "CHFUSD":{
                "close":1.09152,
                "high":1.09269,
                "low":1.0769,
                "open":1.08127
             },
             "EURUSD":{
                "close":1.08626,
                "high":1.0875,
                "low":1.08022,
                "open":1.08498
             }
          },
          "2023-02-01":{
             "CHFEUR":{
                "close":1.00156,
                "high":1.00507,
                "low":0.9997,
                "open":1.00493
             },
             "CHFUSD":{
                "close":1.10089,
                "high":1.10213,
                "low":1.09005,
                "open":1.09204
             },
             "EURUSD":{
                "close":1.09892,
                "high":1.10013,
                "low":1.08525,
                "open":1.08637
             }
          },
          "2023-02-02":{
             "CHFEUR":{
                "close":1.0037,
                "high":1.00633,
                "low":0.99968,
                "open":1.00113
             },
             "CHFUSD":{
                "close":1.09513,
                "high":1.10353,
                "low":1.09439,
                "open":1.1
             },
             "EURUSD":{
                "close":1.0911,
                "high":1.10332,
                "low":1.08855,
                "open":1.09893
             }
          }
       },
       "start_date":"2023-01-30-00:00"
    }

The idea is to pull a dataframe with :

DATE	CHFEUR	CHFUSD	EURUSD
2023-01-30	0.99925	1.08579	1.08609
2023-01-31	0.99684	1.08127	1.08498
2023-02-01	1.00493	1.09204	1.08637
2023-02-02	1.00113	1.1	1.09893

I tried to use transpose but after this I'm unable to pull the value "open" from the content of each values :

response = requests.get(url, params=querystring)
data = response.json() 
df = pd.DataFrame(data['price']).transpose().reset_index().rename(columns={'index': 'date'})
print(df)

date    CHFEUR  CHFUSD  EURUSD
2023-01-30  {'close': 0.99612, 'high': 0.99939, 'low': 0.9...   {'close': 1.08098, 'high': 1.08884, 'low': 1.0...   {'close': 1.08518, 'high': 1.0914, 'low': 1.08...
2023-01-31  {'close': 1.00489, 'high': 1.00532, 'low': 0.9...   {'close': 1.09152, 'high': 1.09269, 'low': 1.0...   {'close': 1.08626, 'high': 1.0875, 'low': 1.08...
2023-02-01  {'close': 1.00156, 'high': 1.00507, 'low': 0.9...   {'close': 1.10089, 'high': 1.10213, 'low': 1.0...   {'close': 1.09892, 'high': 1.10013, 'low': 1.0...
2023-02-02  {'close': 1.0037, 'high': 1.00633, 'low': 0.99...   {'close': 1.09513, 'high': 1.10353, 'low': 1.0...   {'close': 1.0911, 'high': 1.10332, 'low': 1.08...

Any idea on how to pull only the value "open" in the nested json content ?

thank you !

Shubham Sharma · Accepted Answer · 2023-02-08 16:04:41Z

2

If you want to use pandas only ;-)

pd.DataFrame(data['price']).stack().str.get('open').unstack(0)

             CHFEUR   CHFUSD   EURUSD
2023-01-30  0.99925  1.08579  1.08609
2023-01-31  0.99684  1.08127  1.08498
2023-02-01  1.00493  1.09204  1.08637
2023-02-02  1.00113  1.10000  1.09893

answered Feb 8, 2023 at 16:04

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

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

Comments

Andrej Kesely · Accepted Answer · 2023-02-08 15:54:44Z

2

Try (data contains your dictionary from the question):

all_data = []
for k, v in data['price'].items():
    all_data.append({'DATE':k, **{kk:v[kk]['open'] for kk in v}})

df = pd.DataFrame(all_data)
print(df)

Prints:

         DATE   CHFEUR   CHFUSD   EURUSD
0  2023-01-30  0.99925  1.08579  1.08609
1  2023-01-31  0.99684  1.08127  1.08498
2  2023-02-01  1.00493  1.09204  1.08637
3  2023-02-02  1.00113  1.10000  1.09893

answered Feb 8, 2023 at 15:54

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Comments

ljmc · Accepted Answer · 2023-02-11 15:57:31Z

1

import pandas as pd

lst_data = [{'date':key,'CHFEUR':item['CHFEUR']['open'],'CHFUSD':item['CHFUSD']['open'],'EURUSD':item['EURUSD']['open']} for key,item in data['price'].items()]

df = pd.DataFrame(lst_data)

print(df)

edited Feb 11, 2023 at 15:57

ljmc

5,3732 gold badges11 silver badges30 bronze badges

answered Feb 8, 2023 at 16:14

sudheesh shivan

413 bronze badges

1 Comment

ljmc Over a year ago

Please do not post an answer that consists essentially of code. Please edit your answer to include an explanation of how and why the code solves the problem, when it should be used, what its limitations are, and if possible a link to relevant documentation.

Collectives™ on Stack Overflow

Python : nested json to dataframe

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related