Here is my lookup table solution. It seemed like there was a lot of typing of A, B, C, and X, so if you needed to update it down the road, it'd be a pain.
Step 1. Make lookup table with summary functions
Step 2. Run the summary functions on the dataset
Step 3. Gather (i.e., unpivot) the data to long format
Step 4. Join, filter, and slice.
I added a lot of stuff to steps 3 and 4 since positions seemed of interest.
library(tidyverse)
df <- tribble(
~A, ~B, ~C, ~X,
10, 12, 8, 5,
8, 5, 7, 4,
4, 2, 4, 3,
2, 1, 2, 2,
0, 0, 0, 1
)
df2 <- bind_rows(df, df + 2, df + 4, .id = "grp")
lookup_funs <- tribble(
~letter_grp, ~functions,
'A', 'prod(max(A), 0.5)',
'B', 'prod(max(B), 0.5)',
'C', 'prod(max(C), 0.5)',
'X', 'prod(mean(max(A),max(B),max(C)), 0.5)'
)
summary_data <- lookup_funs%>%
group_by(letter_grp)%>%
do(summarize(group_by(df2,grp), lookup_val = eval(parse(text = .$functions))))
df2%>%
gather(key = 'letter_grp', value = 'value', A:X, -grp)%>%
group_by(letter_grp)%>%
mutate(absolute_position = row_number())%>%
group_by(grp, letter_grp)%>%
mutate(relative_position = row_number())%>%
inner_join(summary_data)%>%
filter(value < lookup_val)%>%
slice(1)
# A tibble: 12 x 6
# Groups: grp, letter_grp [12]
grp letter_grp value absolute_position relative_position lookup_val
<chr> <chr> <dbl> <int> <int> <dbl>
1 1 A 4 3 3 5
2 1 B 5 2 2 6
3 1 C 2 4 4 4
4 1 X 4 2 2 5
5 2 A 4 9 4 6
6 2 B 4 8 3 7
7 2 C 4 9 4 5
8 2 X 5 8 3 6
9 3 A 6 14 4 7
10 3 B 6 13 3 8
11 3 C 4 15 5 6
12 3 X 6 14 4 7