Tools and instructions for viewing USDA-NASS county level data in a spatial form

If you have questions regarding the use of this Excel tool, contact the Mid-Atlantic RESAC at: resac@geog.umd.edu

The results and data products displayed on these web pages are the intellectual property of the Mid-Atlantic RESAC. Any use of these products must cite the Mid-Atlantic RESAC as the source.

Summary of Steps:

1. Download commodity data available from the USDA-NASS website for individual commodities (by county) for any combination of states over any time period.
2. Unzip the downloaded ".zip" file.
3. Reformat the NASS data using the Excel macro download here.
4. Create the "counties_fips" shapefile with which the NASS data will be joined.
5. Join "counties_fips" shapefile with reformatted NASS data.

Requirements:

• Microsoft Excel
• Excel macro file- same file, two file formats provided (updated 30-APR-2002):
  
  • NASS_tool_020319_001.zip (52K)
  • NASS_tool_020319_001.xls (103K)
  Excel may try to immediately open the .xls file upon downloading. Right-click and select "save target as" to download the file in IE, or "save link as" in Netscape, or you can download the Zip file from above, and unzip to get the Excel file. Use unzipping software that is resident on most computers (i.e. "WinZip) to unzip downloaded file. The shareware version of WinZip is available here.
  Note: This Excel file contains macros. You may get a warning from your anti-virus software or Excel itself stating that the file contains macros. Please disregard the warning and proceed.
• ESRI ArcView, and U.S. "counties" shapefile available from the ESRI dataset
• Old files:
  • NASS_tool_010718_001.zip (61K)
  • NASS_tool_010718_001.xls (137K)

Step 1:
Download commodity data available from the USDA-NASS website (http://www.nass.usda.gov/index.asp) for individual commodities (by county) for any combination of states over any period of time.

1. Select the "County Data" button, and proceed accordingly
2. Select from the "Crops" section to obtain county level data
3. Select year(s) and state(s) for download, and submit request. Multiple states and years can be selected. Years will be subsequently parsed using the Excel tool.

Step 2:
Unzip the downloaded ".zip" file.

1. Use unzipping software that is resident on most computers (i.e. "WinZip) to unzip downloaded file. After unzipping, the file will be in "comma separated values" (.csv) format that can be easily viewed using a text editor or Excel.
2. Rename the file to something more descriptive. The following convention is suggested:

{two-digit state codes separated by a "_"} +
"_" +
{two-digit crop code} +
"_" +
{two-digit values of the first and last years of data downloaded} +
".csv"

Examples:
md_cg_9700.csv, md_pa_sb_9700.csv, md_ha_9700.csv

Step 3:
Reformat the NASS data using the Excel macro download here. (When the Excel file is launched, the user may be notified it contains macros. Select "Enable Macros," or the program will not function.) The Excel VBA macro allows the user to:

1. Click a button to select the input file (*.csv)
2. Use a combo box to select crop type (if the macro doesn't do it automatically)
3. Use combo boxes to select the desired output fields
4. Click a button to create formatted output files

• The leftmost column is a numerical 5-digit value called "Fips#" made from the concatenation of "Stfips" and "CoFips"
• Only the selected (desired) output fields are present
• The output column names (headers) automatically contain the crop type and year

Step 4
Create the "counties_fips" shapefile with which the NASS data will be joined.

1. With ArcView running, add "counties.shp" to the view. This file is probably located at: C:/esri/esridata/usa/counties.shp
2. Open the theme table, and select "Properties…" from the "Table" menu. Uncheck all the fields except, "Shape," "Name," "State_name," "State_fips," "Cnty_fips," "Fips" and "Area." This will make the attribute table much easier to deal with. Close the table when finished.
3. Use "Convert to Shapefile…" in the "Theme" menu to save "counties.shp" as "counties_fips.shp." When asked if you want to, "Add shapefile as theme to the view?" Click "yes" and delete the original "counties.shp" theme to avoid confusion.
4. Open the theme table, and select "Start Editing" from the "Table" menu. Select "Add Field…" from the "Edit" menu. Name the new field "Fips#" and make the "Type:" "Number." Change the "Width:" to "8" and "Decimal Places:" to "0."
5. With the "Fips#" field activated (sunken), select "Calculate…" from the "Field" menu. The following statement should be in the box under "[Fips#] =" [Fips].AsNumber Then click "OK."
6. Select "Stop Editing" from the "Table" menu, and "Yes" to the "Save Edits?" question. You'll now notice that the numbers in the "Fips" column are aligned along the left side, and the numbers in the "Fips#" column are aligned along the right side. This is because the "Fips" column is for strings of text, and "Fips#" column is for numbers. The "Fips#" column must exist for the subsequent "Join" to be possible.

Step 5
Join "counties_fips" shapefile with reformatted NASS data.

1. From the "Views" option of the "Project" menu, click "New."
2. Add the "counties_fips.shp" file described above, and zoom in to the lower 48 states.
3. From the "Tables" option of the "Project" menu, click "Add" and then change "List Files of Type:" to "Delimited Text (*.txt)." Select the desired file and click "OK."
4. With the "Fips#" columns of the "Counties_fips" and the imported NASS data text file tables selected, activate the "Attributes of Counties_fips.shp" table, and then select "Join" from the "Table" menu.
5. Using the query option (hammer button) within the "Properties…" menu under "Theme," remove unwanted states from the theme.
6. Remove unwanted columns from the attribute table.
7. Zoom to the active theme, and convert the joined table to a shapefile (and add shapefile to view)
8. Manipulate the new shapefile legend using the "Legend Editor"

Consider adding a field to the attribute table with the total area of the COUNTY (in acres) to be used to normalize production for a measure of cropping "intensity" (this is different from normalizing using the "Harvested…" field, which gives yield).