如何基于连续行时间间隔对红外相机动物拍摄数据用R dplyr做聚合

实现方案 #

你需要先为每一段符合条件的连续目击记录生成唯一的事件分组ID，再基于分组聚合即可，完整可运行代码如下：

library(dplyr)
library(lubridate)

# 原始数据
df <- structure(list(camera_id = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 
2L, 2L, 3L, 3L, 3L, 3L), date = c("11-May-21", "11-May-21", "11-May-21", 
"15-May-21", "15-May-21", "10-May-21", "10-May-21", "12-May-21", 
"12-May-21", "12-May-21", "12-May-21", "12-May-21", "13-May-21", 
"13-May-21"), time = c("5:23:46", "5:23:50", "5:32:34", "9:35:20", 
"9:35:35", "23:11:16", "23:11:17", "11:06:08", "11:15:09", "11:24:10", 
"2:04:01", "2:04:03", "1:15:00", "1:15:50"), organism = c("mouse", 
"mouse", "bird", "squirrel", "squirrel", "mouse", "mouse", "woodchuck", 
"woodchuck", "woodchuck", "mouse", "mouse", "mouse", "mouse")), class = "data.frame", row.names = c(NA, 
-14L))

# 合并日期时间列
df$datetime <- as.POSIXct(paste(df$date, df$time), format ="%d-%B-%y %H:%M:%S")

# 聚合目击事件
df_result <- df %>%
  # 先按相机ID、物种、时间排序，保证记录顺序正确
  arrange(camera_id, organism, datetime) %>%
  # 按相机+物种分组计算相邻记录时间差，生成事件ID
  group_by(camera_id, organism) %>%
  mutate(
    timediff = as.numeric(difftime(datetime, lag(datetime, default = first(datetime)), units = "mins")),
    # 相邻记录间隔≥10分钟时新增事件ID
    event_id = cumsum(timediff >= 10)
  ) %>%
  # 按相机、物种、事件ID分组聚合
  group_by(camera_id, organism, event_id) %>%
  summarise(
    start_datetime = min(datetime),
    end_datetime = max(datetime),
    encounter_time = seconds_to_period(as.numeric(difftime(end_datetime, start_datetime, units = "secs"))),
    .groups = "drop"
  ) %>%
  # 转换为你需要的时间格式
  mutate(
    start_datetime = format(start_datetime, "%m/%d/%Y %H:%M"),
    end_datetime = format(end_datetime, "%m/%d/%Y %H:%M"),
    encounter_time = sprintf("%d:%02d:%02d", hour(encounter_time), minute(encounter_time), second(encounter_time))
  ) %>%
  select(camera_id, start_datetime, end_datetime, organism, encounter_time)

运行后得到的df_result和你给出的预期输出完全匹配。另外你之前预处理时出现负时间差，是因为计算时间差仅按物种分组、没有区分相机，上述方案修正了分组逻辑，不需要单独处理负数值和NA值。

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