百万级DataFrame：替代For循环的R语言高效数据匹配统计方案

需要从data_frame_1的每行提取用户ID和日期，统计data_frame_2中对应用户、日期早于该行日期的job_id数量。当前用For循环实现，但实际场景中data_frame_1有200万行、data_frame_2有800万行，循环耗时过长。尝试过连接操作但结果表过大，希望找到更快的向量化或优化连接方案。

原循环代码：

store_info <- numeric(nrow(data_frame_1))
start_time = Sys.time()
for (i in seq(1:dim(data_frame_1)[1]) ) {
  
  users_id = data_frame_1$user_id1[i]
  date = data_frame_1$date_1[i]
  
  store_info[i] = length(data_frame_2[data_frame_2$user_id2 == users_id &
                           data_frame_2$date_2 < date, "job_id"])
  
}
end_time = Sys.time()
(diff_time = end_time - start_time)

示例数据生成代码：

start_date <- as.Date("2021-01-01")
end_date <- as.Date("2022-12-31")
dates_pull = seq(start_date, end_date, by = "day")
random_dates <- sample(dates_pull, 80000, replace = TRUE)

# 生成随机用户ID
possible_chars <- c(letters, LETTERS, 0:9)
num_ids <- 800 # 生成的ID数量
id_length <- 8 # 每个ID的长度
random_ids <- replicate(num_ids, paste0(sample(possible_chars, 
                                id_length, replace = TRUE), collapse = ""))
sample_ids <- sample(random_ids, 80000, replace = TRUE)

data_frame_1 = data.frame(user_id1 = sample_ids, date_1 = random_dates)

# 生成随机任务ID
random_ids_j <- replicate(num_ids, paste0(sample(possible_chars, id_length, replace = TRUE), collapse = ""))
sample_ids_j <- sample(random_ids, 500000, replace = TRUE)

random_dates <- sample(dates_pull, 500000, replace = TRUE)
sample_ids <- sample(random_ids, 500000, replace = TRUE)

data_frame_2 = data.frame(user_id2 = sample_ids, date_2 = random_dates,
                          job_id = sample_ids_j)

针对大规模数据，推荐用分组+滚动计数的向量化方法，避免循环和全量连接，以下是几种高效实现：

方法1：使用data.table（性能最优） #

data.table的分组和滚动操作专为大数据量优化，速度远快于基础R循环：

library(data.table)

# 转换为data.table格式
setDT(data_frame_1)
setDT(data_frame_2)

# 按用户ID和日期排序，确保滚动计数逻辑正确
data_frame_2 <- data_frame_2[order(user_id2, date_2)]

# 为每个用户的记录生成累计计数
data_frame_2[, cum_count := seq_len(.N), by = user_id2]

# 匹配每个data_frame_1行对应的最大累计计数
result <- data_frame_1[data_frame_2, on = .(user_id1 = user_id2, date_1 > date_2), 
                       mult = "last", .(user_id1, date_1, cum_count)]

# 无符合条件记录时填充0
data_frame_1$store_info <- fifelse(is.na(result$cum_count), 0, result$cum_count)

方法2：使用dplyr窗口函数 #

代码更易读，适合熟悉tidyverse的场景：

library(dplyr)

# 预处理data_frame_2：按用户分组排序后生成累计计数
df2_processed <- data_frame_2 %>%
  arrange(user_id2, date_2) %>%
  group_by(user_id2) %>%
  mutate(cum_count = row_number()) %>%
  ungroup()

# 匹配并统计每个data_frame_1行的有效记录数
result <- data_frame_1 %>%
  left_join(df2_processed, by = c("user_id1" = "user_id2")) %>%
  filter(date_2 < date_1) %>%
  group_by(user_id1, date_1) %>%
  summarise(store_info = max(cum_count, na.rm = TRUE)) %>%
  ungroup()

# 填充无匹配记录的行
final_result <- data_frame_1 %>%
  left_join(result, by = c("user_id1", "date_1")) %>%
  mutate(store_info = ifelse(is.na(store_info), 0, store_info))

方法3：去重预处理（进一步优化） #

如果data_frame_1存在大量重复的(user_id1, date_1)组合，先去重统计再合并，减少计算量：

library(data.table)

setDT(data_frame_1)
setDT(data_frame_2)

# 对data_frame_1去重
unique_df1 <- unique(data_frame_1[, .(user_id1, date_1)])

# 按方法1逻辑处理去重后的行
data_frame_2 <- data_frame_2[order(user_id2, date_2)]
data_frame_2[, cum_count := seq_len(.N), by = user_id2]

unique_result <- unique_df1[data_frame_2, on = .(user_id1 = user_id2, date_1 > date_2), 
                            mult = "last", .(user_id1, date_1, cum_count)]
unique_result[, store_info := fifelse(is.na(cum_count), 0, cum_count)]

# 合并回原data_frame_1
data_frame_1 <- data_frame_1[unique_result, on = .(user_id1, date_1), store_info := i.store_info]

性能说明 #

• 避免全量连接：全量连接会生成冗余行导致内存溢出，上述方法通过滚动匹配+累计计数仅保留有效统计值。
• 排序提升效率：对data_frame_2按用户和日期排序后，累计计数可线性生成，匹配操作的效率大幅提升。
• data.table在超大数据量下的性能显著优于dplyr，优先推荐使用。

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