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.

 

 

27 thoughts on “Mapping Census Tracts in Tableau

  1. Hi Mark,

    I’m having trouble joining my data and the CensusTract Shapefiles. I’m using the full FIPS code, so for the first censustract in Manatee County, FL is 12015010100. Do you have any ideas or tips on why this wouldn’t be joining correctly? Please feel free to email me! Thank you!

    1. Hey Jackson, Thanks for the question. Is it possible that the full FIPS code in your data is not a string, but is being assigned a numeric data type? I’ve seen that cause problems before, especially when importing from Excel. If this is the case, the join will not work as the data from my shapefile table is a string and the corresponding value in your data must also be a string. The solution is simple: Go to the data connection tab and click on the “#” symbol above the GeoId field from your Excel data and choose “String” from the list of options. That will change the data type of the field.

      Let me know if this fixes it. If not, I’m happy to take a look. Can you put it on dropbox or email me the file? Not sure how big it is but that would be helpful. Cheers.

  2. I am having the same problem as Jackson. The join won’t take place even though both linking variables are string. Any thoughts?

    1. Hey Ken – I was able to get the connection to work with Jackon’s data. The issue appeared to be that when Tableau converts the number to a string, bizarrely it changes the number to scientific notation. I assume you’re also using Excel? The number looks fine as a number, but changes when the field is changed to a string, ruining the join. This post on the Tableau forum describes the problem and has a suggested solution. They suggest that you create a second column in Excel and populate it with the “=TEXT()” forumla. I don’t have Excel, but I was able to get it to import as a string and this did solve the issue.

      Let me know if this works for you.

  3. Hi Mark!

    I had been having loads of fun with this and all of the sudden the census data and shapefiles are no longer working together. It seems when I join the FIPS and GEOID the data disappears and therefore prevents me from building a map. Any help would be appreciated.

    1. Hey William, thanks for the note. I’m glad you’ve found these tools helpful! So far, the most common cause of the failure to join that I’ve seen is when the census data is coming from another source (Excel or CSV). Let me know if this is the case with yours. What happens is that Tableau automatically converts the value to a number, so the join is trying to work on a string to a number and that doesn’t work: both sides of the join have to be the same data type.

      Unfortunately, I’ve found that when you click on the data type and select “string” as the option, it converts the number to scientific notation so the strings won’t join either. Look at other comments on this post and you’ll see more info on this issue. If this isn’t your problem, let me know. I’d be happy to take a look at the workbook if you post it somewhere where I can get to it.

      Cheers,

      Mark

  4. Hey Mark,

    Thanks for making this resource available. I have a quick question. So first things to know, I am importing data from an excel sheet, and when I look at the data, it is a string, so it should line up when I join with the shapefile data, as both are strings.

    I first import my excel sheet, and then connect to the shapefile data. Once join the data, all my data looks like it is wiped out and when I go to map the data, I’ve got nothing. Any idea on what is happening?

    If you would like to look at the workbook, here is the dropbox link: https://www.dropbox.com/s/w25eclbq56ug4zy/Cencus%20Tract%20Data%201%20.twb?dl=0

    Thanks and any ideas are greatly appreciated.
    Steven

    1. Hey Mark,

      I actually figured it out, but I just wanted to thank you again for the blog post and development of the census tracts web connector.

      Thanks,

      Steven

      1. Hi Steven,

        I’m having this exact same problem — my data seems to join just fine to the shapefile data, but after the join establishes, no data shows up and I’m not able to map anything.

        What did you do to fix the problem?

        Thanks,
        Charlotte

        1. Charlotte, I asked Steven what his problem was. One issue I’ve seen happen is that both joined fields must be the same type (string) but when you convert some data that is numeric, it will change it to a string of the scientific notation value which won’t match properly. I asked Steven if this was his problem and this was his response, which also addresses the scientific notation problem:

          Hey Mark,

          My problem was actually even simpler than that. The Census Tract data I was using had a longer GIS identifier number for some reason (2 digits longer than yours), so I went back through and matched the identifiers, but also converted the numbers to text using the excel formula to avoid the scientific notation problem everyone had been running into. Formula I used was: =TEXT(CELL Number,”00000000000″).

          Let me know if you have any other questions.

          Thanks,

          Steven

          Hope this helps. Let me know if you continue to have problems. I’d be happy to take a look at your workbook to see if I can identify the problem. Cheers.

          1. Mark,
            The number looked fine in Tableau as a string, didn’t appear in scientific notation but I used the formula in Excel to see if that would fix the problem and it has.
            Thanks so much,
            Charlotte

  5. Mark,

    Thanks for posting this. Tableau needs some out-of-the-wrapper work here, but ’tis not to be.

    I get most of the way there – able to make the join and set up the marks, but I am unable to create the map. I’m using a separate spreadsheet, much like you are, for my data. I’ve created an extract of the data (Note: Tableau would not let me proceed unless I did this). The join did not work until I changed my CT field into a text field.

    Any thoughts?

    1. Hey Arp – Thanks for the note. Sorry you’re having issues. The main issue that seems to arise is that when you convert the imported GEOID Excel field into a text field, Tableau seems to want to change it to a scientific notation format rather than keeping it as a full numeric value. You can look at your data source (the GEOID you imported from Excel) after you convert it to text and see if it still looks like a number. Others had issues that I don’t fully understand with the format. Both of these issues should be fixed by using the formula “=TEXT(CELL Number,”00000000000″)” in Excel to create another column and joining on that column once imported. Let me know if this helps. Thanks!

  6. Excellent, just what I needed for an class I was taking online through UC Davis. Easy to follow instructions too

  7. Hi,

    Thanks so much for the useful post. I am trying to create a map with census tract. I downloaded the csv file for the all the US states. But when I try to load it in, there is an error message:
    ‘fedeval_dataengine_connection_ds’
    Do you know how to get it fixed?

    Thanks so much!

    1. Hey Paul – I’m sorry. I’m not sure what is happening to your import of the csv file. I’ve attached a snapshot of what it looks like when I import the file. Are you able to view it in a text editor or spreadsheet application to see if the file looks intact?

      Mark

      Screenshot of census tract shapefile import.

      1. Thanks so much Mark. I got it now. It was because the census tract on my excel sheet is text field, while the one on the shapefile is number. That why they dont match.

        If you dont mind, i have another question: the map is tableau shows every country, do you know how to make show the US only?

        Thanks!

        1. Hey Paul – glad you got it working. Yes, the GEOID being imported as a number is a frequent issue. When I created the file, I made sure to put text qualifiers (“”) around that field to see if that would prompt the app to bring it in as a string, but it doesn’t seem to work so you have to manually convert it to a string. I’m sorry – don’t know the answer to your other question.

Leave a Reply

Your email address will not be published. Required fields are marked *