1
$\begingroup$

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
$\endgroup$
2
$\begingroup$

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
$\endgroup$
1
$\begingroup$

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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