python : nested json to dataframe

Question

我正在尝试使用 python 将嵌套的 json 内容转换为数据帧:

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

这个想法是用以下方式提取数据框:

<表类=“s-表”><标题>日期瑞士法郎瑞士法郎美元欧元美元 <正文>2023-01-300.999251.085791.086092023-01-310.996841.081271.084982023-02-011.004931.092041.086372023-02-021.001131.11.09893

我尝试使用转置，但此后我无法从每个值的内容中提取值“open”:

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

知道如何仅提取嵌套 json 内容中的“open”值吗？

谢谢!

Answer 1

如果你只想使用 pandas ;-)

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