Adding a numeric year to numeric month

Question

I've had a long trawl through similar questions and I can't quite find the answer I'm looking for.

I have the following data frame with months occuring over two calandar years.

My aim is to represent the months as 10-2019 through to 09-2020.

I currently have the following data frame:

1    Oct  Running    30.86
2    Nov  Running     28.2
3    Dec  Running    38.44
4    Jan  Running    44.68
5    Feb  Running    22.32
6    Mar  Running    33.68
7    Apr  Running    34.59
8    May  Running    42.28
9    Jun  Running    33.89
10   Jul  Running    26.99
11   Aug  Running    40.07
12   Sep  Running    11.01

I wrote the following function to convert the month abbreviation to a numeric:

month_to_number <- function(x) match(tolower(x), tolower(month.abb))
distance$month <- month_to_number(c("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))

But obviously this doesn't accomodate a year and it results in the data being reordered sequentially, out of order and inconsistent with the data variable. It's a mess:

 month activity distance
1      1  Running    30.86
2      2  Running     28.2
3      3  Running    38.44
4      4  Running    44.68
5      5  Running    22.32
6      6  Running    33.68
7      7  Running    34.59
8      8  Running    42.28
9      9  Running    33.89
10    10  Running    26.99
11    11  Running    40.07
12    12  Running    11.01

So, is there a means of incorporating a function that would convert my month abbreviation to a numeric whilst accomodating the given year (i.e. 2019, 2020)?

user_3962914 · Answer

Maybe you can try :

library(dplyr)
df %>%
  mutate(year_mon = match(V2, month.abb), 
         year_mon = paste(year_mon, 2019 + 
                          cumsum(c(FALSE, diff(year_mon) < 0)), sep = "-"))

#   V1  V2      V3    V4 year_mon
#1   1 Oct Running 30.86  10-2019
#2   2 Nov Running 28.20  11-2019
#3   3 Dec Running 38.44  12-2019
#4   4 Jan Running 44.68   1-2020
#5   5 Feb Running 22.32   2-2020
#6   6 Mar Running 33.68   3-2020
#7   7 Apr Running 34.59   4-2020
#8   8 May Running 42.28   5-2020
#9   9 Jun Running 33.89   6-2020
#10 10 Jul Running 26.99   7-2020
#11 11 Aug Running 40.07   8-2020
#12 12 Sep Running 11.01   9-2020

month.abb is built-in constant in R with abbreviations of month name we use it with match to get the month number. We start adding the year from 2019 and increment the year when the month number decreases from the previous value.

data

df <- structure(list(V1 = 1:12, V2 = c("Oct", "Nov", "Dec", "Jan", 
"Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep"), V3 = c("Running", 
"Running", "Running", "Running", "Running", "Running", "Running", 
"Running", "Running", "Running", "Running", "Running"), V4 = c(30.86, 
28.2, 38.44, 44.68, 22.32, 33.68, 34.59, 42.28, 33.89, 26.99, 
40.07, 11.01)), class = "data.frame", row.names = c(NA, -12L))

Adding a numeric year to numeric month

1 Answers