[SOLVED] Inexact joining data based on greater equal condition

Inexact joining data based on greater equal condition

I have some values in df:

# A tibble: 7 × 1
   var1
  <dbl>
1     0
2    10
3    20
4   210
5   230
6   266
7   267

that I would like to compare to a second dataframe called value_lookup

# A tibble: 4 × 2
   var1 value
  <dbl> <dbl>
1     0     0
2   200    10
3   230    20
4   260    30

In particual I would like to make a join based on >= meaning that a value that is greater or equal to the number in var1 gets a values of x. E.g. take the number 210 of the orginal dataframe. Since it is >= 200 and <230 it would get a value of 10.

Here is the expected output:

  var1 value
1    0     0
2   10     0
3   20     0
4  210    10
5  230    20
6  266    30
7  267    30

I thought it should be doable using {fuzzyjoin} but I cannot get it done.

value_lookup <- tibble(var1 = c(0, 200,230,260),
                       value = c(0,10,20,30))

df <- tibble(var1 = c(0,10,20,210,230,266,267))

library(fuzzyjoin)
fuzzyjoin::fuzzy_left_join(
  x = df, 
  y = value_lookup ,
  by = "var1",
  match_fun = list(`>=`)
)

Solution

An option is also findInterval:

df$value <- value_lookup$value[findInterval(df$var1, value_lookup$var1)]

Output:

  var1 value
1    0     0
2   10     0
3   20     0
4  210    10
5  230    20
6  266    30
7  267    30

As you're mentioning joins, you could also do a rolling join via data.table with the argument roll = T which would look for same or closest value preceding var1 in your df:

library(data.table)

setDT(value_lookup)[setDT(df), on = 'var1', roll = T]