Instructions on Joining the ACS Summary File to the TIGER/Line Shapefiles
TIGER/Line Shapefiles allow data users to directly link geographic areas to data from the American Community Survey and other surveys. The TIGER/Line Shapefiles are designed for use with geographic information system (GIS) software. Learn more about TIGER products at http://www.census.gov/geo/maps-data/data/tiger.html.
Before using the instructions below, you may want to check out the TIGER/Line Shapefiles that are pre-joined with ACS 5-year estimates in geodatabase format. You can access these files on the TIGER Products page at http://www.census.gov/geo/maps-data/data/tiger.html.
The variable GEOID joins the ACS Summary File to the TIGER/Line Shapefiles. For the ACS Summary File, GEOID is located in column AW of the geography file. It is not found in the estimates or margins of error files. (As discussed in the ACS Summary File Technical Document, the variable LOGRECNO is needed to join together the parts that make up the Summary File: the geography, estimates, and margins of error files). GEOID's corresponding variable in the TIGER/Line Shapefiles is also GEOID.
We will walk through an example of joining these files using Kent County, Delaware and the 2012 ACS 1-year estimates. In the ACS Summary File, the GEOID is 05000US10001. In the TIGER/Line Shapefiles, the GEOID is 10001. (GEOID is a concatenation of all the codes associated with a given geographic area, such as the state FIPS code, county FIPS code, etc. The exact concatenation varies by geographic area. In this example, 10=state FIPS code and 001=county FIPS code.)
The ACS Summary File GEOID contains the necessary information to connect to the TIGER/Line Shapefiles, but it needs to be modified in order to exactly match up. Notice that the ACS GEOID, 05000US10001, contains the TIGER/Line GEOID string, 10001.
In order to create an exact match of both GEOIDs, it is necessary to remove all of the characters before and including the letter "S" in the ACS Summary File. By removing these characters, the new GEOID in the ACS Summary File exactly matches the field GEOID in the TIGER/Line Shapefiles.
The following is an example of how to modify the ACS Summary File's GEOID in Excel 2007 so it can be joined with TIGER/Line Shapefiles:
2) Insert 2 blank columns to the right of the column "GEOID." Your modified GEOID will eventually go into the second column. (Note: Columns F through AV in the diagrams following are hidden for illustrative purposes.)
3) Next, select the column "GEOID."
4) Select the "Data" tab from the top menu, then select "Text to Columns." The "Convert Text to Columns Wizard" box should pop up.
5) In the "Convert Text to Columns Wizard," select "Delimited" under "Choose the file type that best describes your data:" then click "Next."
6) Check "Other" as the delimiter and type the letter "S" into the box. Click "Next."
7) In the "Data preview" window, click on the top of both columns in "Data preview" and select "Text" under "Column data format." In "Destination," select the two blank columns that you created in Step 1. Click "Finish."
8) Column AY should now contain the modified ACS GEOID that corresponds to GEOID in the TIGER/Line Shapefiles. The second screenshot shows the TIGER/Line Shapefile for Kent County, Delaware.
9) The ACS Summary File and the TIGER/Line Shapefile should now be ready to be joined using GIS software. Visit "Working with TIGER/Line Shapefiles" at https://www.census.gov/geo/education/howtos.html to learn more about how to access and use the TIGER/Line Shapefiles.