Python：嵌套JSON转DataFrame

huangapple
go评论104阅读模式
英文:

Python : nested json to dataframe

问题

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

  1. {
  2.    "end_date":"2023-02-02-00:00",
  3.    "price":{
  4.       "2023-01-30":{
  5.          "CHFEUR":{
  6.             "close":0.99612,
  7.             "high":0.99939,
  8.             "low":0.99408,
  9.             "open":0.99925
  10.          },
  11.          "CHFUSD":{
  12.             "close":1.08098,
  13.             "high":1.08884,
  14.             "low":1.08041,
  15.             "open":1.08579
  16.          },
  17.          "EURUSD":{
  18.             "close":1.08518,
  19.             "high":1.0914,
  20.             "low":1.08393,
  21.             "open":1.08609
  22.          }
  23.       },
  24.       "2023-01-31":{
  25.          "CHFEUR":{
  26.             "close":1.00489,
  27.             "high":1.00532,
  28.             "low":0.99497,
  29.             "open":0.99684
  30.          },
  31.          "CHFUSD":{
  32.             "close":1.09152,
  33.             "high":1.09269,
  34.             "low":1.0769,
  35.             "open":1.08127
  36.          },
  37.          "EURUSD":{
  38.             "close":1.08626,
  39.             "high":1.0875,
  40.             "low":1.08022,
  41.             "open":1.08498
  42.          }
  43.       },
  44.       "2023-02-01":{
  45.          "CHFEUR":{
  46.             "close":1.00156,
  47.             "high":1.00507,
  48.             "low":0.9997,
  49.             "open":1.00493
  50.          },
  51.          "CHFUSD":{
  52.             "close":1.10089,
  53.             "high":1.10213,
  54.             "low":1.09005,
  55.             "open":1.09204
  56.          },
  57.          "EURUSD":{
  58.             "close":1.09892,
  59.             "high":1.10013,
  60.             "low":1.08525,
  61.             "open":1.08637
  62.          }
  63.       },
  64.       "2023-02-02":{
  65.          "CHFEUR":{
  66.             "close":1.0037,
  67.             "high":1.00633,
  68.             "low":0.99968,
  69.             "open":1.00113
  70.          },
  71.          "CHFUSD":{
  72.             "close":1.09513,
  73.             "high":1.10353,
  74.             "low":1.09439,
  75.             "open":1.1
  76.          },
  77.          "EURUSD":{
  78.             "close":1.0911,
  79.             "high":1.10332,
  80.             "low":1.08855,
  81.             "open":1.09893
  82.          }
  83.       }
  84.    },
  85.    "start_date":"2023-01-30-00:00"
  86. }

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 :

  1. response = requests.get(url, params=querystring)
  2. data = response.json() 
  3. df = pd.DataFrame(data['price']).transpose().reset_index().rename(columns={'index': 'date'})
  4. print(df)
  1. date	CHFEUR	CHFUSD	EURUSD
  2. 2023-01-30	{'close': 0.99612, 'high': 0.99939, 'low': 0.99408, 'open': 0.99925}	{'close': 1.08098, 'high': 1.08884, 'low': 1.08041, 'open': 1.08579}	{'close': 1.08518, 'high': 1.0914, 'low': 1.08393, 'open': 1.08609}
  3. 2023-01-31	{'close': 1.00489, 'high': 1.00532, 'low': 0.99497, 'open': 0.99684}	{'close': 1.09152, 'high': 1.09269, 'low': 1.0769, 'open': 1.08127}	{'close': 1.08626, 'high': 1.0875, 'low': 1.08022, 'open': 1.08498}
  4. 2023-02-01	{'close': 1.00156, 'high': 1.00507, 'low': 0.9997, 'open': 1.00493}	{'close': 1.10089, 'high': 1.10213, 'low': 1.09005, 'open': 1.09204}	{'close': 1.09892, 'high': 1.10013, 'low': 1.08525, 'open': 1.08637}
  5. 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 !

英文:

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

  1.     {
  2.        "end_date":"2023-02-02-00:00",
  3.        "price":{
  4.           "2023-01-30":{
  5.              "CHFEUR":{
  6.                 "close":0.99612,
  7.                 "high":0.99939,
  8.                 "low":0.99408,
  9.                 "open":0.99925
  10.              },
  11.              "CHFUSD":{
  12.                 "close":1.08098,
  13.                 "high":1.08884,
  14.                 "low":1.08041,
  15.                 "open":1.08579
  16.              },
  17.              "EURUSD":{
  18.                 "close":1.08518,
  19.                 "high":1.0914,
  20.                 "low":1.08393,
  21.                 "open":1.08609
  22.              }
  23.           },
  24.           "2023-01-31":{
  25.              "CHFEUR":{
  26.                 "close":1.00489,
  27.                 "high":1.00532,
  28.                 "low":0.99497,
  29.                 "open":0.99684
  30.              },
  31.              "CHFUSD":{
  32.                 "close":1.09152,
  33.                 "high":1.09269,
  34.                 "low":1.0769,
  35.                 "open":1.08127
  36.              },
  37.              "EURUSD":{
  38.                 "close":1.08626,
  39.                 "high":1.0875,
  40.                 "low":1.08022,
  41.                 "open":1.08498
  42.              }
  43.           },
  44.           "2023-02-01":{
  45.              "CHFEUR":{
  46.                 "close":1.00156,
  47.                 "high":1.00507,
  48.                 "low":0.9997,
  49.                 "open":1.00493
  50.              },
  51.              "CHFUSD":{
  52.                 "close":1.10089,
  53.                 "high":1.10213,
  54.                 "low":1.09005,
  55.                 "open":1.09204
  56.              },
  57.              "EURUSD":{
  58.                 "close":1.09892,
  59.                 "high":1.10013,
  60.                 "low":1.08525,
  61.                 "open":1.08637
  62.              }
  63.           },
  64.           "2023-02-02":{
  65.              "CHFEUR":{
  66.                 "close":1.0037,
  67.                 "high":1.00633,
  68.                 "low":0.99968,
  69.                 "open":1.00113
  70.              },
  71.              "CHFUSD":{
  72.                 "close":1.09513,
  73.                 "high":1.10353,
  74.                 "low":1.09439,
  75.                 "open":1.1
  76.              },
  77.              "EURUSD":{
  78.                 "close":1.0911,
  79.                 "high":1.10332,
  80.                 "low":1.08855,
  81.                 "open":1.09893
  82.              }
  83.           }
  84.        },
  85.        "start_date":"2023-01-30-00:00"
  86.     }

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 :

  1. response = requests.get(url, params=querystring)
  2. data = response.json() 
  3. df = pd.DataFrame(data['price']).transpose().reset_index().rename(columns={'index': 'date'})
  4. print(df)
  1. date	CHFEUR	CHFUSD	EURUSD
  2. 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...
  3. 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...
  4. 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...
  5. 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 !

答案1

得分: 2

  1. all_data = []
  2. for k, v in data['price'].items():
  3.     all_data.append({'DATE': k, **{kk: v[kk]['open'] for kk in v}})
  5. df = pd.DataFrame(all_data)
  6. print(df)
英文:

Try (data contains your dictionary from the question):

  1. all_data = []
  2. for k, v in data['price'].items():
  3.     all_data.append({'DATE':k, **{kk:v[kk]['open'] for kk in v}})
  5. df = pd.DataFrame(all_data)
  6. print(df)

Prints:

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

答案2

得分: 2

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

If you want to use pandas only Python：嵌套JSON转DataFrame

  1. pd.DataFrame(data['price']).stack().str.get('open').unstack(0)
  1.              CHFEUR   CHFUSD   EURUSD
  2. 2023-01-30  0.99925  1.08579  1.08609
  3. 2023-01-31  0.99684  1.08127  1.08498
  4. 2023-02-01  1.00493  1.09204  1.08637
  5. 2023-02-02  1.00113  1.10000  1.09893

答案3

得分: 1

  1. import pandas as pd
  3. lst_data = [{'date': key, 'CHFEUR': item['CHFEUR']['open'], 'CHFUSD': item['CHFUSD']['open'], 'EURUSD': item['EURUSD']['open']} for key, item in data['price'].items()]
  5. df = pd.DataFrame(lst_data)
  7. print(df)
英文: 
  1. import pandas as pd
  3. lst_data = [{'date':key,'CHFEUR':item['CHFEUR']['open'],'CHFUSD':item['CHFUSD']['open'],'EURUSD':item['EURUSD']['open']} for key,item in data['price'].items()]
  5. df = pd.DataFrame(lst_data)
  7. print(df)

huangapple
  • 本文由 发表于 2023年2月8日 23:45:52
  • 转载请务必保留本文链接：https://go.coder-hub.com/75388262.html
  • dataframe
  • json
  • pandas
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定