在R中按最近POSIXct时间戳合并数据集的问题

核心需求回顾 #

你需要将5分钟间隔的df1（含Event列）按最近时间戳匹配到10分钟间隔的df2（含Temperature列），要求保留df2的所有行且无重复，匹配时需按TimeDateAnimal分组（避免跨动物匹配）。

问题根源 #

之前使用merge(..., roll = "nearest", all = T)或fuzzyjoin时出现重复，大概率是因为：

• 未按TimeDateAnimal分组匹配，导致跨组匹配产生冗余
• 未限制主表为df2，双向保留了两个表的行
• 未对同一主表行的多个候选匹配做筛选

具体解决方法 #

以下是两种可靠的实现方式，均基于示例数据演示：

示例数据构造

library(data.table)
set.seed(123)

# df1：5分钟间隔的事件数据
df1 <- data.table(
  TimeDate = seq(as.POSIXct("2024-01-01 00:00:00"), by = "5 mins", length.out = 10),
  TimeDateAnimal = rep(c("A", "B"), each = 5),
  Event = sample(c("Feed", "Drink", "Rest"), 10, replace = TRUE)
)

# df2：10分钟间隔的温度数据
df2 <- data.table(
  TimeDate = seq(as.POSIXct("2024-01-01 00:00:00"), by = "10 mins", length.out = 5),
  TimeDateAnimal = rep(c("A", "B"), length.out = 5),
  Temperature = runif(5, 36, 39)
)

方法1：data.table 分组精准匹配

利用data.table的键索引和滚动匹配，严格以df2为主表匹配最近行：

# 设置分组+时间的复合键，确保按动物分组匹配时间
setkey(df1, TimeDateAnimal, TimeDate)
setkey(df2, TimeDateAnimal, TimeDate)

# 从df2出发，匹配df1中最近的行，mult="first"避免一个df2行对应多个df1行
result_dt <- df2[df1, roll = "nearest", mult = "first"]

# 整理列顺序，保留df2的核心列+匹配到的Event
result_dt <- result_dt[, .(TimeDate = i.TimeDate, TimeDateAnimal, Temperature, Event)]

# 最终去重，确保每个动物的时间戳唯一
result_dt <- unique(result_dt, by = c("TimeDateAnimal", "TimeDate"))

方法2：dplyr + fuzzyjoin 筛选最小时间差

通过计算时间差，为每个df2行筛选出最近的df1行：

library(dplyr)
library(fuzzyjoin)

result_dplyr <- df2 %>%
  # 按动物分组，避免跨组匹配
  group_by(TimeDateAnimal) %>%
  # 模糊左连接，限制时间差在5分钟内（符合你的数据间隔）
  fuzzy_left_join(
    df1,
    by = c("TimeDateAnimal" = "TimeDateAnimal", "TimeDate" = "TimeDate"),
    match_fun = list(`==`, function(x, y) abs(difftime(x, y, units = "mins")) <= 5)
  ) %>%
  # 计算时间差，用于筛选最近行
  mutate(time_diff = abs(difftime(TimeDate.x, TimeDate.y, units = "mins"))) %>%
  # 按动物+df2时间戳分组，取时间差最小的匹配行
  group_by(TimeDateAnimal, TimeDate.x) %>%
  slice(which.min(time_diff)) %>%
  ungroup() %>%
  # 整理列名和顺序
  select(TimeDate = TimeDate.x, TimeDateAnimal, Temperature, Event) %>%
  # 确保无重复行
  distinct(TimeDate, TimeDateAnimal, .keep_all = TRUE)

关键注意事项 #

• 必须按TimeDateAnimal分组匹配，否则会出现不同动物的时间交叉匹配
• 始终以df2为主表做左连接，避免保留df1的冗余行
• 对同一主表行的多个候选匹配，必须通过mult="first"或which.min(time_diff)筛选唯一结果

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