1 Reply Latest reply on Jan 9, 2013 5:57 PM by Bora Beran

    Best way to scrub, reshape and merge two data sets

    Kyle Kim

      I have two data sets – one from the OECD, the other from Brookings Institute – that I would like to merge into one. Basically, I want to visualize a few economic indicators (middle class population, manufacturing numbers, GDP growth, etc) with the world's top ten economies. The OECD data will have the majority of the information – I just need to merge the middle class numbers from the Brookings data file.

       

      Both don't adhere to the format Tableau requires and I just wanted to confirm that the step's I listed is the correct workflow to reduce future headaches. I've read Tableau's "Preparing Excel Files for Analysis" article so I think I know what I need to do, but just not entirely sure how to carry it out technically.

       

      1. In the TOP 10 economies xls, reshape the data so that years are all in one column using the reshaper tool. I was playing around with the reshape tool for it to do this, but no dice. A search in the Tableau community forums turned up with nothing also. Do you know if there is a Tableau guide on how to use the reshaper tool?

       

      2. In the Brookings xls, reformat the data so the "Middle Class Population (Headcount ratio)" for the top ten countries are displayed. I only know how to do this manually (unless the reshaper tool also allows you to filter out data?)

       

      3. Merge the two xls files.  Not sure exactly what the best practice is for this one. Do I merge the two sets before Tableau manually, or is there a way to do this in Tableau by matching the country field?

        • 1. Re: Best way to scrub, reshape and merge two data sets
          Bora Beran

          Hi Kyle,

          For your first question, please try the following.

          • Click Reshape Data in Tableau tab in Excel after installing the reshaper tool
          • In the first tab in the dialog that pops up select E2 (which is where the data you want to reshape starts) then click OK
          • This should create a new sheet in the workbook in the format you need. There should be two new columns with auto-generated names. You may rename them to something more meaningful.

           

          For your second question

          • You don't need to use reshaper tool at all. Just delete the first row in Excel and you're ready to read it into Tableau.
          • You don't need to filter the data in Excel. Once data is in Tableau there are multiple ways to filter. You can use a filter or create a set for the Top 10 economies.

           

          For the third question

          • Since you have a field (country name) shared between the two data sets, simplest way to do this is using data blending feature of Tableau.

           

          I hope this helps.