r - 根据组计算日期范围之间的情况

Question

我一直在尝试找到一种相对简单的方法来使用 R 按组对日期范围内的出现次数进行计数。我认为必须有一种比我正在尝试的方法更简单的方法。

我有超过 6,000 个组，每个组内有 1 到 100 个 ID，每个组的开始日期和结束日期为 1990 年 1 月 1 日到今天。我想制作一个数据框，每列一组，每行一天，计算从2013年4月1日到2018年3月31日每天活跃的ID数量。出于显而易见的原因，在excel中使用countifs不会减少它。

我试图使用this question作为起点，如下所示:

df1 <- data.frame(group = c(1,1,2,3,3),
              id = c(1,2,1,1,2),
              startdate = c("2016-01-01","2016-04-04","2016-03-02","2016-08-01","2016-04-01"), 
              enddate = c("2016-04-04","2999-01-01","2016-05-02","2016-08-05","2999-01-01"))

report <- data.frame(date = seq(from = as.Date("2016-04-01"),by="1 day", length.out = 7))
report <- cbind(report,matrix(data=NA,nrow=7,ncol=3))
names(report) <- c('date',as.vector(unique(df1$group)))

daily <- function(i,...){
    report[,i+1] <- sapply(report$date, function(x)
    sum(as.Date(df1$startdate) < as.Date(x) &
        as.Date(df1$enddate) > as.Date(x) & 
       df1$group == unique(df1$group)[i]))
        }

for (i in unique(df1$group))
  daily(i)

但是，这似乎没有做任何事情(也没有抛出错误)。有没有更简单的方法来做到这一点？我离基地太远了吗？感谢这位非程序员的任何帮助!

请求其他帮助:我正在尝试修改下面答案中的 Jaap 代码，以包含组开始和组结束时间，以便在组不活动时数据表显示 NA。

示例数据:

df2 <- data.frame(group = c(1,1,2,3,3),
                  groupopendate = c("2016-04-02","2016-04-02","2016-04-01","2016-04-02","2016-04-02"),
                  groupclosedate = c("2016-04-08","2016-04-08","2016-04-10","2016-04-09","2016-04-09"),
                  id = c(1,2,1,1,2),
                  startdate = c("2016-04-02","2016-04-04","2016-04-03","2016-04-02","2016-04-05"), 
                  enddate = c("2016-04-04","2016-04-06","2016-04-10","2016-04-08","2016-04-08"))

Jaap 的解决方案给了我这个:

       active grp1 grp2 grp3
1: 2016-04-02    1    0    1
2: 2016-04-03    1    1    1
3: 2016-04-04    1    1    1
4: 2016-04-05    1    1    2
5: 2016-04-06    0    1    2
6: 2016-04-07    0    1    2

但是，我想要的是这样的:

        active grp1 grp2 grp3
1:  2016-04-01   NA    0   NA
2:  2016-04-02    1    0    1
3:  2016-04-03    1    1    1
4:  2016-04-04    1    1    1
5:  2016-04-05    1    1    1
6:  2016-04-06    1    1    2
7:  2016-04-07    0    1    2
8:  2016-04-08   NA    1    0
9:  2016-04-09   NA    1   NA
10: 2016-04-10   NA   NA   NA

感谢任何帮助!

Answer 1

使用 data.table 的可能替代解决方案:

# load the package & convert 'df1' to a data.table
library(data.table)
setDT(df1)

# convert the date columns to a date format
# not needed if they are 
df1[, `:=` (startdate = as.Date(startdate), enddate = as.Date(enddate))]

# create a new data.table with the 'active' days
DT <- data.table(active = seq(from = as.Date("2016-04-01"), by = "day", length.out = 7))

# use a join and dcast to get the desired result
DT[df1
   , on = .(active > startdate, active < enddate)
   , allow = TRUE
   , nomatch = 0
   , .(active = x.active, group, id)
   ][, dcast(.SD, active ~ paste0("grp",group), value.var = "id", fun = length)]

给出:

       active grp1 grp2 grp3
1: 2016-04-01    1    1    0
2: 2016-04-02    1    1    1
3: 2016-04-03    1    1    1
4: 2016-04-04    0    1    1
5: 2016-04-05    1    1    1
6: 2016-04-06    1    1    1
7: 2016-04-07    1    1    1

注意:我在 dcast 步骤中使用了 paste0("grp",group) 而不是仅使用 group，因为它会导致更好的列名(最好不要仅使用数值作为列名)

---

关于您的附加示例，您可以按如下方式解决:

setDT(df2)

df2[, c(2:3,5:6) := lapply(.SD, as.Date), .SDcols = c(2:3,5:6)]

DT <- data.table(active = seq(from = min(df2$groupopendate),
                              to = max(df2$groupclosedate),
                              by = "day"))

df2new <- df2[, .(active = seq.Date(startdate, enddate, by = "day"))
              , by = .(group, id)
              ][, .N, by = .(group, active)
                ][df2[, .(active = seq.Date(groupopendate[1], groupclosedate[.N] - 1, by = "day"))
                      , by = .(group)]
                  , on = .(group, active)
                  ][is.na(N), N := 0
                    ][, dcast(.SD, active ~ paste0("grp",group))]

nms <- setdiff(names(df2new), "active")

DT[df2new
   , on = .(active)
   , (nms) := mget(paste0("i.",nms))][]

给出:

> DT
        active grp1 grp2 grp3
 1: 2016-04-01   NA    0   NA
 2: 2016-04-02    1    0    1
 3: 2016-04-03    1    1    1
 4: 2016-04-04    2    1    1
 5: 2016-04-05    1    1    2
 6: 2016-04-06    1    1    2
 7: 2016-04-07    0    1    2
 8: 2016-04-08   NA    1    2
 9: 2016-04-09   NA    1   NA
10: 2016-04-10   NA    1   NA