Browsed by
Month: September 2016

Mapping Census Tracts in Tableau

Mapping Census Tracts in Tableau

As a follow up to my previous post on the US Census Tableau web data connector I created, I wanted to also share another web data connector that allows Tableau users to selectively import Census Tract shapes for mapping within Tableau.

The Census Tract is a key geography within US Census data, and it is the one that my commute map was based on. I wanted to figure out how to map at this level as I played with the census data so I could drill down below the county level using the built-in Tableau maps.

The elusive census tract “shapefiles” seemed mysterious, but it also seemed like there could only be one real logical way to store and communicate this info: with a series of coordinates (latitude and longitude in this case) that define a shape. After finding a website dedicated to this topic, I downloaded its very helpful Tableau workbook that had shapefiles for all census tracts in the US. This can be used to get the info you need, and it is what I imported into my SQL server and used to feed my web data connector.

However, I thought this web data connector might be slightly more elegant and easy to use than pulling the info from this master file. In the example below, I’m adding census tract mapping data and building a heat map for a Tableau worksheet that I used to import census data at the census tract level for the state of Michigan. I imported the percentage of homes built before 1939. See my previous post on how to do this.

Mapping Census Tracts Using the Web Data Connector

tableau_blog3To use the census tract shapefile web data connector, “add a connection” to your workbook. For the URL, enter:

bigbytes.mobyus.com/tableau/censustractshapeswdc11.aspx

After a few seconds, an interface will pop up and allow you to choose the state, county, or individual census tract you’d like to download shapefiles for.

Select your criteria and click on “Retrieve Census Tract Shapefile Data”.

 

tableau_blog4

  • tableau_blog5As the data is loaded, Tableau will ask you to define the relationship between the tract level data you had in your workbook and the new shapefile information. The GEOID column contains the identifier for the shape information. If you used my census data connector to import the census data, then use the value “FIPS” from that table and, as indicated, “GEOID” for the shape table info.
  • This will establish the link between the two tables you now have in your Tableau workbook. Click on “update now” to finish creating the table with the shape information.
  • tableau_blog6Now create a sheet. Right click on “PointOrder” and change it to a dimension. Right click on PointLatitude and change it to “Geographic Role –> Latitude” and do the same for PointLongitude, changing it to “Geographic Role –> Longitude”. Double-click on each of these to add them each to the sheet.
  • Right click on the measure you want to map (we’ll assume a heat map) and select “Add to sheet”.
  • On the “Marks” card, change the dropdown value at top to “Polygon”.Drag the dimension “PointOrder” and drop it on the “Path” icon on the Marks card. This tells Tableau the order in which to draw connected points on the map.
  • Drag the GEOID dimension onto the bottom area of the Marks card.
  • Drag the SubPolygonId dimension onto the bottom area of the Marks card. As a quick aside, I wondered why this value is needed and it is because it defines the areas (which are polygons) and it is only really needed when the shape for a particular census tract has non-contiguous regions. That is, completely separate regions that need to be defined and shaded, but are part of the same defined census tract.
  • Finally, click on the control directly to the left of the measure you added to the Marks card and select “Color”.
  • Your “Marks” card should look like the example below.

tableau_blog7

If everything went according to plan, you should have a heat map displayed like the one shown above. In the example above, the dark blue regions have higher percentages of home built before 1939 and the lighter regions have lower percentages. If you don’t have this… well, leave a comment or email me and I’ll see if we can figure it out.

Hope this new shapefile source adds a tiny bit of extra functionality to Tableau for those of you who work with data by census tract. Please feel free to comment or email me if you have questions or suggestions.  Thanks.

 

 

Tableau, Census Data, and Web Data Connectors

Tableau, Census Data, and Web Data Connectors

Tableau seems to be very hot right now. At least I seem to see it listed frequently when I’m looking through job postings. From my perspective, Tableau takes a lot of the presentation power of Excel (not so much the high power modeling features of Excel) and moves it into a more contemporary platform, by which I mean it is browser-based. I’ve developed many, many scorecards, charts, visualizations, reports, and analytical tools within Excel and it makes perfect sense to adapt these kinds of deliverables into a browser-based tool.

Tableau has a public version that you can download for free and use to create and publish workbooks to Tableau Public for everyone to see. I’m still learning the features, and there are a lot of learning resources on the web if you search around.

One thing I wanted to do was to tap into a great, diverse data source and create some Tableau workbooks to play around with. My commute map used US Census data from the American Community Survey, and when it was making the rounds back in June I was contacted by the Census Bureau asking if I was aware of their API. I wasn’t, though I wasn’t surprised to learn of one. I decided to tap into this data as I learn Tableau, because the Census Data has great breadth of information but also has a terrific multi-tiered geographic component.

Tableau has the ability out of the box to accept data from a wide variety of data sources, but it also has a way for developers to build a conduit into Tableau from any data source. This standardized data conduit into Tableau is called a “Web Data Connector” and a number of organizations have built these to enable Tableau users to import data. I looked for a Web Data Connector (WDC) for the Census API, but wasn’t able to find a working version. Always one to support scope-creep, I decided to build one myself and I did. It connects to the ACS Profile data for the five year estimate ending in 2014.

Unfortunately, I built it based on their WDC version 1.1 just as they were transitioning to a new version (2.0) with more features. If this gets any real use, I’ll look at upgrading the connector to 2.0. Below is a step by step on how to retrieve Census Data into Tableau using my WDC:

Importing Census Data into Tableau

 

Choose "web data connector"
Choose “Web Data Connector”

Obviously, in order to use the WDC, you have to have Tableau loaded and if you do and you start a new workbook, you should see that you can choose “Web Data Connector.”

 

 

 

 

 

 

 

 

 

When you do choose “web data connector”, you’ll see a screen that asks for the WDC’s URL. To use my Census Data WDC, you’ll want to enter:

bigbytes.mobyus.com/tableau/censuswdc11.aspx

tableau_blog1.5
Enter “bigbytes.mobyus.com/tableau/censuswdc11.aspx” as the URL for the web data connector.

 

Select Geographic criteria and variables you'd like to retrieve in the web connector interface.
Select Geographic criteria and variables you’d like to retrieve in the web connector interface.

Once the data connector interface loads, you can choose a Geography (State, County, SubCounty, Tract, Zip Code, etc.) that you would like to retrieve and choose which variables you would like to include. The variables are one of the harder things to manage here. There are lots of them (about 2,500) and the names are cryptic. The list of variables for this particular Census dataset can be used as a reference.

You can use the hyperlinked text just above the variable list to include or exclude variable types. For example, if you want to include only “Estimates” then unselect the other three types “MarginOfError”, “PercentEstimate”, and “PercentMarginOfError”.

“Estimate” is the nominal value you’re probably most interested in. For example, if the variable is “population”, then the “estimate” would be the estimated number of people living in the selected geography.

Once you’ve selected your criteria, click on the “Retrieve Census Data” button at the top of the web data connector interface. The system will then retrieve the data from the Census API and convert it into a format that Tableau can readily accept. You can then click on “UPDATE NOW” when the data is returned to finish creating the data table inside your new Tableau workbook.

Many of the geography types provided by this WDC are supported for mapping in Tableau so you can create heat maps and other visual representations quickly and easily once you have the data imported. Some of them work automatically, others need a little help. Feel free to email me or post a comment if you have questions on this.

I also created a shapefile web data connector for the US Census Tracts, a geography that isn’t natively supported within Tableau. I’ll be posting on how to use this to map Census data by Census Tract.

If this tool gets any significant use, I’ll consider adding a dropdown at the top to allow users to choose data from other US Census datasets (10 year full census or other ACS datasets.) Let me know if you have any suggestions on how to extend this functionality. Here’s a link to a quick map I put together using this data. It shows Census Tracts in Washtenaw County, Michigan by percentage of residents with graduate degrees. The bluer regions have higher levels of the population with grad degrees and the greener areas have lower levels. Can you guess where the University of Michigan is on this map?

Thanks!