r - custom aggregation function in dcast -

i have table need reformatted. table looks like:

date   itemid   newprice   sale amount 1-1     1         5            3 1-1     2         8            2 1-1     3         3            5 1-2     1         6            4 1-2     3         4            3 1-3     2         7            2 1-3     3         2            1 

the first table want reformulate looks like:

date   item_1    item_2    item_3 1-1      3         2         5  1-2      4         0         3 1-3      0         2         1 

the item id becomes column names, , value sale amount. tricky part that, days, there no record items, no item record item 2 in 1-2. in case, sale amount should fill 0.

the second table reformulate looks like:

date     item_1     item_2     item_3 1-1        5          8          3 1-2        6          8          4 1-3        6          7          2 

so thing want use item_id column, , newprice value, each date.

the tricky part that, in each day, there items not show up, there no newprice item in day. in case, newprice should last day's newprice.

here base r solution first part:

xtabs(`sale amount` ~ date + itemid, df) ##      itemid ## date  1 2 3 ##   1-1 3 2 5 ##   1-2 4 0 3 ##   1-3 0 2 1 

and second part use na.locf in zoo tapply. na.rm = false in case first date has na. in case leave na.

library(zoo)  na.locf(tapply(df$newprice, df[c("date", "itemid")], c), na.rm = false) ##      itemid ## date  1 2 3 ##   1-1 5 8 3 ##   1-2 6 8 4 ##   1-3 6 7 2 

note: input df in reproducible form is:

lines <- "date   itemid   newprice   'sale amount' 1-1     1         5            3 1-1     2         8            2 1-1     3         3            5 1-2     1         6            4 1-2     3         4            3 1-3     2         7            2 1-3     3         2            1" df <- read.table(text = lines, header = true, check.names = false)