在R语言中按日期区间统计匹配项的实现方法

现有两个数据框：

• DF_1：包含ID、TYPE、DT_START（区间起始日期）和DT_END（区间结束日期），其中ID+TYPE组合唯一
• DF_2：包含ID、TYPE、DT（单个日期）

需按ID和TYPE匹配，统计DF_2中DT落在DF_1对应行DT_START与DT_END区间内的次数，最终生成带COUNT列的DF_3。

示例数据 #

DF_1 定义代码 #

ID <- c(111,222,222,333,444,444)
TYPE <- c('A1','A1','B1','B1','A1','B1')
DT_START <- as.Date(c('2022/07/20','2022/07/18','2022/07/10','2022/07/05','2022/07/03','2022/07/01'), "%Y/%m/%d")
DT_END <- as.Date(c('2021/07/20','2021/07/18','2021/07/10','2021/07/05','2021/07/03','2021/07/01'), "%Y/%m/%d")
DF_1 <- data.frame(ID,TYPE,DT_START,DT_END)

DF_2 定义代码 #

ID <- c(111,111,111,111,222,222,444,444,444)
TYPE <- c('A1','A1','A1','A1','A1','A1','A1','B1','B1')
DT <- as.Date(c('2022/06/01','2022/05/15','2022/01/01','2021/06/01','2022/03/02','2021/12/21','2021/12/29','2022/06/30','2022/06/15'), "%Y/%m/%d")
DF_2 <- data.frame(ID,TYPE,DT)

目标结果DF_3 #

ID <- c(111,222,222,333,444,444)
TYPE <- c('A1','A1','B1','B1','A1','B1')
DT_START <- as.Date(c('2022/07/20','2022/07/18','2022/07/10','2022/07/05','2022/07/03','2022/07/01'), "%Y/%m/%d")
DT_END <- as.Date(c('2021/07/20','2021/07/18','2021/07/10','2021/07/05','2021/07/03','2021/07/01'), "%Y/%m/%d")
COUNT <- c(3,2,0,0,1,2)
DF_3 <- data.frame(ID,TYPE,DT_START,DT_END,COUNT)

实现方法 #

方法1：dplyr（tidyverse）实现 #

通过关联、区间判断、分组统计三步完成，代码简洁易读：

library(dplyr)

DF_3 <- DF_1 %>%
  left_join(DF_2, by = c("ID", "TYPE")) %>%
  # 注意示例中DT_START晚于DT_END，所以区间判断顺序为DT_END在前
  mutate(in_range = between(DT, DT_END, DT_START)) %>%
  group_by(ID, TYPE, DT_START, DT_END) %>%
  summarise(COUNT = sum(in_range, na.rm = TRUE), .groups = "drop")

若实际数据中DT_START早于DT_END，将between参数改为between(DT, DT_START, DT_END)即可。

方法2：data.table实现 #

适合大数据量场景，运算效率更高：

library(data.table)

setDT(DF_1)
setDT(DF_2)

DF_3 <- DF_1[DF_2, on = .(ID, TYPE), allow.cartesian = TRUE][
  , .(COUNT = sum(between(DT, DT_END, DT_START), na.rm = TRUE)), 
  by = .(ID, TYPE, DT_START, DT_END)
]

方法3：基础R实现 #

无需额外依赖包，适合小数据量快速验证：

DF_3 <- DF_1
DF_3$COUNT <- apply(DF_3, 1, function(row) {
  # 筛选同ID同TYPE的DF_2记录
  target_rows <- DF_2[DF_2$ID == row["ID"] & DF_2$TYPE == row["TYPE"], ]
  # 统计符合区间条件的次数
  sum(target_rows$DT >= as.Date(row["DT_END"]) & target_rows$DT <= as.Date(row["DT_START"]), na.rm = TRUE)
})

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