How to get date from a condition on certain column

Question

I am working of data that has wave number and they represent the data for a certain month of a certain year. The data is bi-monthly so an increment in wave column means the increase of 2 months The column of wave looks something like this: |wave| |----| |1 | |1 | |1 | |2 | |2 | |3 | |3 | |3 | |4 | |5 | |5 | |5 | |7 | |8 |

Now from this I want to create a date column that corresponds to the wave number. I have values of wave ranging from 1 to 64. I want to add the column of date in my data frame as follows

wave	date
1	jan2012
1	jan2012
1	jan2012
2	mar2012
2	mar2012
3	may2012
3	may2012
3	may2012
4	jul2012
5	sep2012
5	sep2012
5	sep2012
6	nov2012
7	jan2013

Answer 1

I used dplyr and zoo which has a year-month date format:

library(dplyr)
library(zoo)

df <- data.frame(wave = c(1,1,1,2,2,3,3,3,4,5,5,5,6,7))

opener <- as.yearmon("2021-01")

df %>% mutate(date = opener + ((wave-1)*2/12))

Which gives us:

   wave     date
1     1 Jan 2021
2     1 Jan 2021
3     1 Jan 2021
4     2 Mar 2021
5     2 Mar 2021
6     3 May 2021
7     3 May 2021
8     3 May 2021
9     4 Jul 2021
10    5 Sep 2021
11    5 Sep 2021
12    5 Sep 2021
13    6 Nov 2021
14    7 Jan 2022

Explanation: Each wave after 1 adds 2/12 (i.e. two months) to the date.

Edit: I put the wrong opening year/month, sorry, but the principle remains the same! Make sure to correct the "opener" if you try this.

Answer 2

For a solution that does not actually use Date objects at all, you can use integer division to turn your wave into month and year, then paste these together:

df$date = paste(
    month.name[((df$wave - 1) %% 6) * 2 + 1],   # the modulo picks the month name
    2012 + (df$wave - 1) %/% 6                  # the integer quotient picks the year
)

> df
   wave           date
1     1   January 2012
2     1   January 2012
3     1   January 2012
4     2     March 2012
5     2     March 2012
6     3       May 2012
7     3       May 2012
8     3       May 2012
9     4      July 2012
10    5 September 2012
11    5 September 2012
12    5 September 2012
13    6  November 2012
14    7   January 2013

In this case, I am using the built-in month.name array for convenience, but to replicate exactly your example, you could set up your own array

months <- c("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")

df$date = paste0(                              # paste0 omits the space by default
    months[((df$wave - 1) %% 6) * 2 + 1],   
    2012 + (df$wave - 1) %/% 6                  
)

> df
   wave    date
1     1 jan2012
2     1 jan2012
3     1 jan2012
4     2 mar2012
5     2 mar2012
6     3 may2012
7     3 may2012
8     3 may2012
9     4 jul2012
10    5 sep2012
11    5 sep2012
12    5 sep2012
13    6 nov2012
14    7 jan2013