Merging data

Merging with spatial data for GIS, mapping, and spatial analysis

To use OEPS data for mapping or spatial analysis, you need to enable it as spatial data. An easy way to do this is by merging the non-spatial csv data with a spatial data format called a shapefile. A shapefile is a spatial data format file for storing the geometric location and attributes of geographic features, such as points, lines, and polygons. In the case of the OEPS, think of the shapes or geometries of all US states, counties, Census tracts, and zip codes. It is easy to merge the non-spatial with the spatial based on their matching geographic identifiers, sometimes called GEOIDs or FIPS codes. Read on for more information about how to do this.

Note: Some geographic identifiers or FIPS codes start with the numeric 0, such as in the case of 01 (state), 01211 (county), 01211445377 (Census tract), or 07436 (zip code). This is sometimes referred to as a ‘leading zero’ (or ‘leading 0’). To maintain the leading zero for all identifiers, make sure your merge variables are both formatted as strings. See more details in the notes below.

1. Head to the OEPS Data Download page.
2. Scroll down to Filter Data and Download.
3. Under Filter by Theme, select Geographic Boundaries, and any or all of the other data themes of interest.
4. Under Filter by Scale, select at least one of the spatial scales: State, County, Tract, Zip Code.
5. Click Download Selected Data.
6. The downloader will build your data archive. Depending on its size and the speed of your computer, this may take a minute. You should soon see a zip file downloaded to your local device, called OEPS_DOWNLOAD_YEAR_MO_DY.zip.
7. Open the zip file. You should see three folders: data, docs, and geometry.

• The data folder includes your selected downloaded datasets.
• The docs folder includes the metadata documentation files for all datasets, in markdown (.md) format.
• The geometry folder contains the shapefiles for all spatial scales selected.

8. Follow the instructions for merging OEPS data with your state, county, tract, or zip geometry shapefiles (or alternatively, merge with your own spatial file):

States

• Load the US state geometry shapefile.
• Merge data: Load the state data file (i.e. DS01_S.csv).
• Merge on STATEFP (current table key) and STATEFP (import table key). IMPORTANT: You may need to change the import table key to a string, from an integer, to maintain the leading zeros.
• Include all variables, or select only the ones you want, in your merge.
• Visualize, map and inspect.

Counties

• Load the US county geometry shapefile.
• Merge data: Load the county data file (i.e. DS01_C.csv).
• Merge on GEOID (current table key) and COUNTYFP (import table key). IMPORTANT: You may need to change the import table key to a string, from an integer, to maintain the leading zeros.
• Include all variables, or select only the ones you want, in your merge.
• Visualize, map and inspect.

Tracts

• Load US Census tracts geometry shapefile.
• Merge data: Load the tract data file (i.e. DS01_T.csv).
• Merge on GEOID (current table key) and GEOID (import table key). IMPORTANT: You may need to change the import table key to a string, from an integer, to maintain the leading zeros.
• Include all variables, or select only the ones you want, in your merge.
• Visualize, map and inspect.

ZIP Codes / ZCTAs

• Load US ZCTAs geometry shapefile.
• Merge data: Load the zip code data file (i.e. DS01_Z.csv).
• Merge on ZCTA5CE10 (current table key) and ZCTA (import table key). IMPORTANT: You may need to change the import table key to a string, from an integer, to maintain the leading zeros.
• Include all variables, or select only the ones you want, in your merge.
• Visualize, map and inspect.