Getting a list of column names in ascending order for each row in a data frame

Question

I have a data frame which consists of a column of IDs and many columns with different values for each ID, many of which are NA. e.g:

     ID  w  x  y  z
1 User1  3  2 NA NA
2 User2  7  9 NA  4
3 User3 NA NA  1 NA
4 User4  3 NA NA  5

Is there a way to get a list for each ID of the column headings in order from smallest to largest value with NAs removed?

For example:

User 1: x, w

User 2: z, w, x

User3: y

So far I've gotten nowhere with this. I tried just getting the order of rows with by_row like so:

ordered <- by_row(moves.df, function(order) list(order[,2:ncol(moves.df)]), .collate = "list")$.out

but the output of that was just a list of single-observation dataframes for each row which had not been ordered in any way.

ordered2 <- moves.df %>% rowwise() %>% mutate(placelist = list(rank(moves.df[,2:ncol(moves.df)])))

which gave me a column that was a list, but the list was of numbers I didn't recognise.

Any help would be super appreciated!

Answer 1

We can use apply row wise (margin = 1), sort the values and get the column names

apply(df[-1], 1, function(x) names(sort(x)))

#$`1`
#[1] "x" "w"

#$`2`
#[1] "z" "w" "x"

#$`3`
#[1] "y"

#$`4`
#[1] "w" "z"

Answer 2

Here is an option using tidyverse. We gather the dataset into 'long' format, arrange by 'ID', 'val', grouped by 'ID', paste the elements of 'key' together

library(tidyverse)
df %>% 
  gather(key, val, -ID, na.rm = TRUE) %>% 
  arrange(ID, val) %>%
  group_by(ID) %>% 
  summarise(key = toString(key))
# A tibble: 4 x 2
#  ID    key    
#  <chr> <chr>  
#1 User1 x, w   
#2 User2 z, w, x
#3 User3 y      
#4 User4 w, z   

Or if we need a list output for 'key', then wrap it with list in summarise

df %>% 
   gather(key, val, -ID, na.rm = TRUE) %>%
   arrange(ID, val) %>% 
   group_by(ID) %>% 
   summarise(key = list(key))

data

df <- structure(list(ID = c("User1", "User2", "User3", "User4"), w = c(3L, 
7L, NA, 3L), x = c(2L, 9L, NA, NA), y = c(NA, NA, 1L, NA), z = c(NA, 
4L, NA, 5L)), .Names = c("ID", "w", "x", "y", "z"),
  class = "data.frame", row.names = c("1", 
"2", "3", "4"))