CODEWITHSUNDEEP
rdata.tableaggregation
Siruphuhn
Siruphuhn

Reputation: 111

How to add column sum (with condition) as new column?

I have a data.table with the following shape:

    Order_ID Item Amount
1:        a   i1      1
2:        a   i2      2
3:        a   i3      3
4:        b   i1      2
5:        b   i3      1
6:        c   i2      3
7:        c   i3      4

So, there are three orders with some items. What I would like to do with one data.table command is to add the total number of items of an order and give it out in each row:

    Order_ID Item Amount Total
1:        a   i1      1     6
2:        a   i2      2     6
3:        a   i3      3     6
4:        b   i1      2     3
5:        b   i3      1     3
6:        c   i2      3     7
7:        c   i3      4     7

I get there by creating a new data.table an merge it with the first one. But I thought that there has to be a more elegant way. :)

Thanks for your help!

Upvotes: 1

Views: 105

Answers (1)

Pierre L
Pierre L

Reputation: 28441

To utilize the efficiency of data.table it helps to use something like:

dt[, Total := sum(Amount), by=Order_ID]
#    Order_ID Item Amount Total
# 1:        a   i1      1     6
# 2:        a   i2      2     6
# 3:        a   i3      3     6
# 4:        b   i1      2     3
# 5:        b   i3      1     3
# 6:        c   i2      3     7
# 7:        c   i3      4     7

Upvotes: 7

Related Questions