How to dcast a data.table with missing values before given date

Question

 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: customer id; Date: date of transaction; Price: transaction amount; isFirst: TRUE is the transaction is the customers first. Each customer has 1 IsFirst == TRUE, and [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

I need to cast it into

    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

The NA values should appear only before the customer has made his/her first transaction. After the first transaction the missing values should be filled with 0. I have tried:

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

but this doesn't work as it fills all missing fields with NA. Currently I am using a loop to iterate over all Ids manually set the fields before a customers IsFirst to 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]
  }
}

This is very slow when my data gets large. What is the fastest and most efficient way to do this?

Answer 1

cast-melt-cast

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")

merge-cast

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")

I find this way more intuitive, doing everything in long form before casting.