I used Google form to conduct a survey. I downloaded the response dataset as a spreadsheet, but unfortunately it comes to multiple choice, multiple anwsers responses.
The data is:
Q1 Q2 1 "A, B ,C" 2 "C, D, A" 1 "A, C, B, D" 3 "D, A, C, B" 2 "B, E, A" 3 "C, A" ``` I would like to separate the choices into 5 columns "A, B, C, D, E". I used the code: Result <- data %>% mutate(ID = 1:n()) %>% mutate(Q2 = strsplit(Q2, split = ",")) %>% unnest() %>% mutate(Value = 1) %>% spread(Q2, Value, fill = 0) %>% select(-ID) However, the result is not what I expected. Instead of separting the text "A,B,C,D,E" into 5 columns, the results is more than 5 columns. Does anyone know how to do this? Please help me! Thank you so much!