r - Doing a left join with exact values plus closest values

Question

I have two data sets:

table1 <- data.frame(id=c(1000,1001,1002,1003), 
                    date=as.POSIXct(c("2012-05-13","2012-09-23","2011-04-09","2014-11-08"))) 

table2 <- data.frame(id2=c(1000,1000,1001,1002,1003,1003), 
                   date2=as.POSIXct(c("2012-05-13","2012-05-16","2012-09-24","2011-04-15","2014-11-09", "2014-11-10"))) 

I want to do a left join on table1 based on matching ID and Date, however not all dates have an exact match so I was wondering how could I join the dates based on the closest day? For example for id 1001, "2012-09-23" would match "2012-09-24" for id2 1001 since it is the only date for id2 and for 1003 the "2014-11-08" would match "2014-11-09" of 1003 for id2 since it is the closest day.

Desired result:

  id       date      date2
1 1000 2012-05-13 2012-05-13
2 1001 2012-09-23 2012-09-24
3 1002 2011-04-09 2011-04-15
4 1003 2014-11-08 2014-11-09

标签： r

Answer 1

我还建议您遵循非 equidata.table连接，但如果您出于某种原因想要坚持使用dplyr并且您的数据不是很大或者您有足够的内存，您也可以尝试：

library(dplyr)

table1 %>%
  left_join(table2, by = c("id" = "id2")) %>%
  group_by(id) %>%
  slice(which.min(abs(date - date2)))

输出：

# A tibble: 4 x 3
# Groups:   id [4]
     id date                date2              
  <dbl> <dttm>              <dttm>             
1  1000 2012-05-13 00:00:00 2012-05-13 00:00:00
2  1001 2012-09-23 00:00:00 2012-09-24 00:00:00
3  1002 2011-04-09 00:00:00 2011-04-15 00:00:00
4  1003 2014-11-08 00:00:00 2014-11-09 00:00:00