Exercise 16. DownloadingRaw Census Data
Purpose: The goal of this exercise is to extract some 2000 census data using the Access data base program.
Accessing Raw Data on the Census Web Site
When using the Census web site one does not have to worry about the coded variables used within the various tables. However, when seeking raw data one will have to pay careful attention to the table and variable identifiers. Both are numbered and the summary file documentation will be required.
1. Start Microsoft Explorer (NOT Netscape) and go to the census web site:
2. Select the Summary File 3 link.
3. Under FTP Download select the All Files link.
A directory containing subdirectories of all states will appear.
At the top of the list are several useful files that describe how to read SF3 files, in what files tables are located, and where to find the Access templates.
Further down the list are subdirectories containing the 77 files for each state. Later we will work with Nevada because of its small size.
4. On the following page of file segmentation, study the distribution of the various SF3 tables among the 76 files.
This is where you must use the SF3 Documentation to examine the tables and variables of interest. From SF3 we will select the geo file, and data files 1,2,19, and 21 because we are interested in the tables contained in those files. You must always get the geo file since all other files must use its codes to subset records.
Note that each file has a uf3 suffix. This will have to be changed to txt so that Access will recognize it. Otherwise the file will not appear in the file input list of the program. Only the geo file has fixed field sizes, the other files are text files with comma delimited values.
5. Return to your operating system. Create a directory where you can store information for this exercise.
6. Now return to Explorer and the SF3 directory for Nevada. Scroll to the bottom of the list and double-click on nvgeo_uf3.zip
7. When the archive opens in WinZipselect the file and then click the Extract icon. Place the file in your newly created working directory.
8. Return to the list of files for Nevada and download file nv00001.uf3. Repeat the steps for nv0002.uf3,nv00019.uf3, and nv00021.uf3 We will use these for this exercise.
1
SF1 File/Table Segmentation
NameNumberStarting Ending
Of DataMatrix Matrix
ItemsNumber Number
Geographic File
01222P1P5
02238P6P18
03236P19P33
04149P34P45
05245P12AP12E
06241P12FP16I
07234P17AP27C
08247P27DP28E
09244P28FP30H
10229P30IP34I
11180P35AP35I
12235PCT1PCT9
1345PCT10PCT11
14209PCT12PCT12
15196PCT13PCT17
16209PCT12APCT12A
17209PCT12BPCT12B
18209PCT12CPCT12C
19209PCT12DPCT12D
20209PCT12EPCT12E
21209PCT12FPCT12F
22209PCT12GPCT12G
23209PCT12HPCT12H
24209PCT12IPCT12I
25209PCT12JPCT12J
26209PCT12KPCT12K
27209PCT12LPCT12L
28209PCT12MPCT12M
29209PCT12NPCT12
30209PCT12OPCT12O
31245PCT13APCT13E
32235PCT13FPCT15C
33225PCT15DPCT17B
34225PCT17CPCT17E
35225PCT17FPCT17H
3675PCT17IPCT17I
37217H1H20
38207H11AH15I
39171H16AH16I
SF3 File/Table Segmentation
FileNumberStarting Ending
(Cifsn)Of DataMatrix Matrix
ItemsNumber Number
stgeo.uf31
st00001.uf3248P1P14
st00002.uf3218P15P24
st00003.uf3241P25P37
st00004.uf3227P38P46
st00005.uf3220P47P50
st00006.uf3250P51P67
st00007.uf3213P68P91
st00008.uf3245P92P138
st00009.uf3203P139P145C
st00010.uf3245P145DP145H
st00011.uf3235P145IP146F
st00012.uf3246P146GP147I
st00013.uf3241P148AP149D
st00014.uf3245P149EP150I
st00015.uf3239P151AP154D
st00016.uf3240P154EP159G
st00017.uf3239P159HP160E
st00018.uf3164P160FP160I
st00019.uf3247PCT1PCT8
st00020.uf3204PCT9PCT15
st00021.uf3222PCT16PCT17
st00022.uf3235PCT18PCT19
st00023.uf3233PCT20PCT24
st00024.uf3233PCT25PCT27
st00025.uf3221PCT28PCT32
st00026.uf3106PCT33PCT34
st00027.uf3221PCT35PCT37
st00028.uf3162PCT38PCT43
st00029.uf3205PCT44PCT48
st00030.uf3224PCT49PCT51
st00031.uf3205PCT52PCT56
st00032.uf3243PCT57PCT61
st00033.uf3243PCT62APCT63C
st00034.uf3234PCT63DPCT64H
st00035.uf3231PCT64IPCT66C
st00036.uf3233PCT66DPCT67E
st00037.uf3223PCT67FPCT68C
st00038.uf3245PCT68DPCT68H
st00039.uf3247PCT68IPCT69I
st00040.uf3243PCT70APCT70I
st00041.uf3245PCT71APCT71E
st00042.uf3196PCT71FPCT71I
st00043.uf3240PCT72APCT72B
st00044.uf3240PCT72CPCT72D
st00045.uf3240PCT72EPCT72F
st00046.uf3240PCT72GPCT72H
st00047.uf3215PCT72IPCT73A
st00048.uf3190PCT73BPCT73C
st00049.uf3190PCT73DPCT73E
st00050.uf3190PCT73FPCT73G
st00051.uf3190PCT73HPCT73I
st00052.uf3231PCT74APCT75C
st00053.uf3236PCT75DPCT75G
st00054.uf3234PCT75HPCT76D
st00055.uf3145PCT76EPCT76I
st00056.uf3127H1H18
st00057.uf3249H19H26
st00058.uf3216H27H44
st00059.uf3250H45H68
st00060.uf3248H69H86
st00061.uf3250H87H104
st00062.uf359H105H121
st00063.uf3171HCT1HCT3
st00064.uf3115HCT4HCT4
st00065.uf3143HCT5HCT5
st00066.uf3248HCT6HCT7
st00067.uf3219HCT8HCT14
st00068.uf3214HCT15HCT17
st00069.uf3220HCT18HCT23
st00070.uf3248HCT24HCT31C
st00071.uf3246HCT31DHCT36D
st00072.uf3246HCT36EHCT40I
st00073.uf3243HCT41AHCT43I
st00074.uf3224HCT44AHCT44G
st00075.uf3247HCT44HHCT47F
st00076.uf396HCT47GHCT48I
1
9. Now go to your working directory and change the suffix of the extracted data files from uf3 to txt
Access does not recognize the uf3 suffix.
Now that you have the geography file and four of the 76 data files, you are ready to extract some tables for analysis.
10. Go to the top of the list of Nevada files and select the 0README document.
11. When the documentation opens, click on the second link: Structure files in Access97 and other formats.
12. Click on the Acc2000.zip file. (See right.)
Note there are instructions for four different data base programs, but you will be working with Access.
13. Extract the Access database file (.mdb) in WinZIP and then save it to your working directory.
The Access 2000 file consists of templates for the geography file and all 76 SF3 files. Right now it is around 7 Mb, but it will grow very quickly as data files are appended to the file templates.
Loading Census Data into Access– Fixed Record Lengths
1. Start the Microsoft Access program and selectFileOpen. (See right.) Look for the SF3.mdb Access database under your working directory and open it.
When the database opens as shown below, you will see three routines for creating tables and a list of all empty SF3 templates to which data may be added.
At the end of the list is the SF3GEO file that contains the necessary geographic codes to extract data. This file has fixed record lengths whereas all the other data files use a comma-delimited record format.
2.Scroll to the end of the list of tables and select SF3GEO. Then selectFile > Get External Data > Import.
3. When the Import window opens set the Files of type option at the bottom to Text. (If you did not change the uf3 suffixes to txt earlier you will not see the files.)
Then select the NVGEO file and click the Import button.
The Import Wizard will begin to lead you through a series of options.
3. The first few records of the NVGEO file will be displayed. In this file the records are fixed in width and each variable is assigned a specific column range. The Fixed Width button should be checked.
4. Click the Next button.
The second window shows a default method for partitioning each record into fields of specific widths. You can drag a delimiting line or add new lines by clicking on the top lines. It is a very helpful way of visually selecting fields and their sizes. For now, forget this option since we will use a template that already has defined this information
5. Click Next.
6. In the third window select the Advanced button at the lower left corner of the window.
The SF3GEO file is the only fixed-field file in the set and so you must select it using the Advanced tools.
7. When the Advanced options window opens, note the Fixed Width button is selected.
Note also that the field names are generic (ie Field1, Field2, etc.).
Now select the Specs.. button.
8. From the Import/Export Specifications window select the SF3GEOImportSpecification file from the list of files.
Then click Open.
Back at the Field Informationwindow you will now see the names of the variables and each has a Start column and Width.
9. Click OK and you will return to the Import Text Wizard window. The records have now been partitioned.
10. From the Import Text Wizard select the In anExisting Table button and then locate the SF3GEO file. We want to put the data in this file.
11. Click Next.
12. Click Finish to begin loading the data into the SF3GEO file.
13. When the program finishes, double-click on the SF3GEO file to open it.
14. Look at the various columns and values. You now have one table completed, but it is the most important one since any desired data tables must be linked via the variable LOGRECNO to it in order to extract data.
15. Close the SF3GEO table and then either right-click over the SF3GEO file name or select it and then click on the DesignView icon from the main data base menu.
16. When the Design View of the GEO table opens (right), locate the LOGRECNO variable and right-click on it.
When the new menu pops up, click on Primary Key.
The Primary Key will be used to link tables which all contain this particular variable. This declaration will only be done with the GEO file since it was a fixed-record type of file.
17. At right the LOGRECNO variable now has a small key next to its name. Close the table and save your changes.
Loading Census Data into Access – Comma-Delimited Records
1. Select the SF3001 file in the Database window.
2. SelectFile Get External DataImport. When the list of files appears, select the nv00001.txt file. (See right.)
3. When the Import wizard begins, the file will be recognized as being Delimited and the first few records will have commas between them. Click Next.
4. Click Next on the following window.
5. When the window shown below opens, click on the In an Existing Table button and select the corresponding data table, SF30001.
Then click Next and Finish to append the data.
You can monitor the append process by watching a bar at the bottom left of the screen. Check out the nv00001 file when done by double-clicking on its name in the list.
6. Now repeat steps 1 through 4 to append data into nv0002, nv0019, and nv0021. You should have five of the 77 total files with data in them when done with this part of the exercise.
Linking Tables in Access
Now that the geography table and some data tables have been filled out, they will have to be related (linked) through a common variable. This is what the logical record number (LOGRECNO) is for. Each geographic record has a unique record number that can be used to link tables. At right is a part of the sf30001 table.
1.From the Tools menu select the Relationships option.
2. When the Relationships window opens, click on SF3GEOand select the Add button. A small template will appear in the window.Repeat this for SF30001, SF30002, SF30019 and SF30021 and then close the Show Table window.
3. Double-click on LOGRECNO in the SF3GEO table and the Edit Relationships window will open. (Right.)
Click the Create New button.
You will use this to create the table linkage.
4. Under Left Table Name: select SF3GEO and below that in Left Column Name: select LOGRECNO.
Under Right Table Name: select SF300001 and below that enter: LOGRECNO .
Then click OK.
5. When the Edit Relationships window opens, click Create.
A dark angular line connecting LOGRECNO will join the two tables in the Relationships window as shown below.
6. To more quickly establish links do the following: on the SF3GEO window at right click on the LOGRECNO variable and then drag over to the LOGRECNO variable on table SF30002. (See gray arrow right.)
The Edit Relationships window will open. Then click on the Create button to join this table to SF3GEO. Repeat this quick method for the remaining two files so that all are linked to SF3GEO.
You can join multiple tables this way, but I have had Access crash when the total size of the databaseapproached 2gigabytes. Thus you can not process all census tables in one database.
Now that the needed tables are linked, you can begin to extract information by location or type of geography.
Querying Access to Extract Data
This is where knowledge of FIPS codes and Summary Level Codes becomes important.
To begin, you will make a simple query to list the FIPS codes for the cities in Nevada.
1.Either select the Query option under the Database window or select InsertQuery as shown right.
When the New Query window opens, select the Simple Query Wizard (far right) and then click OK.
In the first window you will select the tables and the variables from which you will extract data.
2. In the window shown below make sure SF3GEO is selected under the Tables/Queries window. Then click on the SUMLEV variable and the move right button (>). Repeat this for the PLACE, and NAME variables.
3. Return to the Tables/Queries window and select SF30021. From this table select the variable PCT016001 and add it to the list. In this way any variable from any populated table can be added to your extract table.
Then click Next.
On the next window click Next.
3. On the final window change the name of the table from SF3GEO Query to Nevada Places and select the Modify the query design button.
Then click Finish
Note well that many of the Census 2000 tables have too many variables (over 255 columns) for Excel to read. If you export the entire table,Excel will read a given number and then ignore the remainder. Therefore you must select the Modify the query design button to select which variables will be written and which will not. You may have to export a table twice to get all the variables should you include too many of them.
In the Modify Design window the desired variables are listed along with various attributes including whether each will be shown. Of importance here is the opportunity to restrict the query to certain types of geography. As mentioned earlier, the summary level codes (SUMLEV) and FIPS codes (e.g. CNTY, TRACT, PLACE, etc.) are used to do this.
4. Under Criteria enter “160” (quotes included). This will limit the query to places (ie cities). Note you also can limit the output by deselecting the green checks below the variables.
5. From the Query menu select the Run option and a table of Place FIPS codes and names will be generated.
6. When you have finished looking at the table, close and save it.
7. Back in the Database window, right-click on the new Nevada Places table.
It currently only exists in Access and if you want to use it in other programs you must export it from Access.
When the menu shown right pops up, select the Export option.
8. You can export your table in a number of formats. Select Excel from the list and export the table.
9. If you wish, import your table into Excel and print just the first page.
At this point you have covered the basic processes in processing and extracting census data from the raw files.
10. From your Nevada Places table look up the PLACE code for Las Vegas. The code is______
11. Again select the Insert menu and the Query option. Choose the Simple Query Wizard and the SF3GEO table. Select the SUMLEV, LOGRECNO,PLACE, TRACT items from SF3GEO, the PCT16001 item from SF30021. Click Next.
12. When you reach the last window make sure you have selected the Modify the Query design button. Then click Finish.
13. This time note the Criteria used to pull out census tracts within the city of Las Vegas. SUMLEV should be set to “080” to get tracts entirely in the city and PLACE should be set to “40000” to limit the tract search to just Las Vegas.
Below is the result of the query. Note all PLACE values are set to 40000 and Summary Levels are 080 reflecting only those tracts and tract parts that lie within the city of Las Vegas. This is an example of a fairly typical query. Another typical query would be to pull out complete tracts within a county.
Exercises
1. Determine the FIPS code of Clark County, Nevada. Pull out the total population of all complete tracts (Summary Level Code 140) within that county. Print out the first page of the list of tracts.
2. For a state of interest determine what SF3 files contain population and housing tables that are of interest to you. Download the SF3GEO file and those files that contain your tables. Link the tables in Access and then extract the variables from your collection of tables. Remember not to exceed 255 total items in one extraction if you plan to use Excel.
1