R fuzzyjoin on most recent previous record

Question

I want to join two tables A & B by ID and find in B the most recent date that is anterior to A[date].

After some search it seems that fuzzyjoin allow to join on date ranges :

library(fuzzyjoin)
fuzzy_left_join(A, B,
              by = c("ID" = "ID",
                     "date" =  "date"), 
                      match_fun = list("==","<"))

Problem is that this will return many records (if they exist in B), when I just want the most recent.

Any idea on how to proceed ?

EDIT :

A <- data.frame(ID=c(1,2,3),
                date = c('2019-04-03','2019-05-13','2019-05-27'))

B <- data.frame(ID=c(1,1,2,3,4),
                date = c('2018-01-01','2019-01-01','2019-02-20','2019-06-01','2019-01-01'),
                value = c(1,1.5,1.2,3.7,4))

> A
  ID       date
1  1 2019-04-03
2  2 2019-05-13
3  3 2019-05-27

> B
  ID       date value
1  1 2018-01-01   1.0
2  1 2019-01-01   1.5
3  2 2019-02-20   1.2
4  3 2019-06-01   3.7
5  4 2019-01-01   4.0

Expected output :

  ID       date value
1  1 2019-04-03   1.5
2  2 2019-05-13   1.2
3  3 2019-05-27    NA

Could you add some data and a desired output? – s__ Oct 08 '19 at 12:09 — s__, Oct 08 '19 at 12:09
@s_t Edited the question with dummy data – cicero Oct 08 '19 at 12:31 — cicero, Oct 08 '19 at 12:31

score 2 · Answer 1 · answered Oct 09 '19 at 01:24

Another option using rolling join from data.table:

library(data.table)
setDT(A)[, date:=as.Date(date)]
setDT(B)[, date:=as.Date(date)]
B[A, on=.(ID, date), roll=Inf]

output:

   ID       date value
1:  1 2019-04-03   1.5
2:  2 2019-05-13   1.2
3:  3 2019-05-27    NA

score 1 · Accepted Answer · answered Oct 09 '19 at 04:59

We can do group_by A then select the last row in each group

library(fuzzyjoin)
fuzzy_left_join(A, B,
            by = c("ID" = "ID","date" = "date"), 
            match_fun = list(`==`,`>`)) %>% 
            group_by(ID.x) %>% 
            slice(n()) %>% 
            select(-ends_with('y')) %>%
            rename_at(vars(ends_with('x')), ~gsub('.x','',.))

# A tibble: 3 x 3
# Groups:   ID.x [3]
   ID   date       value
  <dbl> <date>     <dbl>
1     1 2019-04-03   1.5
2     2 2019-05-13   1.2
3     3 2019-05-27  NA

R fuzzyjoin on most recent previous record

2 Answers2