How to sort multiple ascending/descending columns in a data.frame: R

Question

I am fairly new to R and perhaps don't know the best way to tackle this task but I have attempted a few various suggestions and none of them have allowed me to sort multiple columns in a data.frame.

Essentially for:

df = data.frame(matrix(c(-1,3,6,1,3,-5,2,4,9,-3,-1,-6,1,4,5), ncol=3)

       X1 X2 X3
   [1] -1 -5 -1
   [2]  3  2 -6
   [3]  6  4  1
   [4]  1  9  4
   [5]  3 -3  5

I want to sort all of the columns independently from one another such that I could make all columns sort "descending", make half of the columns sort "ascending" and half "descending" etc.

       X1 X2 X3
   [1] -1 -5  5
   [2]  1 -3  4
   [3]  3  2  1
   [4]  3  4 -1
   [5]  6  9 -6

The purpose of this is create column-wise gradients for use in heatmap.2 while also being able to control the mean, variance etc. of the columns. For example my data.frame would most likely be along the lines of:

df <- data.frame(matrix(runif(5200,0,1), ncol=10))

However, when I attempt to use the sort or order command, I fail to be able to sort the columns independently of one another.

dfi <- df[order(df[[1]], decreasing =FALSE),]

        X1 X2 X3
    [1] -1 -5 -1
    [4]  1  9  4
    [2]  3  2 -6
    [5]  3 -3  5
    [3]  6  4  1


dfi <- df[order(df[[1]], df[[2]], decreasing =FALSE),]
        X1 X2 X3
    [1] -1 -5 -1
    [4]  1  9  4
    [2]  3  2 -6
    [5]  3 -3  5
    [3]  6  4  1

I have attempted using for loops but to no success. I faced an inability to sort or order the columns independently (I don't have a reasonable for loop to show that someone could reproduce, and if this is the route to solve this problem, whoever answers will be much more knowledgable than I at the appropriate notation.)

Does anyone have any advice as to how to best go about this? I have been able to get the desired result by individually creating the columns, sorting them and then binding them together. However, since I need to make many many varying iterations of this (different variances, means, numbers of columns etc), that process is way too inefficient unless.

user_1777111 · Answer

How about sapply:

n   <- ncol(df)
as  <- 3 # columns to be sorted ascending
de  <- 2 # columns to be sorted descending
out <- sapply(1:n, function(x) {
  if(x %in% as) {
    return(sort(df[,x], decreasing = F))
  } else if (x %in% de) {
    return(sort(df[,x], decreasing = T))
  }
  return(df[,x])
})
out


    [,1] [,2] [,3]
[1,]   -1   -5   -1
[2,]    3   -3   -6
[3,]    6    2    1
[4,]    1    4    4
[5,]    3    9    5

We simply loop over all columns and apply a function on each of them checking if they are either part of the vector as (ascending) or the vector de (descending). If a column is part in neither of them we just return it as it is.

user_5150629 · Answer

You can also do this with mutate_at + sort:

library(dplyr)

df %>%
  mutate_at(1:2, funs(sort(.))) %>%
  mutate_at(3, funs(sort(., decreasing = TRUE)))

Result:

You can also make it into a convenience function:

library(rlang)

arrange_indep = function(DF, asc = 1:ncol(DF), dsc=0){
  asc_quo = enquo(asc)
  dsc_quo = enquo(dsc)

  temp = DF %>%
    mutate_at(vars(!!asc_quo), funs(sort(.))) 

  if(dsc_quo != quo(0)){
    temp = temp %>%
      mutate_at(vars(!!dsc_quo), funs(sort(., decreasing = TRUE)))
  }
  return(temp)  
}

Results & Usage:

1.) First two cols ascending, third col descending:

df %>%
  arrange_indep(1:2, 3)

  X1 X2 X3
1 -1 -5  5
2  1 -3  4
3  3  2  1
4  3  4 -1
5  6  9 -6

2.) Same as 1.), but with non-standard evaluation:

df %>%
  arrange_indep(X1:X2, X3)

  X1 X2 X3
1 -1 -5  5
2  1 -3  4
3  3  2  1
4  3  4 -1
5  6  9 -6

3.) First two cols ascending, keeping third col unsorted:

df %>%
  arrange_indep(1:2)

  X1 X2 X3
1 -1 -5 -1
2  1 -3 -6
3  3  2  1
4  3  4  4
5  6  9  5

4.) First two cols descending, third col defaults to ascending:

df %>%
  arrange_indep(dsc=1:2)

  X1 X2 X3
1  6  9 -6
2  3  4 -1
3  3  2  1
4  1 -3  4
5 -1 -5  5

5.) Using the default of arranging all cols by ascending order:

df %>%
  arrange_indep()

  X1 X2 X3
1 -1 -5 -6
2  1 -3 -1
3  3  2  1
4  3  4  4
5  6  9  5

How to sort multiple ascending/descending columns in a data.frame: R

2 Answers