在R中按ID基于eventdate生成order列的序列填充方案

需求说明：

• 为现有data.table生成名为order的新列，以eventdate为中心填充整数序列-3:3：eventdate对应的order值为0，日期更早的行依次为-3、-2、-1，日期更晚的行依次为1、2、3
• 每个eventdate仅保留自身及前后3行（共7行）
• 同一ID存在多个eventdate时，即使间隔过近也要分别生成完整序列，互不影响

现有数据 #

df <- structure(list(Date = structure(c(15047L, 15048L, 15049L, 15050L, 
                                  15051L, 15052L, 15053L, 15054L, 15142L, 15143L, 15144L, 15145L, 
                                  15146L, 15147L, 15148L, 15119L, 15120L, 15121L, 15122L, 15123L, 
                                  15124L, 15125L, 15126L, 15497L, 15498L, 15499L, 15500L, 15501L, 
                                  15502L, 15503L, 15504L, 15505L, 15506L), class = c("IDate", "Date"
                                  )), ID = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
                                             1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 
                                             3L, 3L, 3L, 3L), eventdate = structure(c(NA, NA, NA, NA, 15051L, 
                                                                                      NA, NA, NA, NA, NA, NA, 15145L, NA, NA, NA, NA, NA, NA, 15122L, 
                                                                                      NA, NA, NA, NA, NA, NA, NA, NA, 15501L, NA, 15503L, NA, NA, NA
                                             ), class = c("IDate", "Date")), Value = c(10L, 11L, 12L, 11L, 
                                                                                       15L, 17L, 18L, 15L, 20L, 21L, 22L, 25L, 26L, 25L, 26L, 5L, 6L, 
                                                                                       7L, 9L, 10L, 11L, 15L, 16L, 20L, 22L, 23L, 24L, 28L, 29L, 25L, 
                                                                                       24L, 26L, 24L)), row.names = c(NA, -33L), class = c("data.table", 
                                                                                                                                           "data.frame"))

期望输出 #

output <- structure(list(Date = structure(c(15048L, 15049L, 15050L, 15051L, 
                                  15052L, 15053L, 15054L, 15142L, 15143L, 15144L, 15145L, 15146L, 
                                  15147L, 15148L, 15119L, 15120L, 15121L, 15122L, 15123L, 15124L, 
                                  15125L, 15498L, 15499L, 15500L, 15501L, 15502L, 15503L, 15504L, 
                                  15500L, 15501L, 15502L, 15503L, 15504L, 15505L, 15506L), class = c("IDate", 
                                                                                                     "Date")), ID = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
                                                                                                                      1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 
                                                                                                                      3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), eventdate = structure(c(NA, 
                                                                                                                                                                               NA, NA, 15051L, NA, NA, NA, NA, NA, NA, 15145L, NA, NA, NA, NA, 
                                                                                                                                                                               NA, NA, 15122L, NA, NA, NA, NA, NA, NA, 15501L, NA, NA, NA, NA, 
                                                                                                                                                                               NA, NA, 15503L, NA, NA, NA), class = c("IDate", "Date")), Value = c(11L, 
                                                                                                                                                                                                                                                   12L, 11L, 15L, 17L, 18L, 15L, 20L, 21L, 22L, 25L, 26L, 25L, 26L, 
                                                                                                                                                                                                                                                   5L, 6L, 7L, 9L, 10L, 11L, 15L, 22L, 23L, 24L, 28L, 29L, 25L, 
                                                                                                                                                                                                                                                   24L, 24L, 28L, 29L, 25L, 24L, 26L, 24L), order = c(-3L, -2L, 
                                                                                                                                                                                                                                                                                                      -1L, 0L, 1L, 2L, 3L, -3L, -2L, -1L, 0L, 1L, 2L, 3L, -3L, -2L, 
                                                                                                                                                                                                                                                                                                      -1L, 0L, 1L, 2L, 3L, -3L, -2L, -1L, 0L, 1L, 2L, 3L, -3L, -2L, 
                                                                                                                                                                                                                                                                                                      -1L, 0L, 1L, 2L, 3L)), row.names = c(NA, -35L), class = c("data.table", 
                                                                                                                                                                                                                                                                                                                                                                "data.frame"))

实现方法 #

利用data.table的分组和筛选特性，按以下步骤实现：

library(data.table)

# 1. 提取所有有效的eventdate记录（非NA）
events <- df[!is.na(eventdate), .(ID, eventdate)]

# 2. 遍历每个事件日期，筛选对应ID的日期范围并计算order
result_list <- lapply(1:nrow(events), function(i) {
  current_id <- events$ID[i]
  current_event <- events$eventdate[i]
  # 筛选当前ID下，日期在事件前后3天内的行
  subset_df <- df[ID == current_id & Date >= current_event - 3 & Date <= current_event + 3]
  # 计算order：日期与事件日期的差值（IDate类型差值为整数天数）
  subset_df[, order := as.integer(Date - current_event)]
  subset_df
})

# 3. 合并所有子集得到最终结果
final_output <- rbindlist(result_list)

代码说明： #

• 先提取所有有效事件日期，确保每个事件都被单独处理
• 针对每个事件，筛选对应ID下符合日期范围的行，直接通过日期差值得到order值，逻辑简单高效
• 最后合并所有子集，即使同一ID的事件日期间隔过近，每个事件的7行都会被完整保留，互不干扰

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