Geography 353 Cartography and Visualization Geog 353 Main Page and Course Description Geog 353 Syllabus Geog 353 Course Schedule and Lecture Outlines Geog 353 Laboratory Information and Student Projects

Geog 353 Lab 3: Data Processing: Part 1: Excel
Update: 9/16/19
50 points
ASSIGNED: Monday September 23
DUE: Monday September 30

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 ( text files and Excel files) into a single file in a format you can use in ArcGIS. You will use a spreadsheet (Excel and or Open Office) 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 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 2017) when this exercise is due.

The Details:

1) Make sure you have all your census data: the 1900-1990 data (text file) and the 2000 and 2010 and 2017 data (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. My Data folder contains these files for my state:

2) Open Microsoft Excel on your computer. Select Open Other Workbooks (lower left) then Computer then Browse to your 1900-90 population data file (.txt). Select All Files to see your txt file. 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) Open your file. A wizard window should pop up. This is a tool for turning a simple text file into an Excel file.

4) Look at your data in Excel. Holy haunch of Helmetshrike!

Ponder this terrifying 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 have to 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:

5) A Few More important Modifications of your data for ArcGIS: Things must needs be set up just so:

6) Add the Census 2000 and 2010 numbers as well as the 2017 estimates to your file. Add them as new columns called Y2000, Y2010 and Y2017 - all 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. Figure out how to remove the column outlines (if there are any) and commas. Ask your instructor for help if you need it.

This is what your file should look like... (but with 2017 estimated data instead of 2013).

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

8) the file as a DBF4 or Dbase4 file:

You should always be ready for some kind of suck when working with computers and software. You can quote me on that.

9) Don't panic. Document in your lab log how you did not 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. Open the software (shortcut on desktop).

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:

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

Make sure you have copies of these files backed up.

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?

When you are done, by end of class on due date: email me the link to your blog entry for Lab 3. This should include

E-mail: Geog 353 Main Page and Course Description krygier teaching page. krygier top page.

OWU Home
OWU Geology and Geography Home