基于±3分钟时间窗口识别鱼类种间共现状态并补全观测

需求说明 #

• 数据集包含大西洋鲑和褐鳟的遥测检测记录，每行对应单条鱼的一次检测事件
• 需识别两种鱼在±3分钟时间窗口内的共现情况，同时保留所有观测（共现/非共现）
• 列名规则：褐鳟专属列前缀为bt_，大西洋鲑专属列前缀为salm_，共享指标列保留原名
• 新增overlapNEW列标记共现状态：1表示共现，0表示非共现

解决方案（基于data.table） #

以下方法通过foverlaps实现时间窗口匹配，同时通过全连接逻辑保留所有观测记录：

library(data.table)

# 转换为data.table格式
setDT(positions)

# 1. 拆分物种数据并定义时间窗口
salmon_dt <- positions[Species == "Atlantic Salmon"]
salmon_dt[, `:=`(time = as.numeric(detection_timestamp_AST),
                 start = time - 180,  # 窗口左边界：当前时间减3分钟（180秒）
                 end = time + 180)]   # 窗口右边界：当前时间加3分钟

trout_dt <- positions[Species == "Brown Trout"]
trout_dt[, `:=`(time = as.numeric(detection_timestamp_AST),
                start = time,
                end = time)]  # 鳟鱼以自身检测时间为单点窗口

# 2. 设置键用于重叠匹配
setkey(trout_dt, start, end)

# 3. 匹配鲑鱼与鳟鱼的共现记录（保留所有鲑鱼记录，无匹配则填NA）
salmon_matches <- foverlaps(salmon_dt, trout_dt, type = "any", nomatch = NA)

# 4. 处理鲑鱼匹配结果的列名与共现标记
salmon_matches <- salmon_matches[, `:=`(
  overlapNEW = as.integer(!is.na(Fish_ID.1)),
  salm_Fish_ID = Fish_ID,
  salm_ROM_min = ROM_min,
  salm_Weight_g = Weight_g,
  salm_FL_cm = FL_cm,
  salm_TagLoc = TagLoc,
  salm_CWR_Loc = CWR_Loc,
  salm_meters_x = meters_x,
  salm_meters_y = meters_y
)][, c(names(positions), "time", "start", "end", "Fish_ID") := NULL]

# 重命名鳟鱼相关列
setnames(salmon_matches,
         old = c("Fish_ID.1", "ROM_min.1", "Weight_g.1", "FL_cm.1", "TagLoc.1", "CWR_Loc.1", "meters_x.1", "meters_y.1"),
         new = c("bt_Fish_ID", "bt_ROM_min", "bt_Weight_g", "bt_FL_cm", "bt_TagLoc", "bt_CWR_Loc", "bt_meters_x", "bt_meters_y"))

# 5. 处理未与鲑鱼共现的鳟鱼记录
trout_unmatched <- foverlaps(trout_dt, salmon_dt[, .(start, end, salm_Fish_ID = Fish_ID)], type = "any", nomatch = NA) %>%
  .[is.na(salm_Fish_ID)] %>%
  .[, `:=`(
    overlapNEW = 0,
    bt_Fish_ID = Fish_ID,
    bt_ROM_min = ROM_min,
    bt_Weight_g = Weight_g,
    bt_FL_cm = FL_cm,
    bt_TagLoc = TagLoc,
    bt_CWR_Loc = CWR_Loc,
    bt_meters_x = meters_x,
    bt_meters_y = meters_y
  )][, c(names(positions), "time", "start", "end", "Fish_ID") := NULL]

# 6. 合并所有结果并补充共享列
full_result <- rbindlist(list(salmon_matches, trout_unmatched), fill = TRUE)

# 提取共享列（非物种专属指标）
shared_cols <- setdiff(names(positions), c("Fish_ID", "Species", "ROM_min", "Weight_g", "FL_cm", "TagLoc", "CWR_Loc", "meters_x", "meters_y"))

# 为鲑鱼记录填充共享列
salmon_shared <- positions[Species == "Atlantic Salmon", ..shared_cols]
full_result[!is.na(salm_Fish_ID), (shared_cols) := salmon_shared[,.SD]]

# 为未匹配的鳟鱼记录填充共享列
trout_shared <- positions[Species == "Brown Trout", ..shared_cols]
full_result[!is.na(bt_Fish_ID) & is.na(salm_Fish_ID), (shared_cols) := trout_shared[,.SD]]

# 调整列顺序（可选，优化可读性）
setcolorder(full_result, c("overlapNEW", "salm_Fish_ID", "bt_Fish_ID", shared_cols, grep("salm_|bt_", names(full_result), value = TRUE)))

# 查看最终结果
print(full_result)

关键逻辑说明 #

• 通过foverlaps的nomatch=NA参数保留所有鲑鱼记录，无共现的鳟鱼列自动填充NA
• 单独提取未匹配的鳟鱼记录，确保所有观测都被保留
• 拆分并重命名物种专属列，严格遵循命名规则
• 补充共享指标列，保证每条记录的共享数据完整

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