Grouping by multiple dimensions, summarise and add calculated column

user_5064352 0
Viewed 39

I have this df:

  boxChange   sameCat
# C1 > C2     TRUE
# C1 > C2     TRUE
# A0 > A1     TRUE
# A1 > E4     FALSE
# C3 > E6     FALSE
# E0 > E3     TRUE
# ...         ...

I would like to group by both columns, count the occurrences and arrange by their number. By using dplyr I would go like this:

df2 <- df %>%  
group_by(boxChange, sameCat) %>%
summarise(occs = n()) %>%
arrange(desc(occs))

Obtaining:

  boxChange   sameCat   occs
# C1 > C2     TRUE      312
# A0 > A1     TRUE      189
# E0 > E3     TRUE      13
# C3 > E6     FALSE     123
# A1 > E4     FALSE     70

Now I would like to compute the percentage of each occs over the total and the cumulative percentage, obtaining something like this

  boxChange   sameCat   occs   perc   cump
# C1 > C2     TRUE      312    44      44
# A0 > A1     TRUE      189    27      71
# E0 > E3     TRUE       13     2      73
# C3 > E6     FALSE     123    17      90
# A1 > E4     FALSE      70    10     100

I tried with the following

df2 <- df %>%  
group_by(boxChange, sameCat) %>%
summarise(occs = n()) %>%
arrange(desc(occs)) %>%
mutate(perc = occs/sum(occs)*100) %>%
mutate(cump = cumsum(perc))

But the output is the following

  boxChange   sameCat   occs   perc   cump
# C1 > C2     TRUE      312    100     100
# A0 > A1     TRUE      189    100     100
# E0 > E3     TRUE       13    100     100
# C3 > E6     FALSE     123    100     100
# A1 > E4     FALSE      70    100     100

I cannot understand why it is like this and couldn't find any other thread reporting a similar issue. Do you have any insight?

dplyr aggregate r
1 Answers

We may need ungroup

df2 <- df %>%  
       group_by(boxChange, sameCat) %>%
        summarise(occs = n()) %>%
        arrange(desc(occs)) %>%
        ungroup %>%
        mutate(perc = occs/sum(occs)*100, 
               cump = cumsum(perc))

--

Or if we need to keep the grouping intact, use sum(.$occs)

Update

If we start from the OP's arraged 'occs'

df %>% 
  ungroup %>% 
  mutate(perc = round(occs/sum(occs) * 100),
         cump = cumsum(perc))
#   boxChange sameCat occs perc cump
#1   C1 > C2    TRUE  312   44   44
#2   A0 > A1    TRUE  189   27   71
#3   E0 > E3    TRUE   13    2   73
#4   C3 > E6   FALSE  123   17   90
#5   A1 > E4   FALSE   70   10  100
user_37322710
Related