Score by value in two variables - r
I have this framework:
df<-data.frame(
var1 = c(rep(c(rep(1,2), rep(2,3), rep(3,2), rep(4,1)),2), 1),
var2 = c(rep(1,8), rep(2,8),3)
)
df
var1 var2
#1 1 1
#2 1 1
#3 2 1
#4 2 1
#5 2 1
#6 3 1
#7 3 1
#8 4 1
#9 1 2
#10 1 2
#11 2 2
#12 2 2
#13 2 2
#14 3 2
#15 3 2
#16 4 2
#17 1 3
I would like to make a third variable which is the rank. Rows get the highest rank if 1) they have the lowest numbers in var2
- and then according to how low the numbers are in var1
. e.g. Rows 1 and 2 with var2 = 1 and var1 = 1 should be ranked 1. If rows 9 and 10 with var2 = 2 and var1 = 1 would be ranked 5.
If my data is ordered in ascending order of var2 and then var1, I did the following using my favorite R function rle
to achieve the ranking I got after:
rle(df$var1)
N <- length(rle(df$var1)$lengths)
df$ranks <- rep(1:N, rle(df$var1)$lengths)
df
var1 var2 ranks
#1 1 1 1
#2 1 1 1
#3 2 1 2
#4 2 1 2
#5 2 1 2
#6 3 1 3
#7 3 1 3
#8 4 1 4
#9 1 2 5
#10 1 2 5
#11 2 2 6
#12 2 2 6
#13 2 2 6
#14 3 2 7
#15 3 2 7
#16 4 2 8
#17 1 3 9
This works, but it requires mine to df
be pre-sorted. I need a solution that doesn't need this. I feel like it should be a simple one-liner using rank
and that I have a blind spot. Any help is appreciated - thanks.
EDIT 1:
- adding a larger example to test the suggested answer
dput(df1)
df1 <- structure(list(var1 = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L,
3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 4L, 5L, 5L, 5L, 5L, 5L, 7L,
7L, 7L, 7L, 7L, 8L, 8L, 8L, 8L, 8L, 6L, 9L, 10L, 10L, 10L, 11L,
12L, 12L, 12L, 13L, 14L, 14L, 14L, 14L, 15L, 16L, 16L, 16L, 16L,
16L, 17L, 17L, 17L, 17L, 17L, 18L, 18L, 18L, 18L, 18L, 19L, 19L,
20L, 20L, 21L, 22L, 22L, 22L, 22L, 22L, 23L, 23L, 23L, 23L, 23L,
24L, 24L, 24L, 24L, 24L, 25L, 25L, 25L, 25L, 25L, 1L, 2L, 2L,
2L, 2L, 4L, 5L, 5L, 5L, 5L, 6L, 6L, 6L, 6L, 6L, 7L, 7L, 7L, 7L,
8L, 9L, 10L, 10L, 10L, 10L, 3L, 11L, 11L, 11L, 11L, 12L, 13L,
13L, 13L, 13L, 14L, 14L, 14L, 14L, 14L, 15L, 15L, 15L, 15L, 15L,
12L, 16L, 16L, 16L, 16L, 17L, 17L, 17L, 17L, 17L, 18L, 18L, 18L,
18L, 18L, 19L, 19L, 19L, 19L, 19L, 20L, 20L, 20L, 20L, 21L, 22L,
22L, 22L, 23L, 25L, 24L, 24L, 24L, 24L, 24L, 26L, 26L, 26L, 26L,
26L, 27L, 27L, 27L, 27L, 27L, 1L, 2L, 2L, 2L, 2L, 3L, 3L, 3L,
3L, 3L, 4L, 4L, 4L, 4L, 4L, 5L, 5L, 5L, 5L, 5L, 6L, 6L, 6L, 6L,
6L, 7L, 7L, 7L, 7L, 7L, 8L, 9L, 9L, 9L, 9L, 10L, 10L, 10L, 10L,
11L, 12L, 12L, 13L, 14L, 15L, 16L, 17L, 17L, 18L, 18L, 19L, 19L,
19L, 19L, 20L, 21L, 21L, 21L, 21L, 21L, 22L, 22L, 22L, 22L, 22L,
23L, 23L, 23L, 23L, 23L, 24L, 24L, 24L, 24L, 24L, 25L, 25L, 25L,
25L, 25L, 26L, 26L, 26L, 27L, 27L, 28L, 28L, 28L, 28L, 28L, 1L,
1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 4L, 4L,
4L, 4L, 5L, 6L, 7L, 7L, 7L, 7L, 8L, 8L, 8L, 8L, 8L), var2 = c(1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L,
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L,
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L,
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L,
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L,
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L,
4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L,
4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L), ranks = c(1L, 1L,
1L, 1L, 1L, 12L, 12L, 12L, 12L, 12L, 19L, 19L, 19L, 19L, 19L,
20L, 20L, 20L, 20L, 20L, 21L, 21L, 21L, 21L, 21L, 23L, 23L, 23L,
23L, 23L, 24L, 24L, 24L, 24L, 24L, 22L, 25L, 2L, 2L, 2L, 3L,
4L, 4L, 4L, 5L, 6L, 6L, 6L, 6L, 7L, 8L, 8L, 8L, 8L, 8L, 9L, 9L,
9L, 9L, 9L, 10L, 10L, 10L, 10L, 10L, 11L, 11L, 13L, 13L, 14L,
15L, 15L, 15L, 15L, 15L, 16L, 16L, 16L, 16L, 16L, 17L, 17L, 17L,
17L, 17L, 18L, 18L, 18L, 18L, 18L, 26L, 37L, 37L, 37L, 37L, 47L,
48L, 48L, 48L, 48L, 49L, 49L, 49L, 49L, 49L, 50L, 50L, 50L, 50L,
51L, 52L, 27L, 27L, 27L, 27L, 46L, 28L, 28L, 28L, 28L, 29L, 30L,
30L, 30L, 30L, 31L, 31L, 31L, 31L, 31L, 32L, 32L, 32L, 32L, 32L,
29L, 33L, 33L, 33L, 33L, 34L, 34L, 34L, 34L, 34L, 35L, 35L, 35L,
35L, 35L, 36L, 36L, 36L, 36L, 36L, 38L, 38L, 38L, 38L, 39L, 40L,
40L, 40L, 41L, 43L, 42L, 42L, 42L, 42L, 42L, 44L, 44L, 44L, 44L,
44L, 45L, 45L, 45L, 45L, 45L, 53L, 64L, 64L, 64L, 64L, 74L, 74L,
74L, 74L, 74L, 75L, 75L, 75L, 75L, 75L, 76L, 76L, 76L, 76L, 76L,
77L, 77L, 77L, 77L, 77L, 78L, 78L, 78L, 78L, 78L, 79L, 80L, 80L,
80L, 80L, 54L, 54L, 54L, 54L, 55L, 56L, 56L, 57L, 58L, 59L, 60L,
61L, 61L, 62L, 62L, 63L, 63L, 63L, 63L, 65L, 66L, 66L, 66L, 66L,
66L, 67L, 67L, 67L, 67L, 67L, 68L, 68L, 68L, 68L, 68L, 69L, 69L,
69L, 69L, 69L, 70L, 70L, 70L, 70L, 70L, 71L, 71L, 71L, 72L, 72L,
73L, 73L, 73L, 73L, 73L, 81L, 81L, 81L, 81L, 81L, 82L, 82L, 82L,
82L, 82L, 83L, 83L, 83L, 83L, 83L, 84L, 84L, 84L, 84L, 85L, 86L,
87L, 87L, 87L, 87L, 88L, 88L, 88L, 88L, 88L)), .Names = c("var1",
"var2", "ranks"), row.names = c(NA, -300L), class = "data.frame")
From this suggested answer the rank variable was derived:
df1$ranks1 <- dense_rank(paste(df1$var2, df1$var1))
source to share
Solutions that include paste[0]
will only work if the values inside each vector are integers with a fixed number of digits. This is because it paste
converts to symbol and:
- The symbolic (lexicographic) order differs from the numeric one:
rank(c(1 , 2, 11)); rank(as.character(c(1 , 2, 11)))
- concatenation introduces ambiguities:
paste0(2,12); paste0(21,2)
Peter Dalgaard made a related post in 2011 http://r.789695.n4.nabble.com/Function-rank-for-data-frames-or-multiple-vectors-td3765685.html
Now we ignore the same lines. Note that rank (x) == order (order (x)) and order
takes multiple order columns, so if you don't mind how the same rows are split order(order(df$var2, df$var1))
does the job.
This splits identical lines according to their original order. There are several ways to rank identical rows http://en.wikipedia.org/wiki/Ranking#Strategies_for_assigning_rankings .
In 2011, Peter Dahlgaard proposed ave(order(order(df$var2, df$var1)), df$var2, df$var1)
, which gives what Wikipedia calls "Fractional Ranking", but which base::rank
is the default ties.method="average"
.
In your example, Wikipedia is calling "Dense ranking", which is not available in base::rank
, but as David Arenburg commented, is provided dplyr::dense_rank
, so you can library(dyplr)
and use:
dense_rank(ave(order(order(df$var2, df$var1)), df$var2, df$var1))
Looking at the code for dense_rank it is simple
function (x)
{
r <- rank(x)
match(r, sort(unique(r)))
}
assuming that if you don't want to load dplyr and are happy with a two-statement solution creating another variable - for example r
- you can use
r <- ave(order(order(df$var2, df$var1)), df$var2, df$var1); match(r, sort(unique(r)))
Edited to add ...
You can make things a little neat by realizing that the dataframe is indeed a list, so you can rank by columns in order of appearance:
dense_rank(ave(order(do.call(order, df)), df))
You are occupying space across the columns in reverse order, so
dense_rank(ave(order(do.call(order, rev(df))), df))
or by explicitly specifying the columns and their order
dense_rank(ave(order(do.call(order, df[,2:1])), df[,2:1]))
source to share