Stand cat reshape

The first thing I did when I downloaded the Stand results was to load the csv file into Tableau. It was able to do some nice basic mapping, but when it came to graphing and mapping categories it wasn't happy. The categories are assigned to the responses in pieces.( A brief data dictionary might be in order.) To the best of my knowledge, the 4 Stand questions were broken into 5 possible responses each. This was derived from the fact that the paper survey, which most of the results came in on, had 5 lines under each question. So some people filled it out as bullet points, 1-2-3-4-5, while others used the lines for a narrative response. Either way, each question was broken into 5 parts and each part could receive 3 category designations. Four questions, 5 parts, 3 categories gives us the potential of having 60 category designations per question/respondent. This is wide data. Many columns per row. Whether due to the way Tableau works or my ignorance of it, I couldn't get the wide data to work when trying to do category analysis per response record or per category designation. I needed to take the wide data and make it long data, or one row per respondent per category number. Excel was the savior in this. I downloaded an extension for Excel that did just that and reshaped the data to create one row for each designation. Five minutes later I had a spreadsheet with case numbers and categories that their responses fit into. Luckily, not all of the responses were 5 lines with 3 categories, otherwise it would have gone beyond Excel's limits. It turned out 273,000+ rows. I used this sheet for one of my four final database tables for looking at Stand data. I will outline and share the others in upcoming posts. While I do have 2 primary dbs of Stand data, I mostly use the Stand web interface for quick queries and preliminary scanning.

Note: My intention was to put up an interactive visual Stand exploration tool on this blog. It might still happen as I imagined, however the free Tableau Public tool has a row limit which the category sheet above more than surpasses. I hope to still create a map interface that then displays results from the web interface via its API.