1 Reply Latest reply on Sep 7, 2018 9:41 AM by Michael Gillespie

    Formatting American Community Survey Data

    Lauren Gibson

      I have downloaded multiple ACS datasets and want to use them in Tableau desktop. When I upload them in the excel format that they come in from ACS, the data does not format correctly.

      For example, I want to graph the % of renter occupied units with a household income of less that $20,000 that pay 30% or more of their income in housing costs versus the % of renter occupied units with a household income of $20,00-$34,999 that pay 30% or more of their income in housing costs. Because of the way the columns are structured, I am unable to do that. Attached is also the excel spreadsheet for reference. Does anyone know how to format the data before I upload it so I can use all the data points?

      acs 3.png

        • 1. Re: Formatting American Community Survey Data
          Michael Gillespie

          Lauren, welcome to the wonderful world of ACS data in Tableau.  It's ugly.

           

          You can likely do a lot of stuff in Prep to make this more usable, but conceptually you need to pivot the data so that you have a lot fewer columns and a lot more rows.  It's been a while since I had to do this, but it was a very manual process for me.  Prep should help you automate it.

           

          You'll have to consider exactly what you want to do very carefully (for instance, you'll have to TRIM() the values in  Column A to get rid of the embedded indentations).  You may very well end up manually copying and pasting data into a new spreadsheet entirely.