如何在R语言中依据idx及diff（超14天）进行数据分组？

需求说明 #

同一idx分组内，若相邻日期的差值（diff）不超过14天则归为同一分组；若差值超过14天，则划分为新的分组。

初始数据处理代码 #

idx = c("a","a","a","a","b","b","b","c","c","c","c")
date = c(20201115, 20201116, 20201117, 20201105, 20201107, 20201110, 20210113, 20160930, 20160504, 20160913, 20160927)
group = c("1","1","1","1","2","2","3","4","5","6","6")
df = data.frame(idx,date,group)
df <- df %>% arrange(idx,date)
df$date <- as.Date(as.character(df$date), format='%Y%m%d')
df <- df %>% group_by(idx) %>% 
  mutate(diff = date - lag(date))

解决方案代码 #

通过标记新分组触发条件，再累积求和生成组内分组号，最后转换为全局唯一分组编号：

library(dplyr)

df <- df %>%
  group_by(idx) %>%
  # 标记需要开启新分组的行：第一行或日期差超过14天
  mutate(new_group = ifelse(is.na(diff) | diff > 14, 1, 0)) %>%
  # 组内累积求和得到组内分组序号
  mutate(group_in_idx = cumsum(new_group)) %>%
  ungroup() %>%
  # 生成全局唯一的分组编号
  mutate(global_group = dense_rank(paste(idx, group_in_idx, sep = "_"))) %>%
  # 对齐示例结果的列名和结构
  select(idx, date, global_group) %>%
  rename(group = global_group)

最终结果 #

运行后得到的结果与期望一致：

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