Grouping in `dplyr` becomes overly complicated when calculating grouping proportion instead of sum

user_4706952 0
Viewed 57

I have a tibble like:

df <- tibble(
  weight = c(1,2,1,3,2,1,3,2,3,1),
  quantity = c(rep(4:1, 2),3,4),
  year = c(rep(1:2, 5))
)

Now I can calculate the sum per year per weight class like:

df %>% group_by(year, weight) %>% summarise(sum_quantity = sum(quantity))
# A tibble: 6 x 3
# Groups:   year [2]
   year weight sum_quantity
  <int>  <dbl>        <dbl>
1     1      1            6
2     1      2            4
3     1      3            5
4     2      1            7
5     2      2            4
6     2      3            1

The output is - as I expect - six rows long, because there are six groups.

Now I try to do the same, but with a proportion. My attempt is:

df %>% 
  group_by(year) %>% 
  mutate(total_quantity_year = sum(quantity)) %>%
  group_by(year, weight) %>% 
  summarise(sum_quantity = sum(quantity)/total_quantity_year) %>%
  distinct(year, weight, sum_quantity)

# A tibble: 6 x 3
# Groups:   year, weight [6]
   year weight sum_quantity
  <int>  <dbl>        <dbl>
1     1      1       0.4   
2     1      2       0.267 
3     1      3       0.333 
4     2      1       0.583 
5     2      2       0.333 
6     2      3       0.0833

This works but seems overly complicated.

Are there more concise options available?

dplyr r
3 Answers

You can simplify it with add_count and count:

library(dplyr)

df %>% 
  add_count(year, wt = quantity) %>% 
  count(year, weight, wt = quantity/n, name = "sum_quantity")
#> # A tibble: 6 x 3
#>    year weight sum_quantity
#>   <int>  <dbl>        <dbl>
#> 1     1      1       0.4   
#> 2     1      2       0.267 
#> 3     1      3       0.333 
#> 4     2      1       0.583 
#> 5     2      2       0.333 
#> 6     2      3       0.0833
user_99182650 
df %>% 
  group_by(year, weight) %>% 
  summarize(quantity = sum(quantity)) %>% 
  mutate(proportion = quantity / sum(quantity))

# Groups:   year [2]
   year weight quantity proportion
  <int>  <dbl>    <dbl>      <dbl>
1     1      1        6     0.4   
2     1      2        4     0.267 
3     1      3        5     0.333 
4     2      1        7     0.583 
5     2      2        4     0.333 
6     2      3        1     0.0833
user_64365450

A data.table can solve this pretty efficiently. Here I am chaining/piping two statements - the first giving the sum by year and weight, the second giving the proportion by year.

df[, sum(quantity), by=.(weight, year)][
   , .(weight, "prop" = V1/sum(V1)), by=year]

You'll first need to convert to a data.table. The data.table uses the general notation of dt[i, j, by] where dt is your data.table, i is the sorting/sampling/subsetting of dt, j is the operations on variables, and by is one or more variables to group the j-operation by. Note also that .() is a synonym for list().

library(data.table)
setDT(df)
user_10400350
Related