基于data.table实现鲑鱼与褐鳟检测时间窗口重叠判断优化

需求说明 #

现有鱼类检测数据，每行对应一次检测：

• 褐鳟：Fish.ID以TB_开头
• 鲑鱼/幼鲑：Fish.ID以TA_开头

需要新增overlap列，标记鲑鱼检测是否落在任意褐鳟检测的±10分钟时间窗口内（1=存在重叠，0=无重叠）。数据量较大（50304条），需用data.table实现高效操作。

示例数据 #

allpositions_sample <- data.frame(
  detection_timestamp_AST = as.POSIXct(c(
    "2024-06-30 06:55:44",  # TA
    "2024-06-26 16:43:42",  # TA
    "2024-08-07 18:33:33",  # TA
    "2024-06-30 06:59:00",  # TA 应与TB重叠
    "2024-06-29 07:42:40",  # TA 无重叠
    "2025-06-12 04:48:58",  # TA 无重叠
    "2024-06-30 06:57:00",  # TB
    "2024-06-30 12:00:00"   # TB
  )),
  Fish.ID = factor(c("TA_17","TA_02","TA_19","TA_17","TA_17","TA_40","TB_1","TB_2")),
  Year = c(2024, 2024, 2024, 2024, 2024, 2025, 2024, 2024),
  stringsAsFactors = FALSE
)

列类型：

detection_timestamp_AST Fish.ID  Year
  <dttm>                  <fct>   <dbl>

原代码问题 #

原代码存在两个核心问题：

1. 时间窗口计算错误：将±10分钟写成了30*60（实际是30分钟），导致无法正确识别重叠
2. 步骤冗余：拆分数据集、创建多个临时表再合并，代码冗长且效率低

简洁高效的解决方案 #

方案1：使用foverlaps区间连接（高效处理大数据） #

直接在原data.table上操作，避免拆分合并：

library(data.table)

# 转换为data.table
dt <- as.data.table(allpositions_sample)

# 1. 提取褐鳟的±10分钟时间区间
tb_intervals <- dt[grepl("^TB_", Fish.ID), 
                   .(start = detection_timestamp_AST - 10*60, 
                     end = detection_timestamp_AST + 10*60)]

# 2. 给鲑鱼行创建"点区间"（start=end=自身检测时间），并保留原行号
dt[grepl("^TA_", Fish.ID), 
   `:=`(start = detection_timestamp_AST, end = detection_timestamp_AST, row_id = .I)]

# 3. 设置键并执行区间连接
setkey(tb_intervals, start, end)
setkey(dt, start, end)

# 4. 标记重叠并清理临时列
dt[, overlap := 0L]
# 获取所有匹配的鲑鱼行号
matched_rows <- foverlaps(dt[grepl("^TA_", Fish.ID)], tb_intervals, type = "any", nomatch = 0L)$row_id
dt[row_id %in% matched_rows, overlap := 1L]

# 清理临时列
dt[, c("start", "end", "row_id") := NULL]

# 查看结果
print(dt[, .(Fish.ID, detection_timestamp_AST, overlap)])

方案2：使用非等值连接（更直观） #

利用data.table的非等值连接和%between%函数，代码更简洁：

library(data.table)

dt <- as.data.table(allpositions_sample)

# 提取所有褐鳟的±10分钟时间范围
tb_windows <- dt[grepl("^TB_", Fish.ID), 
                 .(tb_start = detection_timestamp_AST - 10*60, 
                   tb_end = detection_timestamp_AST + 10*60)]

# 初始化overlap列，默认0
dt[, overlap := 0L]

# 给鲑鱼行标记重叠：判断当前时间是否落在任意褐鳟的窗口内
dt[grepl("^TA_", Fish.ID), 
   overlap := as.integer(any(detection_timestamp_AST %between% tb_windows[, .(tb_start, tb_end)])),
   by = .I]

# 查看结果
print(dt[, .(Fish.ID, detection_timestamp_AST, overlap)])

结果验证 #

运行后示例数据的overlap列结果应为：

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