R get correct index using which() condition

Question

I have to find an observation satisfying some criteria and then merge this indices with an other dataset. So I don't need the index of the observations satisfying the condition, but the index that refers to all the observations.

For instance, I want to find the max(x1) given that x2>20 and then use this index in another dataset later. I need the right index, in other words:

dat <- data.frame(name= c("A","B","C","D"),
           x1= c(1,2,3,4),
           x2= c(10,20,30,40))
dat$name[which.max(dat$x1[dat$x2>20])]
[1] B

I want to get

[1] D

i.e. an index of 4, not 2.

Answer 1

Here's one way using data table

library(data.table)
dat <- as.data.table(dat)
which(dat[,name]==dat[x2>20,][which.max(x1),name])

Can do something similar using data frames, but it will be rather more verbose.

which (dat$name==dat$name[which(dat$x2>20)][which.max(dat$x1[which(dat$x2>20)])])

Note that this method depends on the assumption that name contains unique values for each row.

Answer 2

Just use max instead of which.max. However, the whole data frame needs to be sorted based on x1, as max does 1:1 mapping. (Thanks @myk_raniu for clarifying)

dat <- dat[order(dat$x1),]
dat$name[max(dat$x1[dat$x2>20])]
#[1] D

Answer 3

The reason which.max doesn't give the right answer is that the filtered list of x1 is shorter than the dat$name list and there is no longer a 1:1 correspondance

Try this instead

dat <- data.frame(name= c("A","B","C","D"),
                  x1= c(1,2,3,4),
                  x2= c(10,20,30,40))

dat$name[dat$x1==max(dat$x1[dat$x2>20])]