如何用Python将嵌套JSON转换为提取open值的DataFrame

问题 #

尝试用Python将嵌套JSON转换为DataFrame，目标生成包含DATE、CHFEUR、CHFUSD、EURUSD列的表格，其中各汇率列仅提取open字段的值。已用transpose处理，但汇率列仍为嵌套字典，无法提取open值。

嵌套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"
}

目标DataFrame结构 #

尝试的代码 #

import requests
import pandas as pd

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

解决方案 #

方法1：对现有DataFrame提取open值 #

基于已有的代码，直接对每个汇率列用apply提取字典中的open字段：

import requests
import pandas as pd

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

# 提取各汇率列的open值
df['CHFEUR'] = df['CHFEUR'].apply(lambda x: x['open'])
df['CHFUSD'] = df['CHFUSD'].apply(lambda x: x['open'])
df['EURUSD'] = df['EURUSD'].apply(lambda x: x['open'])

print(df)

方法2：提前构建目标字典再转DataFrame #

直接遍历原始JSON的price数据，构建只包含日期和对应汇率open值的字典，转换为DataFrame更高效：

import requests
import pandas as pd

response = requests.get(url, params=querystring)
data = response.json() 

# 构建目标数据结构
target_data = {}
for date, rates in data['price'].items():
    target_data[date] = {
        'CHFEUR': rates['CHFEUR']['open'],
        'CHFUSD': rates['CHFUSD']['open'],
        'EURUSD': rates['EURUSD']['open']
    }

df = pd.DataFrame.from_dict(target_data, orient='index').reset_index().rename(columns={'index': 'DATE'})
print(df)

方法3：用列表推导式快速生成DataFrame #

通过列表推导式直接提取所需字段，代码更简洁：

import requests
import pandas as pd

response = requests.get(url, params=querystring)
data = response.json() 

df = pd.DataFrame({
    'DATE': list(data['price'].keys()),
    'CHFEUR': [d['CHFEUR']['open'] for d in data['price'].values()],
    'CHFUSD': [d['CHFUSD']['open'] for d in data['price'].values()],
    'EURUSD': [d['EURUSD']['open'] for d in data['price'].values()]
})

print(df)

内容的提问来源于stack exchange，提问作者MisterT