This question is inspired by this and this question.
I am trying to calculate the proportion of different values within each group, but I do not want to create "new" rows for the groups but new columns.
Taking the example from the second question above. If I have the following data:
data <- structure(list(value = c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L,
2L, 2L, 2L, 3L, 3L, 3L, 3L), class = structure(c(1L, 1L, 1L,
2L, 2L, 2L, 1L, 1L, 1L, 1L, 2L, 2L, 1L, 1L, 1L, 1L), .Label = c("A",
"B"), class = "factor")), .Names = c("value", "class"), class = "data.frame", row.names = c(NA,
-16L))
I can calculate the proportion of each value (1,2,3) in each class (A,B):
data %>%
group_by(value, class) %>%
summarise(n = n()) %>%
complete(class, fill = list(n = 0)) %>%
group_by(class) %>%
mutate(freq = n / sum(n))
# A tibble: 6 x 4
value class n freq
<int> <fctr> <dbl> <dbl>
1 1 A 3 0.2727273
2 1 B 3 0.6000000
3 2 A 4 0.3636364
4 2 B 2 0.4000000
5 3 A 4 0.3636364
6 3 B 0 0.0000000
However I end up with a line for each value/class pair instead I want something like this:
# some code
# A tibble: 6 x 4
class n 1 2 3
<fctr> <dbl> <dbl> <dbl> <dbl>
1 A 11 0.2727273 0.3636364 0.3636364
2 B 5 0.6000000 0.4000000 0.0000000
With a column for each group. I could write for loops to construct a new data frame from the old one but I am certain there is a better way. Any suggestions?
Thank you
We can use pivot_wider at the end
library(dplyr)
library(tidyr)
data %>%
group_by(value, class) %>%
summarise(n = n()) %>%
complete(class, fill = list(n = 0)) %>%
group_by(class) %>%
mutate(freq = n / sum(n), n = sum(n)) %>%
pivot_wider(names_from = value, values_from = freq)
# A tibble: 2 x 5
# Groups: class [2]
# class n `1` `2` `3`
# <fct> <dbl> <dbl> <dbl> <dbl>
#1 A 11 0.273 0.364 0.364
#2 B 5 0.6 0.4 0
Or as @IcecreamToucan mentioned, the complete is not needed as the pivot_wider have the option to fill with a custom value (default is NA)
data %>%
group_by(value, class) %>%
summarise(n = n()) %>%
group_by(class) %>%
mutate(freq = n / sum(n), n = sum(n)) %>%
pivot_wider(names_from = value, values_from = freq, values_fill = list(freq = 0))
If we are using a previous version of tidyr, then use spread
data %>%
group_by(value, class) %>%
summarise(n = n()) %>%
complete(class, fill = list(n = 0)) %>%
group_by(class) %>%
mutate(freq = n / sum(n), n = sum(n)) %>%
spread(value, freq)