dplyr - Why the mutate function dose not work in R?

Question

I do not know what is wrong with my code. I want to total/sum(total column. But, it just filled by 1. I wrote this code:

eth_3 <- pop %>% 
  filter(ETHNICITY == 2) %>% 
  group_by(PROVINCE, ETHNICITY) %>% 
  summarise(total = sum(WEIGHT)) %>% 
  select(PROVINCE, total) %>% 
  mutate(S = total/sum(total))

Got this result

PROVINCE   total     S
     <int>   <dbl> <dbl>
1       11 93925.      1
2       12  2016.      1
3       13    40       1
4       14   255.      1
5       16    10       1
6       18    58.3     1

the output must be:

   PROVINCE    total         S
      <int>    <dbl>     <dbl>
 1       11 93925.   0.968    
 2       12  2016.   0.0208   
 3       13    40    0.000412 
 4       14   255.   0.00263  
 5       16    10    0.000103 
 6       18    58.3  0.000601 
 7       19     9.67 0.0000997
 8       21    50.3  0.000519 
 9       31    34.7  0.000358 
10       32   142.   0.00147 

this is the dput

structure(list(PROVINCE = c(11L, 12L, 13L, 14L, 16L, 18L, 19L, 
21L, 31L, 32L, 33L, 34L, 35L, 36L, 52L, 62L, 63L, 64L, 74L, 81L, 
91L), total = c(93925.4300413131, 2015.98999500274, 40, 255.349998474121, 
10, 58.3199987411499, 9.6700000762939, 50.340000152588, 34.6899995803834, 
142.189999580384, 30.0199995040892, 48.5600004196165, 160.789996147154, 
60.8100004196172, 9.8800001144409, 52.199997901915, 21.60000038147, 
19.7199993133544, 10.130000114441, 9.8999996185303, 28.1400003433227
), S = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1)), row.names = c(NA, -21L), groups = structure(list(PROVINCE = c(11L, 
12L, 13L, 14L, 16L, 18L, 19L, 21L, 31L, 32L, 33L, 34L, 35L, 36L, 
52L, 62L, 63L, 64L, 74L, 81L, 91L), .rows = structure(list(1L, 
    2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 
    15L, 16L, 17L, 18L, 19L, 20L, 21L), ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), row.names = c(NA, 21L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"))

Thank you

question from:https://stackoverflow.com/questions/65896735/why-the-mutate-function-dose-not-work-in-r

Answer 1

summarise by default ungroups one level which is the last one. So after summarise your data is still grouped by PROVINCE. You should ungroup before calculating proportions.

library(dplyr)

eth_3  <- pop %>% 
           filter(ETHNICITY == 2) %>% 
           group_by(PROVINCE, ETHNICITY) %>% 
           summarise(total = sum(WEIGHT)) %>% 
           select(PROVINCE, total) %>%
           ungroup %>%
           mutate(S = total/sum(total))
           #mutate(S = prop.table(total))

If you have dplyr > 1.0.0 you can specify .groups = 'drop' instead of using ungroup.

pop %>% 
  filter(ETHNICITY == 2) %>% 
  group_by(PROVINCE, ETHNICITY) %>% 
  summarise(total = sum(WEIGHT), .groups = 'drop') %>% 
  select(PROVINCE, total) %>%
  mutate(S = total/sum(total))