1

I have the below dataset, and I would like to calculate the month interval of for each row.(Including the start month and end month)

month1 <- c("Jul-96","Aug-96","Sep-96")
month2 <- c("Jul-97","Sep-97","Nov-97")

data <- as.data.frame(cbind(month1,month2))

I would like to results to be a dataset as below:

month1  month2  interval 
Jul-96  Jul-97  13
Aug-96  Sep-97  14
Sep-96  Nov-97  15

Thank you.

cyrusjan
  • 637
  • 1
  • 8
  • 23
  • what did you try? does this help http://stackoverflow.com/questions/13287798/calculating-days-per-month-between-interval-of-two-dates?rq=1 fyi you can create `data` simply with `data.frame(month1 = c("Jul-96","Aug-96","Sep-96"), month2 = c("Jul-97","Sep-97","Nov-97"))` – rawr May 26 '16 at 02:24
  • I tried that, but it seems that I could not calculate it in a dataset. Thank you – cyrusjan May 26 '16 at 02:39

2 Answers2

1

It's not overly pretty, but by converting to an as.POSIXlt datetime object, you can then do some simple sums:

with(
  lapply(data, function(x) as.POSIXlt(paste0("01-", x), format="%d-%b-%y", tz="UTC") ),
  (month2$year - month1$year)*12 + month2$mon - month1$mon + 1
)
#[1] 13 14 15
thelatemail
  • 91,185
  • 12
  • 128
  • 188
0

We could do this by converting to yearmon class from zoo and get the difference

library(zoo)
12*(as.yearmon(data$month2, '%b-%y')-as.yearmon(data$month1, '%b-%y'))+1
#[1] 13 14 15

Or using lapply to loop over the columns, convert to yearmon class, get the difference of the corresponding values in the list elements with Reduce, take the absolute (in case it is negative) and do the arithmetic as above.

abs(Reduce(`-`,lapply(data, as.yearmon, format = '%b-%y')))*12 + 1
#[1] 13 14 15
akrun
  • 874,273
  • 37
  • 540
  • 662