Mutate between dates from external lookup table

Question

I want to mutate a tibble which contains a list of dates by looking up an external 'dates lookup table':

date_lookup<-tibble(start = lubridate::dmy("01012020", "01022020"),
           end = lubridate::dmy("31012020", "28022020"),
           id = c(1, 2))

df<-tibble(record = c("A", "B"),
           date = lubridate::dmy("15022020", "03012020"))

If a date in df is between a start or end date in date_lookup I want to pull across the appropriate id from date_lookup.

I have tried the following:

df %>% rowwise() %>% 
  mutate(id = ifelse(between(date, date_lookup$start, date_lookup$end), date_lookup$id, NA))

But as you can see, the first row in df is showing as NA (it should be showing the number 2).

Expected output:

# A tibble: 2 x 3
# Rowwise: 
  record date          id
  <chr>  <date>     <dbl>
1 A      2020-02-15     2
2 B      2020-01-03     1

A dplyr solution would be preferable.

Answer 1

You can use fuzzyjoin here :

fuzzyjoin::fuzzy_inner_join(df, date_lookup, 
           by = c('date' = 'start', 'date' = 'end'), match_fun = list(`>=`, `<=`))

# A tibble: 2 x 5
#  record date       start      end           id
#  <chr>  <date>     <date>     <date>     <dbl>
#1 A      2020-02-15 2020-02-01 2020-02-28     2
#2 B      2020-01-03 2020-01-01 2020-01-31     1

---

Using tidyverse functions :

tidyr::crossing(df, date_lookup) %>% dplyr::filter(date >= start, date <= end)

Answer 2

Here is a data.table approach which uses non-equi joins and then assigns the value to the original data.frame.

library(data.table)
library(tibble)
date_lookup<-tibble(start = lubridate::dmy("01012020", "01022020"),
                    end = lubridate::dmy("31012020", "28022020"),
                    id = c(1, 2))

df<-tibble(record = c("A", "B"),
           date = lubridate::dmy("15022020", "03012020"))

setDT(date_lookup)
setDT(df)

df[date_lookup,
   on = .(date >= start,
          date <= end),
   id := id]

df
#>    record       date id
#> 1:      A 2020-02-15  2
#> 2:      B 2020-01-03  1