Mar 302015
 

Overview

Tableau 9.0 includes a built-in data prepping tool that makes reshaping survey data so it plays nicely with Tableau a much smoother experience than using the Tableau Excel add-in.  While this new feature won’t replace by trusty copy of Alteryx (for reasons that I explain later in this post) there are many occasions where Tableau’s new pivot feature will be more than adequate.

In this post I will walk through using the new pivot feature along with “temporary” blending to create a solid framework for using survey data with Tableau.

Special thanks to Susan Baier for bringing this to my attention and Jonathan Drummey for showing me Tableau’s Create Primary Group feature.

So, what do we have here?

Note: if you want to follow along you can download the Excel file here.

Consider an Excel workbook that contains two sheets.  The first sheet has the survey results, a snippet of which is shown here.

Figure 1 -- Some raw survey data

Figure 1 — Some raw survey data

Notice the format: one row for each survey respondent and a separate column for each question in the survey where each question is identified with a Question ID (e.g.,  Q0, Q1, Q2, Q134a, etc.).

Column A contains a unique ID for each survey taker, Columns B through D contain demographic information, and Column E contains a weight for each survey respondent.

The second sheet maps each Question ID to a human-readable version of the question and groups related questions into logical buckets.

Figure 2 -- Helper file that maps each Question ID to the wording of the question from the survey

Figure 2 — Helper file that maps each Question ID to the wording of the question from the survey

Note that when I first blogged about survey stuff I didn’t use a helper file but now I won’t take on a project without creating one as I don’t want to spend time aliasing hundreds of question IDs.  The Grouping column also makes is much easier to select related questions and visualize them together.

The data wants to be tall and thin

Anyone who has read up on the subject know that life with survey data and Tableau is a lot easier when the data is reshaped so let’s see how to do this with Tableau 9.0.

  1. In Tableau, connect to the data source and the sheet that contains the data you want to reshape and visualize. This is what it looks like on my screen.

    Figure 3 -- Survey data prior to pivoting (reshaping)

    Figure 3 — Survey data prior to pivoting (reshaping)

  2. Select the fields you want to merge / pivot / reshape, in this case everything except the Resp ID, demographic fields, and Weight field.
  3. Click in any of the highlighted fields and select Pivot. Tableau will combine the 20+ fields into two fields, as shown here.

    Figure 4 -- Data after it has been pivoted

    Figure 4 — Data after it has been pivoted

  4. Rename the first field Question ID and the second field Value.

    Figure 5 – Pivoted fields renamed

    Figure 5 – Pivoted fields renamed

  5. Indicate whether you want an extract (a good idea when Excel is the data source) and go to the Tableau worksheet.
  6. Drag Question ID onto the rows shelf. Your screen should look like this.

    Figure 6 -- Reshaped data in Tableau.  Instead of 20 measures for each question we have only one measure.

    Figure 6 — Reshaped data in Tableau. Instead of 20 measures for each question we have only one measure.

Creating the temporary blend

Now we need to connect and relate the Helper file to our pivoted survey data.  We will do this with a blend, but then use a very slick feature of data blending that will allow us to ditch the secondary data source. Here are the steps.

  1. Click the Add New Data source tool.8_dataSource
  2. Connect to the Helper File sheet from the same Excel workbook and indicate whether or not you want to create an Extract (of course you do!)

    Figure 7 -- The secondary data source

    Figure 7 — The secondary data source

  3. Return to the Tableau worksheet.
  4. Drag the Grouping field to the left of Question ID on the rows shelf, and Wording to the right, as shown below.  Note that you don’t *have* to do this but it’s always useful to see if the hierarchy is working correctly.

    Figure 8 -- Blended Data

    Figure 8 — Blended Data

  5. Right-click the Grouping pill on the Rows shelf and select Create Primary Group.
  6. Rename the group Grouping as shown below.

    Figure 9 -- Leveraging the blend to create an ad-hoc group based on Question ID fields.

    Figure 9 — Leveraging the blend to create an ad-hoc group based on Question ID fields.

  7. Click OK.
  8. Right-click the Wording pill on the Rows shelf and select Create Primary Group.
  9. Rename the group Wording and click OK.
  10. Click the primary data source (the one from which we initially selected Question ID). Notice the two groups that Tableau generated for us.

    Figure 10 -- Tableau-generated groups

    Figure 10 — Tableau-generated groups

At this point we no longer need the secondary data source as the primary source now has groups that map and alias the Question IDs.  Very slick.

Seeing this in action

Now that we have the groups it’s easy for us to do some very quick analysis.  For example, let’s suppose we want to see the average Likert scale score for the collection of Likert scale questions.

  1. Create a new worksheet.
  2. Drag Grouping into the Filters shelf and select the collection of questions you want to view, in this case Likert Set 1.

    Figure 11 -- The Grouping group makes is easy to indicate which sets of related questions you want to examine.

    Figure 11 — The Grouping group makes is easy to indicate which sets of related questions you want to examine.

  3. Drag Wording to Rows.
  4. Right-Drag Value to Columns and select AVG(Value).
  5. Sort in descending order.

Isn’t this great?  We didn’t have to go groping around for the right Question IDs and we didn’t have to alias anything.

So, are there any shortcomings?  Is this blend approach as good as being able to join the pivoted data with the helper file?

Yes, there are shortcomings

There are several things that a join will give us that we can’t get with a blend.

You cannot refer to the group in a calculated field

You can’t refer to a group in a calculated field, so something like this won’t be available:

IF [Grouping] =”Things you Measure” then [Value] END

You need to update the group members if you add new questions

Tableau’s generation of the primary group is much like populating the members of a parameter with the members of a field.  Tableau will do it when you click a button, as it were, but it won’t update the list automatically.

If you end up adding new questions to your survey or reorganizing how questions are categorized in your helper file will either need to regenerate the primary data source groups or manually edit them.

You cannot combine text results with numeric results

This is one of the major “gotchas” for me, at least for larger surveys.  With most commercial survey systems you can download the data in a label format or a numeric format.  For example, when downloaded as labels survey responses might look like this:

Strongly disagree
Disagree
Neutral
Agree
Strongly agree

When downloaded as numbers the same responses would look like this:

1
2
3
4
5

I find I like to have both label and numeric responses, so I pivot / reshape both sets of data and then join them together using Question ID and Response ID.  Using Alteryx I can perform the join but I cannot do it with Tableau 9 and pivoted data.

Conclusion

For complex surveys where I need to do a fair amount of data cleanup and need both next and numeric values I’ll continue to use Alteryx.  For shorter surveys where I don’t need to do a lot of prep work and where either labels or numeric values will suffice, Tableau 9.0’s new pivot feature suits me just fine.  It’s a great addition to a great product.

[suffusion-the-author]

[suffusion-the-author display='description']
 Posted by on March 30, 2015 2) Visualizing Survey Data, Blog Tagged with: , , ,  Add comments

  29 Responses to “Reshaping Survey Data with Tableau 9.0”

Comments (28) Pingbacks (1)
  1. Steve,

    “I don’t always work with Excel data, but when I do, I use the data reshaping feature.”

    I’m really liking the ability to split, pivot, ignore extra headers and merged cells. Survey data isn’t the only kind of data that often requires some reshaping — so I’m very glad to see these features!

    Regards,
    Joshua

  2. Joshua, I just *knew* you were the most interesting man in the world!

  3. Another great post Steve. Thanks for the step-by step instructions. I look forward to giving it a try.

    However, I have one question already. My surveys run continuously. Each month I download a csv file and use Extract > Refresh to update the Tableau dashboards for clients. I use custom SQL to edit headers and reshape the data.

    Custom SQL versus Excel is great in that it’s only to set up once.

    However, it’s increasingly a pain to set up custom SQL for a new client (the interface has deteriorated since Tableau v8.1).

    I don’t reshape the demographic data which means I end up with a Primary data source (demographic data) and Secondary data source (Likert scale data) and I can’t filter as I want.

    So, I hoped Tableau v9 would remove (a) the need for custom SQL and (b) the Primary and Secondary datasource issue.

    From your post, It seems it would BUT (just as for Excel) I would have to go through the process you describe for every client every month. Is that right?

    If so, is Alteryx the answer?

    • I just stumbled upon this blog post. Interesting. I guess you already solved your issue Helen. Otherwise, I would consider Power Query in Excel 2013. In Excel 2016, it is already embedded in the Data menu.

  4. Very helpful tutorial! I wanted to add a tip for anyone working with survey data exported from Qualtrics.

    Question ID’s (column headers) will include underscores (example: Q1.1_1_Rank) when exporting from Qualtrics. When you perform the pivot inside Tableau of your full dataset, it strips out the underscore characters. This means Tableau can’t easily establish the relationship between the data table and the helper data.
    I had to remove the underscores in my data description/grouping table and replace them with regular space characters to resolve this.

    • Tripp,

      Thanks for the “heads-up” on this. I didn’t realize this would happen so you will indeed need to do some one-time futzing with the Helper table.

      I still much prefer to use Alteryx for all of my ETL stuff, but it is pricey.

      Steve

  5. Great, instructive blog! I’m new to Tableau and want to use it to generate dashboards for my sales team to access our survey data quickly. I’m following along on the “Create Temporary Blend” and at step 5, I’m not seeing a “create primary group” option when clicking on the “Grouping” pill in the rows shelf. (I’m using Tableau 9.0.4 (9000.15.0720.1008) 64-bit). I’m able to see a “create group” option in the tray to the left, but it doesn’t have a “create primary group” option either. When I just create a group using that menu, the created group does not show up in the primary data page. Is there something funky going on, or am I just missing locating the “create primary group” function?

    Thanks!

    • Ed, I just walked through the instructions using the sample data set and it appears the instructions are accurate. Are you trying with the sample data set or your own? I’d give it a go with the sample and see how it goes. If you’re still not getting any joy maybe we do a quick five minute screen-sharing session and I can trouble shoot.

      Steve

  6. Yep, found it in the example data, but for some reason it doesn’t show up in my survey data…I will play around with it some more in my data set to see if I can replicate. Probably something stupid/operator error.

  7. Hey all,

    Is there an easy way to deal with the multiple alias/value problem.

    For instance, say I have survey data that includes a variety of questions and UNIQUE Likert scales.

    After reshaping the data, you would have columns for IDs, the questions, and the responses.

    All the response data will have values from 1 – 5. However, for certain questions 5 might equal “Very Cold” and for others 5 might equal “Very Interesting”. However, because of the data structure, you cannot provide aliases for each value within questions.

    I haven’t been able to find much on Tableaus community forums, and the fixes they do have are iffy.

    Thanks!

    • Mohammad,

      Indeed, this is a big shortcoming of not being able to perform a join with pivoted data as I want both numeric and text values so I don’t have to go nuts with aliasing (this is why I use Aletryx).

      That said, you can use the old Tableau Excel add-in with two sets of data. If you go to my “visualizing survey data” page you will find a link to my presentation at the 2014 Tableau Conference. I explain how to do what you want.

      Hope this helps.

      Steve

      • Thanks for the quick reply, Steve!

        I’ll check out the presentation, thanks so much.

      • Hi Steve,

        I’m using the instructions in your presentation to prepare my survey data, but the two columns of data (labels and numbers) do not align after reshaping. It looks like the Tableau reshaping add-in is excluding null cells within the labels, but not excluding them from the numbers. Without recoding all null cells in the data labels as “null,” do you know of a way to correct or work around this?

        Thank you!
        Jessica

        • Jessica,

          I have yet to see downloaded CSV data (or downloaded Excel data) where the data is made available in both label and numeric format? Do you in fact have two sets of columns in the same data set and are you attempting to pivot the data twice?

          Happy to take this offline as I would like to see the data with which you are working.

          Steve

          • Hi Steve,

            Thanks for the quick reply. I meant that I’m in the process of creating the tab you call “merged” in the presentation. While my pivoted “raw data values” tab has about 800k rows, my pivoted “raw data labels” tab only has around 400k rows, so they don’t line up correctly (as you mention at about 17:55 in your presentation). I already ensured that both sets of data have the same number of measures and respondents- it just appears that the Tableau add in skips over nulls in the data labels but retains them in the data values.

            I cannot share the data as it’s confidential, but feel free to contact me at my email address (schue125@umn.edu) if that’s easier.

            Thank you!

  8. Steve, Thanks very much for posting such helpful instructions! I was able to follow the instructions to do everything in this blog post, but I get stuck when I try to go any further (for example, I’m not able to show a bar chart with responses to the “vote” question).

    I know you posted about these topics in another thread, but your other threads reference data that was reformed with the Excel add in or some other tool and the resulting data set looks different. I really would like to know how to move forward with data that is blended as you describe here (using Tableau 9.0), but I don’t understand the logic of this kind of data format.

    Thank you for any help provided!

    • Anne,

      Because of the limitations of only being able to use either numeric values or text values, you’ll need to create a bunch of calculated fields.

      For the Vote question, I created a field called “Yes / No / Maybe” that is defined like this:

      IF [Value]=0 then “No”
      elseif [Value]=1 then “Yes”
      elseif [Value]=2 then “Maybe”
      END

      I then place this on Rows and place SUM(Number of Records” on columns, and I make sure to filter out the nulls.

      I will send you a packaged workbook that contains the solution to your e-mail.

      Hope this helps.

      Steve

      • I’m having the same problem, could you possibly send me the workbook as well? I’m not totally sure I understand how to create these calculated fields in order to proceed with the rest of the instructions (I’m only able to use the reshaping in Tableau, no add-ons for me).

        Thank you so much for providing instructions that make sense!

        -Kat

        • Kat,
          do you want the workbook that goes with the Tableau 9.0 pivoting instructions or the workbook that has all of the ways to create various visualizations (check-all-that apply, Likert, etc.)?
          Steve

  9. Do you know what is the formal limitation as far as the number of grouping you can have in a worksheet. It seems like once I passed 19 groupings, I get an error message saying the data is too complex.

    Thanks!

    • Shelly, I do not know the formal limitation. Are you doing 19 different sets of pivots? I’m not sure why you would need to do so many.

      Steve

  10. Steve, this is a great feature in Tableau 9 that I hadn’t seen yet.

    Here’s the thing:

    For me, I learn better when I work through examples using real data. Fortunately for me, you’ve provided the Excel data and a great guide to get me started. I went ahead and included this 1,100 word tutorial in my Tableau beginner mistakes (I’m new to Tableau and posting my notes..) I hope it might help someone else!

    quick overview of what is covered
    1. The right way to use Tableau and templates together.
    2. How to prevent “information overload” with Tableau dashboards
    3. Mistakes to avoid while blending data in Tableau
    4. 6 tips for easy formatting in Tableau
    5. Step-by-step instructions to “reshape” your data for Tableau

    http://newprediction.com/tableau-beginner-mistakes/

  11. Hi, new Tableau user here, working with survey data. I’m getting stuck on step 4 of creating the temporary blend. When I drag the grouping and wording fields over to rows, I just get nulls. I have no nulls in my data. I’ve been through the exercise numerous times and tried to think of everything I could be doing wrong. Any ideas?

    • Leigh,

      I’m traveling but if you still have not been able to figure this out we can do a quick screen share early next week.

      Let me know.

      Steve

  12. Hi Steve

    Thanks for the very useful blog and white paper on using survey data available on the Tableau website.

    I too cannot see the ‘create primary group’ function when I right click the group pill? I am using 10.1 for Mac.

    It would be good to remove the secondary data source.

    Additionally what would you recommend for large survey data (which is available in numeric values only)? Should I create a helper text file as described? I do not have access to Alteryx (too costly) and Excel Add In (Windows only) so could only blend using Tableau 10.1?

    I look forward to your reply.

    Thanks
    Colin

Leave a Reply to Joshua Milligan (@VizPainter) Cancel reply

(required)

(required)