I have data such as this.
data_in <- read_table2("**Characteristic** **N=175**
Q50_1.Enrollreason-Bystudentchoice
Never 65/153(42%)
Rarely 31/153(20%)
Sometimes 42/153(27%)
Often 15/153(9.8%)
Q50_2.Enrollreason-Byparentchoice
Never 4/153(31%)
Rarely 9/153(19%)
Sometimes 7/153(37%)
Often 2/153(13%)
Q50_3.Enrollreason-ExpelledByanotherschool
Never 3/152(20%)
Rarely 1/152(8.6%)
Sometimes 36/152(24%)
Often 72/152(47%)
Q60_1.Exitlreason-ReferredByanotherschool
Yes 39/152(26%)
No 13/152(8.6%)
Q60_2.Exitreason-SARB
Yes 44/152(29%)
No 1/ 15(12%)
Q60_3.Exitreason-Other
Yes 8/40(20%)
No 2/40(5.0%)
Q60_4.Exitreasonoffice
Yes 32/155(21%)
No 28/155(18%)
")
I want to re-format this data so that it looks like this (I have not filled in all the data). Basically the responses for each question should be to the right of each question, grouped by question number. For example, all the Q50 questions should be grouped together.
This is what I have worked on so far, but as you can see, R creates new columns for each response type...making it messy to export into excel. I want the "Yes" and "No" to be stacked below the "never", "Rarely" series so that it's easier to view.
The reason for this, is that my actual data has many more answer options, so when I pivot wider the way that I have, I create a very wide data frame that is difficult to read. I need to produce something that is easier for my team to navigate in excel.
data_in %>%
tidyr::separate("**Characteristic**", c("question",'description'), sep = "Q", remove = F) %>%
fill(description) %>%
mutate(description = paste("Q",description)) %>%
dplyr::filter(!is.na(`**N = 175**`)) %>%
select(-`**Characteristic**`) %>%
pivot_wider(id_cols = description, names_from = question, values_from = `**N = 175**`)
