Reputation: 397
I cant seem to get this working. I'd like to rank a variable based on max value in a range of years. I can rank within a group ok, but I cant seem to be able to assign the ranking across groups.
It needs to be a dplyr mutate solution if possible as I am piping this into a plot.
Data:
data <- structure(list(
YEAR = c(2020L, 2019L, 2020L, 2019L, 2020L, 2019L),
Grp = c("A", "A", "B", "B", "C", "C"),
Value = c(25L, 24L, 35L, 34L, 45L, 44L)),
class = "data.frame", row.names = c(NA, -6L))
Table looks like this:
YEAR | Grp | value |
---|---|---|
2020 | A | 25 |
2019 | A | 24 |
2020 | B | 35 |
2019 | B | 34 |
2020 | C | 45 |
2019 | C | 44 |
I'd like to create the following output that ranks the Grp based on the value of the maximum year - in this case 2020.
YEAR | Grp | value | Rank |
---|---|---|---|
2020 | A | 25 | 3 |
2019 | A | 24 | 3 |
2020 | B | 35 | 2 |
2019 | B | 34 | 2 |
2020 | C | 45 | 1 |
2019 | C | 44 | 1 |
Upvotes: 2
Views: 761
Reputation: 35737
You could try
library(dplyr)
data %>%
group_by(Grp) %>%
mutate(Rank = Value[which.max(YEAR)]) %>%
ungroup() %>%
mutate(Rank = dense_rank(-Rank))
# YEAR Grp Value Rank
# 1 2020 A 25 3
# 2 2019 A 24 3
# 3 2020 B 35 2
# 4 2019 B 34 2
# 5 2020 C 45 1
# 6 2019 C 44 1
Upvotes: 2