I am analyzing a survey and have reshaped my data as recommended with all of the questions in one column and all of the responses in another. Along with these columns, I have accompanying demographic and latitute / longitude columns so that I cross-tabulate results by various demographic categories, and also map responses related to street addresses, etc.
My problem is I want to be able to map and color-code responses by some of the questions that are embedded in the Questions column, but cannot figure out how to do this. Heres a simple example that will explain what I mean.
In my survey, I've asked respondents to indicate street intersections where they live, and then assigned latitude and longitude coordinates to the intersection. From there, I can easily illustrate where respondents are living. Because "Age" is one of my demographic responses that was not part of data-reshaping, I can throw the "Age" field on the color shelf and my map produces a pie chart for the intersection color-coded by age. Simple -- no problem.
The difficult comes when I want to illustrate the map by type of housing unit (e.g., apartment / condo / house / etc.) the respondent lives in. The unit type response is part of the data that I reshaped, so there is no specific field for unit type; rather, for each respondent there is a "question" column, with many questions including "what type of unit do you live in?" and an answer column with many answers including the respondent's answer to the specific unit type question -- e.g., "house." If I throw the "Question" column on the color shelf, I get meaningless information because there are 100+ questions from the survey.
Is there a way to color code my responses by unit type (or other similar question embedded in my reshaped data), or do I have to regenerate my database with "unit type" as another type of demographic data that I can use for cross-tabbing. (Not sure how I'd do that and still have the data available for analyzing as a survey question.)
See attached "Test File.twb" -- open to Map01 to see how I've mapped according to demographic data (Q116 on the color code shelf). Then go to "Test" worksheet and see that I've selected Q007 (Unit type) from the Question column, and would like to illustrate different pie segments at each map intersection based on the types of units of the respondents.
I hope this explanation is clear. The whole data reshaping activity is still pretty fuzzy for me.
Test File.twb.zip 116.9 KB