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?

score 55 · Accepted Answer · answered Oct 17 '11 at 12:10

55

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

answered Oct 17 '11 at 12:10

Roman Luštrik

69,533
24
154
197

Thank you. I am still a bit overwhelmed by everything R has, and did not know about the rev function. In the meantime, I had figured it out that if the second vector is numeric, I could order by max minus the vector, but the character vector was still a problem until you answered. – rumtscho Oct 17 '11 at 15:15
@rumtscho yes, that's how it's done in the help file of order which is where I got my inspiration from. ;) – Roman Luštrik Oct 18 '11 at 07:54
11

Be sure to check out @dudusan's answer for data where this method would fail. The problem is that using rev() no longer keeps the rows all together, it simply flips the column upside down. It only worked in this case because the example was "nicely" arranged. – MrFlick May 10 '14 at 06:30
1

Please see below the answer by @dudusan, this answer is sadly not correct! – francoiskroll Oct 14 '21 at 17:26

score 33 · Answer 2 · answered May 22 '14 at 11:16

33

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

answered May 22 '14 at 11:16

Michele

8,563
6
45
72

3

Simple one without rank : rum[order(rum$I1, -rum$I2, decreasing = TRUE), ] – Somnath Kadam Nov 19 '15 at 11:36
Thanks, helped me ordeing my date column (whcih does not work with -) – shosaco Apr 11 '20 at 18:03
@SomnathKadam the minus sign doesn't work in front of character vectors -c("a") returns an errors – Luke Jul 01 '20 at 13:09

dudusan · Answer 3 · 2014-05-12T04:13:28.897

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

Check out Michele's answer, which seems to work and is more straightforward than my own. — dudusan, Sep 26 '16 at 06:19

score 7 · Answer 4 · answered Jul 27 '14 at 13:18

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.

score 4 · Answer 5 · answered Mar 15 '15 at 19:53

    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)

score 3 · Answer 6 · answered Jan 08 '15 at 23:49

3

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),]

answered Jan 08 '15 at 23:49

Rick

888
8
10

score 3 · Answer 7 · answered Nov 19 '15 at 11:37

3

Simple one without rank :

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

answered Nov 19 '15 at 11:37

Somnath Kadam

6,051
6
21
37

score 1 · Answer 8 · edited Jul 29 '14 at 11:15

1

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

edited Jul 29 '14 at 11:15

David Arenburg

91,361
17
137
196

answered Jan 24 '13 at 11:57

Dmitri B

76
2

1

clarify answer with explanation – IanO.S. Jan 24 '13 at 12:16
1

This does not always work. If T2 is a date vector it will give the error: Error in `-.Date`(rum$T2) : unary - is not defined for Date objects. This doesn't apply to the specific example provided but as general advice it's good to know. – tjnel Apr 25 '13 at 01:13

score 0 · Answer 9 · answered May 23 '14 at 06:27

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.

Marcos Pontes · Answer 10 · 2021-03-31T19:16:41.237

0

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)

edited Mar 31 '21 at 19:16

answered Mar 31 '21 at 17:03

Marcos Pontes

1
2

Please, show the output of your command in order to verify the correctness. Also, your answer relies on functions from an add-on package which has not been mentioned. Please, add the relevant `library()` call. Thank you. – Uwe Mar 31 '21 at 17:40

score 0 · Answer 11 · answered Jan 21 '22 at 10:44

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

score 0 · Answer 12 · edited Jan 22 '13 at 01:58

0

The correct way is:

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

edited Jan 22 '13 at 01:58

pnewhook

4,048
2
31
49

answered Nov 20 '11 at 05:16

dinh

65
1

5

This would be very neat, but doesn't seem to work: compare the output of Roman Luštrik's `rum[order(rum$I1, rev(rum$I2), decreasing = TRUE), ]` to your `rum[order(rum$I1, rum$I2, decreasing = c(TRUE, FALSE)), ]`. I don't think `decreasing` accepts a vector of values. – Michael Dunn Nov 20 '11 at 08:04
2

This is just wrong. Decreasing does not accept a vector, and this answer should not be relied on. – TARehman Mar 17 '15 at 21:57
This will actually work (in at least R 3.4.0) since the default `method="auto"` will use `"radix"` for "short numeric vectors, integer vectors, logical vectors and factors", and `"decreasing"` can be a vector when `"radix"` is used. See the docs [here](https://stat.ethz.ch/R-manual/R-devel/library/base/html/order.html) – Giuseppe Dec 04 '17 at 17:49

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

12 Answers12

Linked

Related