如何在R中重构独角鲸行为数据以关联起止时间

我有一份记录独角鲸行为的数据集，包含8头鲸鱼的ID（whaleID）、多种行为类型（behaviour），每头鲸鱼可与其他0头、部分或全部鲸鱼互动（modID），每个行为事件的开始/结束状态（status）对应以秒为单位的时间（time_s）。数据由行为记录程序生成，每个事件的起止时间分属不同行。

示例数据集：

library(dplyr)
df <- data.frame(whaleID = c('c','a','b','c','c','a','b','c','c','c','b','c','c','b',
                             'b','b','b','a','b','a','b','b','b','b','c','c'),
                 status = c('start','start','start','stop','start','stop','stop','stop',
                            'start','stop','start','start','stop','stop',
                            'start','stop','start','start','stop','stop',
                            'start','stop','start','stop','start','stop'),
                 time_s = c(40.554,44.801,44.801,60.300,76.053,87.302,87.302,87.302,
                            41.056,42.801,45.304,54.800,56.871,75.054,
                            92.798,94.801,98.844,101.676,105.052,108.160,
                            113.918,118.914,132.170,136.799,248.227,252.568),
                 modID = c('d','b','a','d','d','b','a','d','d','d','a','d','d','a',
                           'a,c','a,c','a','b','a','b','','','','','',''),
                 behaviour = c('contact','contact','contact','contact','contact','contact','contact','contact',
                               'rub','rub','rub','rub','rub','rub',
                               'ventral','ventral','ventral','ventral','ventral','ventral',
                               'erect','erect','erect','erect','erect','erect')) %>% 
  arrange(time_s)

需求：将每个行为事件整合为包含起止时间的单行记录，期望输出如下：

df2 <- data.frame(whaleID = c('c','a','b','c','c','b','c','b','b','a','b','b','c'),
                  start = c(40.554,44.801,44.801,76.053,41.056,45.304,54.800,
                            92.798,98.844,101.676,113.918,132.170,248.227),
                  stop = c(60.300,87.302,87.302,87.302,42.801,75.054,56.871,
                           94.801,105.052,108.160,118.914,136.799,252.568),
                  modID = c('d','b','a','d','d','a','d','a,c','a','b','','',''),
                  behaviour = c('contact','contact','contact','contact','rub','rub','rub',
                                'ventral','ventral','ventral','erect','erect','erect')) %>% 
  arrange(start)

我尝试过在reframe、summarize中嵌套case_when，以及使用pivot_wider，但都没成功，尝试的代码如下：

dfTime <- df %>% 
  group_by(whaleID, behaviour, modID) %>% 
  reframe(start = case_when(status == "START" & min(time_s) ~ time_s),
          stop = case_when(status == "STOP" & min(time_s)~ time_s)) %>%
  ungroup()

核心思路是给每个成对的start/stop事件分配唯一分组ID，精准匹配同一事件的起止时间。以下提供两种可行方法：

方法一：使用pivot_wider #

library(dplyr)
library(tidyr)

df2 <- df %>%
  # 按鲸鱼ID、行为、互动对象分组，给每个start事件生成递增的分组ID
  group_by(whaleID, behaviour, modID) %>%
  mutate(event_id = cumsum(status == "start")) %>%
  ungroup() %>%
  # 转换为宽表，将start/stop状态转为列
  pivot_wider(
    id_cols = c(whaleID, behaviour, modID, event_id),
    names_from = status,
    values_from = time_s,
    values_fn = first  # 确保每个分组仅取一个时间值
  ) %>%
  # 移除多余分组ID，按开始时间排序
  select(-event_id) %>%
  arrange(start)

方法二：使用summarize（无需tidyr） #

df2 <- df %>%
  group_by(whaleID, behaviour, modID) %>%
  mutate(event_id = cumsum(status == "start")) %>%
  # 基于新增的事件ID分组，提取起止时间
  group_by(whaleID, behaviour, modID, event_id) %>%
  summarize(
    start = time_s[status == "start"],
    stop = time_s[status == "stop"],
    .groups = "drop"
  ) %>%
  arrange(start)

两种方法均可生成目标输出，解决了原代码中未区分同一鲸鱼同一行为下多个独立事件的问题，通过event_id将成对的start/stop行绑定为同一事件记录。

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