Generate date series in data.table

Question

I have a fairly simple question. See the following data.table (dt):

library(data.table)

dt = data.table(id = c(rep("1",5), rep("2",10)))
dt$date <- as.Date("2000-01-01")
dt[, Index := seq_len(.N)-1, by = c("id")]

dt
 id          date Index
 1:  1 2000-01-01     0
 2:  1 2000-01-01     1
 3:  1 2000-01-01     2
 4:  1 2000-01-01     3
 5:  1 2000-01-01     4
 6:  2 2000-01-01     0
 7:  2 2000-01-01     1
 8:  2 2000-01-01     2
 9:  2 2000-01-01     3
10:  2 2000-01-01     4
11:  2 2000-01-01     5
12:  2 2000-01-01     6
13:  2 2000-01-01     7
14:  2 2000-01-01     8
15:  2 2000-01-01     9

From this I want to create an additional column (lets call it date2) where each value in Index represents the relevant month in the future.

I.e. when index is 0 (first and sixth row) date2 = date. However, when index = 2 then date2 = (date + 2 months) = 2000-03-01 and so on.

A tried this function:

dt$date2 <- seq(dt$date, by = paste(dt$Index ,"months"), length = 2)[2]

Unfortunately, I can only pass one value to from and by. I have a very large dataset and would like to have a fast and simple solution.

score 2 · Accepted Answer · answered Dec 28 '15 at 10:55

You can use the lubridate package and the MONTH function:

library(lubridate)

# First copye the date column
dt$date2 <- dt$date
# Then apply MONTH function
month(dt$date2) <- month(dt$date2) + dt$Index

The result is

    id       date Index      date2
1:   1 2000-01-01     0 2000-01-01
2:   1 2000-01-01     1 2000-02-01
3:   1 2000-01-01     2 2000-03-01
4:   1 2000-01-01     3 2000-04-01
5:   1 2000-01-01     4 2000-05-01
6:   2 2000-01-01     0 2000-01-01
7:   2 2000-01-01     1 2000-02-01
8:   2 2000-01-01     2 2000-03-01
9:   2 2000-01-01     3 2000-04-01
10:  2 2000-01-01     4 2000-05-01
11:  2 2000-01-01     5 2000-06-01
12:  2 2000-01-01     6 2000-07-01
13:  2 2000-01-01     7 2000-08-01
14:  2 2000-01-01     8 2000-09-01
15:  2 2000-01-01     9 2000-10-01

Generate date series in data.table

1 Answers1