I have a dataset of reservoir volumes (9 columns for 9 reservoirs) and I want to use a lookup (or similar functions) in R to convert the volumes to levels using a rating table (my rating table has got two columns, volumes and levels). If I use lookup, it does not interlope between rating table values and generates series of N/A.
Note: the number of rows in rating table is different with the number of rows in the dataset.
My code is similar to this
storagelevel1 <- lookup(storagevolume[,1],storagerating)
This code lookup the level from storagerating (two columns, volumes and levels) using the volumes from storagevolume but it does not interpolate in the storageraitng.
How can I do this to interpolate or at least find the closest match in the lookup table? Thanks
As 42 commented, very difficult to provide you with help if we don't know what you are working with. That said, does this code give you any insight?
storagerating <- data.frame(volumes = c(10, 100, 1000, 10000),
levels = c("A","B","C","D"))
# volumes levels
#1 10 A
#2 100 B
#3 1000 C
#4 10000 D
z <- 888 # a random volume
storagerating$levels[which.min(abs(z - storagerating$volumes))] # closest rating
#[1] C
#Levels: A B C D
EDIT: for a vectorized solution
z <- round(runif(300, 1, 10000)) # a random volumes
# OPTION 1: sapply
z_levels1 <- sapply(z, function(x) storagerating$levels[which.min(abs(x - storagerating$volumes))])
z_levels1
# OPTION 2: for loop
z_levels2 <- vector("numeric",length(z))
for(i in 1:length(z)){
z_levels2[i] <- storagerating$levels[which.min(abs(z[i] - storagerating$volumes))]
}
storagerating$levels[z_levels2]
# OPTION 3: make a function with sapply
lookup <- function(x, volumes){
sapply(x, function(x) which.min(abs(x - volumes)))
}
storagerating$levels[lookup(z, storagerating$volumes)]
EDIT2: Interpolation
storagerating <- data.frame(volumes = seq.int(100,400,100),
levels = c(1:4))
storagerating # given this
# volumes levels
#1 100 1
#2 200 2
#3 300 3
#4 400 4
mod <- lm(levels ~ volumes, data = storagerating) # linear model example
df_new <- data.frame(volumes = z) # use our model results to estimate
levels_new <- predict(mod, newdata = df_new) # must be data.frame with same var name
storagerating_new <- cbind(df_new, levels_new)
head(storagerating_new); tail(storagerating_new)
# volumes levels_new
#1 1 0.01
#2 3 0.03
#3 5 0.05
#4 7 0.07
#5 9 0.09
#6 11 0.11
# volumes levels_new
#195 389 3.89
#196 391 3.91
#197 393 3.93
#198 395 3.95
#199 397 3.97
#200 399 3.99