CODEWITHSUNDEEP
rdate
Joep_S
Joep_S

Reputation: 537

Compare year month in data frame and retrieve current month -1

In a data frame with 2 date columns, how can I compare the month year and create a new variable? In particular I would like to compare dat1 with sysdat and check if dat1 is in the month prior to the month of sysdat.

df <- data.frame(dat1 = as.Date(c("2019-01-01","2019-02-15","2019-08-23","2019-09-12")),
             sysdat = as.Date(c("2019-09-24","2019-09-24","2019-09-24","2019-09-24"))

The result I would like is an extra column displaying 1 if dat1 is one month prior to the month of sysdat and 0 in all other cases. So in the example below that would only be row 3.

dat1     sysdat x
1 2019-01-01 2019-09-24 0
2 2019-02-15 2019-09-24 0
3 2019-08-23 2019-09-24 1
4 2019-09-12 2019-09-24 0

Upvotes: 0

Views: 269

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389175

In base R, we can extract year and month from the columns using format and return 1 if the years are same and the difference between months is 1.

df$x <- with(df, as.integer(format(dat1, "%Y") == format(sysdat, "%Y") & 
      (as.integer(format(sysdat, "%m")) - as.integer(format(dat1, "%m")) == 1)))

df
#        dat1     sysdat x
#1 2019-01-01 2019-09-24 0
#2 2019-02-15 2019-09-24 0
#3 2019-08-23 2019-09-24 1
#4 2019-09-12 2019-09-24 0

In lubridate, we can use year and month functions to get year and month respectively.

library(lubridate)
df$x <- with(df, as.integer(year(dat1) == year(sysdat) & 
                (month(sysdat) - month(dat1) == 1))

Upvotes: 1

Related Questions