按分组ID筛选每30天首个观测值的R代码求助

按分组ID筛选观测值，规则如下：

• 每个ID的第一条记录必须保留
• 后续记录仅保留与上一个保留记录间隔至少30天的首个观测值
• 每次保留新记录后，30天的判断窗口重置

示例数据与预期结果 #

df <- data.frame(
  id = c("a","a","a","a","a",'a',"b","b","b","b","b","b"),
  date = c('12/01/22','12/15/22','01/02/22','02/03/22','02/17/22','04/15/22',
           '12/01/22','02/02/22','03/15/22','03/31/22','04/15/22','05/31/22'),
  keep = c('keep','delete','keep','keep','delete','keep',
           'keep','keep','keep','delete','keep','keep')
)
cutoff <- 30

其中keep列为预期的保留/删除标记。

当前测试代码 #

df.t <- df %>% 
  # 尝试按日期间隔分组，间隔小于cutoff则归为同一组
  group_by(g= accumulate(date, ~ if (.y - .x < cutoff) .x else .y)) %>%  
  # 取每组首行
  slice_head(n = 1) %>% 
  ungroup()                 

存在问题：未按id分组，且原数据中date为字符类型，无法直接计算日期差，导致逻辑失效。

补充真实数据集 #

structure(list(id_code = c("CH02-1", "CH02-10", "CH02-10", "CH02-10", 
"CH02-100", "CH02-100", "CH02-1000", "CH02-1001", "CH02-1002", 
"CH02-1002", "CH02-1002", "CH02-1002", "CH02-1002", "CH02-1002", 
"CH02-1003", "CH02-1004", "CH02-1004", "CH02-1005", "CH02-1006", 
"CH02-1007", "CH02-1007", "CH02-1007", "CH02-1008", "CH02-1009", 
"CH02-101", "CH02-101", "CH02-101", "CH02-101", "CH02-1010", 
"CH02-1010", "CH02-1011", "CH02-1011", "CH02-1012", "CH02-1012", 
"CH02-1013", "CH02-1014", "CH02-1015", "CH02-1016", "CH02-1017", 
"CH02-1017", "CH02-1018", "CH02-1019", "CH02-1019", "CH02-1019", 
"CH02-102", "CH02-102", "CH02-1020", "CH02-1021", "CH02-1022", 
"CH02-1022"), date = structure(c(18789, 19014, 19041, 19048, 
18950, 18961, 18786, 18767, 18950, 18951, 18952, 18970, 18995, 
18996, 18860, 19043, 19045, 18702, 18709, 18814, 18818, 18846, 
18795, 18926, 18789, 18793, 18810, 18820, 18697, 18702, 18748, 
18765, 18783, 18785, 18796, 18901, 18690, 18966, 18817, 18825, 
18950, 18695, 18701, 18708, 18987, 19017, 18782, 18992, 18724, 
18739), class = "Date"), row = 1:50), row.names = c(NA, -50L), class = c("tbl_df", 
"tbl", "data.frame"))

核心思路：按ID分组后，用accumulate跟踪上一个保留的日期，逐个判断当前日期是否满足间隔要求，同时更新保留日期。

修正后的示例数据处理代码 #

library(dplyr)
library(purrr)

cutoff <- 30

df_processed <- df %>%
  # 将字符型日期转为Date类型
  mutate(date = as.Date(date, format = "%m/%d/%y")) %>%
  # 按ID分组处理
  group_by(id) %>%
  # 标记需要保留的行
  mutate(
    keep_flag = accumulate(date, function(last_kept, current) {
      if (current - last_kept >= cutoff) {
        current  # 保留当前行，更新last_kept为当前日期
      } else {
        last_kept  # 不保留，last_kept不变
      }
    }, .init = first(date)) %>%
      tail(-1) %>%  # 去掉初始化的首值，匹配原数据行数
      equals(date)  # 比较当前日期是否等于更新后的last_kept，相等则为保留行
  ) %>%
  # 筛选保留行
  filter(keep_flag) %>%
  ungroup() %>%
  # 恢复原keep列格式
  mutate(keep = ifelse(keep_flag, "keep", "delete")) %>%
  select(-keep_flag)

print(df_processed)

补充数据集处理代码 #

补充数据中date已为Date类型，直接按id_code分组即可：

library(dplyr)
library(purrr)

cutoff <- 30

supplement_processed <- supplement_df %>%
  group_by(id_code) %>%
  mutate(
    keep_flag = accumulate(date, function(last_kept, current) {
      if (current - last_kept >= cutoff) {
        current
      } else {
        last_kept
      }
    }, .init = first(date)) %>%
      tail(-1) %>%
      equals(date)
  ) %>%
  filter(keep_flag) %>%
  ungroup()

print(supplement_processed)

代码说明 #

1. 日期类型转换：确保date为Date类型，才能正确计算日期间隔
2. 分组处理：按ID分组，避免不同ID的日期互相干扰
3. accumulate逻辑：
   • 初始化last_kept为每组的第一个日期（默认保留首行）
   • 遍历后续日期，若与last_kept间隔≥30天，则保留该行并更新last_kept
   • 通过比较当前日期与更新后的last_kept，标记保留行
4. 结果筛选：过滤出keep_flag为TRUE的行，得到最终结果

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