R Dplyr: Group by two variables and count the number of rows of the outer group

Question

I have the following tibble, which is grouped by Case.ID AND weekday


    # A tibble: 10 x 4
    # Groups:   Case.ID [3]
        Case.ID weekday   count proportion
          <dbl> <chr>     <int>      <dbl>
     1 2771451. Friday        1   0.000992
     2 2771451. Monday        7   0.00694 
     3 2771451. Thursday      3   0.00298 
     4 2771451. Tuesday      20   0.0198  
     5 2771451. Wednesday    22   0.0218  
     6 2783345. Monday        9   0.00893 
     7 2783345. Thursday      1   0.000992
     8 2783345. Tuesday       1   0.000992
     9 2783345. Wednesday    40   0.0397  
    10 2846764. Friday       18   0.0179  

    > dput(a)
    structure(list(Case.ID = c(2771451, 2771451, 2771451, 2771451, 
    2771451, 2783345, 2783345, 2783345, 2783345, 2846764), weekday = c("Friday", 
    "Monday", "Thursday", "Tuesday", "Wednesday", "Monday", "Thursday", 
    "Tuesday", "Wednesday", "Friday"), count = c(1L, 7L, 3L, 20L, 
    22L, 9L, 1L, 1L, 40L, 18L), proportion = c(0.000992, 
    0.00694, 0.00298, 0.0198, 
    0.0218, 0.00893, 0.000992, 
    0.000992, 0.0397, 0.0179)), .Names = c("Case.ID", 
    "weekday", "count", "proportion"), row.names = c(NA, -10L), class = c("grouped_df", 
    "tbl_df", "tbl", "data.frame"), vars = "Case.ID", drop = TRUE, indices = list(
        0:4, 5:8, 9L), group_sizes = c(5L, 4L, 1L), biggest_group_size = 5L, labels = structure(list(
        Case.ID = c(2771451, 2783345, 2846764)), row.names = c(NA, 
    -3L), class = "data.frame", vars = "Case.ID", drop = TRUE, .Names = "Case.ID"))

Currently the "proportion" is calculated as the percentage over the total number of rows (I know the figures are wrong because this is just the head of my tibble...), as I used to my original dataset:

group_by(Case.ID,weekday) %>% 
    summarise(count = n(),proportion = count / nrow(.))

I would like to calculate the proportion based on the sum of the count column per Case.ID group.

For example, for the 2771451 Case, Friday, it should be 1/ (1+7+3+20+22).

I would prefer a dplyr solution...

Kim Kim · Accepted Answer · 2019-01-08T22:54:37

That would be the following:

df %>% dplyr::group_by(Case.ID) %>% dplyr::mutate(proportion = count / sum(count))

Note that I'm only grouping over the Case.ID and I'm not using summarise.

R Dplyr: Group by two variables and count the number of rows of the outer group

1 Answers