Keelin
Keelin

Reputation: 397

Apply a rank across groups

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

Answers (1)

Darren Tsai
Darren Tsai

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

Related Questions