Get frequencies from counts, with multiple levels of grouping

user_2063292 0
Viewed 58

I have a data frame like the following:

df <- data.frame(group1=c("A","A","A","A","B","B","B","B"),
                 group2 = c("X", "X", "Y", "Y","X", "X", "Y", "Y"),
                 type = c("a", "b", "c","d","e","f","g","h"),
                 count = c(1,2,3,4,5,6,7,8))

> df
  group1 group2 type count
1      A      X    a     1
2      A      X    b     2
3      A      Y    c     3
4      A      Y    d     4
5      B      X    e     5
6      B      X    f     6
7      B      Y    g     7
8      B      Y    h     8

I would like to put the relative frequency of each type, grouped by group1 and group2, into a column. I have a way to do it but it's clunky, requiring summarizing and then joining. I feel like there's got to be a way to do it within one dplyr statement. But what I can't figure out is after summarizing, I need to go back to having all the rows.

My clunky way:

df.summ <- df %>% group_by(group1, group2) %>% 
  summarize(tot = sum(count))

df <- left_join(df, df.summ)

df <- df %>% mutate(freq = count/tot)

> df
  group1 group2 type count tot      freq
1      A      X    a     1   3 0.3333333
2      A      X    b     2   3 0.6666667
3      A      Y    c     3   7 0.4285714
4      A      Y    d     4   7 0.5714286
5      B      X    e     5  11 0.4545455
6      B      X    f     6  11 0.5454545
7      B      Y    g     7  15 0.4666667
8      B      Y    h     8  15 0.5333333
dplyr r
2 Answers

It is more direct if we use mutate instead of summarise/left_join as summarise by default returns only a single row per group while mutate creates a new column in the original dataset

library(dplyr)
df1 <- df %>% 
   group_by(group1, group2) %>%
   mutate(freq = count/sum(count)) %>%
   ungroup
user_37322710

A base R one-liner:

df$freq <- with(df, ave(count, list(group1, group2), FUN = function(x) x/sum(x)))
df
#  group1 group2 type count      freq
#1      A      X    a     1 0.3333333
#2      A      X    b     2 0.6666667
#3      A      Y    c     3 0.4285714
#4      A      Y    d     4 0.5714286
#5      B      X    e     5 0.4545455
#6      B      X    f     6 0.5454545
#7      B      Y    g     7 0.4666667
#8      B      Y    h     8 0.5333333
user_82454060
Related