代码之家 › 专栏 › 技术社区 › A. Mullins

基于组的日期范围之间的计数

A. Mullins · 技术社区 · 6 年前

我一直在试图找到一种相对简单的方法,用r按组计算某个日期范围内的事件。我明白,一定有一种比我正在尝试的方法更简单的方法。

我有6000多个组,每个组内有1到100个ID,每个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

感谢您的帮助!

2 回复 | 直到 6 年前

Jaap 6 年前

使用 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

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

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

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

MrFlick 6 年前

我明白了!像往常一样,一旦你发布一个问题,你就会找出答案。当我可以将SAPPLY放到for循环中时,我通过引入函数来过度简化它。

如果有人感兴趣:

for (i in unique(df1$group))
  {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]))}