summarise by subgroups dplyr

Question

I'm fairly new to dyplr and I'm struggling to get something as easy as proportions using the summarise function.

A portion of my dataset

city = rep(c("A","B"), each = 5)
month = c("Jan","Jan","Jan","Feb","Feb","Jan","Jan","Feb","Feb","Feb")
species = c("aegypti","gambiae","quinque","aegypti","quinque",
            "aegypti","gambiae","quinque","aegypti","quinque")
total = c(20, 5, 25, 12, 23, 10, 10, 0, 20, 0)
df = data.frame(city, month, species, total)

Then I want to calculate for each city, month the proportion of each species. I can calculate the total per city and month

df %>% group_by(city, month) %>% summarise(total = sum(total))

# A tibble: 4 x 3
# Groups:   city [2]
#  city  month total
#  <fct> <fct> <dbl>
#1 A     Feb      35
#2 A     Jan      50
#3 B     Feb      20
#4 B     Jan      20

Then I can calculate separately by species

df %>% group_by(city, month, species) %>% summarise(total = sum(total)

# A tibble: 9 x 4
# Groups:   city, month [4]
#  city  month species total
#  <fct> <fct> <fct>   <dbl>
#1 A     Feb   aegypti    12
#2 A     Feb   quinque    23
#3 A     Jan   aegypti    20
#4 A     Jan   gambiae     5
#5 A     Jan   quinque    25
#6 B     Feb   aegypti    20
#7 B     Feb   quinque     0
#8 B     Jan   aegypti    10
#9 B     Jan   gambiae    10

but I need something like this to calculate the proportion. I'd like the table to look similar to this.

# A tibble: 9 x 4
# Groups:   city, month [4]
#  city  month species total total1 prop
#  <fct> <fct> <fct>   <dbl> <dbl>  <dbl>
#1 A     Feb   aegypti    12   35    34.29
#2 A     Feb   quinque    23   35    65.71
#3 A     Jan   aegypti    20   50    40.00
#4 A     Jan   gambiae     5   50    10.00
#5 A     Jan   quinque    25   50    50.00
#6 B     Feb   aegypti    20   20   100.00
#7 B     Feb   quinque     0   20     0
#8 B     Jan   aegypti    10   20    50.00
#9 B     Jan   gambiae    10   20    50.00

I've been playing around but I don't find the way of doing it.. Thanks!

Ronak Shah Ronak Shah · Accepted Answer · 2020-04-13T11:11:28

group_by city and month and divide total by sum in the group to get proportions.

library(dplyr)
df %>% group_by(city, month) %>% mutate(total = total/sum(total) * 100)

#  city  month species total
#   <fct> <fct> <fct>   <dbl>
# 1 A     Jan   aegypti  40  
# 2 A     Jan   gambiae  10  
# 3 A     Jan   quinque  50  
# 4 A     Feb   aegypti  34.3
# 5 A     Feb   quinque  65.7
# 6 B     Jan   aegypti  50  
# 7 B     Jan   gambiae  50  
# 8 B     Feb   quinque   0  
# 9 B     Feb   aegypti 100  
#10 B     Feb   quinque   0

Or in base R :

df$total <- with(df, total/ave(total, city, month, FUN = sum))

summarise by subgroups dplyr

2 Answers