New data.table column returns nth largest value in a row

  • A+
Category:Languages

My data looks similar to this:

set.seed(1) dt <- data.table(rank=c(3,4,2,1),`1`=rnorm(4),`2`=rnorm(4),`3`=rnorm(4),`4`=rnorm(4),`5`=rnorm(4),`6`=rnorm(4))     rank          1          2          3           4           5           6 1:    3 -0.6264538  0.3295078  0.5757814 -0.62124058 -0.01619026  0.91897737 2:    4  0.1836433 -0.8204684 -0.3053884 -2.21469989  0.94383621  0.78213630 3:    2 -0.8356286  0.4874291  1.5117812  1.12493092  0.82122120  0.07456498 4:    1  1.5952808  0.7383247  0.3898432 -0.04493361  0.59390132 -1.98935170 

I would like to add a new column rank_match that finds the nth (taken from the rank column) largest value in the row from columns named 1 to 6. For instance, the first line would look for the 3rd largest value in the row from columns named 1 to 6 which is 0.3295078.

Something like these (but of course they doesn't work):

dt[,rank_match := (sort(`1`:`6`, decreasing = TRUE)[rank])] dt[,rank_match := (sort(.SD, decreasing = TRUE)[rank]), .SDcols=`1`:`6`] 

The output should look similar to this:

   rank          1          2          3           4           5           6 rank_match 1:    3 -0.6264538  0.3295078  0.5757814 -0.62124058 -0.01619026  0.91897737  0.3295078 2:    4  0.1836433 -0.8204684 -0.3053884 -2.21469989  0.94383621  0.78213630 -0.3053884 3:    2 -0.8356286  0.4874291  1.5117812  1.12493092  0.82122120  0.07456498  1.1249309 4:    1  1.5952808  0.7383247  0.3898432 -0.04493361  0.59390132 -1.98935170  1.5952808 

Thanks so much.

 


dt[, rank_match := apply(.SD, 1, function(x) x[order(-x)][rank]), by = rank, .SDcols = `1`:`6`] dt    rank          1          2          3           4           5           6 rank_match 1:    3 -0.6264538  0.3295078  0.5757814 -0.62124058 -0.01619026  0.91897737  0.3295078 2:    4  0.1836433 -0.8204684 -0.3053884 -2.21469989  0.94383621  0.78213630 -0.3053884 3:    2 -0.8356286  0.4874291  1.5117812  1.12493092  0.82122120  0.07456498  1.1249309 4:    1  1.5952808  0.7383247  0.3898432 -0.04493361  0.59390132 -1.98935170  1.5952808 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: