如何基于另一数据框的时间区间筛选数据并合并统计结果

需求梳理 #

• 现有两个数据框：潜水汇总表dfsum、潜水明细表dfdive
• 从dfdive中筛选满足以下任一时间条件的记录：
  • 时间处于dfsum$hour_before与dfsum$start之间
  • 时间处于dfsum$dive_end与dfsum$hour_after之间
• 对筛选出的记录，针对Depth和Temp字段计算标准差(sd)、均值(mean)、中位数(median)、最小值(min)、最大值(max)
• 将统计结果按dive（潜水编号）和Ptt（动物ID）合并回dfsum

原代码问题分析 #

你提供的代码未建立dfsum与dfdive的对应关联，直接在filter中引用dfsum的向量会导致长度不匹配，筛选逻辑失效——每个dfdive的记录需要匹配到对应Ptt和dive的时间区间，而非全局的时间向量。

解决方案（dplyr实现） #

步骤1：加载依赖包并统一列名 #

library(dplyr)
library(tidyr)

# 统一ID列名（dfsum中是ptt，dfdive中是Ptt）
dfsum <- dfsum %>% rename(Ptt = ptt)

步骤2：关联数据并筛选符合条件的记录 #

# 按Ptt关联两个数据框，保留所有汇总表记录及匹配的明细记录
merged_data <- dfsum %>%
  left_join(dfdive, by = "Ptt") %>%
  # 筛选满足任一时间条件的行
  filter(
    (Date >= hour_before & Date <= start) | 
    (Date >= dive_end & Date <= hour_after)
  )

步骤3：分组计算统计量 #

# 按Ptt和dive分组，计算Depth和Temp的统计指标
stats_summary <- merged_data %>%
  group_by(Ptt, dive) %>%
  summarise(
    # Depth相关统计
    Depth_sd = sd(Depth, na.rm = TRUE),
    Depth_mean = mean(Depth, na.rm = TRUE),
    Depth_median = median(Depth, na.rm = TRUE),
    Depth_min = min(Depth, na.rm = TRUE),
    Depth_max = max(Depth, na.rm = TRUE),
    # Temp相关统计
    Temp_sd = sd(Temp, na.rm = TRUE),
    Temp_mean = mean(Temp, na.rm = TRUE),
    Temp_median = median(Temp, na.rm = TRUE),
    Temp_min = min(Temp, na.rm = TRUE),
    Temp_max = max(Temp, na.rm = TRUE),
    .groups = "drop"  # 取消分组
  )

步骤4：合并统计结果回汇总表 #

final_df <- dfsum %>%
  left_join(stats_summary, by = c("Ptt", "dive"))

# 查看结果
print(final_df)

大数据量优化方案（data.table实现） #

如果涉及22个动物的海量数据，用data.table可提升处理效率：

library(data.table)

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

# 统一列名
setnames(dfsum, "ptt", "Ptt")

# 关联并筛选数据
merged_dt <- dfsum[dfdive, on = "Ptt", allow.cartesian = TRUE][
  (Date >= hour_before & Date <= start) | (Date >= dive_end & Date <= hour_after)
]

# 分组计算统计量
stats_dt <- merged_dt[, .(
  Depth_sd = sd(Depth, na.rm = TRUE),
  Depth_mean = mean(Depth, na.rm = TRUE),
  Depth_median = median(Depth, na.rm = TRUE),
  Depth_min = min(Depth, na.rm = TRUE),
  Depth_max = max(Depth, na.rm = TRUE),
  Temp_sd = sd(Temp, na.rm = TRUE),
  Temp_mean = mean(Temp, na.rm = TRUE),
  Temp_median = median(Temp, na.rm = TRUE),
  Temp_min = min(Temp, na.rm = TRUE),
  Temp_max = max(Temp, na.rm = TRUE)
), by = .(Ptt, dive)]

# 合并回汇总表
final_dt <- dfsum[stats_dt, on = c("Ptt", "dive")]

注意事项 #

• 确保两个数据框的Ptt格式一致（如均为字符型），避免关联失败
• 统计时添加na.rm = TRUE，防止缺失值导致统计结果异常
• 示例数据中dfdive的时间分辨率已改为10分钟，实际处理原始10秒数据时逻辑一致

内容的提问来源于stack exchange，提问作者Meg.abytes