5

I currently have a problem, where I have a data table with start and end date together with an ID. A new table should have the same ID with one column date, containing a sequence of days between first and last day, one day for each row, i.e.

old.table
ID          first.date               last.date
1           2001-01-01               2001-01-03
2           2002-02-01               2002-02-04

new table
ID          date
1           2001-01-01
1           2001-01-02
1           2001-01-03
2           2002-02-01
2           2002-02-02
2           2002-02-03
2           2002-02-04

I know, that calling

seq(first.date,last.date,"day")

creates such a sequence. However, calling 

old.table[,date := seq(first.date,last.date,"day")])

throws the exception 

 Error in seq.Date(first.date, last.date, "day") : 
'from' must be of length 1

meaning, that vectorizing the input is not possible (at least not like this).

Is there a way, to get the desired solution?

Jaap
  • 81,064
  • 34
  • 182
  • 193
bublitz
  • 888
  • 2
  • 11
  • 21

1 Answers1

6

As seq takes only a single element in from and to, we can group by 'id' (if there are no duplicates) and then do the seq

setDT(old.table)[, .(date = seq(first.date, last.date, by = '1 day')), by = ID]  
#   ID       date
#1:  1 2001-01-01
#2:  1 2001-01-02
#3:  1 2001-01-03
#4:  2 2002-02-01
#5:  2 2002-02-02
#6:  2 2002-02-03
#7:  2 2002-02-04

If the 'ID' is also duplicated, then group by the sequence of rows instead

setDT(old.table)[, .(date = seq(first.date, last.date, by = '1 day')), 
                        by = .(ID, ID2 = seq_len(nrow(old.table)))]
akrun
  • 874,273
  • 37
  • 540
  • 662