Hi all,
I need to analyze mixed survey data. Some of the questions are single choice/user is requirested to provide just one answer, others are multiple choice. Multiple choice questions are of 2 types:
- 1) assumes 1/0 as acceptable values, e.g. What kind of music do you prefer?
- a) Pop/Not Pop (1 or 0)
- b) Rock/Not Rock, (1 or 0)
- c) Classic/Not Classic (1 or 0)
- 2) assumes predefined values, e.g. How long have you been palying guitar?
- a) 1-2 years
- b) Do not remember
- c) 3-5 years
In total I have 1000 users, each user is identified by record id. The dataset has 1000 rows (one for each user), about 30-35 questions and 1500 columns (in one 1/0 -type question I have 30 different options to choose => 30 columns to cover just one question).
user q1a1 q1a2 q1a3 q1a4 q2a1 q2a2 q2a3 q3
1 1 0 0 1 1 7 4 25
2 0 1 1 0 0 3 1 20
etc
The data as it arrives (1500 columns) becomes quite unmanageable and my first idea was to unpivot it. So from the format one user - one row and many columns I passed to one user - multiple rows and one column (to store all the responses).
user Attribute Value
1 q1a1 1
1 q1a4 1
1 q2a1 1
1 q2a2 7
1 q2a3 4
1 q3 20
The format seems quite suitable to visualize single questions, but when I need to do conditional counts, i.e. users who prefer rock and play guitar and do smth else, this becomes tricky because when I select one question as a filter, others diappear (and this is quite normal).
What I want is to be able to show for those who prefer Rock what are their answers on the other questions.
I have already tried several models but struggle to create one flexible and simple enough at the same time. Keep 1500 columns is not an option, unpivot everything doesn't seem a suitable choice as well. I don't think that my dataset is unique and I'm quite sure that there should be an elegant solution, but I'm just missing something.
Any ideas/advice how to organize the data?
Thanks in advance,
Victoria
