How to group, arrange, mutate, and rank by a date column?

tidyverse
1

Consider the following data:

df <- tribble(
  ~id, ~reg_date, ~quarter,
  "1", "2012-08-22", "2012 Summer",
  "2", "2012-07-31", "2012 Summer",
  "2", "2014-07-21", "2014 Summer",
  "2", "2014-09-14", "2014 Fall",
  "3", "2016-01-01", "2016 Winter",
  "3", "2016-01-10", "2016 Winter",
  "4", "2012-09-10", "2012 Fall",
  "4", "2013-01-24", "2013 Winter",
  "4", "2013-04-03", "2013 Spring"
)

For each id, I want to "rank" the quarters in chronological order (e.g. "2012 Summer" before "2012 Fall", "2013 Winter" before "2013 Spring"). I thought I could do that with a combination of group_by, arrange, mutate and dense_rank, but the quarter_order variable is off for ids 2 and 4:

df %>% 
  mutate(reg_date = lubridate::ymd(reg_date)) %>% 
  group_by(id) %>% 
  arrange(id, reg_date) %>% 
  mutate(quarter_order = dense_rank(quarter))

# A tibble: 9 x 4
# Groups:   id [4]
  id    reg_date   quarter     quarter_order
  <chr> <date>     <chr>               <int>
1 1     2012-08-22 2012 Summer             1
2 2     2012-07-31 2012 Summer             1
3 2     2014-07-21 2014 Summer             3
4 2     2014-09-14 2014 Fall               2
5 3     2016-01-01 2016 Winter             1
6 3     2016-01-10 2016 Winter             1
7 4     2012-09-10 2012 Fall               1
8 4     2013-01-24 2013 Winter             3
9 4     2013-04-03 2013 Spring             2

"2013 Spring", for example, comes after "2013 Winter" (at least when you arrange by reg_date). Is there another ranking function or method I can use?

1 Like
2

If you first arrange by reg_date, you can then turn quarter into a factor with the order of the levels set based on their ordering after the rows are sorted by reg_date.

df %>% 
  mutate(reg_date = lubridate::ymd(reg_date)) %>% 
  arrange(reg_date) %>% 
  mutate(quarter = factor(quarter, levels=unique(quarter))) %>% 
  group_by(id) %>% 
  mutate(quarter_order = dense_rank(quarter)) %>% 
  arrange(id, quarter)

# A tibble: 9 x 4
# Groups:   id [4]
  id    reg_date   quarter     quarter_order
  <chr> <date>     <fct>               <int>
1 1     2012-08-22 2012 Summer             1
2 2     2012-07-31 2012 Summer             1
3 2     2014-07-21 2014 Summer             2
4 2     2014-09-14 2014 Fall               3
5 3     2016-01-01 2016 Winter             1
6 3     2016-01-10 2016 Winter             1
7 4     2012-09-10 2012 Fall               1
8 4     2013-01-24 2013 Winter             2
9 4     2013-04-03 2013 Spring             3
4 Likes
3

Thanks Joel! :muscle: