Sum up counts in a data.frame grouped by multiple variables

Question

This is a snippet of the dataset I am currently working on:

> sample
    name sex count
1  Maria   f    97
2 Thomas   m    12
3  Maria   m     5
4  Maria   f    97
5 Thomas   m     8
6  Maria   m     4

I want to sum up the counts grouped by name and sex to finally get this data.frame:

> result
    Maria Thomas
f   194      0
m     9     20

I wrote a simple loop to iterate over the rows and sum up the counts:

result <- matrix(0, nrow=2, ncol=2)
colnames(result) <- unique(sample$name)
rownames(result) <- unique(sample$sex)

for (i in 1:nrow(sample)) {
    sex <- as.character(sample[i,"sex"])
    name <- sample[i,"name"]
    count <- sample[i,"count"]

    result[sex, name] <- result[sex, name] + count
}

Is it suitable to do it this way? Are there any other ways to do it in a more elegant / shorter fashion?

Edit:

I already tried it with aggregate, but the output is in a different format:

> aggregate(sample$count,by=list(sample$name,sample$sex),sum)
  Group.1 Group.2   x
1   Maria       m   9
2  Thomas       m  20
3   Maria       w 194

Answer 1

You can do this using the xtabs function! Here's how I did it using your example data:

# Create example data...
name <- c("Maria", "Thomas", "Maria", "Maria", "Thomas", "Maria")
sex <- c("f", "m", "m", "f", "m", "m")
count <- c(97, 12, 5, 97, 8, 4)
data <- data.frame("name"=name, "sex"=sex, "count"=count)

# Create table...
xtabs(formula=count~name + sex, data=data)

which gives the following output:

        sex
name       f   m
Maria    194   9
Thomas     0  20

Answer 2

Using data.table is also another option you can explore. Working with data.tables is more efficient when you do certain operations on your table. Its simple to use as well.

require(data.table) 
DT <- data.table(data) 
DT[ , .(Totalcount = sum(count)), by = .(name,sex)]

output

     name sex Totalcount
1:  Maria   f        194
2: Thomas   m         20
3:  Maria   m          9