在R中基于条件实现跨列值结转（carry forward）的方法

问题背景 #

我有如下纵向数据：

structure(list(ID = c(101, 101, 101, 102, 102, 102), 
               `A` = c(7, NA, 0, 0, 0, 0), 
               `B` = c(0, 0, 1, 0, 0, 5), 
               `C` = c(NA, 7, 0, NA, 0, 0), 
               `D` = c(7, NA, 0, 1, 0, 1), 
               `Cycle` = c(1, 2, 3, 1, 2, 3)), 
          class = "data.frame", row.names = c(NA, -6L))

需求是：使用carry forward（结转）方法替换A、B、C列的值，规则为：若某个体在前一周期的变量值为7，则该变量在后续周期的值替换为7。例如个体101在周期1的A变量值为7，那么该个体周期2、3的A变量值需替换为7。

期望生成的数据如下：

structure(list(ID = c(101, 101, 101, 102, 102, 102), 
               `A` = c(7, 7, 7, 0, 0, 0), 
               `B` = c(0, 0, 1, 0, 0, 5), 
               `C` = c(NA, 7, 7, NA, 0, 0), 
               `D` = c(7, 7, 7, 1, 0, 1), 
               `Cycle` = c(1, 2, 3, 1, 2, 3)), 
          class = "data.frame", row.names = c(NA, -6L))

想了解是否可以使用mutate函数（或其他函数）实现该需求？

补充新数据集（需处理的列更多）：

structure(list(ID = c(101, 101, 101, 102, 102, 102, 103, 103, 
103, 104, 104, 104, 108, 108, 108, 122, 122, 122, 123, 123), 
    `7B` = c(NA, 0, 0, NA, 0, 0, NA, 8, 0, NA, 9, 0, NA, 8, 
    0, NA, 8, 0, NA, 8), `7M` = c(NA, 0, 0, NA, 0, 0, NA, 8, 
    0, NA, 9, 0, NA, 8, 0, NA, 8, 0, NA, 8), `7L` = c(NA, 0, 
    0, NA, 0, 0, NA, 8, 2, 5, 7, 0, NA, 8, 0, NA, 8, 0, NA, 8
    ), `7D` = c(NA, 0, 0, NA, 0, 0, NA, 8, 0, 1, 7, 0, NA, 8, 
    0, NA, 8, 0, NA, 8), `7O` = c(NA, 11, 11, NA, 0, 1, NA, 
    8, 11, 11, 7, 5, NA, 8, 0, NA, 8, 5, NA, 8), `6B` = c(0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 9, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
    `6M` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 9, 0, 0, 0, 0, 0, 
    0, 0, 0, 0), `6L` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 9, 0, 
    0, 11, 0, 0, 0, 0, 0, 0), `6D` = c(0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 9, 0, 0, 0, 0, 0, 0, 0, 0, 0), visit = c(1, 2, 3, 
    1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2)), row.names = c(NA, 
20L), class = "data.frame")

解决方案（使用dplyr包） #

完全可以用dplyr的mutate结合group_by和across函数实现，核心逻辑是按个体分组后，对指定列判断是否出现过7，一旦出现则后续所有行替换为7。

1. 处理初始数据集（指定A、B、C列） #

library(dplyr)

# 读取初始数据
df <- structure(list(ID = c(101, 101, 101, 102, 102, 102), 
               `A` = c(7, NA, 0, 0, 0, 0), 
               `B` = c(0, 0, 1, 0, 0, 5), 
               `C` = c(NA, 7, 0, NA, 0, 0), 
               `D` = c(7, NA, 0, 1, 0, 1), 
               `Cycle` = c(1, 2, 3, 1, 2, 3)), 
          class = "data.frame", row.names = c(NA, -6L))

# 执行结转替换
df_processed <- df %>%
  group_by(ID) %>%
  mutate(across(c(A, B, C), ~ ifelse(cummax(.x == 7, na.rm = TRUE) == 1, 7, .x))) %>%
  ungroup()

# 查看结果
df_processed

2. 处理补充的新数据集（批量指定列） #

如果需要处理多列（比如所有以7开头的列），可以用列名匹配的方式批量处理：

# 读取补充数据
df_new <- structure(list(ID = c(101, 101, 101, 102, 102, 102, 103, 103, 
103, 104, 104, 104, 108, 108, 108, 122, 122, 122, 123, 123), 
    `7B` = c(NA, 0, 0, NA, 0, 0, NA, 8, 0, NA, 9, 0, NA, 8, 
    0, NA, 8, 0, NA, 8), `7M` = c(NA, 0, 0, NA, 0, 0, NA, 8, 
    0, NA, 9, 0, NA, 8, 0, NA, 8, 0, NA, 8), `7L` = c(NA, 0, 
    0, NA, 0, 0, NA, 8, 2, 5, 7, 0, NA, 8, 0, NA, 8, 0, NA, 8
    ), `7D` = c(NA, 0, 0, NA, 0, 0, NA, 8, 0, 1, 7, 0, NA, 8, 
    0, NA, 8, 0, NA, 8), `7O` = c(NA, 11, 11, NA, 0, 1, NA, 
    8, 11, 11, 7, 5, NA, 8, 0, NA, 8, 5, NA, 8), `6B` = c(0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 9, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
    `6M` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 9, 0, 0, 0, 0, 0, 
    0, 0, 0, 0), `6L` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 9, 0, 
    0, 11, 0, 0, 0, 0, 0, 0), `6D` = c(0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 9, 0, 0, 0, 0, 0, 0, 0, 0, 0), visit = c(1, 2, 3, 
    1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2)), row.names = c(NA, 
20L), class = "data.frame")

# 处理所有以7开头的列
df_new_processed <- df_new %>%
  group_by(ID) %>%
  mutate(across(starts_with("7"), ~ ifelse(cummax(.x == 7, na.rm = TRUE) == 1, 7, .x))) %>%
  ungroup()

# 验证104个体的7L列：周期2为7，周期3应替换为7
df_new_processed %>% filter(ID == 104) %>% select(ID, visit, `7L`)

逻辑说明 #

• group_by(ID)：按个体分组，确保每个个体的处理独立
• across(c(A,B,C))/across(starts_with("7"))：批量选择需要处理的列
• cummax(.x == 7, na.rm = TRUE)：生成一个累积最大值的逻辑向量，当某行及之前出现过7时，后续所有行都为TRUE（即1）
• ifelse(..., 7, .x)：将逻辑向量为TRUE的行替换为7，其余保留原始值

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