Doing a left join with exact values plus closest values

1
votes

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
2
r
fuzzyjoin is your friend. look up cran.r-project.org/web/packages/fuzzyjoin/README.html and look at this post: community.rstudio.com/t/… - infominer
this is even easier! left_join(table1,table2, by=c("id" = "id2")) - infominer
Possible duplicate of Merge nearest date, and related variables from a another dataframe by group - milan

2 Answers

2
votes

I'd also recommend you to follow the non-equi data.table joins, but in case you'd like to for whatever reason stick with dplyr and your data isn't really big or you have enough memory, you could also try:

library(dplyr)

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

Output:

# 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
0
votes

Use data.table to perform a rolling join to the nearest value. DT1 is updated by reference, so it shoul be very fast, even on large/big data

library(data.table)

sample data

dt1 <- as.data.table(table1)
dt2 <- as.data.table(table2)

code

dt1[, date2 := dt2[dt1, date2, on = c("id2 == id", "date2 == date"), roll = "nearest"]][]

output

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