I try to create a frequency table from a number of variables sharing the same answer categories (Likert-typed). The result with three Variables (Question1-3) and 5 answer categories (-- to ++) should about look like this:
| | -- | - | ~ | + | ++ |
| --------- | --- | --- | --- | --- | --- |
| Question1 | 5% | 20% | 25% | 30% | 20% |
| Question2 | 15% | 10% | 20% | 25% | 30% |
| Question3 | 10% | 30% | 10% | 30% | 20% |
I found one working solution at https://stackoverflow.com/a/44085852/3680150 with functions from the packages expss which is really helpful creating weighted and labelled frequency tables. But I have some troubles using labels, since this solution does not seem to work when the variables are labelled:
1) The solution with expss by @GregoryDemin from: https://stackoverflow.com/a/44085852/3680150
# The data we'll also use in the examples below.
q1<-c(2,2,3,3,3,4,4,4,5,5)
q2<-c(2,3,3,4,4,4,4,5,5,5)
q3<-c(2,2,2,3,4,4,4,5,5,5)
df<-data.frame(q1,q2,q3)
library(expss)
# add value lables for preserving empty categories
val_lab(df) = autonum(1:5)
res = df
for(each in colnames(df)){
res = res %>%
tab_cells(list(each)) %>%
tab_cols(vars(each)) %>%
tab_stat_rpct(total_row_position = "none")
}
res = res %>% tab_pivot()
# add percentage sign
recode(res[,-1]) = other ~ function(x) ifelse(is.na(x), NA, paste0(round(x, 0), "%"))
res
The output:
| | 1 | 2 | 3 | 4 | 5 |
| -- | -- | --- | --- | --- | --- |
| q1 | | 20% | 30% | 30% | 20% |
| q2 | | 10% | 20% | 40% | 30% |
| q3 | | 30% | 10% | 30% | 30% |
That looks good - despite that the NAs should be Zeros, shouldn't they?. How could we make sure that not used categories show 0% instead of NA?
2) Now we add some variable/value labels:
q1<-c(2,2,3,3,3,4,4,4,5,5)
q2<-c(2,3,3,4,4,4,4,5,5,5)
q3<-c(2,2,2,3,4,4,4,5,5,5)
df<-data.frame(q1,q2,q3)
library(expss)
# Label variables and categories
df %<>% apply_labels(q1 = "Question 1",
q2 = "Question 2",
q3 = "Question 3",
q1 = c("strongly agree" = 5, "agree" = 4, "neutral" = 3, "disagree" = 2, "strongly disagree" = 1),
q2 = c("strongly agree" = 5, "agree" = 4, "neutral" = 3, "disagree" = 2, "strongly disagree" = 1),
q3 = c("strongly agree" = 5, "agree" = 4, "neutral" = 3, "disagree" = 2, "strongly disagree" = 1))
# add value lables for preserving empty categories
#val_lab(df) = autonum(1:5) # we labelled before, so no need for that anymore
# Now for the table
res = df
for(each in colnames(df)){
res = res %>%
tab_cells(list(each)) %>%
tab_cols(vars(each)) %>%
tab_stat_rpct(total_row_position = "none")
}
res = res %>% tab_pivot()
# add percentage sign
recode(res[,-1]) = other ~ function(x) ifelse(is.na(x), NA, paste0(round(x, 0), "%"))
res
The output:
| | Question 1 | | | | | Question 2 | | | |
| | strongly disagree | disagree | neutral | agree | strongly agree | strongly disagree | disagree | neutral | agree |
| -- | ----------------- | -------- | ------- | ----- | -------------- | ----------------- | -------- | ------- | ----- |
| q1 | | 20% | 30% | 30% | 20% | | | | |
| q2 | | | | | | | 10% | 20% | 40% |
| q3 | | | | | | | | | |
| Question 3 | | | | |
strongly agree | strongly disagree | disagree | neutral | agree | strongly agree |
-------------- | ----------------- | -------- | ------- | ----- | -------------- |
| | | | | |
30% | | | | | |
| | 30% | 10% | 30% | 30% |
The variables are not stacked anymore but standing side-by-side. It seems that this working solution breaks, if we add variable labels. Do you have an idea how to prevent this from happening?
Method from https://stackoverflow.com/a/44085852/3680150 works only with variables without variable labels. Universal code with zero percents:
q1<-c(2,2,3,3,3,4,4,4,5,5)
q2<-c(2,3,3,4,4,4,4,5,5,5)
q3<-c(2,2,2,3,4,4,4,5,5,5)
df<-data.frame(q1,q2,q3)
library(expss)
# Label variables and categories
df %<>% apply_labels(q1 = "Question 1",
q2 = "Question 2",
q3 = "Question 3",
q1 = c("strongly agree" = 5, "agree" = 4, "neutral" = 3, "disagree" = 2, "strongly disagree" = 1),
q2 = c("strongly agree" = 5, "agree" = 4, "neutral" = 3, "disagree" = 2, "strongly disagree" = 1),
q3 = c("strongly agree" = 5, "agree" = 4, "neutral" = 3, "disagree" = 2, "strongly disagree" = 1))
# add value lables for preserving empty categories
#val_lab(df) = autonum(1:5) # we labelled before, so no need for that anymore
# Now for the table
res = df
for(each in colnames(df)){
res = res %>%
tab_cells(total(label = "|")) %>% # suppress total label
tab_cols(unvr(vars(each))) %>% # remove variable label
tab_stat_rpct(total_row_position = "none", label = var_lab(vars(each))) # use variable label as statistic label
}
res = res %>% tab_pivot()
# add percentage sign
recode(res[,-1]) = other ~ function(x) ifelse(is.na(x), "0%", paste0(round(x, 0), "%"))
res
# | | strongly disagree | disagree | neutral | agree | strongly agree |
# | ---------- | ----------------- | -------- | ------- | ----- | -------------- |
# | Question 1 | 0% | 20% | 30% | 30% | 20% |
# | Question 2 | 0% | 10% | 20% | 40% | 30% |
# | Question 3 | 0% | 30% | 10% | 30% | 30% |