R The best way to create time series from start and end dates for groups
I have a dataset where for each group I have a start and end date. I want to turn this data into one where for every time period (month) I have one observation row for each group.
Here is an example of input, groups are identified by id:
structure(list(id = c(723654, 885618, 269861, 1383642, 250276,
815511, 1506680, 1567855, 667345, 795731), startdate = c("2008-06-29",
"2008-12-01", "2006-09-27", "2010-02-03", "2006-08-31", "2008-09-10",
"2010-04-11", "2010-05-15", "2008-04-12", "2008-08-28"), enddate = c("2008-08-13",
"2009-02-08", "2007-10-12", "2010-09-09", "2007-06-30", "2010-04-27",
"2010-04-13", "2010-05-16", "2010-04-20", "2010-03-09")), .Names = c("id",
"startdate", "enddate"), class = "data.frame", row.names = c("1",
"2", "3", "4", "6", "7", "8", "9", "10", "11"))
I wrote a function and vectorized it. The function takes three parameters stored in each line and generates a time series with group IDs.
genDateRange<-function(start, end, id){
dates<-seq(as.Date(start), as.Date(end), by="month")
return( cbind(month=as.character(dates), id=rep(id, length(dates))))
}
genDataRange<-Vectorize(genDateRange)
I am running the function as follows to get a data frame. I have over 6M lines in the output, so this takes forever. I need a faster way.
range<-do.call(rbind,genDataRange(dat$startdate, dat$enddate, dat$id))
The first ten lines of output looks like this:
structure(c("2008-06-29", "2008-07-29", "2008-12-01", "2009-01-01",
"2009-02-01", "2006-09-27", "2006-10-27", "2006-11-27", "2006-12-27",
"2007-01-27", "723654", "723654", "885618", "885618", "885618",
"269861", "269861", "269861", "269861", "269861"), .Dim = c(10L,
2L), .Dimnames = list(NULL, c("month", "id")))
I would appreciate a faster way to do this. I think I focused too much on something and missed a much simpler solution.
source to share
No need to use a generator function or rbindlist
because it data.table
can easily handle it without it.
# start with a data.table and date columns
library(data.table)
dat <- data.table(dat)
dat[,`:=`(startdate = as.Date(startdate), enddate = as.Date(enddate))]
dat[,num_mons:= length(seq(from=startdate, to=enddate, by='month')),by=1:nrow(dat)]
dat # now your data.table looks like this
# id startdate enddate num_mons
# 1: 723654 2008-06-29 2008-08-13 2
# 2: 885618 2008-12-01 2009-02-08 3
# 3: 269861 2006-09-27 2007-10-12 13
# 4: 1383642 2010-02-03 2010-09-09 8
# 5: 250276 2006-08-31 2007-06-30 10
# 6: 815511 2008-09-10 2010-04-27 20
# 7: 1506680 2010-04-11 2010-04-13 1
# 8: 1567855 2010-05-15 2010-05-16 1
# 9: 667345 2008-04-12 2010-04-20 25
# 10: 795731 2008-08-28 2010-03-09 19
out <- dat[, list(month=seq.Date(startdate, by="month",length.out=num_mons)), by=id]
out
# id month
# 1: 723654 2008-06-29
# 2: 723654 2008-07-29
# 3: 885618 2008-12-01
# 4: 885618 2009-01-01
# 5: 885618 2009-02-01
# ---
# 98: 795731 2009-10-28
# 99: 795731 2009-11-28
# 100: 795731 2009-12-28
# 101: 795731 2010-01-28
# 102: 795731 2010-02-28
This question is related, but the difference is that in your question we are iterating over rather than duplicating rows in the data table.
source to share