I have one data frame and one look up table. What I want is to compare df_dat$value with df_lookup$threshold . If the value falls into threshold range, then create a new column transfer in df_dat so that its values are linearly interpolated from the transfer column in df_lookup
You can write a function to do this using findInterval. Below is an example. Why the code's long (beyond it being for explanation): df_lookup is not in a "tidy" format for your purpose. You want to use it to define spans, where the lower and upper bounds are important. Each span is an object, so each span should get its own row.
Still, I left df_lookup in it's original form, because I don't know how your data comes in.
library(dplyr)
library(tibble)
interpolate_transfer <- function(x) {
# A row id helps with lookup joining later
df_lookup <- tibble(
row_id = 1:3,
threshold = c(0, 100, 200),
transfer = c(0, 15, 35)
)
# Find the span each x belongs to
tibble(lower = findInterval(x, df_lookup[["threshold"]])) %>%
# Then get the lower and upper thresholds and transfer values
# Beware x values not inside any spans
mutate(upper = lower + 1) %>%
mutate(is_out = lower < 1 | upper > nrow(df_lookup)) %>%
mutate(
lower = ifelse(is_out, NA, lower),
upper = ifelse(is_out, NA, upper)
) %>%
left_join(df_lookup, by = c(lower = "row_id")) %>%
rename(
threshold_lower = threshold,
transfer_lower = transfer
) %>%
left_join(df_lookup, by = c(upper = "row_id")) %>%
rename(
threshold_upper = threshold,
transfer_upper = transfer
) %>%
# Find how far each x is along its threshold span (0 to 1)
# Then go that far along its transfer span
mutate(
unit_ratio = (x - threshold_lower) / (threshold_upper - threshold_lower)
) %>%
mutate(
value = transfer_lower + unit_ratio * (transfer_upper - transfer_lower)
) %>%
pull(value)
}
interpolate_transfer(c(0, 30, 105, 150))
# [1] 0.0 4.5 16.0 25.0