skip to Main Content

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 !

3

Answers


  1. 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
    
    Login or Signup to reply.
  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
    
    Login or Signup to reply.
  3. 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)
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search