如何关联数据表获取指定日期标普500成分股数据用于聚合分析？

我有两个数据集，需要合并后用于聚合分析，但尝试多种data.table的merge、foverlaps方法都没得到预期结果：

1. individual_equities：包含TICKER、date、Price、Volume列，date是价格和成交量的记录日期
2. sp500_start_end：包含TICKER、start_date、end_date列，部分股票有多条记录（比如股票A两次纳入标普500的时间段）

数据集示例 #

sp500_start_end：

TICKER start_date   end_date
  A    1996-01-01   1998-12-31
  A    2002-03-12   9999-12-31
  B    1976-01-24   9999-12-31
  C    1969-02-13   1995-03-04

individual_equities：

TICKER   date       Price   vol
  A    1996-01-01   101    34
  B    1996-01-01   45     786
  A    1996-01-02   34      23
  B    1996-01-02   23     333

预期结果 #

需要保留所有原始列，得到如下格式的合并表，后续用于按日期汇总成分股总成交量等指标：

Ticker date      price vol start_date   end_date
  A   1996-01-01 101   34 1996-01-01   1998-12-31

尝试过的代码 #

我试过以下几种方法的变体，但要么日期列被覆盖，要么结果不符合预期：

1. 带条件连接的data.table索引方式：

setkey(sp500_start_end_dt, TICKER, start_date, end_date)

merged_dt <- ind_equities_dt[
  sp500_start_end_dt,
  on = .(TICKER, date >= start_date, date <= end_date),
  nomatch = 0L,
  allow.cartesian = TRUE
]

2. 类似条件连接但拼写错误：

merged_sp500_start_end_dt <- ind_equities_dt[
  sp500_start_end_dt,
  on = .(TICKER, date >= start_date, date <= end_date),
  nomath = 0
]

3. 使用foverlaps：

equities_within_index_periods <- foverlaps(
  x = ind_equities_dt[, .(TICKER, date, PERMNO, PRIMEXCH, PRC, VOL, RET, BID, ASK)],
  y = sp500_start_end_dt[, .(TICKER, start_date, end_date)],
  by.x = c("TICKER", "date", "date"),  # date作为上下边界
  by.y = c("TICKER", "start_date", "end_date"),
  type = "within",  
  nomatch = 0       
)

4. 先全连接再过滤：

merged_sp500 <- merge(
  ind_equities_dt,
  sp500_start_end_dt,
  by = "TICKER",
  allow.cartesian = TRUE,  # 允许多匹配行
  all = FALSE
)

# 过滤日期在纳入窗口内的行
merged_sp500_valid <- merged_sp500[
  date >= start_date & date <= end_date
]

核心问题是条件连接时原始date列被覆盖，以及部分方法的参数设置有误。以下是两种可靠的解决方法：

方法1：修正data.table条件连接，保留原始date列 #

在data.table的条件连接中，date >= start_date会默认将结果列命名为date（覆盖原始列），需要显式引用左表的原始date列：

# 确保所有日期列是Date类型
ind_equities_dt[, date := as.Date(date)]
sp500_start_end_dt[, `:=`(start_date = as.Date(start_date), end_date = as.Date(end_date))]

# 正确的条件连接，保留原始date列
merged_dt <- ind_equities_dt[sp500_start_end_dt, 
                             on = .(TICKER, date >= start_date, date <= end_date),
                             .(TICKER, date = x.date, Price, Volume, start_date, end_date),
                             nomatch = 0L,
                             allow.cartesian = TRUE]

解释：

• 使用x.date引用左表（ind_equities_dt）的原始date列，避免被连接条件生成的新列覆盖
• 显式指定输出列，确保所有需要的字段都被保留

方法2：优化foverlaps参数设置 #

foverlaps需要左右表都有明确的时间区间边界，给左表临时添加同date的上边界列即可：

# 给ind_equities_dt添加临时上边界列
ind_equities_dt[, date_end := date]

# 正确设置foverlaps参数
equities_within_index_periods <- foverlaps(
  x = ind_equities_dt,
  y = sp500_start_end_dt,
  by.x = c("TICKER", "date", "date_end"),
  by.y = c("TICKER", "start_date", "end_date"),
  type = "within",
  nomatch = 0L
)

# 清理临时列并调整列顺序
equities_within_index_periods[, date_end := NULL]
setcolorder(equities_within_index_periods, c("TICKER", "date", "Price", "Volume", "start_date", "end_date"))

聚合分析示例 #

合并完成后，可按日期汇总成分股总成交量：

daily_total_vol <- merged_dt[, .(total_volume = sum(Volume)), by = date]

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