Revising the code on a conditional join with a lookup table (mapping)

[mfherman]

If you want to stay in the tidy-world, you could check out the fuzzyjoin package which allows you to specify non exact matching joins.

Tidy way to range join tables, on an interval of dates

You can do it with the fuzzyjoin package, which implements various not quite exact matching joins in dplyr syntax. library(tidyverse) library(fuzzyjoin) df1 <- tibble::tribble( ~id, ~category, ~date, 1L, "a", "7/1/2000", 2L, "b", "11/1/2000", 3L, "c", "7/1/2002" ) %>% mutate(date = as.Date(date, format = "%m/%d/%Y")) df2 <- tibble::tribble( ~category, ~other_info, ~start, ~end, "a", "x", "1/1/2000", "12/31/2000", "b", "y", "1/1/2001", "12/31/2001", "c", "z", "1/1/2002", "12/31/2002" ) %>% mutate_at(vars(start, end), as.Date, format = "%m/%d/%Y") fuzzy_left_join( df1, df2, by = c( "category" = "c…

There are some other R solutions to a similar problem written up here, including using data.table::foverlaps().

https://www.mango-solutions.com/in-between-a-rock-and-a-conditional-join/