R lookup function and merge

user_10192593 0
Viewed 38

I'm trying to find the appropriate prices in dt for all values in vector a. It's so simple, yet I can't seem to figure it out. It should be a multiple merge...

 a <- 1:10
 
 min <- c( 0, 2, 4, 7)
 max <- c(1, 3, 6, 10)
 price <- c(2, 4, 6, 8)
 
 dt <- data.frame(min = min, max = max, price=price)

This is the output I would like

enter image description here

dplyr r
4 Answers

Using the data.table package, you can do this

library(data.table)
setDT(dt)[data.table(a), .(a = min, price), on = .(min <= a, max >= a)]

Output

     a price
 1:  1     2
 2:  2     4
 3:  3     4
 4:  4     6
 5:  5     6
 6:  6     6
 7:  7     8
 8:  8     8
 9:  9     8
10: 10     8
user_108024990

Are you looking for this:

library(dplyr)
library(purrr)
library(tidyr)
library(tibble)
dt %>% mutate(range = map2(min, max, `:`)) %>% unnest(range) %>% 
inner_join(as.tibble(a), by = c('range' = 'value')) %>% select('a' = range, price)
# A tibble: 10 x 2
       a price
   <int> <dbl>
 1     1     2
 2     2     4
 3     3     4
 4     4     6
 5     5     6
 6     6     6
 7     7     8
 8     8     8
 9     9     8
10    10     8
user_107227520

Here's a base R "lookup and merge" approach:

ranges <- mapply(seq, min, max)
values <- mapply(rep, price, lengths(ranges))

lookup <- data.frame(a = unlist(ranges), values = unlist(values))

merge(data.frame(a, price_lookup))

    a values
1   1      2
2   2      4
3   3      4
4   4      6
5   5      6
6   6      6
7   7      8
8   8      8
9   9      8
10 10      8
user_141611890

We can use fuzzy_join

library(fuzzyjoin)
fuzzy_left_join(dt, tibble(a), by = c('min' = 'a', 'max' = 'a'),
       match_fun = list(`<=`, `>=`)) %>% 
     select(a, price)
#    a price
#1   1     2
#2   2     4
#3   3     4
#4   4     6
#5   5     6
#6   6     6
#7   7     8
#8   8     8
#9   9     8
#10 10     8
user_37322710
Related