本文详解如何将宽格式股价数据（日期为索引、股票为列）与交易记录表（含 date 和 symbol 列）精准关联，通过 stack + merge 实现按日期+股票双重键查找并添加 price 字段。
在金融数据分析中，常需将宽格式的价格数据表（如每列为一只股票、索引为交易日）与长格式的交易事件表（如每行为一笔买卖，含 Date、Symbol、Shares 等字段）进行关联，从而为每笔交易补充对应时刻的成交价格。由于两表结构差异大——前者是 DatetimeIndex × StockColumns 的二维矩阵，后者是普通带列名的 DataFrame——直接使用 pd.concat() 或 join() 并不适用；正确做法是先重塑价格表结构，再执行多键合并。
✅ 正确步骤：stack → reset_index → merge
假设你已加载两个 DataFrame：
- df_prices：索引为 DatetimeIndex（如 '2007-01-10', '2007-01-17'），列名为股票代码（'SPY', 'AAPL', 'IBM', ...）；
- df_trades：普通 DataFrame，含列 'Date'（字符串或 datetime）、'Symbol'、'Order'、'Shares'。
你需要为 df_trades 中每一行，从 df_prices 中查出 相同日期 + 相同股票 对应的价格，并新增 'Price' 列。
? 核心操作解析
# 1. 将宽表 df_prices 转为长表：每行 = (日期, 股票, 价格)
tmp = df_prices.stack().reset_index(name='Price')
# 结果列：level_0（原索引日期）、level_1（原列名股票）、Price
# 2. 与交易表按日期和股票列合并
result = df_trades.merge(
tmp,
left_on=['Date', 'Symbol'],
right_on=['level_0', 'level_1'],
how='inner' # 仅保留有价格匹配的交易
).drop(columns=['level_0', 'level_1']) # 清理冗余键列? stack() 是关键：它将列名（股票代码）“压入”行内，生成一个具有 MultiIndex 的 Series；reset_index(name='Price') 则将其展开为标准三列 DataFrame，便于后续 merge。
? 完整可运行示例
import pandas as pd
# 构造示例价格数据（索引为日期）
df_prices = pd.DataFrame({
'SPY': [126.72, 128.05],
'AAPL': [96.59, 94.54],
'IBM': [89.56, 90.58],
'GOOG': [489.46, 497.28],
'XOM': [62.47, 63.77],
'BAC': [46.40, 46.17],
'GLD': [60.59, 62.64]
}, index=pd.to_datetime(['2007-01-10', '2007-01-17']))
# 构造示例交易数据
df_trades = pd.DataFrame({
'Date': pd.to_datetime(['2007-01-10', '2007-01-17', '2007-01-19']),
'Symbol': ['AAPL', 'AAPL', 'IBM'],
'Order': ['BUY', 'SELL', 'BUY'],
'Shares': [2500, 1500, 400]
})
# 执行合并
tmp = df_prices.stack().reset_index(name='Price')
result = df_trades.merge(
tmp,
left_on=['Date', 'Symbol'],
right_on=['level_0', 'level_1'],
how='inner'
).drop(columns=['level_0', 'level_1'])
print(result)输出结果：
Date Symbol Order Shares Price 0 2007-01-10 AAPL BUY 2500 96.59 1 2007-01-17 AAPL SELL 1500 94.54
⚠️ 注意：第三笔交易 '2007-01-19' 未出现在 df_prices 中（该表只含前两日），因此被 how='inner' 自动过滤。若需保留所有交易并填充 NaN（如 how='left'），请按需调整。
✅ 替代方案对比（不推荐）
- ❌ pd.concat(..., axis=1)：仅适用于索引完全对齐的横向拼接，无法按 Symbol 动态取值；
- ❌ df_trades['Price'] = df_prices.lookup(...)：lookup 已弃用，且要求索引/列严格匹配，容错性差；
- ✅ map + apply：虽可行但效率低，尤其大数据量时明显慢于向量化 merge。
✅ 最佳实践建议
- 始终确保 Date 列类型一致（推荐统一为 datetime64[ns]）；
- 若 df_prices 索引含时区信息，请在 merge 前用 .dt.tz_localize(None) 统一；
- 合并后建议检查 result['Price'].isna().sum()，确认缺失值是否符合预期；
- 对大规模数据，可先对 tmp 设置 set_index(['level_0','level_1']) 加速 merge。
掌握 stack + merge 这一组合技，你就能灵活应对各类“宽表→长表→业务主表”的关联需求，真正打通行情数据与交易逻辑之间的最后一公里。
