R语言如何按3个月内最近日期全连接两个dataframe且避免行重复

核心需求 #

• 仅当两表同ID对应的日期差值不超过3个月时，按最近日期完成匹配关联
• 执行全连接操作，必须完整保留df1和df2的所有行，无匹配的字段留空
• 若3个月时间窗口内存在多个可选匹配行，禁止重复复制另一张表的行，保证每行仅匹配一次

预期输出示例 #

ID  Date.df1     Date.df2   V1_df1  V2_df1  V1_df2   V2_df2
100    NA       07/11/2015    NA      NA      93.3    93.3
100 01/11/2015  03/11/2015    9.3    10.6     93.3    95.5
100 23/12/2016  27/12/2016     8     10.3     97.78   97.78
100 04/11/2017  13/11/2017    9.3     11      98.9    98.89
100 09/11/2018      NA       10.3    9.6       NA     NA
101 07/11/2015  07/11/2015     7     6.6      97.78   97.78
101 21/01/2017  19/12/2016     6     7.3      95.7    95.5
101 18/11/2017      NA        7.6    6.6       NA     NA
101 22/01/2019      NA        6.5     7        NA     NA
102 27/09/2017  26/08/2017     5      7       94.8    94.8
102 01/10/2018      NA        8.6    7.3       NA      NA
102 15/09/2019      NA         9      8        NA      NA
103     NA      14/11/2015     NA     NA      97.7     97

原有方案问题分析 #

dplyr版本问题 #

原有代码先全连接后直接过滤符合3个月时间差的行，直接把日期差超过3个月、本该留空匹配的行删掉了；且仅按df1的日期分组去重，没有处理df2的未匹配行，最终导致两边都有行丢失。
原有代码：

df1 %>%
  full_join(df2, by = "ID") %>%
  mutate(diff = abs(Date.df2 - Date.df1)) %>%
  filter(
    (!is.na(Date.df2) &
      (is.na(Date.df1) | (diff < months(3)))) |
    (!is.na(Date.df1) &
      (is.na(Date.df2) | (diff < months(3))))  
  ) %>%
  arrange(ID, Date.df1, Date.df2) %>%
  group_by(ID, Date.df1) %>%
  filter(row_number() == 1) %>%
  ungroup()

data.table版本问题 #

原有滚动连接仅做了右连接，没有保留df1中无匹配的行，且未设置滚动匹配的最大时间范围，导致超出3个月的日期也被强制匹配，同时没有去重逻辑，出现重复匹配的问题。
原有代码：

library(data.table)

# 转为data.table并添加连接用日期列，保留原始日期字段
setDT(df1)[, join_date := Date.df1]
setDT(df2)[, join_date := Date.df2]

# 按ID和日期滚动近邻匹配
df1[df2, on = .(ID, join_date), roll = "nearest"] [order(ID, join_date)]

修复后的实现代码 #

dplyr修复版 #

library(dplyr)
library(lubridate)

# 1. 给df1和df2添加唯一行号，避免重复匹配
df1 <- df1 %>% mutate(row_id1 = row_number())
df2 <- df2 %>% mutate(row_id2 = row_number())

# 2. 先匹配df1的所有行，每个df1行仅匹配最近的符合3个月窗口的df2行
match_df1 <- df1 %>%
  left_join(df2, by = "ID") %>%
  mutate(diff = abs(Date.df2 - Date.df1)) %>%
  filter(is.na(diff) | diff < months(3)) %>%
  arrange(ID, row_id1, diff) %>%
  group_by(ID, row_id1) %>%
  slice_head(n = 1) %>%
  ungroup()

# 3. 提取已经被匹配的df2行号，剩下的df2行单独补充进来
used_df2_rows <- unique(match_df1$row_id2)
unmatch_df2 <- df2 %>%
  filter(!row_id2 %in% used_df2_rows) %>%
  mutate(
    row_id1 = NA_integer_,
    Date.df1 = as.Date(NA),
    V1_df1 = NA_real_,
    V2_df1 = NA_real_
  )

# 4. 合并两部分结果，排序输出
final_res <- bind_rows(match_df1, unmatch_df2) %>%
  arrange(ID, Date.df1, Date.df2) %>%
  select(ID, Date.df1, Date.df2, V1_df1, V2_df1, V1_df2, V2_df2)

data.table修复版 #

library(data.table)
library(lubridate)

setDT(df1)
setDT(df2)

# 1. 添加行号
df1[, row_id1 := .I]
df2[, row_id2 := .I]

# 2. 滚动匹配，限制3个月窗口
res1 <- df2[df1, on = .(ID, Date.df2 = Date.df1), roll = "nearest", nomatch = NA]
res1[, diff := abs(Date.df2 - i.Date.df1)]
res1[diff >= months(3), c("Date.df2", "V1_df2", "V2_df2", "row_id2") := .(as.Date(NA), NA_real_, NA_real_, NA_integer_)]
setnames(res1, "i.Date.df1", "Date.df1")

# 3. 补充未匹配的df2行
used_row2 <- unique(res1$row_id2)
res2 <- df2[!row_id2 %in% used_row2]
res2[, c("Date.df1", "V1_df1", "V2_df1", "row_id1") := .(as.Date(NA), NA_real_, NA_real_, NA_integer_)]

# 4. 合并输出
final_res <- rbind(res1, res2, fill = TRUE)
setorder(final_res, ID, Date.df1, Date.df2)
final_res <- final_res[, .(ID, Date.df1, Date.df2, V1_df1, V2_df1, V1_df2, V2_df2)]

示例数据集 #

df1 <- structure(list(ID = c(100L, 100L, 100L, 100L, 101L, 101L, 101L, 
101L, 102L, 102L, 102L), Date.df1 = structure(c(16740, 17158, 
17474, 17844, 16746, 17187, 17488, 17918, 17436, 17805, 18154
), class = "Date"), V1_df1 = c(9.3, 8, 9.3, 10.3, 7, 6, 7.6, 
6.5, 5, 8.6, 9), V2_df1 = c(10.6, 10.3, 11, 9.6, 6.6, 7.3, 6.6, 
7, 7, 7.3, 8)), row.names = c(NA, -11L), class = "data.frame")

df2 <- structure(list(ID = c(100L, 100L, 100L, 100L, 101L, 101L, 102L, 
103L), Date.df2 = structure(c(16742, 16746, 17162, 17483, 16746, 
17154, 17404, 16753), class = "Date"), V1_df2 = c(93.3, 93.3, 
97.78, 98.9, 97.78, 95.7, 94.8, 97.7), V2_df2 = c(95.5, 93.3, 
97.78, 98.89, 97.78, 95.5, 94.8, 97)), row.names = c(NA, -8L), class = "data.frame")

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