This is similar to the following question. However, I need to do a few more steps:
• Group by columns ID and order
• For every val in df_dat, look up the corresponding ratio in the df_lookup table with the following conditions:
o If val < min(df_lookup$val), set new_ratio = min(df_lookup$ratio)
o If val > max(df_lookup$val), set new_ratio = max(df_lookup$ratio)
o If val falls within df_lookup$val range, do a simple linear interpolation
My data:
library(dplyr)
df_lookup <- tribble(
~ID, ~order, ~pct, ~val, ~ratio,
"batch1", 1, 1, 1, 0.2,
"batch1", 1, 10, 8, 0.5,
"batch1", 1, 25, 25, 1.2,
"batch2", 2, 1, 2, 0.1,
"batch2", 2, 10, 15, 0.75,
"batch2", 2, 25, 33, 1.5,
"batch2", 2, 50, 55, 3.2,
)
df_lookup
#> # A tibble: 7 x 5
#> ID order pct val ratio
#> <chr> <dbl> <dbl> <dbl> <dbl>
#> 1 batch1 1 1 1 0.2
#> 2 batch1 1 10 8 0.5
#> 3 batch1 1 25 25 1.2
#> 4 batch2 2 1 2 0.1
#> 5 batch2 2 10 15 0.75
#> 6 batch2 2 25 33 1.5
#> 7 batch2 2 50 55 3.2
df_dat <- tribble(
~order, ~ID, ~val,
1, "batch1", 0.1,
1, "batch1", 30,
1, "batch1", 2,
1, "batch1", 12,
2, "batch1", 45,
2, "batch2", 1.5,
2, "batch2", 30,
2, "batch2", 13,
2, "batch2", 60,
)
df_dat
#> # A tibble: 9 x 3
#> order ID val
#> <dbl> <chr> <dbl>
#> 1 1 batch1 0.1
#> 2 1 batch1 30
#> 3 1 batch1 2
#> 4 1 batch1 12
#> 5 2 batch1 45
#> 6 2 batch2 1.5
#> 7 2 batch2 30
#> 8 2 batch2 13
#> 9 2 batch2 60
The previous solution did not respect the grouping which generated wrong results.
Example:
For order = 2 and ID = batch1, new_ratio should be NA as those conditions aren’t in the lookup table.
For order = 1, ID = batch2 and val = 30, new_ratio should not be higher than 1.2 (max ratio value).
For order = 1, ID = batch1 and val = 2, new_ratio = 0.243 which is the interpolated ratio value between 0.2 and 0.5.
Any help appreciated!
#error
df_dat %>%
group_by(ID, order) %>%
mutate(new_ratio = with(df_lookup, approx(val, ratio, val))$y)
#> Error: Column `new_ratio` must be length 4 (the group size) or one, not 7
#wrong output
df_dat %>%
group_by(ID, order) %>%
mutate(val1 = val) %>%
mutate(new_ratio = with(df_lookup, approx(val, ratio, val1))$y)
#> # A tibble: 9 x 5
#> # Groups: ID, order [3]
#> order ID val val1 new_ratio
#> <dbl> <chr> <dbl> <dbl> <dbl>
#> 1 1 batch1 0.1 0.1 NA
#> 2 1 batch1 30 30 1.39
#> 3 1 batch1 2 2 0.1
#> 4 1 batch1 12 12 0.643
#> 5 2 batch1 45 45 2.43
#> 6 2 batch2 1.5 1.5 0.15
#> 7 2 batch2 30 30 1.39
#> 8 2 batch2 13 13 0.679
#> 9 2 batch2 60 60 NA
Expected output
# A tibble: 9 x 4
order ID val new_ratio
<dbl> <chr> <dbl> <dbl>
1 1 batch1 0.1 0.2
2 1 batch1 30 1.2
3 1 batch1 2 0.243
4 1 batch1 12 0.643
5 2 batch1 45 NA
6 2 batch2 1.5 0.1
7 2 batch2 30 1.38
8 2 batch2 13 0.65
9 2 batch2 60 3.2
here is my go at your problem, using data.table
I used a lot of in-between steps, so you can check results and operationd each stap, and see what is going on/ So the code can be shortened quite a bit.
library(data.table)
#set data to data.tables
setDT(df_dat); setDT(df_lookup)
#set range df_lookup values by ID and order combination
df_lookup[, `:=`( val2 = shift( val, type = "lead" ),
ratio2 = shift( ratio, type = "lead" ) ),
by = .( ID, order ) ][]
#join non-equi
df_dat[ df_lookup,
`:=`( val_start = i.val,
val_end = i.val2,
ratio_start = i.ratio,
ratio_end = i.ratio2 ),
on = .( ID, order, val > val, val < val2) ][]
#interpolatie new_ratio for values that fall within a range of dt_lookup
df_dat[, new_ratio := ratio_start + ( (val - val_start) * (ratio_end - ratio_start) / (val_end - val_start) )][]
#create data.table with ratio-value for minimum- and maximum value in df_lookup
df_lookup_min_max <- df_lookup[, .( val_min = min( val ), val_max = max( val ),
ratio_min = min( ratio ), ratio_max = max( ratio ) ),
by = .(ID, order) ]
df_lookup_min_max_melt <- melt( df_lookup_min_max,
id.vars = c( "ID", "order" ),
measure.vars = patterns( val = "^val",
ratio = "^ratio" ) )
df_dat[ is.na( new_ratio ),
new_ratio := df_lookup_min_max_melt[ df_dat[ is.na( new_ratio ), ],
ratio,
on = .(ID, order, val ),
roll = "nearest" ] ][]
df_dat[, `:=`(val_start = NULL, val_end = NULL, ratio_start = NULL, ratio_end = NULL)][]
final output
# order ID val new_ratio
# 1: 1 batch1 0.1 0.2000000
# 2: 1 batch1 30.0 1.2000000
# 3: 1 batch1 2.0 0.2428571
# 4: 1 batch1 12.0 0.6647059
# 5: 2 batch1 45.0 NA
# 6: 2 batch2 1.5 0.1000000
# 7: 2 batch2 30.0 1.3750000
# 8: 2 batch2 13.0 0.6500000
# 9: 2 batch2 60.0 3.2000000
edit
the line 5: 2 batch1 45.0 NA is here because there is no order == 2 & ID == batch1 combination in your df_lookup...
perhaps this is a typo?
Nevertheless: the code seems to hande it just fine ;-)