4

I have the following dataframe (df1)

Type     CA     AR       OR
alpha    2      3        5
beta     1      5        6
gamma    6      2        8
delta    8      1        9
Total    17     11       28

I want to reorder this dataframe such that it is in descending order, based on "Total" row.

The resulting dataframe should look like this (df2)

    Type     OR    CA     AR       
    alpha     5    2      3        
    beta      6    1      5        
    gamma     8    6      2        
    delta     9    8      1        
    Total     28   17     11

Notice that the columns that were ordered before as "CA, AR, OR" have now become "OR, CA, AR" based on the total row (ordered in descending order.)

In order to do this, I attempted to use the rank function as follows:

rank_total <- rank(-df1)

This gives me the ranks:

 [1] 2 1 3

However, I don't know how to now reorder, based on these ranks.

Does anyone know how to continue from here? Or if there is another way to do this altogether, that would be great!

Thanks so much in advance!!

user4918087
  • 421
  • 1
  • 6
  • 14

1 Answers1

3

Try

 df2 <- df1[,c(1, order(-unlist(df1[df1$Type=='Total',-1]))+1)]
 df2
 #   Type OR CA AR
 #1 alpha  5  2  3
 #2  beta  6  1  5
 #3 gamma  8  6  2
 #4 delta  9  8  1
 #5 Total 28 17 11

Or using rank

 df1[,c(1, match(1:3, rank(-unlist(df1[nrow(df1),-1])))+1)]

data

 df1 <- structure(list(Type = c("alpha", "beta", "gamma", "delta", "Total"
 ), CA = c(2L, 1L, 6L, 8L, 17L), AR = c(3L, 5L, 2L, 1L, 11L), 
  OR = c(5L, 6L, 8L, 9L, 28L)), .Names = c("Type", "CA", "AR", 
 "OR"), class = "data.frame", row.names = c(NA, -5L))
akrun
  • 874,273
  • 37
  • 540
  • 662
  • thanks for the answer. However, this only gives me the first column.. that is the "type" column. – user4918087 May 29 '15 at 16:26
  • 2
    @user4918087 Updated with the data I used. It gives me the expected output though. – akrun May 29 '15 at 16:27
  • 1
    the second solution with rank works perfectly! Thank you so much!! – user4918087 May 29 '15 at 16:31
  • 1
    @user4918087 Both solutions work for me. Maybe next time you should use `dput` to show the exact data structure you are working with (like akrun has at the bottom of this answer). – Frank May 29 '15 at 16:32
  • 1
    By the way, `Type` might make more sense as row names, allowing one to do: `df2[,order(-df2["Total",])]` where df2 is `df2 <- df[,-1]; rownames(df2) <- df$Type` – Frank May 29 '15 at 16:33
  • 1
    @Frank It is more convenient rather than adding `+1` to make the index matching – akrun May 29 '15 at 16:35
  • @akrun, I have some additional columns at the end called "total" and "percentage." - I don't want to include that in the column reordering. Could you let me know how I can update my code to include those columns in my final data frame, without including it into the ranking? Do I need to change the "-1" or "+1"? Thank you in advance! – user4918087 May 29 '15 at 16:38
  • @user4918087 In that case, subset only the columns that are needed in the `order`. Here I used `+1` because there was already a column 'Type'. So, you may use `df1[c(1, order(-unlist(df1[df1$Type=='Total', 2:4]))+1, 5:ncol(df1)]` (not tested though) – akrun May 29 '15 at 16:42