Change date variable to month

Question

I have a framework with a Date column, which is in a date format:

df<- data.frame(date=c("1997-01-01", "1997-01-02", "1997-01-03", "1997-01-04", "1997-01-05", "1997-01-06" ,"1997-01-07" ,"1997-01-08","1998-01-12", "1998-01-13", "1998-01-14", "1998-01-15" ,"1998-01-16", "1998-01-17", "1998-01-18", "1998-01-19")) 

And I need to create a column that changes the unique combination of year / month in the Date column to a continuous month variable. I have 20 years of data, and I will have months from 1-240.

So the example for df above will return:

 output<- data.frame(date=c("1997-01-01", "1997-01-02", "1997-01-03", "1997-01-04", "1997-01-05", "1997-01-06" ,"1997-01-07" ,"1997-01-08","1998-01-12", "1998-01-13", "1998-01-14", "1998-01-15" ,"1998-01-16", "1998-01-17", "1998-01-18", "1998-01-19"), continuous_month=c("1", "1", "1", "1", "1", "1" ,"1" ,"1","13", "13", "13", "13" ,"13", "13", "13", "13")) 

NOTE: 01/1997 will be the first month, and I skipped the months 02/1997 (2nd month) -12/1997 (12th month) in the sample data frame, so 01/1998 will be the 13th month in the series.

Answer 1

You can use year and month for lubridate to retrieve relevant information

 library(lubridate) df$date = ymd(df$date) temp = 12*(year(df$date) - min(year(df$date))) + month(df$date) df$output = temp - min(temp) + 1 df$output # [1] 1 1 1 1 1 1 1 1 13 13 13 13 13 13 13 13 

Answer 2

1) class yearmon

The bounce class is "yearmon" year / month within the country as a year plus 0, 1/12, 2/12, etc. for January, February, March, etc. So:

 library(zoo) ym <- as.yearmon(df$date) 12 * (ym - ym[1]) + 1 ## [1] 1 1 1 1 1 1 1 1 13 13 13 13 13 13 13 13 

2) POSIXlt class

Solution using the "POSIXlt" base class:

 with(as.POSIXlt(df$date), 12 * (year - year[1]) + mon - mon[1] + 1) ## [1] 1 1 1 1 1 1 1 1 13 13 13 13 13 13 13 13 

Answer 3

we can use base R

 df$continuous_month <- seq(1, 240, by = 12)[(as.integer(sub(".*-", "", df$date))%/%12) + 1] df$continuous_month #[1] 1 1 1 1 1 1 1 1 13 13 13 13 13 13 13 13 

Answer 4

Another basic R solution with another change added to the data:

 > # Format as date > df$date <- as.Date(df$date) > > # Add some more variations to the data > df$date <- df$date + sample(1:100, size = length(df$date)) > > # First the year difference and then the month difference. > df$continuous_month <- (as.integer(format(df$date, "%Y")) - 1997L) * 12L + + as.integer(format(df$date, "%m")) > df date continuous_month 1 1997-01-28 1 2 1997-02-05 2 3 1997-01-31 1 4 1997-01-09 1 5 1997-01-15 1 6 1997-01-29 1 7 1997-03-23 3 8 1997-02-24 2 9 1998-02-20 14 10 1998-02-18 14 11 1998-01-17 13 12 1998-02-22 14 13 1998-02-01 14 14 1998-04-06 16 15 1998-02-12 14 16 1998-03-01 15