Calculate proportion of several binary variables by another variable

user_6105259 0
Viewed 136

I have data with several binary variables, and I want to calculate the proportion of each one, by another variable.

Example

I survey people and ask them:
Please mark which of the following fruits you like (can mark more than one choice):
☐ Banana ☐ Apple ☐ Orange ☐ Strawberry ☐ Peach

Each person who checked the box gets 1 in the data, and when leaving blank it's denoted as 0. The data looks like that:

library(dplyr)

set.seed(2021)

my_df <-
  matrix(rbinom(n = 100, size = 1, prob = runif(1)), ncol = 5) %>%
  as.data.frame() %>%
  cbind(1:20, ., sample(c("male", "female"), size = 20, replace = T)) %>%
  setNames(c("person_id", "banana", "apple", "orange", "strawberry", "peach", "gender"))

my_df
#>    person_id banana apple orange strawberry peach gender
#> 1          1      1     1      1          0     0 female
#> 2          2      1     0      0          0     1 female
#> 3          3      0     0      1          0     1 female
#> 4          4      1     1      0          1     0 female
#> 5          5      1     1      1          0     0   male
#> 6          6      1     1      1          0     1 female
#> 7          7      0     1      0          1     1   male
#> 8          8      1     1      0          0     0   male
#> 9          9      1     1      1          0     0 female
#> 10        10      0     0      0          0     0   male
#> 11        11      1     1      1          1     1   male
#> 12        12      1     1      0          0     1   male
#> 13        13      1     1      0          1     0   male
#> 14        14      1     1      0          0     0   male
#> 15        15      0     0      0          0     1   male
#> 16        16      0     1      0          0     1   male
#> 17        17      1     0      0          0     1   male
#> 18        18      1     1      1          1     1   male
#> 19        19      0     0      1          1     1 female
#> 20        20      0     0      0          0     0 female

Created on 2021-02-01 by the reprex package (v0.3.0)

I want to get the proportion for each fruit, split by gender. From this answer I learned how to do it for one variable (for example, banana):

my_df %>%
  group_by(gender) %>%
  summarise(n_of_observations = n(), prop = sum(banana == 1)/n())

## `summarise()` ungrouping output (override with `.groups` argument)
## # A tibble: 2 x 3
##   gender n_of_observations  prop
##   <chr>              <int> <dbl>
## 1 female                10   0.6
## 2 male                  10   0.4

But how can I get such a table for all fruits?

Desired output:

##    fruit      gender  prop
##    <chr>      <chr>  <dbl>
##  1 banana     female   0.6
##  2 banana     male     0.4
##  3 apple      female   0.4
##  4 apple      male     0.3
##  5 orange     female   0.3
##  6 orange     male     0.1
##  7 strawberry female   0.4
##  8 strawberry male     0.4
##  9 peach      female   0.3
## 10 peach      male     0.6

I'm looking for a dplyr solution, if possible. Thanks a lot!

dplyr r
2 Answers

You can use across to summarize multiple variables at once:

my_df %>%
  group_by(gender) %>%
  summarise(across(banana:peach, list(n = ~length(.x), prop = ~sum(.x == 1) / n())))


# A tibble: 2 x 11
  gender banana_n banana_prop apple_n apple_prop orange_n orange_prop strawberry_n strawberry_prop peach_n peach_prop
  <chr>     <int>       <dbl>   <int>      <dbl>    <int>       <dbl>        <int>           <dbl>   <int>      <dbl>
1 female        8       0.625       8       0.5         8       0.625            8           0.25        8      0.5  
2 male         12       0.667      12       0.75       12       0.25            12           0.333      12      0.583

Note that the first argument of across specifies the variables you want to summarize. Here, I wrote banana:peach meaning all columns between banana and peach.

user_54368770

You can use tidyr to pivot your data first and then summarize it:

library(tidyr)

tidyr::pivot_longer(my_df, banana:peach,
                    names_to = "fruit") %>% 
  dplyr::group_by(gender, fruit) %>% 
  dplyr::summarize(prop = sum(value) / n())

   gender fruit       prop
   <chr>  <chr>      <dbl>
 1 female apple      0.5  
 2 female banana     0.625
 3 female orange     0.625
 4 female peach      0.5  
 5 female strawberry 0.25 
 6 male   apple      0.75 
 7 male   banana     0.667
 8 male   orange     0.25 
 9 male   peach      0.583
10 male   strawberry 0.333

You can pipe it to arrange if you want to sort by fruit. You can also add the number of observations in the summarize function with n = n().

user_63824340
Related