Check if column value is in between (range) of two other column values

We can loop over each x$number using sapply and check if it lies in range of any of y$number1 and y$number2 and give the value accordingly.

x$found <- ifelse(sapply(x$number, function(p) 
                 any(y$number1 <= p & y$number2 >= p)),"YES", NA)
x

#  id number found
#1  1   5225   YES
#2  2   2222  <NA>
#3  3   3121   YES

Using the same logic but with replace

x$found <- replace(x$found, 
         sapply(x$number, function(p) any(y$number1 <= p & y$number2 >= p)), "YES")

EDIT

If we want to also compare the id value we could do

x$found <- ifelse(sapply(seq_along(x$number), function(i) {
           inds <- y$number1 <= x$number[i] & y$number2 >= x$number[i]
           any(inds) & (x$id[i] == y$id[which.max(inds)])
           }), "YES", NA)

x$found
#[1] "YES" NA    "YES"

Here is an option using fuzzy_join

library(fuzzy_join)
library(dplyr)
fuzzy_left_join(X, Y[-1], by = c("number" = "number1", "number" = "number2"), 
     match_fun  =list(`>=`, `<=`)) %>% 
    mutate(found = c(NA, "YES")[(!is.na(number1)) + 1]) %>% 
    select(names(X))
#    id number found
#1  1   5225   YES
#2  2   2222  <NA>
#3  3   3121   YES

Or another option is a non-equi join with data.table

library(data.table)
setDT(X)[, found := NULL]
X[Y, found := "YES", on = .(number >= number1, number <= number2)]
X
#   id number found
#1:  1   5225   YES
#2:  2   2222  <NA>
#3:  3   3121   YES

data

X <- structure(list(id = 1:3, number = c(5225L, 2222L, 3121L), found = c(NA, 
  NA, NA)), class = "data.frame", row.names = c(NA, -3L))

Y <- structure(list(id = 1:3, number1 = c(4000L, 2500L, 7000L), number2 = c(6000L, 
    3300L, 8000L)), class = "data.frame", row.names = c(NA, -3L))

Tags:

R