Calculate subtotals with dplyr and tidyr

Question

expand.grid(country = c('Sweden','Norway', 'Denmark','Finland'),
            sport = c('curling','crosscountry','downhill')) %>% 
    mutate(medals = sample(0:3, 12, TRUE)) -> 
 data

Using reshape2's dcast achieves this in one line. Using custom names for the margins require extra steps.

library(reshape2)

data %>% 
  dcast(country ~  sport, margins = TRUE, sum) %>% 

 # optional renaming of the margins `(all)`
  rename(Total = `(all)`) %>% 
  mutate(country = ifelse(country == "(all)", "Total", country))

My dplyr + tidyr approach is verbose. What is the best (compact and readable) way of writing this using tidyr and dplyr.

library(dplyr)
library(tidyr)

data %>% 
  group_by(sport) %>% 
  summarise(medals = sum(medals)) %>% 
  mutate(country = 'Total') ->
  sport_totals

data %>% 
  group_by(country) %>% 
  summarise(medals = sum(medals)) %>% 
  mutate(sport = 'Total') ->
  country_totals

data %>% 
  summarise(medals = sum(medals)) %>% 
  mutate(sport = 'Total',
         country = 'Total') ->
  totals

data %>% 
  bind_rows(country_totals, sport_totals, totals) %>% 
  spread(sport, medals)

This is one of those basic things that's ridiculously easy in Excel and WAY(!) too time consuming in R. Recommend you check out rpivotTable — Nettle

erc erc · Accepted Answer · 2016-02-23T13:47:51

I don't know if this is the best (compact and readable) but it works ;)

data %>%
  spread(sport, medals) %>%
  mutate(Total = rowSums(.[2:4])) %>%
  rbind(., data.frame(country="Total", t(colSums(.[2:5]))))

  country curling crosscountry downhill Total
1  Sweden       0            2        0     2
2  Norway       1            1        0     2
3 Denmark       2            2        1     5
4 Finland       3            0        2     5
5   Total       6            5        3    14

Calculate subtotals with dplyr and tidyr

1 Answers