Archive for R

Excel VLOOKUP in R via Rolling Join

Imagine a car park with different parking costs for parking per hour or part thereof. Assume also there is no pattern, thus a mapping table of hour -> cost:

hr cost
0 0.30
1 0.60
2 0.80
3 1.20
4 1.30
5+ 1.60

Parking beyond 5 hours will max your charges at $1.60.

In Excel there is the VLOOKUP function, with Range_lookup=TRUE to find the nearest match.

In R we can do a rolling join on a data table. Without the roll, it works like Range_lookup=FALSE; it finds an exact match.

> # install.packages("data.table")
> library(data.table)
> fees <- data.table(hr=c(0, 1, 2, 3, 4, 5), 
                   cost=c(0.3, 0.6, 0.8, 1.2, 1.3, 1.6))
> fees
   hr cost
1:  0  0.3
2:  1  0.6
3:  2  0.8
4:  3  1.2
5:  4  1.3
6:  5  1.6
> query <- data.table(parked=c(0.4, 1.5, 2, 2.14, 4.5, 10))
> setkey(fees, hr)
> fees[query]
      hr cost
1:  0.40   NA
2:  1.50   NA
3:  2.00  0.8
4:  2.14   NA
5:  4.50   NA
6: 10.00   NA
> fees[query, roll=TRUE]
      hr cost
1:  0.40  0.3
2:  1.50  0.6
3:  2.00  0.8
4:  2.14  0.8
5:  6.00  1.6
6: 10.00  1.6

Comments