Say I had the following table DataTable
Cat1 | Cat2 | Val1 | Val2
--------------------------------------------
A | A | 1 | 2
A | B | 3 | 4
B | A | 5 | 6
B | B | 7 | 8
A | A | 2 | 4
A | B | 6 | 8
B | A | 10 | 12
B | B | 14 | 16
Which I wanted to Aggregate by Cat1 and Cat2, taking the Sum and Avg of Val1 and Val2 respectively, how might I acheive this?
Cat1 | Cat2 | Sum Val1 | Avg Val2
--------------------------------------------
A | A | 3 | 3
A | B | 9 | 6
B | A | 15 | 9
B | B | 21 | 12
I've achieved single variable aggregation with the aggregate function:
aggregate(
Val1
~ Cat1 + Cat2
data=DataTable,
FUNC=sum
)
but despite playing around with cbind, can't get the behaviour I want. I'm 24 hrs into learning R, so I'm not familiar enough with the concepts to fully understand what I've been doing (always dangerous!) but think this must be simple to achieve. |
set.seed(45)
df <- data.frame(c1=rep(c("A","A","B","B"), 2),
c2 = rep(c("A","B"), 4),
v1 = sample(8),
v2 = sample(1:100, 8))
> df
# c1 c2 v1 v2
# 1 A A 6 19
# 2 A B 3 1
# 3 B A 2 37
# 4 B B 8 86
# 5 A A 5 30
# 6 A B 1 44
# 7 B A 7 41
# 8 B B 4 39
v1 <- aggregate( v1 ~ c1 + c2, data = df, sum)
v2 <- aggregate( v2 ~ c1 + c2, data = df, mean)
out <- merge(v1, v2, by=c("c1","c2"))
> out
# c1 c2 v1 v2
# 1 A A 11 24.5
# 2 A B 4 22.5
# 3 B A 9 39.0
# 4 B B 12 62.5
**Edit:** I'd propose that you use data.table as it makes things really easy:
require(data.table)
dt <- data.table(df)
dt.out <- dt[, list(s.v1=sum(v1), m.v2=mean(v2)),
by=c("c1","c2")]
> dt.out
# c1 c2 s.v1 m.v2
# 1: A A 11 24.5
# 2: A B 4 22.5
# 3: B A 9 39.0
# 4: B B 12 62.5