Calculate proportions for multiple variables according to factor level across multiple columns

user_10597690 0
Viewed 50

I have survey data with several demographic variables and question variables that have been coded as binary data.

Demo variables look like this:

urban     labor_force            
<fct>     <fct>            
rural     in labor force            
urban     in labor force         
rural     not in labor force

Question variables look like this:

Q65_armedforces     Q69_police   Q70_courts
<int>               <int>        <int>
1                   1            1
1                   0            1
0                   0            0

The data set as a whole is structured like this:

urban     labor_force      Q65_armedforces     Q69_police   Q70_courts
<fct>     <fct>                <int>               <int>        <int>
rural    in labor force          1                   1            1 
urban    in labor force          1                   0            1
rural    not in labor force      0                   0            0

What I would like to achieve is a table that I can write to csv that has the proportion of 1s for each factor level in the demographic variables, for all of the question variables. So the final product would look like this (updated with actual numbers from dataset):

                    urban   rural   in labor force    not in labor force   
Q65_armed forces    0.85    0.88    0.86              0.86             
Q69_police          0.84    0.84    0.83              0.85                 
Q70_courts          0.74    0.76    0.75              0.75

Numbers derived from

wvs_final %>% group_by(urban) %>% summarize(mean(Q65_armedforces, na.rm=TRUE))
wvs_final %>% group_by(urban) %>% summarize(mean(Q69_police, na.rm=TRUE))
wvs_final %>% group_by(urban) %>% summarize(mean(Q70_courts, na.rm=TRUE))

wvs_final %>% group_by(labor_force) %>% summarize(mean(Q65_armedforces, na.rm=TRUE))
wvs_final %>% group_by(labor_force) %>% summarize(mean(Q69_police, na.rm=TRUE))
wvs_final %>% group_by(labor_force) %>% summarize(mean(Q70_courts, na.rm=TRUE))

I have about 50 question variables so I don't want to try and use group_by to get each of these individually, but am stuck on how I would go about automating this.

Thanks for any help.

dplyr tidyverse r
1 Answers

You can get the data in long format and for each column calculate mean.

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = urban:labor_force) %>%
  group_by(value) %>%
  summarise(across(Q65_armedforces:Q70_courts, mean, na.rm = TRUE))
user_39629140
Related