Sum values by criteria in r

Question

I have been trying to get this right but i cant find the right way to apporach this problem. Lets say i have some data that look like this:

hhid   totalplacevisited
1              5
1              6
1              2
2              2
2              4
3              1

How can i aggregate the data so i can get values in this format:

hhid   totalplacevisited   totalplacedvisitedbyhh
1              5                    13
1              6                    13
1              2                    13
2              2                    6
2              4                    6 
3              1                    1

“How can i aggregate” – fittingly, using `aggregate`. – Konrad Rudolph Sep 26 '13 at 20:11 — Konrad Rudolph, Sep 26 '13 at 20:11

score 1 · Accepted Answer · answered Sep 26 '13 at 20:08

One alternative is using ave

> transform(df, totalplacedvisitedbyhh = with(df,ave(totalplacevisited, hhid, FUN=sum)))
  hhid totalplacevisited totalplacedvisitedbyhh
1    1                 5                     13
2    1                 6                     13
3    1                 2                     13
4    2                 2                      6
5    2                 4                      6
6    3                 1                      1

Other alternative is using data.table

> library(data.table)
> DT <- data.table(df)
> DT[, totalplacedvisitedbyhh := sum(totalplacevisited), by=hhid]
> DT
   hhid totalplacevisited totalplacedvisitedbyhh
1:    1                 5                     13
2:    1                 6                     13
3:    1                 2                     13
4:    2                 2                      6
5:    2                 4                      6
6:    3                 1                      1

Thanks the ave() worked great – Daniel Sep 26 '13 at 20:21 — Daniel, Sep 26 '13 at 20:21

score 1 · Answer 2 · answered Sep 26 '13 at 20:09

1

The data.table package is the fastest way:

dt = data.table(df)
dt[,totalplacesvisitedbyhh:=sum(totalplacevisited),by=hhid]

answered Sep 26 '13 at 20:09

Señor O

17,049
2
45
47

score 0 · Answer 3 · answered Sep 26 '13 at 20:22

Here is the solution from plyr package

library(plyr)
ddply(mydf,.(hhid),transform, totalplacedvisitedbyhh=sum(totalplacevisited))

 hhid totalplacevisited totalplacedvisitedbyhh
1    1                 5                     13
2    1                 6                     13
3    1                 2                     13
4    2                 2                      6
5    2                 4                      6
6    3                 1                      1

Sum values by criteria in r

3 Answers3