如何在R语言中统计事件前后满足条件的连续天数

我有一份R语言数据集，需要统计事件（Event=="Yes"）前后满足Value>1条件的连续天数。

示例数据 #

Event <- c("No", "No", "Yes", "No", "No", "No")
Date <- c("2021-01-01", "2021-01-02", "2021-01-03", "2021-01-04", "2021-01-05", "2021-01-06")
Value <- c(0, 2, 3, 2, 1, 0)

df <- as.data.frame(cbind(Date, Event, Value))

尝试过的方法 #

我试过用lead和lag函数，也写过下面的代码，但它会计算整个数据集的累加值，没法只统计事件前后符合条件的连续天数：

df_new <- df %>%
  mutate(Cond = if_else(Value > 1, "Yes", "No"),
         Cons_Days = if_else(Event == "Yes", cumsum(Cond == "Yes"), 0))

期望的最终结果 #

期望得到的最终数据集如下：

Event <- c("No", "No", "Yes", "No", "No", "No")
Date <- c("2021-01-01", "2021-01-02", "2021-01-03", "2021-01-04", "2021-01-05", "2021-01-06")
Value <- c(0, 2, 3, 2, 1, 0)
Cond <- c("No", "Yes", "Yes", "Yes", "No", "No")
Cons_Days <- c(0, 0, 3, 0, 0, 0)

df_final <- as.data.frame(cbind(Event, Date, Value, Cond, Cons_Days))

解决方案 #

可以通过rle函数识别连续符合条件的区间，再定位包含事件的区间计算长度，具体代码如下：

library(dplyr)

# 修正原数据集类型问题（cbind会将所有列转为字符型），重新构造数据框
df <- tibble(
  Event = c("No", "No", "Yes", "No", "No", "No"),
  Date = c("2021-01-01", "2021-01-02", "2021-01-03", "2021-01-04", "2021-01-05", "2021-01-06"),
  Value = c(0, 2, 3, 2, 1, 0)
)

df_final <- df %>%
  mutate(
    Cond = if_else(Value > 1, "Yes", "No"),
    # 生成连续符合Cond=="Yes"的分组ID
    group_id = with(rle(Cond == "Yes"), rep(seq_along(values), lengths))
  ) %>%
  # 按分组筛选包含事件的区间
  group_by(group_id) %>%
  mutate(
    has_event = any(Event == "Yes"),
    Cons_Days = ifelse(Event == "Yes" & has_event, n(), 0)
  ) %>%
  ungroup() %>%
  select(-group_id, -has_event)

print(df_final)

运行后即可得到目标结果，核心逻辑是用rle识别连续的符合条件区间，再将该区间的长度赋值给事件所在行。

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