如何在R中基于Cust_ID和3天内日期生成唯一ID列

问题背景 #

我有如下数据框：

dataframe <- data.frame(Cust_ID = c("1","2","2","3","1","3"), 
                        App_date = as.Date(c("2023-05-01","2023-05-02","2023-05-03","2023-05-06","2023-04-28","2023-04-04")),
                        Product = c("AA","AA","BB","AA","CC","BB"))

希望新增一列Unique_id，规则是：同一Cust_ID下，App_date处于3天时间范围内的行共享相同的唯一ID。期望得到的结果如下：

result <- data.frame(Cust_ID = c("1","2","2","3","1","3"), 
                        App_date = as.Date(c("2023-05-01","2023-05-02","2023-05-03","2023-05-06","2023-04-28","2023-04-04")),
                        Product = c("AA","AA","BB","AA","CC","BB"),
                        Unique_id = c("A1","A2","A2","A3","A1","A4"))

请问如何在R中实现这个需求？

解决方案 #

方法1：使用dplyr + lubridate #

核心逻辑是按客户分组后，通过日期间隔划分连续的时间块，再给每个时间块分配全局唯一ID：

library(dplyr)
library(lubridate)

dataframe %>%
  arrange(Cust_ID, App_date) %>%
  group_by(Cust_ID) %>%
  # 标记新时间块的起点：首行或与上一行日期差超过3天的行
  mutate(break_point = ifelse(row_number() == 1, TRUE, App_date - lag(App_date) > 3)) %>%
  # 累计求和生成组内时间块编号
  mutate(block = cumsum(break_point)) %>%
  ungroup() %>%
  # 将客户+组内块的组合转换为全局唯一的A开头ID
  mutate(Unique_id = paste0("A", dense_rank(paste(Cust_ID, block))))

方法2：使用data.table（适合大数据集） #

用data.table的语法实现相同逻辑，处理大规模数据时效率更高：

library(data.table)

setDT(dataframe)[order(Cust_ID, App_date), 
                 block := cumsum(c(TRUE, diff(App_date) > 3)), 
                 by = Cust_ID][, 
                               Unique_id := paste0("A", dense_rank(paste(Cust_ID, block)))]

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