r - 如何在给定日期之前广播缺少值的data.table

 DT <- data.table(Id = c(1, 1, 1, 1, 10, 100, 100, 101, 101, 101),
                 Date = as.Date(c("1997-01-01", "1997-01-02", "1997-01-03", "1997-01-04",
                                  "1997-01-02", "1997-01-02", "1997-01-04", "1997-01-03",
                                  "1997-01-04", "1997-01-04")),
                 Price = c(29, 25, 14, 26, 30, 16, 13, 62, 12,  6),
                 IsFirst = c(T,F,F,F,T,T,F,T,F,F))

ID:客户ID；日期:交易日期；价格:交易金额； isFirst:TRUE是交易是客户至上。每个客户都有1个IsFirst == TRUE，并且[0，Inf)IsFirst == FALSE。

>DT
     Id       Date Price IsFirst
 1:   1 1997-01-01    29    TRUE
 2:   1 1997-01-02    25   FALSE
 3:   1 1997-01-03    14   FALSE
 4:   1 1997-01-04    26   FALSE
 5:  10 1997-01-02    30    TRUE
 6: 100 1997-01-02    16    TRUE
 7: 100 1997-01-04    13   FALSE
 8: 101 1997-01-03    62    TRUE
 9: 101 1997-01-04    12   FALSE
10: 101 1997-01-04     6   FALSE

我需要把它放进去

    Id 1997-01-01 1997-01-02 1997-01-03 1997-01-04
1:   1         29         25         14         25
2:  10         NA         30          0          0
3: 100         NA         16          0         13
4: 101         NA         NA         62         18

NA值应仅在客户进行首次交易之前出现。在第一笔交易之后，缺失值应填充为0。我尝试过:

dcast.data.table(DT, Id ~ Date, fun = sum, value.var = "Price", fill = NA)

但这不起作用，因为它会用NA填充所有缺少的字段。目前，我正在使用循环遍历所有Id，然后在将客户IsFirst转到NA之前手动设置字段:

DT2 <- dcast.data.table(DT, Id ~ Date, fun = sum, value.var = "Price")

Ids <- unique(DT$Id)
for(id in Ids){
  if(DT[(Id == id & IsFirst == T),]$Date > as.Date(names(DT2)[2])){
    DT2[Id == id, 2:(which(names(DT2)==as.character(DT[(Id == id & IsFirst == T),]$Date))-1) := NA, with = F]
  }
}

当我的数据变大时，这非常慢。什么是最快，最有效的方法？

最佳答案

类型转换熔铸

dDT <- dcast(DT, Id~Date, sum, value.var="Price")
setDT(dDT) # if not using data.table 1.9.5+

mDT <- melt(dDT,id.vars = c("Id"), variable.name="Date", value.name="Price")
mDT[, `:=`(idi = 1:.N, first_sale = which.max(!!Price)), by=Id]
mDT[ idi < first_sale, Price := NA_real_ ]

res <- dcast(mDT, Id~Date, sum, value.var="Price")

合并广播

setkey(DT,Id,Date)
mergeDT <- DT[, .(Price=sum(Price)), by=key(DT)][CJ(unique(Id),unique(Date))]
mergeDT[, ok := cumsum(!is.na(Price)) > 0, by=Id]
mergeDT[ok & is.na(Price), Price := 0]

res2 <- dcast(mergeDT, Id~Date, value.var="Price")

我发现这种方式更直观，可以在转换前以长篇幅形式进行所有操作。