大数据框迭代过滤的循环替代方案求助

当前处理57000条比赛数据时，采用逐行循环的方式执行以下逻辑：

• 提取每行R1[i]和R2[i]的排名，计算各自的筛选区间（排名±20%±5）
• 过滤出所有R1或R2落在这两个区间内的比赛记录
• 进一步筛选出这些记录中Rank_Dif ≥ 当前行Rank_Dif -5的条目
• 计算符合条件条目Data_Dif的均值，赋值给原数据的Rank_Adv[i]

现有代码可正常运行，但处理57000条数据耗时约4小时，亟需高效的替代方案用于每日运行。

当前代码片段 #

# 循环内的核心逻辑（完整循环结构未展示）
Rank <- Data %>% 
  filter(between(R1, Data$R1[i]-5-(Data$R1[i]*0.2), Data$R1[i]+5+(Data$R1[i]*0.2)) | 
           between(R1, Data$R2[i]-5-(Data$R2[i]*0.2), Data$R2[i]+5+(Data$R2[i]*0.2))) %>%
  filter(between(R2, Data$R1[i]-5-(Data$R1[i]*0.2), Data$R1[i]+5+(Data$R1[i]*0.2)) | 
           between(R2, Data$R2[i]-5-(Data$R2[i]*0.2), Data$R2[i]+5+(Data$R2[i]*0.2))) 
Rank_Difference <- Data$Rank_Dif[i]
Rank <- Rank %>% filter(Rank_Dif >= Rank_Difference-5)
Data$Rank_Adv[i] <- mean(Rank$Data_Dif)

数据示例 #

Data
     R1   R2 Rank_Dif Data_Dif    Rank_Adv
1     2    1        1   -0.272 0.037696970
2    10   34       24    0.377 0.146838617
3    10   29       19    0.373 0.130336232
4     2    5        3    0.134 0.076242424
5    34   17       17   -0.196 0.094226519
6     1   18       17    0.144 0.186158879
7    17   25        8    0.264 0.036212219
8    42   18       24    0.041 0.102343915
9     5   13        8   -0.010 0.091952381
10   34   21       13   -0.226 0.060790576
11    2   14       12    0.022 0.122350649
12   10  158      148    0.330 0.184901961
13   11    1       10   -0.042 0.109918367
14   29   52       23    0.463 0.054469108
15   10 1000      990    0.628 0.437600000
16   17  329      312    0.445 0.307750000
17   11   20        9    0.216 0.072621875
18  417  200      217   -0.466 0.106737401
19    5   53       48    0.273 0.243890710
20   14    7        7   -0.462 0.075739414

高效优化方案 #

R的逐行循环对大数据量效率极低，核心优化方向是用向量化操作/非等值连接替代循环，以下是两种可行方案：

方案1：使用data.table实现极速处理 #

data.table的非等值连接和分组聚合性能远优于基础R与dplyr，是大数据场景的首选：

library(data.table)
setDT(Data)

# 1. 预计算每条记录的R1/R2筛选区间边界
Data[, `:=`(
  R1_low = R1 - 5 - 0.2*R1,
  R1_high = R1 + 5 + 0.2*R1,
  R2_low = R2 - 5 - 0.2*R2,
  R2_high = R2 + 5 + 0.2*R2
)]

# 2. 非等值连接匹配所有符合区间条件的记录对
# 匹配规则：R1落在当前行的R1/R2区间，且R2落在当前行的R1/R2区间
matched <- Data[Data, on = .(
  R1 >= R1_low, R1 <= R1_high | R1 >= R2_low, R1 <= R2_high,
  R2 >= R1_low, R2 <= R1_high | R2 >= R2_low, R2 <= R2_high
), allow.cartesian = TRUE]

# 3. 过滤Rank_Dif条件并计算分组均值
matched <- matched[Rank_Dif >= i.Rank_Dif - 5, 
                   .(Rank_Adv = mean(Data_Dif)), 
                   by = .(i.R1, i.R2, i.Rank_Dif)]

# 4. 将结果映射回原数据
Data[matched, Rank_Adv := i.Rank_Adv, on = .(R1 = i.R1, R2 = i.R2, Rank_Dif = i.Rank_Dif)]

# 清理临时列
Data[, c("R1_low", "R1_high", "R2_low", "R2_high") := NULL]

方案2：dplyr结合fuzzyjoin实现模糊匹配 #

若偏好dplyr语法，可使用fuzzyjoin包的模糊连接替代循环：

library(dplyr)
library(fuzzyjoin)

# 1. 预计算区间边界
Data <- Data %>%
  mutate(
    R1_low = R1 - 5 - 0.2*R1,
    R1_high = R1 + 5 + 0.2*R1,
    R2_low = R2 - 5 - 0.2*R2,
    R2_high = R2 + 5 + 0.2*R2
  )

# 2. 模糊连接匹配区间条件
matched <- fuzzy_inner_join(
  Data, Data,
  by = c(
    "R1_low" = "R1", "R1_high" = "R1",
    "R2_low" = "R1", "R2_high" = "R1",
    "R1_low" = "R2", "R1_high" = "R2",
    "R2_low" = "R2", "R2_high" = "R2"
  ),
  match_fun = list(`<=`, `>=`, `<=`, `>=`, `<=`, `>=`, `<=`, `>=`)
) %>%
  # 明确过滤区间条件（模糊连接的match_fun可能需要二次验证）
  filter((R1.y >= R1_low.x & R1.y <= R1_high.x) | (R1.y >= R2_low.x & R1.y <= R2_high.x)) %>%
  filter((R2.y >= R1_low.x & R2.y <= R1_high.x) | (R2.y >= R2_low.x & R2.y <= R2_high.x)) %>%
  # 过滤Rank_Dif条件
  filter(Rank_Dif.y >= Rank_Dif.x - 5) %>%
  # 分组计算均值
  group_by(R1.x, R2.x, Rank_Dif.x) %>%
  summarise(Rank_Adv = mean(Data_Dif.y), .groups = "drop")

# 3. 合并结果回原数据并清理临时列
Data <- Data %>%
  left_join(matched, by = c("R1" = "R1.x", "R2" = "R2.x", "Rank_Dif" = "Rank_Dif.x")) %>%
  select(-R1_low, -R1_high, -R2_low, -R2_high)

核心优化点 #

• 消除逐行循环：用批量的非等值连接替代O(n²)的循环逻辑，将计算复杂度从线性循环转化为更高效的批量处理
• 预计算区间：提前计算所有记录的筛选边界，避免重复计算浪费资源
• 选择高效工具：data.table的底层实现为C++，在大数据量下的连接、聚合速度比基础R快10~100倍

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