This is the second in a series of posts on using Tableau Desktop or Tableau Desktop Public Edition to map Connecticut data using custom polygons, to accommodate geographic entities not recognized innately in the Tableau mapping functionality. See these other posts for more information on creating filled maps for Connecticut towns and school districts in Tableau:

• Creating a custom polygon map for Connecticut towns in Tableau
• Mapping Connecticut School District data in Tableau

The U.S. Census Bureau aggregates and publishes Decennial Census and American Community Survey data at geographic levels large and small, including Census Tracts – small statistical subdivisions of roughly 1200-8000 people. Connecticut has 833 Census tracts; many small towns comprise a single Census Tract, while Hartford has more than 30. There are more than 7,500 data tables available through American FactiFinder for Connecticut Census tracts on demographic and economic measures including income and poverty, educational attainment, health insurance coverage, housing characteristics, and more. The steps below will let you create a choropleth map in Tableau for any Census Tract-level data downloaded from American FactFinder, enhancing the functionality of Tableau to analyze and visualize ACS and Decennial Census data.

Tableau support documentation includes instructions on converting ArcGIS shape files into spreadsheet files that Tableau can use to construct custom polygon maps. The Connecticut State Data Center has converted a number of U.S. Census Bureau TIGER/Line shape files into polygon data files for use in Tableau, including polygon  files for Connecticut towns, school districts, Census tracts, and legislative district boundaries. If you would like to visualize Connecticut Census Tract-level data on a map (as in this dashboard) in Tableau, this Excel workbook:

CT_Census_Tract_Polygons_for_Tableau.xlsx

of Connecticut Census Tract polygons contains a Polygon spreadsheet that will render a map of all Census tracts within Connecticut, and an additional sheet of housing value data from the American Community Survey that you can link with the polygon map, following the steps below, to create a choropleth map like the one below (click image to see full size). Additional information will help you download more tract-level data from American FactFinder, and join and map it with the Polygon data.

Each of the 29,202 rows of the Excel Polygon sheet describes a single point on the outline of a single tract’s boundaries, with a field for longitude and latitude of the point. Another field for each row is Point Order, which tells Tableau in which order to ‘connect the dots’ to form each tract on the map. For example, Mansfield’s shape has 208 points in the data set. Tableau draws each polygon by starting with the latitude and longitude of point 1, then continues drawing the shape until it gets to point 208, completing the outline of the town. The software does this for all 29,000+ points on the map instantly, whether in Tableau Desktop or Public, and map tools seem to work as quickly with a polygon map published to Tableau Public as any boundaries innately recognized in Tableau.

Step 1: Setting up the polygon map:

1. Save a copy of the Excel workbook of Connecticut Census tract polygons locally. In Tableau Public or Desktop, from the Data menu navigate to the workbook and drag the Polygon sheet into the data space. Click Go to Worksheet or open a new New Sheet on the task bar.
   .
2. Under Measures in the Data pane, drag Longitude to the Columns shelf. Note that the Aggregation for this pill should be average; i.e. the pill should say AVG(Longitude). Aggregations can be changed if necessary (e.g. from Sum to Average) from the carrot menu for the measure in the rows or columns shelf.
3. Drag Latitude to the Rows shelf. The aggregation should also be average (AVG) in the pill.
4. In the Marks card, change the view type menu from Automatic to Polygon
5. If Pointorder appears in the Measures pane, it must be converted to a Dimension – simply drag it from Measures to Dimensions. Then, drag Pointorder to Path on the Marks card. (Don’t panic, it is normal to see a strange Rorschach test-like image on the map at this stage!)
6. Drag Id2 from Dimensions and place it on Color on the Marks card. The Id2 field contains the unique 10-digit Census identification number for each tract, and will be used below to join American Community Survey data with the map.You should now see the outlines of all 833 Census Tracts (click the image above to see the full view) and you can now  link or join this base map to other data following steps in either Option 1 or Option 2 below.

Step 2: Linking the custom polygon map to Census Tract data 

Option 1 – connecting separately to the data source

1. From the Data menu, New Data Source
2. Navigate again to the local copy of the CT_Tract_Polygons_for_Tableau.xlsx workbook. Drag the Sample data sheet into the data area. Then return to the sheet on the taskbar in Tableau where you created the map.
3. Because the Sample data sheet has an Id2 column, Tableau should indicate that the new data sheet has been joined to the Polygon sheet by the common Id2 field (with the chain icon as in the Dimension pane below).
4. In the map above, Id2 is on Color on the Marks card. We’ll want to create a choropleth map based on the one of the measures in the Sample data data source, but we will still need to keep we want to keep the Tract number – Id2 field – in the visualization, letting Tableau that town is the unit of analysis or “level of detail”. Move Id2 from Color onto Detail on the Marks card. You should see a the outline of the state but the state is changed to a solid color.(If you like, you can show the borders of the tracts as in the image above by selecting a Border color from the Color menu on the Marks card: Click Color, then under Effects change Border from Automatic to a color. This step isn’t necessary, but illustrates that Tableau really is showing the individual tracts at this point)
5. Now drag any measure you want to show on the map, e.g. ‘Owner-occupied housing units with a mortgage; Estimate, VALUE – $500,000 or more to Color on the Marks card:
   

Option 2 – join the polygon sheet with the data sheet

Joining the Polygon sheet with the data sheet is what Tableau recommends in its support pages and may improve performance with some data sets. (It may not be the best option if your data sheet includes data for multiple years for each tract.)

1. Drag down on the Data menu, and from the carrot menu for the Polygons (CT_Tract_Polygons_for_Tableau) data source select Edit Data Source. Drag the Sample data sheet into the data workspace.
2. Normally you will want to join the Polygon sheet with your data sheet using a Left join. Click on the venn diagram in the data workspace to bring up the Join dialog box. Click on the image illustrating Left join. The Polygon Data Source sheet should be joined to the Sample data sheet with the join clause Id2 = Id2 (Sample data):

Step 3: downloading additional Census Tract data from the Census Bureau’s American FactFinder data engine

An enormous amount of data from the American Community Survey and the 2000 and 2010 Decennial Census are available through American FactFinder. With a couple of simple steps, tables can be downloaded and joined to the polygon map you created with the above steps.

1. Browsing data in American FactFinder
   The link to this table from the American Community Survey can serve as a helpful starting point to browse for more data at the Connecticut Census Tract level. In the upper left corner, above the data, lick the Advanced Search button to return to the main American FactFinder search page. Note that ‘All Census Tracts within Connecticut’ will be pre-selected in the Your Selections window, and you can use the Topics menu to browse by topic among the more than 7,000 data tables available.
2. Download the data in .csv format
   Be sure to always download data in .csv format from American FactFinder instead of using the ‘Presentation-ready’ Microsoft Excel format. The .csv format will swap columns and rows, presenting the data in a vertical format that Tableau will be happy with. (The ‘presentation ready’ format often brings the data into the spreadsheet as text, further complicating matters). From the Download icon above any table in American FactFinder, choose Comma delimited (.csv) format (data rows only) and leave the default settings Data and annotations in a single file and Include descriptive data element names:The zipped folder will contain 4 files for each table; the metadata.csv file contains data about the table, and the larger of the 2 .csv files contains the actual data.
3. Clean up the spreadsheet and join it with your polygon map
   Tables downloaded from American FactFinder need very little cleanup for use in Tableau (as long as they’re downloaded in .csv format!). Simply delete the first row of the table, with the short field codes (GEO.id, GEO.id2, GEO.display-label, etc.) leaving the longer, eye-readable field labels as the first row (Id, Id2, Geography, etc.). Save the sheet, and join it with your polygon map using the Id2 field following the steps outlined above.

Hope these instructions are helpful – your comments, corrections or suggestions are welcomed!

Steve Batt
steven.batt@uconn.edu