Converting months to consecutive numbers spanning 4 years

Question

I have

> table(a)
a
 2017-1 2017-10 2017-11  2017-2  2017-4  2017-5  2017-6  2017-7  2017-8  2017-9  2018-1 
      4       3       3       4       3       4       4       3       1       1       1 
2018-10  2018-2  2018-3  2018-4  2018-5  2018-6  2018-7  2019-1 2019-10 2019-11 2019-12 
      2       2       3       1       1       1       1       3       1       2       4 
 2019-2  2019-3  2019-4  2019-5  2019-6  2019-7  2019-8  2020-1 2020-10 2020-11 2020-12 
      1       1       6       2       1       7       3       1       3       3       3 
 2020-2  2020-3  2020-4  2020-5  2020-6  2020-7  2020-8  2020-9 
      2       1       1       2       2       1       1       7

Corresponding to yyyy-m and spanning from 2017-1 to 2020-12, i.e. 48 months. I need to count each month consecutively with 2017-1 corresponding to 1 and 2020-12 to 48.

I followed this thread, but did not solve my question.

I prefer a solution in dplyr, and I tried:

arrange(a, date) %>% mutate(cons_date = dense_rank(date))

But there is obviously something wrong - a$date is not arranged correctly as e.g. 2017-2 exist but comes after 2017-10:

> head(arrange(a, date) %>% mutate(cons_date = dense_rank(date)), 10)
      date cons_date
1   2017-1         1
2   2017-1         1
3   2017-1         1
4   2017-1         1
5  2017-10         2
6  2017-10         2
7  2017-10         2
8  2017-11         3
9  2017-11         3
10 2017-11         3

Data

a <- structure(list(date = c("2018-3", "2019-8", "2017-1", "2020-11", 
"2018-6", "2019-7", "2018-3", "2017-6", "2017-1", "2017-5", "2018-4", 
"2019-5", "2017-11", "2017-11", "2017-10", "2019-11", "2019-6", 
"2019-7", "2019-5", "2020-5", "2017-5", "2019-1", "2017-7", "2019-4", 
"2019-12", "2017-5", "2020-10", "2020-5", "2020-7", "2019-11", 
"2017-9", "2018-2", "2017-4", "2017-2", "2017-2", "2020-2", "2019-4", 
"2020-9", "2017-10", "2017-6", "2018-3", "2017-5", "2017-7", 
"2020-11", "2019-7", "2020-1", "2019-4", "2019-10", "2020-12", 
"2020-3", "2020-6", "2020-11", "2019-4", "2020-6", "2019-4", 
"2018-10", "2017-2", "2020-12", "2019-4", "2018-7", "2019-7", 
"2017-10", "2020-10", "2017-1", "2017-4", "2017-2", "2020-9", 
"2019-1", "2020-9", "2020-8", "2018-1", "2020-2", "2019-7", "2017-6", 
"2020-10", "2019-7", "2017-11", "2018-10", "2019-1", "2018-2", 
"2020-9", "2019-12", "2017-4", "2019-8", "2020-4", "2017-7", 
"2020-9", "2020-12", "2019-2", "2020-9", "2017-1", "2019-12", 
"2019-7", "2018-5", "2019-8", "2017-6", "2020-9", "2019-12", 
"2017-8", "2019-3")), row.names = c(NA, -100L), class = "data.frame")

akrun · Accepted Answer · 2021-03-10T20:45:32.920

3

It is not a Date class, therefore, the order will be alphanumeric. Either we convert to Date class by pasteing a day ('01') or convert to yearmon class with as.yearmon from zoo

library(zoo)
library(dplyr)
a %>%
    arrange(as.yearmon(date)) %>%
    mutate(cons_date = dense_rank(as.yearmon(date))) %>% 
    slice_head(n = 10)

-output

#      date cons_date
#1  2017-1         1
#2  2017-1         1
#3  2017-1         1
#4  2017-1         1
#5  2017-2         2
#6  2017-2         2
#7  2017-2         2
#8  2017-2         2
#9  2017-4         3
#10 2017-4         3

Regarding OP's concern about missing 'date' and if the OP wanted to skip the missing index, then we can use match

a %>% 
   mutate(date1 = as.yearmon(date)) %>%
   arrange(date1) %>% 
   mutate(cons_date = match(date1, as.yearmon(seq(min(as.Date(date1)),
         max(as.Date(date1)), by = '1 month')))) %>% 
   select(-date1) %>% 
   slice_head(n = 10)
#      date cons_date
#1  2017-1         1
#2  2017-1         1
#3  2017-1         1
#4  2017-1         1
#5  2017-2         2
#6  2017-2         2
#7  2017-2         2
#8  2017-2         2
#9  2017-4         4
#10 2017-4         4

edited Mar 10 '21 at 20:45

answered Mar 10 '21 at 20:23

akrun

874,273
37
540
662

Hi akrun. Thank you very much for your feedback. Your output is incorrect as `2017-2` should correspond to `2`, and `2017-4` should be `4`. – cmirian Mar 10 '21 at 20:35
@cmirian why would it be 4 instead of 3. You don't have 2017-3 – akrun Mar 10 '21 at 20:38
Not in this `dput` but I have all months per year in my original data, i.e. `2017-1.. -2.. -3.. -4..` so that all years range `1-12` – cmirian Mar 10 '21 at 20:40
@cmirian in that case, it should work on your original dataset – akrun Mar 10 '21 at 20:43
@cmirian its okay. I added one more solution if there are missing elements and to skip those – akrun Mar 10 '21 at 20:46

Converting months to consecutive numbers spanning 4 years

1 Answers1