R: data.table group and sum two columns

Question

Ok, I am stuck with trying to use data.table package to group and sum two separate columns.

My DT:

PARK   WTG    T_stop    T_AF
PC    81970   4.743     4.742
PC    81970   48.850    47.462
PC    81970   16.714    0.000
PC    81970   0.121     0.115
PC    81970   0.004     0.004
PC    81970   0.015     0.000
PC    81970   0.049     0.046
PC    81970   0.090     0.090
PC    81970   0.060     0.060
PC    81970   0.163     0.152

Now using the data.table function I have tried:

DT[, lapply(.SD, sum), by = c("PARK", "WTG")]

but my results look like this:

PARK   WTG    T_stop    T_AF
PC    81970   70.808    5.267

What I am looking for is:

PARK   WTG    T_stop    T_AF
PC    81970   70.808    52.671

Now this is just a snippet of my data set with around 700 rows

No, I just like using that package, but maybe a different approach might be suited. — BB.squared, Dec 19 '18 at 16:58
If I try your code on the example data, I get your expected result. — phiver, Dec 19 '18 at 16:59
@phiver yes, but when run it over my complete DT with 700 rows, the T_AF is always 5.267 not 52.671 — BB.squared, Dec 19 '18 at 17:01
try `DT[, .(T_AF = sum(T_AF), T_stop = sum(T_stop)), by = c("PARK", "WTG")]` — phiver, Dec 19 '18 at 17:06
both of your DT codes work, but when I view the results in Rstudio my final result is always T_AF = 5.267106e+01 — BB.squared, Dec 19 '18 at 17:15
Interesting as that result is 52.671 in scientific notation. I don't have this issue. Have you tried restarting R? — phiver, Dec 19 '18 at 17:37
Ok everyone, I figured out my issue. so my original code worked. I changed my options to not show scientific notation. [How to prevent scientific notation in R?](https://stackoverflow.com/questions/25946047/how-to-prevent-scientific-notation-in-r) — BB.squared, Dec 19 '18 at 18:50

score 1 · Accepted Answer · answered Dec 19 '18 at 18:50

1

From your comments it seems like you are getting the desired result but in scientific notation. Try rounding to 3 decimals if you want to see it as you say:

DT[, lapply(.SD, function(x) round(sum(x), 3)), by = c("PARK", "WTG")]

answered Dec 19 '18 at 18:50

ebeneditos

2,542
1
15
35

1

I figured out my issue. so my original code worked. I changed my options to not show scientific notation. [How to prevent scientific notation in R?](https://stackoverflow.com/questions/25946047/how-to-prevent-scientific-notation-in-r) – BB.squared Dec 19 '18 at 18:52

T. Ciffréo · Answer 2 · 2018-12-20T09:59:18.117

0

With package plyr you can try this:

DT_sum <- ddply(DT, .("PARK","WTG"), colSums)

edited Dec 20 '18 at 09:59

answered Dec 19 '18 at 16:59

T. Ciffréo

126
10

R: data.table group and sum two columns

2 Answers2