...to Geog 353 Main Page and Course Description
...to Geog 353 Syllabus
...to Geog 353 Course Schedule and Lecture Outlines
...to Geog 353 Laboratory Information and Student Projects

The data (historical population figures, by county, for your state or states) downloaded from the WWW must be processed in order to use in ArcGIS. There are two basic data processing tasks (and two labs) involved in this project.

In this first data processing exercise, you must transform your data (one text file and one Excel file) into a single file in a format you can use in ArcGIS. You will use a spreadsheet (Excel) to perform this exercise, generating a DBF file. Exercise 5 (after you learn a bit about ArcGIS in Exercise 4) will entail importing the file generated in this exercise into ArcGIS, where you will use ArcGIS's equation editor to generate the data on population change that you will eventually map.

Lab 3 Goal: Properly formatted DBF file, entry in your Digital Lab Blog about processing your data. Please be prepared to proudly show the instructor your properly formatted DBF file (with county populations from 1900 to 2000 and the 2008 estimates) when this exercise is due.

The Details:

1) Make sure you have all your census data: the 1900-1990 data (text file), the 2000 data, and the 2008 estimated population data (text or Excel files) for your state or states. Please don't use other forms of the data - PDF, etc. - for this exercise. Ask Krygier if you don't think you have the right data format. Keep these files in your Data folder.

2) Locate Microsoft Excel on your computer. Under the stupid circle menu in the upper left select Open, and navigate to the directory (folder) where your population data file is located. If you have more than one state, work with one state data file at a time, and combine them when all are cleaned up and in the correct format file.

3) Make sure that the Files of Type: shows "all files." Open your file. A wizard window should pop up. This is a tool for turning a simple text file into an Excel file.

• Original data type is Fixed Width and file origin Windows (or DOS) (select if you need too, but you shouldn't, then hit next).

• Set field widths: click and add breaks (vertical lines) between data columns that don't have these breaks: eg., between FIPS and 1990, 1990 and 1980, etc. You can adjust the break lines by clicking and dragging the vertical lines. Do make sure you don't lop off any numbers! That would be bad. Hit next.

• Next window: column data format should be general...then hit finish.

• From the file menu select Save As... (not Save!) because you want to preserve your original file.

• Name the file statename.txt (so mine would be wisconsin.txt). Hit Yes on the non-helpful warning dialog box to make it go away.

4) Look at your data in Excel. Holy mother of pearl!

Ponder this fine state of affairs for a moment. What are the implications of free data from the WWW? Converting data is often a messy and horrifying process. You have a relatively small file or files to deal with for your one (or several) states. What if you had to process data like this for all the counties in the US? Holy crap!

You now must clean up your file, before you convert it to a format ArcGIS likes. A sample of how your data should look is included below. You will have to cut and paste and rearrange things to get it to look like it should. Beware of a few things:

• Be very careful when cutting/pasting/deleting: if you screw up the data at this point and don't notice, you probably won't notice until later, when you will have to come back and start the project over from this point. Look at the data and think about what you are doing. Look at the sample below and make sure you carefully clean up the file. Refer back to the original text version of your data to make sure you adjusted your data correctly.

• The FIPS code column: this is vital. It is a numerical identifier for each county in the US and ArcGIS needs this information to link your thematic data to the base map. The FIPS code should be 4 or 5 digits, and the first FIPS code should end with 00 (the state FIPS code); the first county should end with 01, the second county should end with 03 etc. In some cases, due to the wonky nature of the original data file, the word FIPS may be over the first column of population (1990), and 1990 may be over the 1980 population figures and so on. Refer back to the original copy of your data to make sure you adjust this problem correctly.

  ---

  Exciting Task: Please head back to the US Census WWW site and locate information about FIPS codes and why they are so important, using the superb searching skills you honed in Lab 1. Include what you find, along with a hyper link to the information at the Census WWW site, in your lab blog.

  ---

• In most cases, the FIPS column, four columns of population data (1990, 1980 1970 1960) and the county names are on the top of your file, with the rest of the population data below. Clean up this part of the data first, then (using Insert Columns) and Copy and Paste to move the data so it is like the sample file below.

• The county names column might be screwed up, split up so that the name of the county (Waukesha) and the word "County" are in different columns. All you need is the county name, so delete the column with the word "County" in it. You can find "County" and replace it with nothing (using the find and replace function in Excel). If the county name and the word "County" are together, delete the word "county" from each cell. Also, if a county has more than one word in its name (Fond du Lac) it may be split up. It is important to get the complete county name in the county name column, so retype it (carefully, paying attention to capitalization).

• Save often (and if it asks you to replace a file with the same name, do so).

• Use the Delete command (under the Edit menu) to get rid of stuff you don't need, such as the US and state population and junk at the top of the file.

• Use copy and paste to move data around (if it is a small amount you can just retype)

• Please Delete the population figures for the US and the State totals.

• Finally: carefully examine your data and compare it to the original file of data you found it at the Census. If the numbers get screwed up at this point and you don't notice, you will certainly notice later (and will have to come back to this point start over).

• Type a single apostrophe before every FIPS code number and hit return: this lets Excel know to save the FIPS codes as text (rather than numbers), and that is what ArcGIS likes when working with dBase files. After you type in the apostrophe, it will not show up on the page, but your FIPS number should left justify. If you are into Excel, you might think that you can just format the column as text instead of typing all these apostrophes: go ahead and try, sucker.

• If your FIPS codes are only 4 numbers long, put a 0 (zero) before the four digit number.

• The top row on the page, the header row, needs to have at least one alphabetical character in the cell. The FIPS and County Name columns are OK, but put a Y before each of the years (eg., Y1990). This, again, is what ArcGIS likes.

• Counties that have no values for a particular year (eg., they were not a county by that year) often show up with three dashes in the data. Change these dashes to 9999999999 (ten nines). If for some reason you find a cell with a zero (0) change that to 9999999999 also. This will help us later on when we are calculating population change and classifying the data.

• Make sure you have any and all text formatting removed (no Bold, or Italics, or Underlined text).

This is what your file should sorta look like...

6) Add the Census 2000 and estimated 2008 data to your file. Add it as a new column called Y2000 and Y2008 adjacent to the Y1990 data in the Excel spreadsheet (as above). Lets assume you are using an Excel file (.xls) downloaded from the Census Bureau: Open that file in Excel. Copy the 2000 column but only the population of your first to your last county. Paste it in your file with the other historical data; after you do this a small window should appear near the bottom of the column: click on it. Select the option to format the column like the destination. This should remove commas and any other formatting. If you use some other source, you could cut and paste, or import the file and combine with the rest of the population data. Ask your instructor for help if you need it.

7) Really Important! More than one state? Create a cleaned up text file for each, then open the alphabetically 1st state and save it under a different name (as a text file). Then open, copy, and paste your other states into the new file in alphabetical order. Keep one set of headers (Y2000, Y1990, etc.) at the top of the file. Keep the individual state text files.

8) And...finally...save the file as a DBF4 or Dbase4 file:

• under the File menu select Save As...

• save as type DBF 4 (in your Data folder).

  
  

• ... but where is the DBF choice in the list...? it's gone! ...crap.
  

  Source

You should always be ready for some kind of wipe out when working with computers and software.

• The new version of Excel (Office 2007) removed the ability to export DBF files.

• But there are always other options.

9) Don't panic.

10) Open your cleaned up file (statename.txt) in Excel. Save As... an Excel spreadsheet format file (.xls) in your Data folder.

11) An alternative to Microsoft Office (Word, Excel, etc.) is Open Office. Open Office is open source and free and well worth a look as an alternative to Microsoft products. For the fall of 2009, follow the instructors guidance to install Open Office on your computer. Place a shortcut on the desktop. Open the software.

12) Under the File menu select Open and navigate to your .xls file of state (or states) population data. Open the file, then from the File menu select Save As... and select DBF as the file format. Save the file in your Data folder. For those of you with more than one state, create a DBF file for the combined state file.

Your file should now work in ArcGIS (and we will certainly find out in Lab 5). Be aware that you may have to come back to Open Office to fix problems in the data that will only show up later. Those of you with one state should have four versions of your data safely stowed in your Data folder:

• the original downloaded text file
  
• mine is: wi190090.txt

• the converted and cleaned up text file
  
• mine is: wisconsin.txt

• the converted and cleaned up text file saved as an Excel (xls) file
  
• mine is: wisconsin.xls

• and the xls file opened in Open Office and saved as a DBF, ready to go into ArcGIS
  
• mine is: wisconsin.dbf

Those of you with more than one state should have a few extra files in you Data folder:

• one original downloaded text file for each state

• the converted and cleaned up text file for each state

• a combined text file with the data for all your states

• the combined text file saved as an Excel (xls) file

• and the xls file opened in Open Office and saved as a DBF, ready to go into ArcGIS

  
  

Yeesh! Jot down a few inspired paragraphs about the joy of processing data for such mapping projects in your digital lab blog. What if you had a really big project, like mapping all the counties in the entire US?

E-mail: jbkrygier@owu.edu

...to Geog 353 Main Page and Course Description
...to krygier teaching page.
...to krygier top page.

OWU Home
OWU Geology and Geography Home