Creating Basic Visualizations in Tableau Using Census Bureau Data

0

Introduction

Data is becoming more available and so are ways to display it. This demonstration will show you how to quickly find and present data using tools offered by the U.S. Census Bureau and the software visualization tool, Tableau

What you will need

Microsoft Excel

Tableau Public 9.2

An Internet Connection

First Steps

Finding your data

Your data for your Tableau data visualization can come from a number of different places. As the title suggests we are going to use U.S. Census Bureau data. You can get Census Bureau data from a number of different places, but we are going to focus on American FactFinder because of the amount of data that is found their and it’s relatively easy to use.

  1. Go to factfinder.census.gov
  2. Click on Advance Search
    1. On that same page, click “Show Me All
  3. From here there are a number of different ways you can go about finding your data. You can search for it by typing in key words or use the blue search tabs on the left hand side to sift through the different kinds of data.
    1. For this example, lets say we want to determine the percentage of Households that are Food Stamp Recipients. I will go ahead and type “Food Stamps” into the topic search.
    2. After doing the search, we find that there is table named Food Stamps/SNAP or ID S2201.
    3. Before we select this table, lets add a geography so we can see the results for every county. To do this click “Geographies”.
    4. For this example, lets look at counties. To do this select county under “select a geography type”. Then since we want to look at all the counties in the U.S., we will select “All counties within United States” . Finally, click “Add to Your Selections” and close the window.
    5. We will now select S2201. Let’s use the dataset that is labeed “2014 ACS 5-year estimates.”
  4. All we need to do now is click, Download which is up in the “Actions” column. You can then click “OK” on the window that appears. Once the file is ready, click “Download” to start the download.

Manipulating your data

  1. Once the data is downloaded, you will have a zipfile you can open. The zipfile contains 4 files. We can go ahead and click the one that is labeled “ACS_14_5YR_S2201_with_ann”.
  2. Now that the file is open, you can see there is a number of rows and columns. Lets first get rid off the ones we don’t need. The top row contains label titles used by the Census, some researchers, and developers. Since Tableau will read the first row and use that to as the name of the variable, we would rather use the second row which uses more user friendly names. To delete the row,  right click row number one, and click delete.
  3. Now since we want to visualize the percentage of households the receive food stamps, lets find the variables we need and move them to a new spreadsheet. Go ahead and click “File” and then “New”.
  4. Copy the first 3 columns, Id Id2, and Geography to the first three columns of the new worksheet.
  5. For the column, “Geography”, we have the county and the state in the same cell. Tableau will need them seperated if we want to use the map functions. Lets go ahead and split that row.
    1. You can split the row by going to the data tab in the excel ribbion.Then click on row C so it is highlighted and then click “Text to Columns.
    2. You will now follow the wizard to split the field. We will use the delimited option. Then click the check mark next to column and then next. Finally, go ahead and click “Finish” The row will now be split. Finish up by renaming column “C” “County” and Column D “State”
  6. Lets Finish up our new Excel book by finding the variables with the total number of households and the households receiving food stamps. These happen to be Column D and Column F in the workbook downloaded from American FactFinder.
  7. Once you have variables in the sheet let create a column with the percentage. Let’s right “% Receiving Food Stamps” in Cell G1. Then in G2, write =F2/E1. This will show the percentage. You can calculate the rest by clicking the cell and then double clicking the small square in the bottom right corner.
  8. Lets go ahead and save the new file and call it “Food Stamps”

If you want to skip these steps or want to make sure you go it correct, you can find the Excel workbook here, “Food Stamps

Getting Started in Tableau

  1. Tableau offers a free version of their software to demo on their website, https://public.tableau.com/s/ . All you need to do is provide your email and your free download will start.
  2. Once the program is installed, you will come to the home screen which asks you to open a saved workbook or connect to a data source. Since we have an excel workbook we want to visualize, lets click Excel, under Connect. Then find the file that you saved.
  3. Once you have “Food Stamps” open, you will be on the Data Source screen. You can confirm your data come over correctly here.
  4. To start your visualization, click on Sheet 1.
  5. On the left hand side, you can see the all the variables listed out. Lets start our visualization by loading the geography. Double click “County” in from the “Dimensions”
  6. At this point you should see a number of dots over all the counties in the US. The bottom right hand corner shows that one is unknown. This is because it doesn’t match what Tableau is expecting. Click on 1 Unknown and then click “Edit Locations”
    1. Petersburg Borough is the unrecognized county. If you click unrecognized, and then start to type Petersburg, it will pop up and you can select it. Then click “OK” to exit.
  7. Now lets add some color to your map. Click and hold % Receiving Food Stamps and drag it onto color.
    1. If you are interested in changing the color hover over the color bar and click the arrow in the top right corner. Then click edit color. You can now choose the color scheme you like best.
  8. Now the county is colored based on the %. Go ahead and zoom in to look at some of the differences. Hovering your mouse over the county will show the detailed info.
  9. The map switched to a filled map. Lets switch it back to a dot map so we can show the number of people living in that county. Pull the Total, Estimate, Households Measure onto size.
    1. It may be a little hard to see. If you click on size you can adjust the size of the circle.

This completes our walk through of creating a Tableau visualization using Census Data. If you have any questions let us know in the comments below.

Share.

Leave A Reply