如何按Situation分组计算DataFrame中Pass与其他事件的时间差？

我有一个包含Pass、RHM、REM三种事件类型的DataFrame，新增了Category和Situation字段，需要按这两个字段分组，为每组内的每个Pass计算其与前一个Pass、前一个RHM、前一个REM的起始时间差，每组需独立重新统计时间差。

示例输入数据 #

理想输出（以R1-Situation1组为例） #

现有问题 #

我目前的循环代码仅适用于单一Situation场景，无法实现分组统计，希望用dplyr工具完成需求，不知道如何替代循环逻辑。现有代码如下：

for (i in 1:nrow(x)) {
  
    # PASSES
    # look in rows above the current row and search for last row containing "Pass"
    previousPasses <- which(x$Event[1:i - 1] == "Pass")
    # get last of these passes
    lastPass <- previousPasses[length(previousPasses)]
    # if there are no previous passes, then leave the TimeLastPass column blank
    if (length(lastPass) == 0) {
        x$TimeLastPass[i] <- NaN
    } else {
        # otherwise, fill in the TimeLastPass column with the time of the last pass
        x$TimeLastPass[i] <- round(x$Start[lastPass] - x$Start[i],2)
    }
    
    ### REM
    # look in rows above the current row and search for last row containing "REM"
    previousREMs <- which(x$Event[1:i - 1] == "Referential Eye Movement")
    # get last of these REMs
    lastREM <- previousREMs[length(previousREMs)]
    # if there are no previous REMs, then leave the TimeLastREM column blank
    if (length(lastREM) == 0) {
        x$TimeLastREM[i] <- NaN
    } else {
        # otherwise, fill in the TimeLastREM column with the time of the last REM
        x$TimeLastREM[i] <- round(x$Start[lastREM] - x$Start[i], 2)
    }

    ### RHM
    # look in rows above the current row and search for last row containing "RHM"
    previousRHMs <- which(x$Event[1:i - 1] == "Referential Head Movement")
    # get last of these RHMs
    lastRHM <- previousRHMs[length(previousRHMs)]
    # if there are no previous RHMs, then leave the TimeLastRHM column blank
    if (length(lastRHM) == 0) {
        x$TimeLastRHM[i] <- NaN
    } else {
        # otherwise, fill in the TimeLastRHM column with the time of the last RHM
        x$TimeLastRHM[i] <- round(x$Start[lastRHM] - x$Start[i],2)
    }
} 

无需循环，用dplyr分组结合窗口函数即可高效实现需求，核心是通过向前填充记录每组内最近一次目标事件的时间，再计算时间差。

library(dplyr)
library(zoo) # 需要na.locf函数做向前填充

# 处理数据
result_df <- df %>%
  group_by(Category, Situation) %>%
  mutate(
    # 处理Pass：记录最近一次Pass的时间，仅Pass行保留当前时间，其余用前一个Pass时间填充
    last_pass_time = ifelse(Event == "Pass", Start, NA),
    last_pass_time = na.locf(last_pass_time, na.rm = FALSE),
    `Time since last Pass` = ifelse(Event == "Pass", Start - lag(last_pass_time), NA),
    
    # 处理RHM：记录最近一次RHM的时间，全组向前填充
    last_rhm_time = ifelse(Event == "RHM", Start, NA),
    last_rhm_time = na.locf(last_rhm_time, na.rm = FALSE),
    `Time since last RHM` = ifelse(Event == "Pass", Start - last_rhm_time, NA),
    
    # 处理REM：记录最近一次REM的时间，全组向前填充
    last_rem_time = ifelse(Event == "REM", Start, NA),
    last_rem_time = na.locf(last_rem_time, na.rm = FALSE),
    `Time since last REM` = ifelse(Event == "Pass", Start - last_rem_time, NA)
  ) %>%
  # 仅保留Pass行，过滤无关数据
  filter(Event == "Pass") %>%
  # 保留需要的字段
  select(Category, Situation, Event, `Time since last Pass`, `Time since last RHM`, `Time since last REM`) %>%
  # 统一保留3位小数，匹配示例格式
  mutate(across(starts_with("Time"), ~round(., 3))) %>%
  ungroup()

代码说明 #

1. 分组隔离：group_by(Category, Situation)确保每组独立计算，不会跨组干扰统计结果。
2. 向前填充事件时间：用ifelse标记对应事件的时间，再通过na.locf将最近一次事件的时间向前填充到后续行，让每一行都能获取截至当前的最近目标事件时间。
3. 计算时间差：仅对Pass行计算当前时间与最近目标事件的差值，非Pass行直接设为NA。
4. 整理输出：过滤出Pass行，保留所需字段，统一小数位数后取消分组。

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