Summing in R with multiple conditions

Question

I'm trying to sum columns 4 (child) ,5 (adult) and 6 (elderly) and return values for each country by year disregarding column 3 (sex). Reading through various forums I cannot combine these:

 country      year   sex  child adult elderly
1 Afghanistan 1995   male    -1    -1      -1
2 Afghanistan 1996 female    -1    -1      -1
3 Afghanistan 1996   male    -1    -1      -1
4 Afghanistan 1997 female     5    96       1
5 Afghanistan 1997   male     0    26       0
6 Afghanistan 1998 female    45  1142      20

I was able to sum the 3 columns by row and create a separate column with the following but still need to combine the male and female rows for each country:

tuberculosiscases <-tuberculosis$child + tuberculosis$adult + tuberculosis$elderly
names(tuberculosiscases) <- c("tuberculosiscases")
tuberculosis <- data.frame(tuberculosis,tuberculosiscases)
head(tuberculosis)

   country    year   sex child adult elderly  tuberculosiscases
1 Afghanistan 1995   male    -1    -1      -1                -3
2 Afghanistan 1996 female    -1    -1      -1                -3
3 Afghanistan 1996   male    -1    -1      -1                -3
4 Afghanistan 1997 female     5    96       1               102
5 Afghanistan 1997   male     0    26       0                26
6 Afghanistan 1998 female    45  1142      20              1207

It is not completely clear what you want. If you want to summarise your dataframe by multiple columns, check: [summation of multiple columns grouped by multiple columns in R and output results as data frame](http://stackoverflow.com/questions/8212699/summation-of-multiple-columns-grouped-by-multiple-columns-in-r-and-output-result) — Jaap, Feb 14 '16 at 20:28
Or: [R Grouping functions: sapply vs. lapply vs. apply. vs. tapply vs. by vs. aggregate](http://stackoverflow.com/questions/3505701/r-grouping-functions-sapply-vs-lapply-vs-apply-vs-tapply-vs-by-vs-aggrega) — Jaap, Feb 14 '16 at 20:32
as you can see I am very new to this, I really appreciate your help — danielhong, Feb 14 '16 at 21:08

Jaap · Accepted Answer · 2016-03-18T11:13:16.080

If you want add the sum to your dataframe, have several options:

# with base R (1)
transform(dat, tuber.sum = ave(tuberculosiscases, country, year, FUN = sum))

# with base R (2)
dat$tuber.sum <- ave(dat$tuberculosiscases, dat$country, dat$year, FUN = sum))

# with the data.table package
library(data.table)
setDT(dat)[, tuber.sum:=sum(tuberculosiscases), by= .(country, year)]

# with the plyr package
library(plyr)
dat <- ddply(dat, .(country, year), transform, tuber.sum=sum(tuberculosiscases))

# with the dplyr package
library(dplyr)
dat <- dat %>% 
  group_by(country, year) %>% 
  mutate(tuber.sum=sum(tuberculosiscases))

all give:

> dat
       country year    sex child adult elderly tuberculosiscases tuber.sum
1: Afghanistan 1995   male    -1    -1      -1                -3        -3
2: Afghanistan 1996 female    -1    -1      -1                -3        -6
3: Afghanistan 1996   male    -1    -1      -1                -3        -6
4: Afghanistan 1997 female     5    96       1               102       128
5: Afghanistan 1997   male     0    26       0                26       128
6: Afghanistan 1998 female    45  1142      20              1207      1207

score 0 · Answer 2 · answered Feb 14 '16 at 20:32

If I correctly understand your question and assuming that the name of the initial data.frame is my_df I would use aggregate:

 aggdata <-aggregate(my_df[,c("child", "adult", "elderly")], 
                     by=list(my_df$country,my_df$year), FUN=sum, na.rm=TRUE)

Summing in R with multiple conditions

2 Answers2

Linked