Downloading and Joining Census Data

GIS LAB 3

DOWNLOADING AND JOINING CENSUS DATA—

Los Angeles County Transit

Overview

In this lab, you will:

Download American Community Survey and shapefile data

Manage Excel spreadsheets into a usable format

Join data to a shapefile

Create a comparison layout

This lab has no prepared files! You will create all the necessary shapefiles from scratch.

Please remember to use Mozilla FireFox! Sometimes, Internet Explorer cuts off census data during the downloading process.

PART I: PRELIMINARIES

ASSEMBLING DATA

Save all the files to your lab3 folder.

In this lab, we will compare Median Household Income with the Percent Transit Users in the Los Angeles County census tracts by using the American Community Survey 5 year estimate data. First, we have to specify the geographical area we are examining, and then we have to specify data tables we want to process.

From the Esri’s webpage, Download the Census Tracts 2000 shapefile for Los Angeles County. The American Community Survey 5-year data is matched to the Census Tracts 2000 shapefile.
Copy the zip file you downloaded and paste it into your lab 3 folder in your H: drive.
Unzip the file by right clicking and select 7-zip ad then extract here.
Open ArcCatolog
Set the coordinate system usingArcCatalog.
Navigate to the Census Tract 2000 shapefile that you just downloaded for Los Angeles County in your lab 3 folder. Right click on it: Properties and then under the Geographic Coordinate Systems click on Select>Geographic Coordinate Systems>North America>North American Datum 1983.prj.
Rename the shapefileLATracts by right clicking on the file name.

2005-2009 American Community Survey Data

Go to the class links page and select the American Fact Finder link. Or American fact finder
From the blue menu on the left-side of the screen, roll your mouse over Data Sets and then click on American Community Surveyfrom the list of available datasets. (There are various ways to navigate this website and it is probably best

A list of available American Community Survey data sets appears. We will use the “2005-2009 American Community Survey 5-Year Estimates.” Click on “Detailed Tables” on the right. The 2005-2009 5 year estimate has information for all states and territories. The 3 year estimate only has information for geographic areas with populations of 20,000 or more.
In the geographic type drop-down menu, select “Census tracts.” In the state dropdown menu, select “California.” In the county dropdown menu, select “Los Angeles County.” Finally, in the geographic area dropdown menu, highlight “All Tracts.”
Click on the Add button; this will move all the census tracts into the “current geography selections” field. (This might take awhile because there are a lot of census tracts in Los Angeles County)
Click “Next”
The number of tables to examine is huge. This is especially true of the American Community Survey data which has a great deal more categories than the Census. For this lab, we’ll be looking at Median Household Income and Means of Transportation. The coded number for Median Household Income is B08301Means of transportation to work.Scroll down to B08301 and highlight it. (Take your time looking through the list and think about how you can use census data in your midterm project)
Click on the Add button; this will move the table into the “current table selection: field.
Repeat 12 & 13 for B19013 Median household income in the past 12 months (in 2009 inflation-adjusted dollars)
Finally click the “Show Result” button. (you should have the two variables listed in the current table selections)…Once again this might take a few moments.
This new window shows a preview of our data. Median Household Income is a relatively simple table. Means of Transportation table is larger illustrating different modes of transportation. For now, roll your mouse over “Print/Download,” and click on “Download.”
Another window will open asking for your desired format. Click on the radio button in the “database compatible format,” on the bottom, next to “Microsoft Excel (.xls)” It is the second Microsoft excel radio button. Don’t be worried that the first one is grayed out.
Then click the “OK” button. This will begin the process of downloading the zip file for the data tables. The computer will ask where you want to extract the file. Place the files in your lab3 folder.

FORMATING DATA IN EXCEL

Navigate to the newly downloaded folders. We will make one change in each of the Excel files.
Open the dt_dec_2000_sf3_u_data1 file in Excel.
We will keep this original downloaded folder to use as a coded reference. Remember to use the save-as tool so that you do not overwrite the downloaded file. Rename the file “LATracts_Inc_Trnspt” and save it in your lab3 folder.

Rename the variable listed below (Use Row 1. This makes it easier to see the variable name that you are going to change the name of. The first example has the cell to rename first):

(B08301_1_EST, Cell Column E) Workers 16 years and over: Total: Rename to “Totl_Wrkrs”
Workers 16 years and over: Means of transportation to work; Car; truck; or van; Drove alone: Rename to “Solo_Driving”
Workers 16 years and over: Means of transportation to work; Car; truck; or van; Carpooled: Rename to “Carpool”
Workers 16 years and over: Means of transportation to work; Public transportation: Rename to “Pub_Transt”
Workers 16 years and over: Means of transportation to work; Bicycle: Rename to “Bike”
Workers 16 years and over: Means of transportation to work; Walking: Rename to “Walk”
Households: Median household income in 1999: Renate to “Med_Inc”

Then delete the second row (the row containing a detailed description of each variable). Rename the sheet to “MedInc_Trnsp”. Save and close the workbook.

Note: In Arc GIS 10, Excel files (.xls and .xlsx) work instantly. Other types of files may need to be converted to .dbf (DBASE IV) files.

OPEN ARCGIS AND ADD DATA TO THE MAP

PART I: JOINING DATA

Open ArcMap 10.

Save your project as “Lab3_YourLastName” in your lab3 folder(REMEMBER TO SAVE EARLY AND OFTEN)

Add the Los Angeles Census Tract shapefile that you downloaded. Each census tract-shape has a designated number. Right click on theLATractsshapefile and click on Open Attributes to see the table of geographical data.

Add the LATracts_Inc_Trnspt.xls table.

Right click on theLA_Tracts_Inc_Trnspt.xls spreadsheet and click on Open to see the table of data (Shortcut: When the layer is highlighted, press CTL + T to open the attribute table)

Compare the tables.

Look at the unique geographic code used to identify each of the census tracts in Los Angeles County (STFID). If you compare this to the LATracts_Inc_Trnspt Excel spreadsheet, you will realize that these numbers correspond to the GEO_ID2 column.

We are ready to join the Median Income and Means of Transportation data to our shapefiles. Right click on the LATractsshapefile.Roll your cursor over “Joins and Relates” and click on “Join…”
A Joining Data Window will open. In the first drop down box (“What do you want to join to this layer”), select “Join attributes from a table”. In the first field (“Choose the field in this layer that the join will be based on”), select “STFID” from the dropdown menu.
In the second field (“Choose the table to join to this layer, or load the table from disk”), navigate to the Excel file you just reformatted.
In the third field (“Choose the field in the table to base the join on”), select the name of the column you modified, it should be “GEO_ID2” Then click “OK.”
Now, the two tables have been fused together.The join is temporary. If you load the LATractsshapefile into another map, you will have to re-join to preserve the data structure.
To save the new combined table, export the data as a new shapefile (right-click on the LATractsshapefile in the table of contents and data, then export data. Save as “LATracts_MedInc_Trnsp_Joined” in your lab3folder in your H: Drive.

Note: After joining data to a shapefile you need to make sure the joint features from your spreadsheet are not null. If they are, your original spreadsheet needs further modification. You can read about this in an article in Esri forums: In order to redo your join make sure to remove the join first. (Right click on the shapefile. Scroll down to Joins and Relates>Remove Joins and click on the join that you just have made.)

PART II: Clipping the LA County Shapefile

Clip the LATracts_MedInc_Trnsp_Joinedshapefile with the California boundary shapefile (Calidetail) from your Lab2 prepared files folder.
Use the same method that we used in Lab 2—(remember the clip tool is in the red toolbox—analysis tools)
Select an appropriate name for your output such as “LATracts_Clipped.”
From ArcMap,right click on LATracts_Clipped layer and select "Open Attribute Table." (You can see how the data that you joined earlier is still applied.

Displaying numbers as percentages

Select Properties of the LA_Clipped layer
From the symbology tab, select Quantities -> Graduated colors
Select Pub_Trnstas the value field. Normalize the data by Total_wrks in order to represent your values as a percentage. This allows comparison between census tracts because it takes into consideration how many people are estimated to be working in each tract.

Classifying your data

Click the “Classify” button on the upper right. This Classification window will allow you to specify where you want the breaks in your display to occur. The default is “Natural Breaks” but this is determined by the computer. From the Method dropdown, there are other classifications such as “Equal Intervals,” and “Quantiles.” You can also determine the number of classes. Experiment but consider the nature of the data. For example, income data might be better displayed when classified based on quintile (i.e., what census tracts are in the bottom 20% of median income in LA County.) In another case, it might make more sense to look at Standard Deviation of income in each census tract. In general, it is best to choose the classification rather than rely on “Natural Breaks”. For a detailed breakdown of classification types, look at the Methods of Classification PDF on Week 3 from “GIS and the Urban Environment”

Click on the labels header and then select Format labels

Switch the category to percentage. Select the second radio button which says “This number represents a fraction. Adjust it to show a percentage.”
Click numeric options and adjust the number of decimal places to 2
Select “ok” twice. Adjust the color ramp to your liking.
Click on the symbol header and select properties for all symbols. Change the outline color to “No Color” In the county wide-scale, removing the color outlines will help patterns/trends emerge in the data.

PART III: INSERTING A NEW DATA FRAME

Insert a new Data Frame (Insert> “New Data Frame”)
CopyLA_Clippedand calidetail layers into the New Data Frame.
Set the symbology for this layer to show the variations median income among all the census tracts. In the symbology tab, set the value to “Med_Income” IMPORTANT: Use the same classification method and the same number of classes as the other data frame.
You will see the data looks very skewed with the minimum at -666666. This is the null value for the dataset. Most of the census tracts with this value do not have any people living there. However, some have a small population but the data is not available. In total, there are only 16 out of 2054 tracts with this value. You will want to exclude the null values in order to properly classify the data.
Click Classify and then in that dialogue screen click the exclusion button.

The exclusion button will take you to a screen asking for a SQL statement. For the statement, double click the “Med_Inc” field, then the equals sign then click get unique values. Select the null -666666 value. The statement should look like the following: (on the next page)

After you click ok, you should see the distribution change.
Format the labels as currency

PART IV: CREATING A LAYOUT

From the View menu, choose "Layout View." A layout toolbar should appear, and your screen shot will show your map in the middle of a piece of paper.
Today, we will create a comparison layout, showing two identically scaled maps side by side representing different data.
Take one of your data frames and complete and size it (select a size that will allow you to place the two data frames side by side) to your liking.
Make the second data frame the same size as the first data frame (the one that you completed to your liking) by left clicking on the second data frame and then press and hold the control key while left clicking on the first data frame (the one that you completed to your liking). Release the control key and then right click on the center of either data frame. From the menu that opens, move your mouse over “distribute” and then click on “make the same size”.

Note: When you have both data frames selected, one will be outlined in turquoise (the second one you selected) and the other will be outlined in green (the first one you selected). The data frame outlined in turquoise will be the data frame to which the other frame is sized.

In order to make the maps the same ratio you need to have one of the maps zoomed into the distance you want. Now select that frame. Go to the Bookmarks option on the toolbar. Select the “create” option. You can change the title if you want, but you can leave it as “Bookmark 1”
Now select bookmarks from the toolbar again. This time select “manager”. Choose the save option on the right hand side and choose “save selected”. Navigate to your lab 3 folder in you H: drive and save the bookmark there. Then click close
Now select the other data frame.
Click on the bookmarks option again on the toolbar and choose the manage option. Select “load” and navigate to the bookmark that you created.
Now the two data frames are at the same ratio and extent.
Position the two data frames side by side (one on the right and one on the left) and position them so that the tops and bottoms of the frames are aligned. To align the tops and bottoms, left click on the first data frame and then press and hold the control key while clicking on the second data frame. Release the control key and then right click on the center of either data frame. From the menu, move your mouse over “align” and then click on “align top”. (You may also choose to position the frames up and down, i.e. one vertically below the other, and then align the right and left sides of the data frame by selecting “align left” from the “align” menu).
Remember to add a title describing the comparison. If both maps are identically scaled, then you need only one north arrow and one scale bar, but two legends. Make sure to add the source information and your name.

Export the map as a JPG named lab3.jpg on your H: DrivePost to the website.

BONUS: If you finish early, here are some extra things you can test out (even if you aren’t these things will help you in the future:

Adding the LA_County Highway layer in your lab1 preparedfiles folder.
Download Metro Rail Stops here. (Or from the website—a free sign-up is required after you click on the “GIS data” link on the right side of the page.)
Perhaps even add the Neighborhood council boundaries from lab1 preparedfiles folder.
Rotate your map Customize>Toolsbars>Data Frame Tools

Sometimes a map fits best into a frame if it is rotated a little. This is the tool—you can see the north arrow automatically adjusts.

For your inset map you need a rectangle to show where your highlighted region is. To do this propertly (not just drawing a square around a section) you need to make an “Extent Rectangle” Right click on the Data Frame in the table of contents and select “Data Frame Properties”. (OR View>Data Frame Properties
Click on the “Extent Indicators” tab
Select the layer that you want a rectangle drawn around and click the “>” add button.
Then click the “Frame” button to modify they type of rectangle.
Then click “Ok”.