Sort dataframe with multiple columns for multiple years

Question

I have a data.frame with multiple columns and first column being Year. I want to sort my data frame in descending order for each year. I have fifteen years of data and then over 3000 columns.

I illustrate as follows:

Year    A   B   C   D
2000    2   3   4   NA
2001    3   4   NA  1

Desired output, my data frame has NAs as well but I can not remove those.

Year    C   B   A
2000    4   3   2
Year    B   A   D
2001    4   3   1

And this verion as well

 Year           
    2000    C   B   A
    2001    B   A   D

I have scripted this code

Asc <-order(df[-1], decreasing=True)

But I'm unable to obtain my desired output. I have referred in R sort row data in ascending order but still its different for what I'm looking for. Would appreciate your help in this regard.

The desired output may create problems with the class of each column. — akrun
In your desired output you are mixing classes within each column which will end up being a character column — David Arenburg
@DavidArenburg okie now I understand does it turn out to be bigger problem because I have to form deciles next. — Aquarius
You won't be able to convert it a numeric class as long as you have alphabetic character there. Which of the two is your desired output? It's not clear — David Arenburg

akrun akrun · Accepted Answer · 2016-01-26T20:03:52

We can use apply with MARGIN=1. We loop through the rows of the dataset (excluding the first column) with apply, get the index of non-NA elements ('i1'), order the non-NA values descendingly ('i2'), and use that to rearrange the column names of the dataset.

m1 <- t(apply(df1[-1], 1, function(x) {
         i1 <- !is.na(x)
         i2 <- order(-x[i1])
         names(df1)[-1][i1][i2]}))
m1
#    [,1] [,2] [,3]
#[1,] "C"  "B"  "A" 
#[2,] "B"  "A"  "D"

If we need the values and also the names, a list approach would be more suitable as it won't create any problems in the class

 lst <- apply(df1[-1], 1, function(x){
            i1 <- !is.na(x)
           list(sort(x[i1],decreasing=TRUE))})
 lst
 #[[1]]
 #[[1]][[1]]
 #C B A 
 #4 3 2 


 #[[2]]
 #[[2]][[1]]
 #B A D 
 #4 3 1

We can extract the names or the elements from the 'lst'

  do.call(rbind, do.call(`c`,rapply(lst, names, 
             how='list')))
  #   [,1] [,2] [,3]
  #[1,] "C"  "B"  "A" 
  #[2,] "B"  "A"  "D"

Or

  t(sapply(do.call(c, lst), names))

and the values as

  t(simplify2array(do.call(c, lst)))

Sort dataframe with multiple columns for multiple years

1 Answers