Aggregate multiple variables with different functions [duplicate]

Question

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. |

Answer 1

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

Answer 2

Here's a base R solution:

First, your data:

x <- structure(list(Cat1 = structure(c(1L, 1L, 2L, 2L, 1L, 1L, 2L, 
2L), .Label = c("A", "B"), class = "factor"), Cat2 = structure(c(1L, 
2L, 1L, 2L, 1L, 2L, 1L, 2L), .Label = c("A", "B"), class = "factor"), 
    Val1 = c(1L, 3L, 5L, 7L, 2L, 6L, 10L, 14L), Val2 = c(2L, 
    4L, 6L, 8L, 4L, 8L, 12L, 16L)), .Names = c("Cat1", "Cat2", 
"Val1", "Val2"), class = "data.frame", row.names = c(NA, -8L))

Then, use ave() and unique() within within().

unique(
  within(x, {
    sum_val1 <- ave(Val1, Cat1, Cat2, FUN = sum)
    mean_val2 <- ave(Val2, Cat1, Cat2, FUN = mean)
    rm(Val1, Val2)
  })
)
#   Cat1 Cat2 mean_val2 sum_val1
# 1    A    A         3        3
# 2    A    B         6        9
# 3    B    A         9       15
# 4    B    B        12       21

Or, if you're comfortable with SQL, use sqldf:

library(sqldf)
sqldf("select Cat1, Cat2, 
      sum(Val1) `Sum_Val1`, 
      avg(Val2) `Avg_Val2` 
      from x group by Cat1, Cat2")