How to order a data frame by one descending and one ascending column?

Question

I have a data frame, which looks like that:

    P1  P2  P3  T1  T2  T3  I1  I2
1   2   3   5   52  43  61  6   "b"
2   6   4   3   72  NA  59  1   "a"
3   1   5   6   55  48  60  6   "f"
4   2   4   4   65  64  58  2   "b"

I want to sort it by I1 in descending order, and rows with the same value in I1 by I2 in ascending order, getting the rows in the order 1 3 4 2. But the order function seems to only take one decreasing argument, which is then TRUE or FALSE for all ordering vectors at once. How do I get my sort correct?

Answer 1

Based on the info given here earlier, the short neat answer is:

rum[order(rum$I1, rum$I2, decreasing=c(TRUE,FALSE),method="radix"),]

and the moral of the story (at least for me) is:

"Don't rely on R Defaults!"

Answer 2

In general, xtfrm() is the generic function to get a numeric vector that sorts like the given input vector. Decreasing sorting can then be done by sorting with the negated value of xtfrm(). (This is exactly how e.g. dplyr’s desc() is implemented.)

For example, with the data in question:

df <- read.table(text = "
P1  P2  P3  T1  T2  T3  I1  I2
2   3   5   52  43  61  6   b
6   4   3   72  NA  59  1   a
1   5   6   55  48  60  6   f
2   4   4   65  64  58  2   b
", header = TRUE)

df[order(-xtfrm(df$I1), df$I2), ]
#>   P1 P2 P3 T1 T2 T3 I1 I2
#> 1  2  3  5 52 43 61  6  b
#> 3  1  5  6 55 48 60  6  f
#> 4  2  4  4 65 64 58  2  b
#> 2  6  4  3 72 NA 59  1  a

This approach can be generalized into a base R function to sort data frames by given columns, that also accepts a vector-valued decreasing argument. From my answer to this recent question:

sortdf <- function(x, by = colnames(x), decreasing = FALSE) {
  x[do.call(order, Map(sortproxy, x[by], decreasing)), , drop = FALSE]
}

sortproxy <- function(x, decreasing = FALSE) {
  as.integer((-1)^as.logical(decreasing)) * xtfrm(x)
}

And with the current example data, we (of course) get:

sortdf(df, by = c("I1", "I2"), decreasing = c(TRUE, FALSE))
#>   P1 P2 P3 T1 T2 T3 I1 I2
#> 1  2  3  5 52 43 61  6  b
#> 3  1  5  6 55 48 60  6  f
#> 4  2  4  4 65 64 58  2  b
#> 2  6  4  3 72 NA 59  1  a

Answer 3

you can use the amazing package dplyr there is a function called arrange. you just set the data-frame and the columns you want to order considering the hierarchy you choose. the defualt is ascending order. but if you want in descreasing order you use the command desc.

rum <- read.table(textConnection("P1 P2 P3 T1 T2 T3 I1 I2 2 3 5 52 43 61 6 b 6 4 3 72 NA 59 1 a 1 5 6 55 48 60 6 f 2 4 4 65 64 58 2 b"), header = TRUE)

library(dplyr)
arrange(rum,desc(I1),I2)

Answer 4

Simple one without rank :

rum[order(rum$I1, -rum$I2, decreasing = TRUE), ]

Answer 5

    library(dplyr)
    library(tidyr)
    #supposing you want to arrange column 'c' in descending order and 'd' in ascending order. name of data frame is df
    ## first doing descending
    df<-arrange(df,desc(c))
    ## then the ascending order of col 'd;
    df <-arrange(df,d)

Answer 6

The default sort is stable, so we sort twice: First by the minor key, then by the major key

rum1 <- rum[order(rum$I2, decreasing = FALSE),]
rum2 <- rum1[order(rum1$I1, decreasing = TRUE),]

Answer 7

rum[order(rum$T1, -rum$T2 ), ]

Answer 8

Let df be the data frame with 2 fields A and B

Case 1: if your field A and B are numeric

df[order(df[,1],df[,2]),] - sorts fields A and B in ascending order
df[order(df[,1],-df[,2]),] - sorts fields A in ascending and B in descending order
priority is given to A.

Case 2: if field A or B is non numeric say factor or character

In our case if B is character and we want to sort in reverse order
df[order(df[,1],-as.numeric(as.factor(df[,2]))),] -> this sorts field A(numerical) in ascending and field B(character) in descending.
priority is given to A.

The idea is that you can apply -sign in order function ony on numericals. So for sorting character strings in descending order you have to coerce them to numericals.

Answer 9

In @dudusan's example, you could also reverse the order of I1, and then sort ascending:

> rum <- read.table(textConnection("P1  P2  P3  T1  T2  T3  I1  I2
+   2   3   5   52  43  61  6   b
+   6   4   3   72  NA  59  1   a
+   1   5   6   55  48  60  6   f
+   2   4   4   65  64  58  2   b
+   1   5   6   55  48  60  6   c"), header = TRUE)
> f=factor(rum$I1)   
> levels(f) <- sort(levels(f), decreasing = TRUE)
> rum[order(as.character(f), rum$I2), ]
  P1 P2 P3 T1 T2 T3 I1 I2
1  2  3  5 52 43 61  6  b
5  1  5  6 55 48 60  6  c
3  1  5  6 55 48 60  6  f
4  2  4  4 65 64 58  2  b
2  6  4  3 72 NA 59  1  a
> 

This seems a bit shorter, you don't reverse the order of I2 twice.

Answer 10

I use rank:

rum <- read.table(textConnection("P1  P2  P3  T1  T2  T3  I1  I2
2   3   5   52  43  61  6   b
6   4   3   72  NA  59  1   a
1   5   6   55  48  60  6   f
2   4   4   65  64  58  2   b
1   5   6   55  48  60  6   c"), header = TRUE)

> rum[order(rum$I1, -rank(rum$I2), decreasing = TRUE), ]
  P1 P2 P3 T1 T2 T3 I1 I2
1  2  3  5 52 43 61  6  b
5  1  5  6 55 48 60  6  c
3  1  5  6 55 48 60  6  f
4  2  4  4 65 64 58  2  b
2  6  4  3 72 NA 59  1  a

Answer 11

I'm afraid Roman Luštrik's answer is wrong. It works on this input by chance. Consider for example its output on a very similar input (with an additional line similar to the original line 3 with "c" in the I2 column):

rum <- read.table(textConnection("P1  P2  P3  T1  T2  T3  I1  I2
2   3   5   52  43  61  6   b
6   4   3   72  NA  59  1   a
1   5   6   55  48  60  6   f
2   4   4   65  64  58  2   b
1   5   6   55  48  60  6   c"), header = TRUE)

rum$I2 <- as.character(rum$I2)
rum[order(rum$I1, rev(rum$I2), decreasing = TRUE), ]

  P1 P2 P3 T1 T2 T3 I1 I2
3  1  5  6 55 48 60  6  f
1  2  3  5 52 43 61  6  b
5  1  5  6 55 48 60  6  c
4  2  4  4 65 64 58  2  b
2  6  4  3 72 NA 59  1  a

This is not the desired result: the first three values of I2 are f b c instead of b c f, which would be expected since the secondary sort is I2 in ascending order.

To get the reverse order of I2, you want the large values to be small and vice versa. For numeric values multiplying by -1 will do it, but for characters its a bit more tricky. A general solution for characters/strings would be to go through factors, reverse the levels (to make large values small and small values large) and change the factor back to characters:

rum <- read.table(textConnection("P1  P2  P3  T1  T2  T3  I1  I2
2   3   5   52  43  61  6   b
6   4   3   72  NA  59  1   a
1   5   6   55  48  60  6   f
2   4   4   65  64  58  2   b
1   5   6   55  48  60  6   c"), header = TRUE)

f=factor(rum$I2)
levels(f) = rev(levels(f))
rum[order(rum$I1, as.character(f), decreasing = TRUE), ]

  P1 P2 P3 T1 T2 T3 I1 I2
1  2  3  5 52 43 61  6  b
5  1  5  6 55 48 60  6  c
3  1  5  6 55 48 60  6  f
4  2  4  4 65 64 58  2  b
2  6  4  3 72 NA 59  1  a

Answer 12

The correct way is:

rum[order(rum$T1, rum$T2, decreasing=c(T,F)), ]

Answer 13

I used this code to produce your desired output. Is this what you were after?

rum <- read.table(textConnection("P1  P2  P3  T1  T2  T3  I1  I2
2   3   5   52  43  61  6   b
6   4   3   72  NA  59  1   a
1   5   6   55  48  60  6   f
2   4   4   65  64  58  2   b"), header = TRUE)
rum$I2 <- as.character(rum$I2)
rum[order(rum$I1, rev(rum$I2), decreasing = TRUE), ]

  P1 P2 P3 T1 T2 T3 I1 I2
1  2  3  5 52 43 61  6  b
3  1  5  6 55 48 60  6  f
4  2  4  4 65 64 58  2  b
2  6  4  3 72 NA 59  1  a