How to separate multiple choice, multiple answers questionnaire data that Google Forms put in one variable?

[Thienthanh]

Hi everyone,

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!

[reddyr]

Checkout cSplit_e() function in splitstackshape package

[rjpat]

Looks like your code is keeping whitespace in your string split. Adding a \\s? to your strsplit will correct it. It looks for zero or more whitespace after the comma when splitting.

data %>%
  mutate(ID = 1:n()) %>%
  mutate(Q2 = strsplit(Q2, split = ",\\s?")) %>%
  unnest() %>%
  mutate(Value = 1) %>%
  spread(Q2, Value, fill = 0) %>%
  select(-ID)

[Thienthanh]

Thank you so much @rjpat

This is exactly what i was looking for. It worked!!