如何按用户ID处理堆叠数据：将戒烟后"从未吸烟"设为NA

需要按**用户ID（HCI_PersonID）分组，以记录日期（CONTACT_DATE）**为时间顺序，将每个用户首次出现"已戒烟（Quit）"事件后的所有"从未吸烟（Never）"值设为NA（此类数据为无效数据）。

字段说明 #

• HCI_PersonID：用户ID
• CONTACT_DATE：数据记录日期（用于判断事件先后顺序）
• TOBACCO_USER：吸烟状态（取值包括：空值、从未吸烟、未询问、已戒烟、当前吸烟）

原始数据 #

SmokAlc<-
  structure(list(HCI_PersonID = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 6, 6), CONTACT_DATE = structure(c(16374, 16731, 17039, 17410, 
                                                                       18277, 18285, 18302, 18302, 18303, 18303, 18395, 18485, 18646, 
                                                                       18667, 18927, 19100, 19151, 19270, 19334, 19340, 17179, 17212, 
                                                                       17268, 17310, 17410, 18796, 18806, 18807, 18809, 18822, 18837, 
                                                                       18843, 18848, 18849, 18857, 18879, 18900, 18921, 18942, 18963, 
                                                                       19003, 19016, 19017, 19024, 19102, 19128, 19131, 19137, 19205, 
                                                                       19221, 19254, 19258, 19265, 19305, 19339, 19369, 19374, 19380, 
                                                                       19403, 19473, 19486, 19499, 19509, 16316, 16680, 17133, 17136, 
                                                                       17165, 17169, 17184, 17198, 17220, 17303, 17308, 17394, 17484, 
                                                                       17681, 17681, 18031, 18388, 18752, 19116, 16703, 16728, 16736, 
                                                                       16757, 16797, 16903, 16904, 17100, 17409, 17448, 17463, 17766, 
                                                                       17787, 17814, 18121, 18171, 18179, 18197, 18199, 18200, 18208, 
                                                                       18213, 18219, 18246, 18400, 18404, 18529, 18550, 18590, 18596, 
                                                                       18598, 18610, 18666, 18715, 18718, 18771, 18773, 18897, 18898, 
                                                                       18899, 18903, 18925, 18925, 18932, 18932, 18960, 18990, 19016, 
                                                                       19030, 19037, 19045, 19046, 19067, 19072, 19100, 19128, 19128, 
                                                                       19130, 19136, 19158, 19184, 19185, 19199, 19205, 19206, 19212, 
                                                                       19212, 19216, 19241, 19256, 19268, 19296, 19324, 19331, 19338, 
                                                                       19339, 19387, 19395, 19415, 19464, 19465, 19501, 19523, 16568, 
                                                                       17520, 17550, 17560, 17575, 17583, 17583, 17589), class = "Date"), 
                 TOBACCO_USER = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
                                            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
                                            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
                                            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
                                            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, NA, 2L, 4L, 4L, 
                                            4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 2L, 2L, 4L, 2L, 
                                            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
                                            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
                                            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
                                            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
                                            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
                                            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L
                 ), levels = c("", "Never", "Not Asked", "Quit", "Yes"), class = "factor")), row.names = c(NA, 
                                                                                                           173L), class = "data.frame")

解决方案（R语言） #

使用dplyr包按用户分组处理，步骤如下：

library(dplyr)

SmokAlc_cleaned <- SmokAlc %>%
  # 按用户ID和记录日期排序，确保时间顺序正确
  arrange(HCI_PersonID, CONTACT_DATE) %>%
  group_by(HCI_PersonID) %>%
  mutate(
    # 计算每个用户首次出现"Quit"的日期，无Quit记录则为Inf
    first_quit_date = min(CONTACT_DATE[TOBACCO_USER == "Quit"], na.rm = TRUE),
    # 替换无效的Never值为NA
    TOBACCO_USER = case_when(
      is.infinite(first_quit_date) ~ TOBACCO_USER,
      CONTACT_DATE > first_quit_date & TOBACCO_USER == "Never" ~ NA_character_,
      TRUE ~ as.character(TOBACCO_USER)
    ) %>% factor(levels = levels(SmokAlc$TOBACCO_USER))
  ) %>%
  # 移除临时辅助字段
  select(-first_quit_date) %>%
  ungroup()

代码说明 #

1. 排序：先按用户ID和记录日期排序，保证时间逻辑的正确性
2. 分组计算首次戒烟日期：用min()提取每个用户最早的"Quit"记录日期，无该记录时返回Inf
3. 条件替换：仅对首次戒烟日期之后的"Never"值替换为NA，其他状态保持不变
4. 恢复因子类型：处理后将字段转回原因子水平，保证数据结构一致性

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