For each row, sort column names by highest value

user_10537248 0
Viewed 156

I have a dataframe that looks like this.

df <- data.frame(name = c("bob", "john", "peter", "chris"),
    a = c(0, 0, 0, 0), b = c(2, 2, 1, 0), c = c(4, 0, 0, 1), d = c(0, 2, 1, 0), e = c(1, 0, 5, 0))
df

   name a b c d e
1   bob 0 2 4 0 1
2  john 0 2 0 2 0
3 peter 0 1 0 1 5
4 chris 0 0 1 0 0

For each row I need to find the columnames ordered by value like in the below examples:

   name output
1   bob  c,b,e
2  john    b,d
3 peter  e,b,d
4 chris      c

I can't figure out how to do this.

if-statement r sorting
2 Answers

You can do:

df <- data.frame(name = c("bob", "john", "peter", "chris"),
                 a = c(0, 0, 0, 0), b = c(2, 2, 1, 0), c = c(4, 0, 0, 1), d = c(0, 2, 1, 0), e = c(1, 0, 5, 0))
sortRowNames <- function(x) { x <- x[x!=0]; y <- sort(x, decr=TRUE); paste0(names(y), collapse=',') }
df$new <- apply(df[-1], 1, sortRowNames)
df
# > df
#    name a b c d e   new
# 1   bob 0 2 4 0 1 c,b,e
# 2  john 0 2 0 2 0   b,d
# 3 peter 0 1 0 1 5 e,b,d
# 4 chris 0 0 1 0 0     c

Here is a short version of the function:

sortRow2 <- function(x) paste0(names(sort(x[x!=0], decr=TRUE)), collapse=',')
user_54144520

Here is a gather/spread way to do it via tidyverse,

library(tidyverse)

df %>% 
 gather(var, val, -name) %>% 
 group_by(name) %>% 
 arrange(-val) %>% 
 mutate(output = toString(var[val != 0])) %>% 
 spread(var, val) %>% 
 select(name, output)

which gives,

# A tibble: 4 x 2
# Groups:   name [4]
  name  output 
  <chr> <chr>  
1 bob   c, b, e
2 chris c      
3 john  b, d   
4 peter e, b, d
user_56355800
Related