按组统计过去30秒内匹配值次数（R语言数据框处理）

我正在处理如下R语言数据框：

df <- data.frame(p1 = c("Tom", "Tom", "Tom", "Tom", "Tom", "Tom", "Brad", "Brad", "Brad", "Brad", "Brad", "Brad", "Brad","Brad", "Brad", "Brad" ),
              elapsed_time = c(0, 10, 17, 28, 47, 65, 83, 100, 135, 180, 210, 225, 237, 253, 276, 281),
              event_type = c("start of period", "play", "play", "timeout", "play", "play", "play", "play", "play", "timeout", "play", "play", "play", "play",
                             "play", "play"),
              scored = c(NA, NA, "Tom", NA, NA, "Tom", NA, NA, NA, NA, NA, "Brad", NA, NA, "Brad", "Brad" ),
             timesincelastbreak = c(0, 10, 17, 0, 19, 37, 0, 17, 52, 0, 30, 45, 57, 73, 96, 101))

数据框预览：

p1    elapsed_time event_type      scored timesincelastbreak
   <chr>        <dbl> <chr>           <chr>               <dbl>
 1 Tom              0 start of period NA                      0
 2 Tom             10 play            NA                     10
 3 Tom             17 play            Tom                    17
 4 Tom             28 timeout         NA                      0
 5 Tom             47 play            NA                     19
 6 Tom             65 play            Tom                    37
 7 Brad            83 play            NA                      0
 8 Brad           100 play            NA                     17
 9 Brad           135 play            NA                     52
10 Brad           180 timeout         NA                      0
11 Brad           210 play            NA                     30
12 Brad           225 play            Brad                   45
13 Brad           237 play            NA                     57
14 Brad           253 play            NA                     73
15 Brad           276 play            Brad                   96
16 Brad           281 play            Brad                  101

我需要添加一列timesscored，要求：

• 基于timesincelastbreak统计当前行及之前30秒内p1 == scored的次数总和
• 每当timesincelastbreak等于0时，timesscored重置为0

预期输出：

p1    elapsed_time event_type      scored timesincelastbreak    timesscored   
   <chr>        <dbl> <chr>           <chr>               <dbl>      <dbl>     
 1 Tom              0 start of period NA                      0          0
 2 Tom             10 play            NA                     10          0
 3 Tom             17 play            Tom                    17          1
 4 Tom             28 timeout         NA                      0          0
 5 Tom             47 play            NA                     19          0
 6 Tom             65 play            Tom                    37          1
 7 Brad            83 play            NA                      0          0
 8 Brad           100 play            NA                     17          0
 9 Brad           135 play            NA                     52          0
10 Brad           180 timeout         NA                      0          0
11 Brad           210 play            NA                     30          0
12 Brad           225 play            Brad                   45          1
13 Brad           237 play            NA                     57          1
14 Brad           253 play            NA                     73          1
15 Brad           276 play            Brad                   96          1
16 Brad           281 play            Brad                  101          2

我试过用cumsum(df$p1 == df$scored)，但不知道怎么结合timesincelastbreak的30秒间隔和重置逻辑。

可以通过分组+滑动窗口统计实现需求，以下提供两种可行方案：

方案一：dplyr + slider（逻辑直观） #

先安装依赖包：

install.packages(c("dplyr", "slider"))

执行代码：

library(dplyr)
library(slider)

df <- df %>%
  # 按断点分组：每次timesincelastbreak=0时生成新分组，实现重置
  mutate(break_group = cumsum(timesincelastbreak == 0)) %>%
  group_by(break_group) %>%
  mutate(
    # 标记有效得分行：p1等于scored记为1，其余为0（NA自动转0）
    score_flag = ifelse(p1 == scored, 1, 0),
    # 滑动窗口统计：当前行及之前30秒内的得分次数
    timesscored = slide_dbl(
      .x = tibble(ts = timesincelastbreak, sf = score_flag),
      .f = ~ sum(.x$sf[.x$ts >= (.x$ts[nrow(.x)] - 30)]),
      .before = Inf  # 覆盖当前行及之前所有行
    )
  ) %>%
  # 清理临时列并取消分组
  select(-break_group, -score_flag) %>%
  ungroup()

方案二：纯dplyr实现（无需额外包） #

library(dplyr)

df <- df %>%
  mutate(break_group = cumsum(timesincelastbreak == 0)) %>%
  group_by(break_group) %>%
  mutate(
    score_flag = ifelse(p1 == scored, 1, 0),
    # 逐行统计符合时间范围的得分次数
    timesscored = pmap_dbl(
      list(row_number(), timesincelastbreak),
      function(r, current_ts) {
        sum(score_flag[1:r][timesincelastbreak[1:r] >= current_ts - 30], na.rm = TRUE)
      }
    )
  ) %>%
  select(-break_group, -score_flag) %>%
  ungroup()

逻辑说明 #

1. 分组重置：用cumsum(timesincelastbreak == 0)生成分组标识，每次遇到断点（timesincelastbreak=0）就新建分组，确保统计范围被限制在当前断点后的时间段内。
2. 得分标记：将p1 == scored的行标记为1，其他行标记为0，简化后续统计。
3. 时间范围统计：对每个分组内的每一行，筛选出该行及之前timesincelastbreak在当前值-30范围内的行，求和得分标记，得到最终的timesscored。

执行后即可得到符合预期的结果。

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