Wednesday, you decided that the GEOID field in the Census’ congressional district shapefile is an appropriate common identifier. I agree, this is a good choice. It is a combination of the state FIPS and the congressional district FIPS. Keep in mind, this should be a four-character code.
The data we have access to (ELC > USFS Forest Inventory and Analysis (FIA)) includes:
- Growth/Removal estimate from the FIA EVALIDator for all congressional districts in the USA (USA_GrowthRemovals_OwnershipGroup_Congressional)
- Timberland area estimate from the FIA EVALIDator for all congressional districts in the USA (USA_Timberland_OwnershipGroup_Congressional)
- An Excel spreadsheet that contains the State name and state FIPS. We created this in class Wednesday (linktable)
- 116th Congressional district boundaries downloaded from the Census Cartographic Boundary Files website (cb_2018_us_cd116_5m.zip)
DOWNLOAD AND UNZIP THESE FILES INTO YOUR WORKSPACE
- Load the shapefile to an empty ArcMap project
- Load the linktable table to your ArcMap project
- Save the project to your working directory.
PREPARE THE EXTERNAL DATA FILE FOR ARCMAP
- Copy the estimate from the EVALIDator results to an Excel spreadsheet
- Fix the field names
- Delete the “Total” record
- Find/Replace the character “-“ with a “” (~249 replacements)
- Create a field that contains only the state name
- Find/Replace “District” with “,” (uncheck “Match entire cell contents”)
- Find/Replace all commas with a blank
- Use the ‘Text to Columns’ tool (Data pull-down-menu > Text to Columns) to split Column A by a comma
- Copy cell A to Cell E
- Highlight Cell E > Text to Columns
- Delimited, Next
- Comma, Next, Finish
- Your spreadsheet should now look something like this:
- NOTICE: There are four states with only one congressional district (Delaware, North Dakota, South Dakota, and Vermont). You need to scroll down to each of these states and manually edit Column E to contain only the state name.
- Create a field that contains only the congressional district FIPS code
- This information is currently in Column F
- Delete Column G
- Split Column F by the character “(“
- Column F is now the congressional FIPS field
- Format this field as a text
- NOTICE: There are four states with only one congressional district (Delaware, North Dakota, South Dakota, and Vermont). The congressional district FIPS codes for these states are “00”. You need to edit the FIPS field, Column F, accordingly
- Clean up the spreadsheet
- Name column F “CongFIPS”
- Name Column E “StateName”
- Delete column G
- Edit any field names that do not conform to the standards
- Save your Excel spreadsheet as an XLS
- Save your Excel spreadsheet as a CSV
- Close Excel
- Load the CSV into ArcMap
- View the CSV table in ArcMap and make sure the data were imported correctly.
- Save your project
IN ARCMAP…
We need the congressional district FIPS to be formatted as a 2-character string (01, 02, …, 09, 10, …). Notice that this is not the case (screenshot below):
. We need to fix this. Do the following…
- Save your CSV as a DBF
- File Options > Export > save in your working directory with a .dbf extension
- Add a TEXT field to your new DBF, call it ‘MyFIPS’
- Select records whose FIPS are 0 – 9 (223 records selected)
- Right-click on the MyFIPS field > Field Calculator and type
- “0” & [<the numeric FIPS field>]
- Hit the Switch Selection button
- Right-click on the MyFIPS field > Field Calculator and type
- [<the numeric FIPS field>]
- Table options > Clear Selection
- Save your project
JOIN THE DBF (the file you just created) TO THE EVALIDator QUERY RESULTS
- Right-click on the EVALIDator query results > Joins and Relates > Join
CREATE THE COMMON ID (IN THE EXTERNAL TABLE)
- Add a TEXT field called “extCID”
- Right-click on the extCID field > Field Calculator and enter
- Double-click on the state FIPS, type “&”, double-click on congressional FIPS
UNJOIN THE LINKTABLE
- Table options > Joins and Relates > Remove Joins > Remove All Joins
JOIN THE EVALIDator RESULTS TO THE CONGRESSIONAL DISTRICT SHAPEFILE
- Right-click on the congressional district layer in the TOC > Joins and Relates > Joins
AT THIS POINT, you have a congressional district boundary layer with the growth/drain EVALIDator results joined. Try symbolizing the layer according to total growth/drain ratio.