r - reshape and aggregate datatable -

i asked similar question , because haven't quite gotten handle on tidyr or reshape have ask question. have datatable containing repeat id values (see below):

id  product ni   1   direct auto 15   2   direct auto 15   3   direct auto 15   4   direct auto 15   5   direct auto 15   6   direct auto 15   6   mortgage    50   9   direct auto 15   10  direct auto 15   11  direct auto 15   12  direct auto 15   13  direct auto 15   14  direct auto 15   15  direct auto 15   16  direct auto 15   1   mortgage    50   5   personal    110   19  direct auto 15   20  direct auto 15   1   direct auto 15  

i id aggregated 1 row, product column 'spread' values become variables, variable containing aggregated count of each product id, , ni summed each of product groups id. see example below:

id  directauto  da_ni   mortgage    mortgage_ni personal    p_ni   1   2   30  1   50  na  na   2   1   15  na  na  na  na   3   1   15  na  na  na  na   4   1   15  na  na  na  na   5   1   15  na  na  1   110   6   1   15  1   50  na  na   9   1   15  na  na  na  na   11  1   15  na  na  na  na   12  1   15  na  na  na  na   13  1   15  na  na  na  na   14  1   15  na  na  na  na   15  1   15  na  na  na  na   16  1   15  na  na  na  na   19  1   15  na  na  na  na   20  1   15  na  na  na  na  

for example, id 1 has 2 direct auto, da_ni 30 , has 1 mortgage ni mortgage_ni = 50.

so, make 'wider' datatable. i'm still reading , practicing tidyr , reshape, in mean-time maybe can help.
here of starting code:

df[, .(tot = .n, ni = sum(ni)), = c("id","product")] 

afterwards, using tidyr & reshape commands can't seem final output want.

data.table v1.9.5 has more nicer features melting , casting. using dcast devel version:

require(data.table) # v1.9.5 dcast(dt, id ~ product, fun.agg = list(sum, length), value.var="ni", fill=na) 

i think you're looking for. can checkout new html vignettes here.

rename columns liking.