CODEWITHSUNDEEP
r
Jason
Jason

Reputation: 23

How to Join two tables with conditions

Question How would I mutate Lookup_Values with the corresponding factor from Factors table. Where the factor would be if n is greater than or equal to Factors$lowN, but less than highN. This is similar to Vlookup(...,True)

lookup_values <- data.frame(n = c(4,7,15))
n
4
7
15 
factors<- data.frame(lowN=c(1,9),
                         highN=c(9,20),
                         factor=c(1,2))
lowN highN factor
1 9 1
9 20 2

Desired output would be

n Factor
4 1
7 1
15 2

Upvotes: 0

Views: 24

Answers (1)

akrun
akrun

Reputation: 886948

We can use non-equi join from data.table

library(data.table)
setDT(lookup_values)[factors, Factor := factor, on = .(n >= lowN, n < highN)]

-output

lookup_values
#    n Factor
#1:  4      1
#2:  7      1
#3: 15      2

Or using fuzzyjoin

library(fuzzyjoin)
fuzzy_left_join(lookup_values, factors, by = c("n" = "lowN", "n" = "highN"), 
   match_fun = list(`>=`, `<`)) %>%
 select(n, Factor = factor)
#   n Factor
#1  4      1
#2  7      1
#3 15      2

Upvotes: 0

Related Questions