Change date variable to continuous month

Question

I have a dataframe with a column called "Date" that is in the 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 make a column that changes the unique year/month combo in the Date column to a continuous month variable. I have 20 years of data and would have months from 1-240.

So the example for the df above would 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 would be the first month, and I have skipped months 02/1997(2nd month)-12/1997 (12th month) in the example dataframe, so 01/1998 would be the 13th month in the series.

Answer 1

You can use year and month of lubridate to extract 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