r - 自事件重复发生以来累计经过的天数(按组)

Question

我想计算自 event==1 以来经过的累计天数。是否可以使用 data.table 在 R 中执行此操作？

期望的结果:

    id       date event passed
 1:  A 2000-01-13     1      0
 2:  A 2000-01-18     0      5
 3:  A 2000-01-25     0     12
 4:  A 2000-01-31     1      0
 5:  B 2012-10-10     1      0
 6:  B 2012-10-11     0      1
 7:  B 2012-10-14     1      0
 8:  B 2012-10-15     0      1
 9:  C 2005-07-25     1      0
10:  C 2005-07-31     0      6

df <- data.table(
  id    = c("A", "A", "A", "A", 
            "B", "B", "B", "B", 
            "C", "C"), 
  date  = c("2000-01-13", "2000-01-18", "2000-01-25", "2000-01-31", # A
            "2012-10-10", "2012-10-11", "2012-10-14", "2012-10-15", # B
            "2005-07-25", "2005-07-31"),                            # C
  event = c(1, 0, 0, 0,
            0, 0, 1, 0,
            1, 0)
    )

编辑 (12/12/17):尝试使用@Psidom 的解决方案。

解决方案要求对 id 和 date 进行排序，这不是问题。但是注意到第 6 行:算作一天，尽管这应该是 0，因为该组尚未发生任何事件。

df2 <- df[sample(nrow(df)),]
df2 = df2[order(id, date)]
df2[, days_from_start := cumsum(c(0, diff(as.Date(date)))), by = .(id, cumsum(event))]

    id       date event days_from_start
 1:  A 2000-01-13     1               0
 2:  A 2000-01-18     0               5
 3:  A 2000-01-25     0              12
 4:  A 2000-01-31     0              18
 5:  B 2012-10-10     0               0
 6:  B 2012-10-11     0               1
 7:  B 2012-10-14     1               0
 8:  B 2012-10-15     0               1
 9:  C 2005-07-25     1               0
10:  C 2005-07-31     0               6

Answer 1

如果event列仅包含 0和 1 ，您可以通过执行 cumsum(event) 创建一个组变量每当 event 时创建一个新组是1 ;然后按这个新变量分组，计算累计天数:

df[, days_from_start := cumsum(c(0, diff(as.Date(date)))), by = cumsum(event)]
#                                                               ^^^^^^^^^^^^^
df
#    id       date event days_from_start
# 1:  A 2000-01-13     1               0
# 2:  A 2000-01-18     0               5
# 3:  A 2000-01-25     0              12
# 4:  A 2000-01-31     1               0
# 5:  B 2012-10-10     1               0
# 6:  B 2012-10-11     0               1
# 7:  B 2012-10-14     1               0
# 8:  B 2012-10-15     0               1
# 9:  C 2005-07-25     1               0
#10:  C 2005-07-31     0               6