如何在R中基于最大X分钟时差的时间戳连接两个数据框

我需要根据时间戳将两个DataFrame合并为一个，两个数据框的时间戳存在数分钟的差异。要求：若两行的日期/时间在指定时差范围内匹配，则获取对应值；否则返回缺失值（NA）。我尝试过基于最大时差合并的方法，但未能正常运行。

数据示例 #

DF1（温度数据） #

DF2（湿度数据） #

可复现的R数据结构 #

DF1 <- structure(list(timstamp = structure(c(1624507200, 1624508100, 1624509000, 1624509900, 1624510800, 1624511700), class = c("POSIXct", "POSIXt"), tzone = ""), Temperature = c(26.6, 26.3, 26.1, 25.3, 25.2, 25.2)), row.names = c("1", "2", "3", "4", "5", "6"), class = "data.frame")

DF2 <- structure(list(timstamp = structure(c(1624485600, 1624486500, 
1624487400, 1624488300, 1624489200, 1624490100, 1624491000, 1624491900, 
1624492800, 1624493700, 1624494600, 1624495500, 1624496400, 1624497300, 
1624498200, 1624499100, 1624500000, 1624500900, 1624501800, 1624502700, 
1624503600, 1624504500, 1624505400, 1624506300, 1624507200, 1624508100, 
1624509000, 1624509900, 1624510800, 1624511700), class = c("POSIXct", 
"POSIXt"), tzone = ""), Temperature = c(26.6, 26.3, 
26.1, 25.3, 25.2, 25.2, 25.2, 25.2, 25.1, 25, 24.8, 24.8, 24.9, 
24.8, 24.8, 24.7, 24.7, 24.7, 24.7, 24.6, 24.5, 24.3, 24.3, 24.2, 
24.1, 24.1, 24.2, 24.6, 24.8, 25.3)), row.names = c("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30"), class = "data.frame")

解决方案 #

推荐使用fuzzyjoin包的fuzzy_left_join函数，它能灵活设置时间差匹配条件：

1. 安装并加载包：

install.packages("fuzzyjoin")
library(fuzzyjoin)

2. 定义时差阈值（示例设置为5分钟，可按需调整）：

time_threshold <- 5 * 60  # 转换为秒单位

3. 执行模糊合并：

merged_df <- fuzzy_left_join(
  DF1,
  DF2,
  by = "timstamp",
  match_fun = function(x, y) abs(difftime(x, y, units = "secs")) <= time_threshold
)

4. 清理结果（可选）：统一时间戳列并重命名，让输出更整洁：

merged_df$timstamp <- pmin(merged_df$timstamp.x, merged_df$timstamp.y, na.rm = TRUE)
merged_df <- merged_df[, c("timstamp", "Temperature.x", "Temperature.y")]
colnames(merged_df) <- c("timstamp", "Temperature_DF1", "Temperature_DF2")

如果偏好data.table，也可以用滚动合并实现：

library(data.table)
setDT(DF1)
setDT(DF2)

merged_dt <- DF1[DF2, on = .(timstamp), roll = time_threshold, rollends = c(TRUE, TRUE)]

注意：你的DF2示例中列名是DT，但可复现代码里列名是timstamp，实际使用时要统一列名，避免匹配错误。

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