CODEWITHSUNDEEP
r
Ally Kat
Ally Kat

Reputation: 211

How do I create a new column with difference in date in r

My data Looks Like this

acc<-c(1:7)
openD<-c("2011-02-12","2015-03-12","2015-01-19","2014-08-12","2013-01-12","2015-01-01","2011-06-15")
Vdate<-c("2011-02-27","2015-04-28","2015-02-02","2015-08-11","2015-01-02","NA","2015-06-15")

datasm <- data.frame(acc,openD,Vdate)

I am trying to figure out how to create a new colume that will compare the openD to the Vdate. essentially and if then statement: IF - openD or Vdate is NA THEN "unknown" ELSE IF Open is in same month as Vdate THEN "InMonth" ELSE IF Open is in following month as Vdate "In Month +1" ELSE IF Open is within 1 year of Vdate "In 1 Year" ELSE IF Open is within 2 years of Vdate "In 2 Years" ELSE "More than 2 years"

Resulting in something like this:

TimeDif<- c("InMonth","In Month +1", "In Month +1", "In 1 Year","In 2 Years", "Unknown", "More than 2 years")
datasre<- data.frame(acc,openD,Vdate,TimeDif)

i have looked all over and cant find anything that looks at dates this way. New to r so bear with me

Upvotes: 1

Views: 313

Answers (1)

jlhoward
jlhoward

Reputation: 59425

Ugly beyond belief, but seems to work. Takes advantage of the internal formatting of yearmon(...) in the zoo package.

f <- function(x,y) {
  require(zoo)   # for as.yearmon(...)
  x <- as.numeric(as.yearmon(as.Date(x)))
  y <- as.numeric(as.yearmon(as.Date(y)))
  ifelse((is.na(x) | is.na(y)), "Unknown",
         ifelse(x==y, "InMonth", 
                ifelse(y-x<0.1, "Month+1", 
                       ifelse(y-x <= 1, "In 1 Year", 
                              ifelse(y-x<=2, "In 2 Years", "More Than 2 Years")))))
}
with(datasm, f(openD, Vdate))
# [1] "InMonth"           "Month+1"           "Month+1"           "In 1 Year"         "In 2 Years"        "Unknown"           "More Than 2 Years"

Slightly cleaner version:

f <- function(x,y) {
  require(zoo)   # for as.yearmon(...)
  x <- as.numeric(as.yearmon(as.Date(x)))
  y <- as.numeric(as.yearmon(as.Date(y)))
  ret.val <- c("InMonth","Month+1", "In 1 Year", "In 2 Years", "More Than 2 Years")
  brks    <- c(0, 0.08, 1, 2, Inf)
  ifelse((is.na(x) | is.na(y)), "Unknown", ret.val[cut(y-x, brks, labels=FALSE, right=FALSE)])
}
with(datasm, f(openD, Vdate))
# [1] "InMonth"    "Month+1"    "Month+1"    "In 1 Year"  "In 2 Years" "Unknown"    "In 2 Years"

Upvotes: 1

Related Questions