select value dependent on subsequent value in different column within a group in data.table R

Question

I have the following data.table. For each group of ID&drug I would like to assign a new variable (maxi). Maxi is the highest value of val1 in the group which comes before the highest val2 in the group.

library(data.table)
id <- c(1,1,1,1,2,2,2,3,3,3)
val1 <- c(17,18,19,13,11,12,14,15,20,21)
val2 <- c(20,21,30,32,33,34,36,20,31,28)
drug <- c("A","A","A","B","B","A","A","A","B","B")
dat <- data.table(id,val1,val2,drug)


   id val1 val2 drug
1:  1   17   20    A
2:  1   18   21    A
3:  1   19   30    A
4:  1   13   32    B
5:  2   11   33    B
6:  2   12   34    A
7:  2   14   36    A
8:  3   15   20    A
9:  3   20   31    B
10: 3   21   28    B

So for id==2 and drug==A the highest value for val2 is 36 and the highest possible value that comes before 36 in column val1 is 14. The output would look something like this:

    id val1 val2 drug maxi
1:  1   17   20    A   19
2:  1   18   21    A   19
3:  1   19   30    A   19
4:  1   13   32    B   13
5:  2   11   33    B   11
6:  2   12   34    A   14
7:  2   14   36    A   14
8:  3   15   20    A   15
9:  3   20   31    B   21
10: 3   21   28    B   21

The final snippet of code I tried is the following:

dat[,maxi := dat[(.I[age < age_check]),age,by = .(id, drug)],by = .(id, drug)]

Thanks!

Could you explain why 21 is the value for the last group? Seems like it does not "come before" the max val2 of 31. — Frank
@Frank, I have edited the question to clarify my intention. Hope this helps! — LB_NL

arg0naut91 arg0naut91 · Accepted Answer · 2019-02-08T00:01:17

Is this what you're after?

dat[, maxi := max(val1[val1 < max(val2)]), by = .(id, drug)]

Output:

    id val1 val2 drug maxi
 1:  1   17   20    A   19
 2:  1   18   21    A   19
 3:  1   19   30    A   19
 4:  1   13   32    B   13
 5:  2   11   33    B   11
 6:  2   12   34    A   14
 7:  2   14   36    A   14
 8:  3   15   20    A   15
 9:  3   20   31    B   21
10:  3   21   28    B   21

select value dependent on subsequent value in different column within a group in data.table R

2 Answers