Reputation: 183
I'm trying to compute a medal table for a sports event.
My data looks like this:
test <- data.frame("ID" = c("1_1", "1_2", "1_3", "1_4","1_5","1_6"),
"gold"=c(10, 4, 1, 7, 7, 1),
"silver"=c(1, 3, 2, 19, 19, 2),
"bronze"=c(1, 8, 2, 0, 0, 2))
First, I want to order the data based on number of "gold", "silver", and "bronze", like this:
(test_ordered <- with(test, test[order(-gold, -silver, -bronze), ]))
Then compute the final medal rank. This is how the final rank column should like:
(test_ordered$rank<-c(1, 2, 2, 4, 5, 5))
# ID gold silver bronze rank
# 1 1_1 10 1 1 1
# 4 1_4 7 19 0 2
# 5 1_5 7 19 0 2
# 2 1_2 4 3 8 4
# 3 1_3 1 2 2 5
# 6 1_6 1 2 2 5
As ID "1_4" and "1_5" have the won the same combination of medals they'd share rank 2, e.g.
My attempts using more than two criteria with rank
(also dplyr::min_ranked
) failed:
with(test, rank(-gold, -silver, -bronze, ties.method = "min"))
# (...) unused argument (-bronze)
Also interaction
was not successful:
as.numeric(interaction(gl(-test$gold), gl(-test$silver), gl(-test$bronze), lex.order = TRUE))
Any ideas how to calculate rank based on multiple variables?
solved using henrik's idea:
as.data.frame(setDT(test)[ , rank := frank(test, -gold, -silver, -bronze, ties.method = "min")]; setorder(test, rank))
Upvotes: 2
Views: 1075
Reputation: 67828
You may use the data.table
equivalent of base::rank
, frank
. A nice feature with frank
is that it accepts, not only vectors (as in rank
), but also a data.frame
or a data.table
as input. For these types of objects, the rank may be based on several columns.
Using your original data.frame
:
test$rank <- data.table::frank(test, -gold, -silver, -bronze, ties.method = "min")
Or if you want to go all in with data.table
functions:
setDT(test)[ , rank := frank(test, -gold, -silver, -bronze, ties.method = "min")]
setorder(test, rank)
Upvotes: 3
Reputation: 5456
A base R solution would be:
test <- data.frame("ID"=c("1_1", "1_2", "1_3", "1_4","1_5","1_6"),
"gold"=c(10,4,1,7,7,1),
"silver"=c(1,3,2,19,19,2),
"bronze"=c(1,8,2,0,0,2))
(test_ordered<-with(test, test[order(-gold,-silver,-bronze),]))
roll.any.greater <- function (mat) {
mat.lead <- head(mat, -1)
mat.lag <- tail(mat, -1)
result <- rep(1, nrow(mat.lead) + 1)
for (i in (2:length(result))) {
result[i] <- ifelse(any(as.logical(abs(mat.lead[i-1, ] - mat.lag[i-1, ]))) != FALSE,
i, result[i-1])
}
return(result)
}
(want <- cbind(test_ordered,
rank =
roll.any.greater(test_ordered[colnames(test_ordered) %in% c("gold", "silver", "bronze")])))
Upvotes: 0