CODEWITHSUNDEEP
rdplyrdata.table
Ignacio
Ignacio

Reputation: 7938

How can I identify the N columns with the largest values for each row?

My data looks something like this:

set.seed(122217)
df <- data.frame(ID = paste0("id",1:100), A = rnorm(100), E = rnorm(100), I = rnorm(100), O = rnorm(100), U = rnorm(100))

I want to produce a new data frame with 100 rows and 1+3 columns. Each row should correspond to each ID from df, the first column would be ID, and the others would be First, Second, Third.

I can do this with some very ugly code:

library(data.table)
library(dplyr)
# transpose
t_df <- transpose(df[,2:6])
# get row and colnames in order
colnames(t_df) <- df[,1]
rownames(t_df) <- colnames(df[,2:6])
id_largest <-function(data, col){
  values <- data[,col]
  names(values) <- row.names(data)
  values <- sort(values, decreasing = T)
  ranking <- names(values)
  out <- data.frame( id= colnames(data)[col], First=ranking[1], Second=ranking[2], Third=ranking[3])
  return(out)
}

ranking <- purrr::map(1:ncol(t_df), id_largest, data=t_df) %>% rbindlist()

This code produces what I want:

> head(ranking)
id First Second Third
1: id1     A      E     I
2: id2     U      O     I
3: id3     A      E     I
4: id4     E      U     I
5: id5     I      A     U
6: id6     I      A     U

But is not very elegant. Is there a cleaner way of doing this?

Upvotes: 1

Views: 76

Answers (2)

Jaap
Jaap

Reputation: 83215

A solution using data.table:

library(data.table)

melt(setDT(df), id = 1)[order(-value)
                        ][, variable[1:3], ID
                          ][, dcast(.SD, ID ~ rowid(ID, prefix = 'p'))]

which gives:

        ID p1 p2 p3
  1: id001  A  E  I
  2: id002  U  O  I
  3: id003  A  E  I
  4: id004  E  U  I
  5: id005  I  A  U
....
 95: id095  O  A  U
 96: id096  U  A  I
 97: id097  A  U  O
 98: id098  U  A  O
 99: id099  I  E  U
100: id100  E  I  U

The same logic implemented with the tidyverse:

library(dplyr)
library(tidyr)

df %>% 
  gather(key, value, -1) %>% 
  group_by(ID) %>% 
  arrange(ID, -value) %>% 
  slice(1:3) %>% 
  select(-value) %>% 
  mutate(rn = paste0('p', row_number())) %>% 
  spread(rn, key)

Used data:

set.seed(122217)
df <- data.frame(ID = sprintf("id%03d",1:100), A = rnorm(100), E = rnorm(100), I = rnorm(100), O = rnorm(100), U = rnorm(100))

Upvotes: 3

pogibas
pogibas

Reputation: 28339

Solution using apply:

 foo <- colnames(df)[-1]
 data.frame(df[, 1], 
            t(apply(df[, -1], 1, function(x) foo[tail(order(x), 3)]))[, 3:1])

Upvotes: 6

Related Questions