Calculate column sums for each combination of two grouping variables

Try

library(dplyr)
df1 %>%
     group_by(Type, Age) %>% 
     summarise_each(funs(sum))
#    Type Age count1 count2
#1    A  35      4      2
#2    A  45      2      3
#3    B  45      6      6

In the newer versions of dplyr

df1 %>%
     group_by(Type, Age) %>%
     summarise_all(sum)

Or using base R

 aggregate(.~Type+Age, df1, FUN=sum)
 #    Type Age count1 count2
 #1    A  35      4      2
 #2    A  45      2      3
 #3    B  45      6      6

Or

library(data.table)
setDT(df1)[, lapply(.SD, sum), .(Type, Age)] 
#   Type Age count1 count2
#1:    A  35      4      2
#2:    A  45      2      3
#3:    B  45      6      6

Update

Based on the new dataset,

 df2 %>%
     group_by(Type, Age,Pop1, Pop2, TypeDescrip) %>% 
     summarise_each(funs(sum), matches('^count'))
 #    Type Age  Pop1  Pop2 TypeDescrip count1 count2
 #1    A  35 30000 50000       alpha      4      2
 #2    A  45 20000 70000        beta      2      3
 #3    B  45 20000 70000        beta      6      6

data

 df1 <- structure(list(Type = c("A", "A", "A", "B", "B"), Age = c(35L, 
 35L, 45L, 45L, 45L), count1 = c(1L, 3L, 2L, 2L, 4L), count2 = c(1L, 
 1L, 3L, 1L, 5L)), .Names = c("Type", "Age", "count1", "count2"
 ), class = "data.frame", row.names = c(NA, -5L))

 df2 <- structure(list(Type = c("A", "A", "A", "B", "B"), Age = c(35L, 
 35L, 45L, 45L, 45L), count1 = c(1L, 3L, 2L, 2L, 4L), count2 = c(1L, 
 1L, 3L, 1L, 5L), Year = c(1990L, 1990L, 1990L, 1990L, 1990L), 
   Pop1 = c(30000L, 30000L, 20000L, 20000L, 20000L), Pop2 = c(50000L, 
   50000L, 70000L, 70000L, 70000L), TypeDescrip = c("alpha", 
   "alpha", "beta", "beta", "beta")), .Names = c("Type", "Age", 
  "count1", "count2", "Year", "Pop1", "Pop2", "TypeDescrip"),
   class =   "data.frame", row.names = c(NA, -5L))

Tags:

R

Aggregate

Recent Posts