How to order a data frame by one descending and one ascending column?
RSortingDataframeR Problem Overview
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?
R Solutions
Solution 1 - R
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
Solution 2 - R
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
Solution 3 - R
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
Solution 4 - R
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.
Solution 5 - R
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)
Solution 6 - R
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),]
Solution 7 - R
Simple one without rank :
rum[order(rum$I1, -rum$I2, decreasing = TRUE), ]
Solution 8 - R
rum[order(rum$T1, -rum$T2 ), ]
Solution 9 - R
The correct way is:
rum[order(rum$T1, rum$T2, decreasing=c(T,F)), ]
Solution 10 - R
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.
Solution 11 - R
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)
Solution 12 - R
decreasing data frame column
df<- df[order(df$Differece, decreasing = TRUE),]
df<- df[order(df$Differece, decreasing = FALSE),]
Solution 13 - R
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