Return first occurring 2nd largest value in data frame rows using colnames & apply

Question

Consider i have a df

> editor
          A  B  C  D  E  F  G  H  I  J
User1     1  0  5  6  5  6  5  6  2  6
User2     0  5  4  6  4  5  5  1  7  5

I want to store the column name of the first occuring 2nd largest value in above rows. Expected results

> editor
          A  B  C  D  E  F  G  H  I  J  2nd_highest
User1     1  0  5  6  5  6  5  6  2  6      C
User2     0  5  4  6  4  5  5  1  7  5      D

i tried edited$2nd_highest <- colnames(edited)[apply(edited, 1, which.max)+1] but did'nt worked well .

Any ideas ?

Answer 1

Here's an attempt to achieve this using algebra in order to keep it vectorized and avoid by row operations (though it still does a matrix conversion similar to apply). The idea here is to find the maximum- then reduce it from the data set, then convert to log (after multiplying by -1) which will result in the largest value becoming -Inf (meaning the smallest value) and then do 1/result in order to find the largest value out of the values left.

indx <- max.col(1/log((editor - editor[cbind(1:nrow(editor), 
                max.col(editor))]) * -1), ties.method = "first")
names(editor)[indx]
# [1] "C" "D"