data.table中两列7日滚动Spearman相关性计算报错问题

我需要在以下data.table中计算close与bop_green列的7日滚动Spearman相关性，尝试两种方法均出现异常：

• 基于Rfast的代码返回了包含443562个元素的向量（预期结果不超过50个）
• 使用frollapply的代码报出“incompatible dimensions”错误

数据样本 #

temp_dt = structure(list(date = structure(c(19390L, 19391L, 19394L, 19395L, 
19396L, 19397L, 19398L, 19401L, 19402L, 19403L, 19404L, 19405L, 
19409L, 19410L, 19411L, 19412L, 19415L, 19416L, 19417L, 19418L, 
19419L, 19422L, 19423L, 19424L, 19425L, 19426L, 19429L, 19430L, 
19431L, 19432L, 19433L, 19436L, 19437L, 19438L, 19439L, 19440L, 
19443L, 19444L, 19445L, 19446L, 19447L, 19450L, 19451L, 19452L, 
19453L, 19457L, 19458L, 19459L, 19460L, 19461L), class = c("IDate", 
"Date")), close = c(21.34, 21.4, 21.45, 21.37, 21.26, 21.14, 
21.33, 21.28, 21.15, 21.03, 21.11, 21.22, 21.47, 21.38, 20.78, 
20.54, 20.24, 19.88, 19.87, 19.87, 20.08, 20.09, 20, 20.11, 19.93, 
19.91, 19.63, 19.64, 19.73, 19.68, 19.5, 19.2, 19.34, 19.65, 
19.9, 20.19, 20.31, 20.33, 20.32, 20.5, 20.78, 20.9, 20.75, 20.94, 
21.05, 21.21, 20.87, 20.64, 20.63, 20.69), bop_green = c(0.00302961, 
0.0231944, 0.0374391, 0.0390877, 0.03104, 0.0131761, 0.0131656, 
0.00135036, -0.0208444, -0.0470593, -0.0502583, -0.0391544, -0.013647, 
-0.0106879, -0.0103398, -0.027718, -0.0562916, -0.0885738, -0.101889, 
-0.117401, -0.115863, -0.118662, -0.123646, -0.114466, -0.126912, 
-0.13905, -0.158688, -0.17043, -0.165913, -0.165853, -0.165351, 
-0.181065, -0.181714, -0.160895, -0.126132, -0.0803255, -0.0442461, 
-0.0255004, -0.0293242, -0.0132638, 0.0143276, 0.0355189, 0.0398848, 
0.0508185, 0.054249, 0.0759373, 0.0894885, 0.0744317, 0.0643457, 
0.0695191)), row.names = c(NA, -50L), class = c("data.table", 
"data.frame"))

问题代码与修正方案 #

1. Rfast版本代码问题 #

尝试的代码

library(data.table)
library(Rfast)
temp_dt[, c(rep(NA_real_, 6), cor(Rfast::colRanks(matrix(value1[sequence(rep(7, .N-6), 1:(.N-6))], 7)), Rfast::colRanks(matrix(value2[sequence(rep(7, .N-6), 1:(.N-6))], 7))))]

问题原因

• 占位符value1/value2未替换为实际列名close/bop_green
• cor()默认计算两个矩阵所有列的两两相关性，返回(N-6)x(N-6)矩阵，展开后生成大量元素
• 滚动窗口矩阵构造逻辑复杂且易出错

修正后的高效代码

library(data.table)
library(Rfast)

temp_dt[, roll_spearman := {
  # 生成7日滚动窗口矩阵，每一行对应一个窗口
  mat_close <- embed(close, 7)
  mat_bop <- embed(bop_green, 7)
  # 转置后用colCors计算每列（对应原窗口）的Spearman相关
  cors <- Rfast::colCors(t(mat_close), t(mat_bop), method = "spearman")
  # 前6行补NA，拼接结果
  c(rep(NA_real_, 6), cors)
}]

2. frollapply版本代码问题 #

尝试的代码

temp_dt[, corr := frollapply(x = value1, n = 7, cor, fill = NA, align = "right", method = "spearman", y = value2)]

问题原因

frollapply仅传入单列数据，y=value2会传入整列而非对应窗口的子集，导致维度不匹配

修正后的代码

library(data.table)

# 合并两列为矩阵，自定义函数提取窗口内数据计算相关性
temp_dt[, corr := frollapply(
  x = cbind(close, bop_green),
  n = 7,
  FUN = function(window_mat) cor(window_mat[,1], window_mat[,2], method = "spearman"),
  fill = NA,
  align = "right"
)]

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