如何在R语言中为每个id生成治疗后的天数变量？

原始数据 #

df = data.frame(id = c(1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2), 
                treatment = c(0,0,1,0,0,0,1,0,0,0,0,0,0,0,1,0,1,0,0,0),
                date = lubridate::ymd(c("2019-07-07", "2019-07-07", "2019-07-07", "2019-07-07", 
"2019-07-07", "2019-07-06", "2019-07-06", "2019-07-05",
"2019-07-05", "2019-04-20", "2019-04-20", "2019-04-20", 
"2019-04-20", "2019-04-19", "2019-04-19", "2019-03-14",
"2019-03-14", "2019-03-14", "2019-03-14", "2019-03-14")))

需求 #

为每个id生成dat变量，规则对应期望输出：

• 当行中treatment=1时，dat设为0
• treatment=1所在行的前面同组行（直到上一个treatment=1）dat设为0
• treatment=1所在行的后面同组行dat按行号依次递增1

解决方案（dplyr版本） #

library(dplyr)

df_result <- df %>%
  group_by(id) %>%
  # 生成反向治疗区间：从最后一行向前累计treatment标记
  mutate(treat_block = rev(cumsum(rev(treatment)))) %>%
  group_by(id, treat_block) %>%
  # 计算区间内计数：treatment=1的行设为0，其余行按行号差值计算
  mutate(dat = ifelse(treatment == 1, 0, row_number() - which(treatment == 1))) %>%
  # 处理最前面无treatment=1的区间，设为0
  mutate(dat = ifelse(is.na(dat), 0, dat)) %>%
  ungroup() %>%
  select(-treat_block)

# 验证结果与期望一致
all.equal(df_result$dat, c(0,0,0,1,2,3,0,1,2,3,4,5,6,0,0,1,0,1,2,3))
# [1] TRUE

解决方案（data.table版本） #

针对大数据集，data.table性能更优：

library(data.table)

setDT(df)
df[, dat := {
  # 定位当前组内所有treatment=1的行索引
  treat_idx = which(treatment == 1)
  # 为每行匹配最近的后续treatment=1的索引
  match_idx = findInterval(seq_len(.N), treat_idx, left.open = TRUE) + 1
  # 计算行号差：前面的行用后续treatment索引减当前行号，后面的行用当前行号减后续treatment索引
  ifelse(match_idx > length(treat_idx), seq_len(.N) - treat_idx[length(treat_idx)], treat_idx[match_idx] - seq_len(.N))
}, by = id]

# 验证结果与期望一致
all.equal(df$dat, c(0,0,0,1,2,3,0,1,2,3,4,5,6,0,0,1,0,1,2,3))
# [1] TRUE

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