Question 109830 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Python : nested json to dataframe

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 !

Tags:

3

Answers


  1. 0

    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. 0

    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. 0 
    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