r - Efficiently inserting default missing rows in a data.table -

suppose i've got following data.table :

dt <- data.table(id=c(1,1,1,1,1,1,2,2,2,2),            wday=c("mon","tue","wed","thu","fri","sat","mon","tue","thu","fri"),            val=c(2,3,5,8,6,2,3,4,2,6))      id wday val  1:  1  mon   2  2:  1  tue   3  3:  1  wed   5  4:  1  thu   8  5:  1  fri   6  6:  1  sat   2  7:  2  mon   3  8:  2  tue   4  9:  2  thu   2 10:  2  fri   6 

this result of aggregation of data.table. represents count (val) of variable depending on week day (wday) different individuals (id). problem is, during operations i've lost week days count 0.

so question : how update data.table object efficiently inserting, each id, many rows there missing week days val=0 ?

the result following :

    id wday val  1:  1  mon   2  2:  1  tue   3  3:  1  wed   5  4:  1  thu   8  5:  1  fri   6  6:  1  sat   2  7:  1  sun   0  8:  2  mon   3  9:  2  tue   4 10:  2  wed   0 11:  2  thu   2 12:  2  fri   6 13:  2  sat   0 14:  2  sun   0 

thanks lot help.

one straightforward way think of right use expand.grid combinations , use subset allow.cartesian = true:

setkey(dt, "id", "wday") vals <- c("mon", "tue", "wed", "thu", "fri", "sat", "sun") idx <- expand.grid(vals, unique(dt$id))[, 2:1] dt[j(idx), allow.cartesian=true]  #     id wday val #  1:  1  mon   2 #  2:  1  tue   3 #  3:  1  wed   5 #  4:  1  thu   8 #  5:  1  fri   6 #  6:  1  sat   2 #  7:  1  sun  na #  8:  2  mon   3 #  9:  2  tue   4 # 10:  2  wed  na # 11:  2  thu   2 # 12:  2  fri   6 # 13:  2  sat  na # 14:  2  sun  na 

alternatively, possible directly build idx data table cj :

dt[cj(unique(dt$id),vals), allow.cartesian=true]