如何在循环中为Pandas DataFrame每次迭代添加唯一股票名称

问题描述 #

批量上传股票数据到Pandas DataFrame时，需要为每次迭代对应的数据集添加股票名称字段，但当前实现仅首次迭代的股票名称有效，其余迭代的stock_name字段均为NaN。

原实现代码 #

from pandas_datareader import data as pdr
import requests
from bs4 import BeautifulSoup
import json, requests
import pandas as pd
import re
import numpy as np
import pandas_datareader.data as web
import yfinance as yfin
from tqdm import tqdm
import numpy as np
import datetime
from datetime import timedelta

################# fetch series names for sic ######################

sic_emisoras_df = pd.json_normalize(
    json.loads(
        requests.get('https://www.bmv.com.mx/es/Grupo_BMV/BmvJsonGeneric?idSitioPagina=6&mercado=CGEN_SCSOP&tipoValor=CGEN_CASEO&random=5845')
            .text
            .split(';(', 1)[-1]
            .split(')')[0]
        )['response']['resultado']
).dropna(axis=1, how='all')

####################################################################

# define time range:
start=datetime.date.today()-datetime.timedelta(days=14)
end=datetime.date.today()

# fetch data
# get all SIC names as list
stock_names = sic_emisoras_df["cveCorta"].values.tolist()

# append information per stock name
sic_market_df = pd.DataFrame([])
sic_market_df["stock_name"] = np.nan

for i in tqdm(stock_names):
     # fetch data per stock_name
    try:
       yfin.pdr_override()
       # append stock name
       sic_market_df["stock_name"]=i
       # fetch information by stock name
       data = web.DataReader(i,start,end)
       # append rows to empty dataframe
       sic_market_df = sic_market_df.append(data)
    except KeyError:
       pass

print("Fetched sic_market_df!")

当前错误输出 #

stock_name Open    High       Low        Close   Adj Close    Volume
2024-02-20  ZS  14.500000   14.950000   14.490000   14.700000   14.700000   30253100.0
2024-02-21  ZS  14.590000   14.860000   14.570000   14.790000   14.790000   23032400.0
2024-02-22  ZS  14.940000   15.280000   14.890000   15.240000   15.240000   35702500.0
2024-02-23  ZS  15.150000   15.290000   14.950000   15.130000   15.130000   22914900.0
2024-02-26  ZS  15.130000   15.480000   15.130000   15.280000   15.280000   23675800.0

期望输出 #

stock_name Open    High       Low        Close   Adj Close    Volume
2024-02-20  ZS  14.500000   14.950000   14.490000   14.700000   14.700000   30253100.0
2024-02-21  ZS  14.590000   14.860000   14.570000   14.790000   14.790000   23032400.0
2024-02-22  ZS  14.940000   15.280000   14.890000   15.240000   15.240000   35702500.0
2024-02-23  ZS  15.150000   15.290000   14.950000   15.130000   15.130000   22914900.0
2024-02-26  ZS  15.130000   15.480000   15.130000   15.280000   15.280000   23675800.0
...    ...      ...     ...          ...      ...     ...   ...    
2024-02-20  AAPL    14.500000   14.950000   14.490000   14.700000   14.700000   30253100.0
2024-02-21  AAPL    14.590000   14.860000   14.570000   14.790000   14.790000   23032400.0
2024-02-22  AAPL    14.940000   15.280000   14.890000   15.240000   15.240000   35702500.0
2024-02-23  AAPL    15.150000   15.290000   14.950000   15.130000   15.130000   22914900.0
2024-02-26  AAPL    15.130000   15.480000   15.130000   15.280000   15.280000   23675800.0

使用的包版本：

pandas==1.5.3
beautifulsoup4==4.12.3
pandas-datareader==0.10.0

问题原因分析 #

原代码的核心错误在于：

1. 初始化的sic_market_df仅包含stock_name列，每次循环先将该列所有值设为当前股票代码，再append不包含stock_name字段的行情数据，导致新追加的行stock_name值为NaN
2. 后续循环修改sic_market_df["stock_name"]=i时，只能覆盖已有行的stock_name值，无法为新追加的行赋值，最终只有首次迭代的行有有效股票名称

修正后的代码 #

from pandas_datareader import data as pdr
import requests
from bs4 import BeautifulSoup
import json, requests
import pandas as pd
import re
import numpy as np
import pandas_datareader.data as web
import yfinance as yfin
from tqdm import tqdm
import numpy as np
import datetime
from datetime import timedelta

################# fetch series names for sic ######################

sic_emisoras_df = pd.json_normalize(
    json.loads(
        requests.get('https://www.bmv.com.mx/es/Grupo_BMV/BmvJsonGeneric?idSitioPagina=6&mercado=CGEN_SCSOP&tipoValor=CGEN_CASEO&random=5845')
            .text
            .split(';(', 1)[-1]
            .split(')')[0]
        )['response']['resultado']
).dropna(axis=1, how='all')

####################################################################

# define time range:
start=datetime.date.today()-datetime.timedelta(days=14)
end=datetime.date.today()

# fetch data
# get all SIC names as list
stock_names = sic_emisoras_df["cveCorta"].values.tolist()

# 初始化空列表存储每个股票的DataFrame
stock_dfs = []

for i in tqdm(stock_names):
    try:
        yfin.pdr_override()
        # 获取当前股票的行情数据
        data = web.DataReader(i, start, end)
        # 为当前股票的所有行添加stock_name字段
        data['stock_name'] = i
        # 将处理后的DataFrame加入列表
        stock_dfs.append(data)
    except KeyError:
        pass

# 合并所有股票的DataFrame
sic_market_df = pd.concat(stock_dfs)
print("Fetched sic_market_df!")

关键改动说明 #

1. 改用列表存储中间结果：避免多次使用append方法（Pandas 1.5.x中append已被标记为过时，且效率低下），改用列表收集每个股票的DataFrame
2. 为单只股票数据添加字段：在获取单只股票行情后直接为其添加stock_name列，确保该股票的所有行都带有正确的标识
3. 一次性合并数据：循环结束后用pd.concat合并所有DataFrame，生成最终的完整数据集，保证所有行的stock_name字段都有效

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