Say I have a csv file with following data format:
ID, Name, Gender, Q1
1, ABC, Male, "A1;A2"
2, ACB, Male, "A2;A3"
3, BAC, Female, "A1"
I would like to transform it into following format so that my data analysis tool can process it properly:
ID, Name, Gender, Questions, Responses
1, ABC, Male, Q1, A1
1, ABC, Male, Q1, A2
2, ACB, Male, Q1, A2
2, ACB, Male, Q1, A3
3, BAC, Female, Q1, A1
Using Text-to-Columns feature I can easily separate Q1 column into different columns, but I am stuck after that. Anyone had experience reshaping such data?
(data is collected via google form, unfortunately google store multiple choice question responses in such format, so not much I can do on data collection.)
EDITED: I have posted a question with more details on [StackOverflow].(http://stackoverflow.com/questions/16139313/transforming-csv-data-for-analysis-and-visualization)