如何格式化含嵌套JSON的DataFrame，提取Symbol字段为单独列？

当前通过Xignite API获取的Pandas DataFrame样式如下：

Date        Open    Close   High    Low     Volume    Security
0 2023-01-27  155.75  155.69  156.96  154.81   646223   {'Symbol': 'A'}
1 2023-01-27   51.39   52.75   53.46   50.98  4772525   {'Symbol': 'AA'}

浏览器返回的原始JSON数据：

[{"Date":"2023-01-27","Open":155.75,"Close":155.69,"High":156.96,"Low":154.81,"Volume":646223.0,"Security":{"Symbol":"A"}},{"Date":"2023-01-27","Open":51.39,"Close":52.75,"High":53.46,"Low":50.98,"Volume":4772525.0,"Security":{"Symbol":"AA"}}]

期望转换后的表格样式：

Date        Open    Close   High    Low     Volume    Symbol
0 2023-01-27  155.75  155.69  156.96  154.81   646223   A
1 2023-01-27   51.39   52.75   53.46   50.98  4772525   AA

现有API调用代码：

import pandas as pd

LIST1 = ["A.XNYS,AA.XNYS,AAC.XNYS"]

for ls in LIST1:

    df = pd.read_json(f'https://globalquotes.xignite.com/v3/xGlobalQuotes.json/GetGlobalDelayedQuotes?IdentifierType=Symbol&Identifiers={ls}&_fields=Security.Symbol,Date,Open,Close,High,Low,Volume&_token=089BErre4D63439499FFE73CA64905F3')

    print(df.head(5))
    if not df.empty:

    df = df.dropna(thresh=7)
   

    #x = df['Symbol'].astype(str)
    #Date = df['date']
    #Open = df['open']
    #Close = df['close']
    #High = df['high']
    #Low = df['low']
    #Volume = df['volume']
    #Symbol = x.str.slice(0, 38)

方法1：使用pd.json_normalize解析JSON #

直接用json_normalize处理API返回的JSON数据，自动展开嵌套字典结构，一步得到目标格式：

import pandas as pd
import requests

LIST1 = ["A.XNYS,AA.XNYS,AAC.XNYS"]

for ls in LIST1:
    # 请求API获取原始JSON数据
    url = f'https://globalquotes.xignite.com/v3/xGlobalQuotes.json/GetGlobalDelayedQuotes?IdentifierType=Symbol&Identifiers={ls}&_fields=Security.Symbol,Date,Open,Close,High,Low,Volume&_token=089BErre4D63439499FFE73CA64905F3'
    response = requests.get(url)
    data = response.json()
    
    # 展开嵌套的Security字段
    df = pd.json_normalize(data)
    
    # 重命名列名，将Security.Symbol改为Symbol
    df.rename(columns={'Security.Symbol': 'Symbol'}, inplace=True)
    
    # 保留原有空值处理逻辑
    if not df.empty:
        df = df.dropna(thresh=7)
    
    # 调整列顺序匹配期望样式
    df = df[['Date', 'Open', 'Close', 'High', 'Low', 'Volume', 'Symbol']]
    
    # 将Volume转为整数（原始数据为float类型，可选操作）
    df['Volume'] = df['Volume'].astype(int)
    
    print(df.head(5))

方法2：直接处理现有DataFrame的嵌套字段 #

如果已经通过pd.read_json得到包含Security列的DataFrame，可直接提取嵌套字段并整理：

import pandas as pd

LIST1 = ["A.XNYS,AA.XNYS,AAC.XNYS"]

for ls in LIST1:
    df = pd.read_json(f'https://globalquotes.xignite.com/v3/xGlobalQuotes.json/GetGlobalDelayedQuotes?IdentifierType=Symbol&Identifiers={ls}&_fields=Security.Symbol,Date,Open,Close,High,Low,Volume&_token=089BErre4D63439499FFE73CA64905F3')

    if not df.empty:
        df = df.dropna(thresh=7)
        
        # 提取Security字典中的Symbol值
        df['Symbol'] = df['Security'].apply(lambda x: x['Symbol'])
        
        # 删除原Security列
        df.drop(columns=['Security'], inplace=True)
        
        # 调整列顺序匹配期望样式
        df = df[['Date', 'Open', 'Close', 'High', 'Low', 'Volume', 'Symbol']]
        
        # 将Volume转为整数
        df['Volume'] = df['Volume'].astype(int)
    
    print(df.head(5))

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