How to order a data frame by one descending and one ascending column?
Solution 1:
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:
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:
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