How can I select the top 2 values in a column per column and report this with both values and row names?

Question

I have columns of a matrix with the names of users' unique ID's. Each row in the column represents a risk factor score based on a regression model that predicted risk. I want to report each individual's top 2 risk factors (both the row name representing the factors and the actual values) along with their ID.

I've searched for solutions and found a lot of near misses. For instance, max would work if I only wanted the top 1 value without a row name, and it I didn't have individuals as columns then I think that I could use some variation of sort(x,partial=n-1)[n-1].

My data looks like this:

        ID.H00034222 ID.H00034305
a.score  0.040093810   0.04009381
b.score -0.038265220  -0.03826522
c.score  0.044418130   0.04441813
d.score -0.418624640  -0.05656504
e.score -0.005192439   0.10851938
f.score  0.005026030   0.02174170

df <- structure(c(0.04009381, -0.03826522, 0.04441813, -0.41862464, 
-0.005192439, 0.00502603, 0.04009381, -0.03826522, 0.04441813, 
-0.05656504, 0.10851938, 0.0217417), .Dim = c(6L, 2L), .Dimnames = list(
    c("a.score", "b.score", "c.score", "d.score", "e.score", 
    "f.score"), c("ID.H00034222", "ID.H00034305")))

My output should give a user ID, and highest 2 row names and values for each ID.

Could you provide the desired output for this example? – Paulo E. Cardoso May 13 '14 at 11:11 — Paulo E. Cardoso, May 13 '14 at 11:11

score 4 · Accepted Answer · answered May 13 '14 at 11:17

Some toy data:

> foo <- matrix(rnorm(80),nrow=20,dimnames=list(letters[1:20],LETTERS[23:26]))
> foo
           W           X           Y          Z
a  1.3550138  1.35438637  0.43150124 -0.7087100
b -1.3200136 -0.82764303 -0.01480796  2.2399561
c -1.0018050 -0.04941702 -2.46454058  0.2516673
d  0.7877259  0.08713982  1.25948179  1.1901716
e  1.5834800  0.06905996  0.37453295  0.5343909
f  0.3607650  0.22815776 -0.24688689 -0.7784353
g  0.2071689 -0.21987249 -0.38334840 -0.0568370
h  0.7502296  0.61355174 -0.38836131  1.8772838
i -1.1608980  0.75128093 -0.13562930 -1.3183407
j -0.5419957 -0.43251911 -1.91773334  0.8227418
k  1.1222314 -0.52768270  1.13687919  0.2458857
l -0.2158528 -0.43057554 -0.93613664  0.7861322
m  0.2933530 -1.22532466  0.98192839  0.6581522
n -0.6324390 -0.90072134 -1.89817529 -1.4419014
o  0.6956912  1.28457179  0.65234730  0.6524573
p -1.4979845 -0.23898356  1.82825548 -0.1819744
q  1.5459565  0.38683197 -1.23645565  1.0340520
r -1.3844880  0.05111238 -0.51715069 -0.1702139
s -2.1096706  0.46527795 -1.13718323  0.8133859
t -1.5700089  0.09305299  2.33807078  0.8382010

Extract the top two values per column:

> apply(foo,2,function(xx)tail(sort(xx),2))
            W        X        Y        Z
[1,] 1.545957 1.284572 1.828255 1.877284
[2,] 1.583480 1.354386 2.338071 2.239956

Extract the names of the top two values per column:

> apply(foo,2,function(xx)tail(names(sort(xx)),2))
     W   X   Y   Z  
[1,] "q" "o" "p" "h"
[2,] "e" "a" "t" "b"
>

How can I select the top 2 values in a column per column and report this with both values and row names?

1 Answers1