在纵向数据中识别连续超3次的观测并生成衍生变量

问题说明 #

给定如下R数据框，需为存在变量A连续出现次数>3的ID添加两列：

• Censor：从该ID首次出现A连续超3次的Day起标记为1，其余为0；无符合条件的ID则全为0
• Day_2：记录该首次出现的Day，无符合条件的ID则全为NA

原始数据 #

df <- structure(list(ID = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), A = c(0L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 0L, 1L, 0L, 0L, 0L, 1L, 1L, 1L, 1L), Day = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L)), row.names = c(NA, 48L), class = "data.frame")

目标数据格式 #

structure(list(ID = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), A = c(0L, 0L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 0L, 1L, 0L, 0L, 0L, 1L, 1L, 1L, 1L), Day = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L), Censor = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 1L), Day_2 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L)), row.names = c(NA, 48L), class = "data.frame")

代码实现 #

使用dplyr和data.table包处理连续值分组：

library(dplyr)
library(data.table)

# 数据处理流程
result_df <- df %>%
  group_by(ID) %>%
  # 为连续相同的A值生成分组ID
  mutate(group_id = rleid(A)) %>%
  # 计算每个连续组的长度和起始日期
  group_by(ID, group_id, A) %>%
  mutate(group_length = n(),
         group_start_day = first(Day)) %>%
  ungroup() %>%
  group_by(ID) %>%
  # 确定首次满足A连续>3次的起始日期
  mutate(first_censor_day = ifelse(any(A == 1 & group_length > 3),
                                  min(group_start_day[A == 1 & group_length > 3]),
                                  NA)) %>%
  # 生成Censor和Day_2列
  mutate(Censor = ifelse(!is.na(first_censor_day) & Day >= first_censor_day, 1L, 0L),
         Day_2 = first_censor_day) %>%
  # 移除辅助列
  select(-group_id, -group_length, -group_start_day, -first_censor_day) %>%
  ungroup()

# 输出结果
print(result_df)

代码逻辑解释 #

1. 连续值分组：用rleid(A)对每个ID内连续相同的A值标记分组ID，方便计算连续出现次数。
2. 组特征计算：按ID、分组ID、A值分组，计算每个连续组的长度和起始Day。
3. 确定首次触发日期：对每个ID，筛选出A=1且连续长度>3的组，取其中最早的起始Day作为first_censor_day；无符合条件的组则设为NA。
4. 生成目标列：
   • Censor：当Day大于等于first_censor_day时标记为1，否则为0；无触发日期则全为0。
   • Day_2：填充当前ID的first_censor_day值，无触发日期则为NA。
5. 清理辅助列：移除中间计算用的辅助列，得到最终结果。

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