My data looks like below and I would like to group it by zip. Along with the grouping I would like the lower, avg, upper, sum of idk, and avgDiff columns. The two calculated fields would be the Total Count by zip and sum of 'idk' (the table below this one). I would specifically like to use DataTable for this....thank you.
zip lower avg upper RISK idk diff avgDiff total
1: 12007 -170.3723 592 1354.372 676 0 84 137.2903 123
2: 12007 -170.3723 592 1354.372 828 1 236 137.2903 123
3: 12007 -170.3723 592 1354.372 627 1 35 137.2903 123
4: 12009 -150.3723 300 1200.372 770 1 178 125.2903 456
5: 12007 -170.3723 592 1354.372 770 1 178 137.2903 123
6: 12010 -100.3723 200 1100.372 893 1 301 300.2903 890
desired result
zip lower avg upper zipCount avgDiff sumidk
1: 12007 -170.3723 592 1354.372 4 137.2903 3
2: 12009 -150.3723 300 1200.372 1 125.2903 1
3: 12010 -100.3723 200 1100.372 1 300.2903 1
The lower, avg, upper, and avgDiff will be the same within the zip.
So far I have DT[, .(zipcount =.N), by = zip]....which is grouping zip and giving me the total zips (rows), but I'm getting stuck at this point.
thank you