在R中使用ifelse函数时重复前一个匹配值的实现方法

我有一个包含各condition点击量的数据框，希望按condition计算点击量的累积和。目前使用ifelse()函数实现，但希望在条件不满足时，重复上一次条件满足时生成的值，而非用NA占位，直到下一次条件满足。

示例代码 #

clicked <- round(runif(n = 20),0)
condition <- sample(c("Intervention", "Control"), size = 20, replace = T)
df <- data.frame(clicked, condition)

df %>%  select(clicked, condition) %>% group_by(condition) %>% 
  mutate(successes.intervention = ifelse(condition == "Intervention", cumsum(clicked), NA),
         N.intervention = ifelse(condition == "Intervention", 1:n(), NA),
         successes.control = ifelse(condition == "Control", cumsum(clicked), NA),
         N.control = ifelse(condition == "Control", 1:n(), NA))

期望输出 #

clicked condition    successes.intervention N.intervention successes.control N.control
     <dbl> <chr>                         <dbl>          <int>             <dbl>     <int>
 1       0 Control                           0              0                 0         1
 2       1 Control                           0              0                 1         2
 3       0 Control                           0              0                 1         3
 4       1 Intervention                      1              1                 1         3
 5       0 Control                           1              1                 1         4
 6       0 Intervention                      1              2                 1         4
 7       0 Intervention                      1              3                 1         4
 8       0 Control                           1              3                 1         5
 9       0 Intervention                      1              4                 1         5
10       1 Intervention                      2              5                 1         5 

解决方案 #

可以结合dplyr的条件计算和tidyr的fill()函数实现需求，步骤如下：

1. 针对每个condition单独计算累积成功数和计数，非对应条件的行暂用NA占位；
2. 使用fill()函数向下填充NA，将上一次满足条件时的值延续到后续不满足条件的行；
3. 对开头未出现对应条件的NA值，替换为0以匹配期望输出格式。

具体代码：

library(dplyr)
library(tidyr)

clicked <- round(runif(n = 20),0)
condition <- sample(c("Intervention", "Control"), size = 20, replace = T)
df <- data.frame(clicked, condition)

df_result <- df %>%
  mutate(
    # 计算Intervention组的累积成功数：仅在condition为Intervention时赋值对应累积和
    successes.intervention = ifelse(
      condition == "Intervention",
      cumsum(clicked[condition == "Intervention"])[cumsum(condition == "Intervention")],
      NA
    ),
    # 计算Intervention组的计数：仅在condition为Intervention时赋值序号
    N.intervention = ifelse(
      condition == "Intervention",
      seq_along(clicked[condition == "Intervention"])[cumsum(condition == "Intervention")],
      NA
    ),
    # 计算Control组的累积成功数
    successes.control = ifelse(
      condition == "Control",
      cumsum(clicked[condition == "Control"])[cumsum(condition == "Control")],
      NA
    ),
    # 计算Control组的计数
    N.control = ifelse(
      condition == "Control",
      seq_along(clicked[condition == "Control"])[cumsum(condition == "Control")],
      NA
    )
  ) %>%
  # 向下填充所有NA值，用上一个有效值替代
  fill(successes.intervention, N.intervention, successes.control, N.control, .direction = "down") %>%
  # 将开头未出现对应条件的NA替换为0
  mutate(
    across(
      c(successes.intervention, N.intervention, successes.control, N.control),
      ~replace_na(., 0)
    )
  )

print(df_result)

代码说明 #

• cumsum(clicked[condition == "Intervention"])先单独计算Intervention组的累积和，再通过cumsum(condition == "Intervention")匹配到原数据框的对应行位置，确保累积值只出现在对应condition的行；
• fill(..., .direction = "down")实现了"保留上一次有效值"的需求，自动将NA替换为最近的非NA值；
• replace_na(., 0)处理了数据开头未出现某condition时的NA，将其替换为0，与示例输出一致。

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