R:在 data.table 中每个 ID 的开始日期和结束日期之间插入新的每日行

Question

我有一个大数据表，其中包含每个 ID 事件的开始和结束日期:

library(data.table)
dt = data.table(
    ID = c(1,1,2,2),
    STARTDATE = as.Date(c("2011-10-10","2011-10-13","2011-10-10","2011-10-13"),format = "%Y-%m-%d"),
    ENDDATE = as.Date(c("2011-10-12","2011-10-15","2011-10-12","2011-10-15"),format = "%Y-%m-%d")
)
dt   
>   ID  STARTDATE    ENDDATE
>1:  1 2011-10-10 2011-10-12
>2:  1 2011-10-13 2011-10-15
>3:  2 2011-10-10 2011-10-12
>4:  2 2011-10-13 2011-10-15

我想将时间窗口中每个 ID 和日期的行添加到此数据表中，预期结果如下:

    STARTDATE    ENDDATE ID      DAILY
1: 2011-10-10 2011-10-12  1 2011-10-10
2: 2011-10-10 2011-10-12  1 2011-10-11
3: 2011-10-10 2011-10-12  1 2011-10-12
4: 2011-10-13 2011-10-15  1 2011-10-13
5: 2011-10-13 2011-10-15  1 2011-10-14
6: 2011-10-13 2011-10-15  1 2011-10-15
7: 2011-10-10 2011-10-12  2 2011-10-10
8: 2011-10-10 2011-10-12  2 2011-10-11
9: 2011-10-10 2011-10-12  2 2011-10-12
10: 2011-10-13 2011-10-15  2 2011-10-13
11: 2011-10-13 2011-10-15  2 2011-10-14
12: 2011-10-13 2011-10-15  2 2011-10-15

我的代码如下所示:

dt[, cbind(.SD, seq(STARTDATE, ENDDATE, 1)), by = list(STARTDATE, ENDDATE)] 

但它不会产生想要的结果:

    STARTDATE    ENDDATE ID         V2
1: 2011-10-10 2011-10-12  1 2011-10-10
2: 2011-10-10 2011-10-12  2 2011-10-11
3: 2011-10-10 2011-10-12  1 2011-10-12
4: 2011-10-13 2011-10-15  1 2011-10-13
5: 2011-10-13 2011-10-15  2 2011-10-14
6: 2011-10-13 2011-10-15  1 2011-10-15
Warnmeldungen:
1: In data.table::data.table(...) :
  Item 1 is of size 2 but maximum size is 3 (recycled leaving remainder of 1 items)
2: In data.table::data.table(...) :
  Item 1 is of size 2 but maximum size is 3 (recycled leaving remainder of 1 items)

它在某处需要 ID，但我无法将其输入 by数据表的一部分。它给出了另一个错误。有任何想法吗？

Answer 1

这是一个选项。请注意，我们可以使用 by = 1:nrow(dt)为每一行指定分组，这会导致一个名为 nrow 的新列.然后我们可以使用 [, nrow := NULL]删除该列。

library(data.table)

dt2 <- dt[, .(STARTDATE, ENDDATE, ID, 
              DAILY = seq(STARTDATE, ENDDATE, by = 1)), 
          by = 1:nrow(dt)][, nrow := NULL]
print(dt2[])
#      STARTDATE    ENDDATE ID      DAILY
#  1: 2011-10-10 2011-10-12  1 2011-10-10
#  2: 2011-10-10 2011-10-12  1 2011-10-11
#  3: 2011-10-10 2011-10-12  1 2011-10-12
#  4: 2011-10-13 2011-10-15  1 2011-10-13
#  5: 2011-10-13 2011-10-15  1 2011-10-14
#  6: 2011-10-13 2011-10-15  1 2011-10-15
#  7: 2011-10-10 2011-10-12  2 2011-10-10
#  8: 2011-10-10 2011-10-12  2 2011-10-11
#  9: 2011-10-10 2011-10-12  2 2011-10-12
# 10: 2011-10-13 2011-10-15  2 2011-10-13
# 11: 2011-10-13 2011-10-15  2 2011-10-14
# 12: 2011-10-13 2011-10-15  2 2011-10-15