aggregate by factor levels and also totals

user_10895091 0
Viewed 376

I'm trying to sum the values of a numeric column for each level of a factor, but also get the total of all levels in the resulting data frame.

for example: 

# Type gender population
# A    male      100
# B    male      150
# A    female    125
# B    female    175

using the aggregate function I can get:

aggregate(population ~ gender, df, sum)

# gender population
# male      250
# female    300

but is there a way to get an output that also sums both levels?

# gender population
# all       550
# male      250
# female    300

this can be done easily in SAS with proc tabulate, hopefully there is a way to do it with R also.

thanks in advance,

EDIT
both answers given do work, but I'm trying to find a solution that is less ad-hoc. I'm looking for something that will work on multiple variables, for instance an output like this for a more complex data frame:

# Type gender population
# all  all     500
# all  male    200
# all  female  300
# A    all     250
# A    male    100
# A    female  150
# B    all     250
# B    male    100
# B    female  150

my apologies if that was not clear enough.

sas r
3 Answers

You can rbind, i.e.

d1 <- aggregate(population ~ gender, df, sum)    
rbind(data.frame(gender = 'total', population = sum(d1$population)), d1)

#  gender population
#1  total        550
#2 female        300
#3   male        250
user_56355800

We can use xtabs with addmargins to get overall totals:

df1 <- read.table(text = "
Type gender population
A    male      100
B    male      150
A    female    125
B    female    175", header = TRUE, stringsAsFactors = FALSE)

df2 <- read.table(text = "
Type gender population
all  all     500
all  male    200
all  female  300
A    all     250
A    male    100
A    female  150
B    all     250
B    male    100
B    female  150", header = TRUE, stringsAsFactors = FALSE)

data.frame(addmargins(xtabs(population ~ gender, df1)))
#   gender Freq
# 1 female  300
# 2   male  250
# 3    Sum  550

data.frame(addmargins(xtabs(population ~ gender, df2)))
#   gender Freq
# 1    all 1000
# 2 female  600
# 3   male  400
# 4    Sum 2000
user_6800680

Also with the package janitor:

x <- aggregate(population ~ gender, d, FUN=sum)
library(janitor)

adorn_totals(x, "row")

 #gender population
 #female        300
 #  male        250
 # Total        550
user_56582700
Related