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))