按分组匹配并合并数据集：匹配df1前最近的df2时间戳

原始数据集 #

df1 <- data.frame(
  Timestamp = as.POSIXct(c('2023-09-22 09:30:00', '2023-09-22 11:30:00', '2023-09-23 11:45:00', '2023-09-23 12:45:00')),
  group = c('A', 'A', 'B', 'B'))

df2 <- data.frame(
  Timestamp2 = as.POSIXct(c('2023-09-22 09:29:00', '2023-09-22 11:31:00', '2023-09-23 11:44:00', '2023-09-23 12:50:00')),
  group = c('A', 'A', 'B', 'B'),
  type = c('01', '33', '22', '98'))

需求说明 #

按group分组，为df1中的每个Timestamp，找到df2中同组、时间在该Timestamp之前且距离最近的Timestamp2，并将df2对应行的信息合并到df1中。需严格按分组处理，同组可能存在重复时间戳。

期望输出 #

result <- data.frame(
  Timestamp = as.POSIXct(c('2023-09-22 09:30:00', '2023-09-22 11:30:00', '2023-09-23 11:45:00', '2023-09-23 12:45:00')),
  Timestamp2 = as.POSIXct(c('2023-09-22 09:29:00', '2023-09-22 09:29:00', '2023-09-23 11:44:00', '2023-09-23 11:44:00')),
  group = c('A', 'A', 'B', 'B'),
  type = c('01', '01', '22', '22'))

解决方案 #

方法1：使用dplyr + fuzzyjoin包 #

先安装并加载所需包：

install.packages(c("dplyr", "fuzzyjoin"))
library(dplyr)
library(fuzzyjoin)

通过分组模糊匹配，筛选出每个df1行对应的最近前置时间戳：

result <- df1 %>%
  group_by(group) %>%
  fuzzy_left_join(df2, 
                  by = c("group" = "group", "Timestamp" = "Timestamp2"),
                  match_fun = list(`==`, `>`)) %>%
  mutate(time_diff = Timestamp - Timestamp2) %>%
  filter(time_diff == min(time_diff)) %>%
  ungroup() %>%
  select(Timestamp, Timestamp2, group, type) %>%
  distinct(Timestamp, .keep_all = TRUE)

方法2：使用data.table包（高效处理大数据） #

安装并加载data.table：

install.packages("data.table")
library(data.table)

转换为data.table格式后，按分组进行滚动匹配：

setDT(df1)
setDT(df2)

# 按group和Timestamp2排序df2
setkey(df2, group, Timestamp2)

# 滚动匹配最近的前置时间戳
result <- df2[df1, on = .(group, Timestamp2 < Timestamp), roll = TRUE, 
              .(Timestamp = i.Timestamp, Timestamp2 = x.Timestamp2, group, type)]

两种方法都能得到符合要求的结果，其中data.table的滚动匹配在处理大规模数据集时效率更高。

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