如何高效过滤DataFrame：基于含重复id键日期的数据集（无循环）

需求说明 #

从df2中筛选出满足以下条件的行：按id匹配df1后，存在至少一个keydate使得 keydate ≤ date ≤ keydate + 10。

数据定义 #

# df1：包含每个id的关键日期（窗口起始日）
df1 <- structure(list(id = structure(1:6, .Label = c("id1", "id1", "id1", "id2", "id2", "id3"), class = "factor"),
                      keydate = structure(c(16500, 17266, 18859, 17876, 18611, 18051), class = "Date")),
                 .Names = c("id", "keydate"),
                 row.names = c(NA, -6L), class = "data.frame")

# df2：待筛选的id和日期数据
df2 <- structure(list(id = structure(1:7, .Label = c("id1", "id1", "id1", "id2", "id2", "id3", "id3"), class = "factor"),
                      date = structure(c(16504, 18868, 18919, 17885, 18656, 18089, 18131), class = "Date")),
                 .Names = c("id", "date"),
                 row.names = c(NA, -7L), class = "data.frame")

解决方案 #

方案1：使用fuzzyjoin（高效直观，兼容dplyr） #

适合大数据集，无需循环，通过模糊匹配直接筛选：

library(dplyr)
library(fuzzyjoin)

result <- df2 %>%
  fuzzy_inner_join(df1, 
                   by = c("id" = "id", "date" = "keydate"),
                   match_fun = list(`==`, function(x, y) x >= y & x <= y + 10)) %>%
  select(id = id.x, date) %>%
  distinct() # 去重，避免同一行匹配多个keydate的重复记录

print(result)

方案2：纯dplyr实现（无需额外包） #

通过分组检查每个日期是否落在任意窗口内：

library(dplyr)

result <- df2 %>%
  group_by(id, date) %>%
  summarise(
    in_window = any(df1$id == cur_group()$id & df1$keydate <= cur_group()$date & df1$keydate + 10 >= cur_group()$date),
    .groups = "drop"
  ) %>%
  filter(in_window) %>%
  select(-in_window)

print(result)

输出结果 #

id       date
1 id1 2015-03-10
2 id1 2021-08-29
3 id2 2018-12-20

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