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?


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

2 Answers 2


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:

name       f   m
Maria    194   9
Thomas     0  20

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.

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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.