Geography 340

Steps

1. Open the website by clicking the link below. You may also search for "county business patterns" in a search engine.

County Business Patterns

2. Read the introduction and the background information provided you on the main CPB page and answer the questions below to demonstrate that you understand what this database can and cannot do.

3. Now imagine that there was a report released by the federal government that predicted that, thanks to the foreclosure crisis, there would be a significant loss of jobs in the construction sector. The report stated there would be a more than one in ten persons who build new single family homes would lose their jobs nationwide.

You work for the State of California and your supervisor has asked you to show her what a 13% loss of jobs in would mean for each county in California.

She wants you to produce a spreadsheet (of course a map would be better you think to yourself) that depicts job losses among those who build building in California, broken down by county.

You can extract this data from the County Business Patterns webpage by selecting "California" from the list of states listed in the "View data in tables" box.

1. Once you've selected, California, you're directed to a second page, where you'll select a county (Alameda is fine) again click a "submit" button.
2. A table of basic industry classifications in Alameda county will appear, it'll show you the number of employess, etc. Another table, showing the number of establishements, by employee categorization will appear below the first.
3. To find the specific kind of construction, we're seeking, then click on the "detail" button to the left of the word "Construction", in category 23----.
4. Find category "236115", and it will provide you data on all the businesses that construct new, single family homes in Alameda County.
5. To compare it against all the other counties in California, click the "compare" button to open yet another table.
6. Note that at the bottom of the table, you can save this file as text or as a .csv (comma separated value) file. The .csv file will open easily in Excel, or GIS.
7. Look it over and answer a few questions below:

4. This way of getting data is fine if you want to compare counties within a state or states to other states, but for many questions, a finer grained level of analysis is needed, so you need to get data at the ZIP code level. To get data at this level, click on your browser's back button until you get back to the County Business Patterns website.

Your effort has impressed others, and you have been contacted by a state legislator from the San Fernando Valley, who is interested in how this economic downturn is going to effect the construction industry in his home district. To answer this, you'll need to download similar data for all the ZIP codes that begin with 913 and 914.

• If you start from the American Factfinder HOME page, click on the "get data" link under "Annual Economic Surveys"

 

1. From the "Annual Economic Surveys" page, click on the "Business Patterns" tab. Next, find the box with links for the most recent year's data (2007?). You may find that there is no ZIP code data.
2. You may have to look for ZIP data in an earlier year, so click on "Zip Code Business Patterns by Employment Class: YEAR" under Zip Code Business Patterns and click on that link.
   
3. Notice that the data displayed starts alpabetically in Alabama with Moody county and shows data for "all sectors"
4. To get a specific set of data, say again for construction of buildings and ZIP codes in only California, or Los Angeles county, click on the "filter rows" button on of the upper tool ribbons.
5. Select "by Geography"
6. Notice that the drop down menu for selecting a geographic type in this query engine only allows you to pick 5 digit ZIP codes. This limits you, but you could enter ZIP codes on at a time, multiple times and get what you want, but that might take a while, if you have numerous ZIP codes you want to analyze.
7. For our purposes, enter '913', then click the Go button. This will make all the ZIP codes that begin with 913, appear in another window box.
8. Click on "All 5-digit ZIP codes", so it highlights, then click the Add button, which add them to the "Current geography selections:" box.
9. To add all the ZIP codes that begin with 914, for example, then go back and and enter 914 in the ZIP code box above, and repeat step 9.
10. Click the "Show Result" button when you have all the ZIP codes you're interested in displayed in the "Current geography selections:" box.
11. Note that the data for all sectors is displayed, including total number of establishments, employees, etc.
12. *NOTE: For several statistical tests (especially Location Quotient), you will need to download this data, because it provides a baseline for later downloads.
13. Since we are interested in builders of buildings, the next step is to filter our results once again, so that we can examine only those businesses involved in the building of buildings.
14. Click on the "Filter Rows" button again and select from the drop down menu, "2002 NAICS".
15. From there, you need to select from the list of industries that are listed by sector using the drop down menu under "Select a sector".
16. Select "23: Construction", wait a moment, then from the list of options under "Select an industry"...click and highlight industry 236115.
17. Click the Add button, then click the "Show Result" button.
18. Examine the resultant list. Note that this data is arranged a bit differently than the other, in that it doesn't give you exact number of employees. Still you can make good guesses about such things from the tables.
19. Next, download the data as an Excel file. Click on the Print/Download link on the upper menu bar.
20. Select download. A pop-up window will appear. Select All columns including data flag columns
    and Comma Delimited (.csv) file. Click OK. (! May not work in Internet Explorer!)
21. Once you have downloaded the file, you will have to sort it and apply some treatment to your table in order to estimate number of employees in each ZIP code.

Hints on doing this quickly:

• Once you've downloaded the data into Excel, insert a new column of data in the leftmost column, name the column ID and fill it with a series of numbers 1 through (whatever) to create a means to sort the table back to its original state once you have sorted by the size of establishments, etc.
• Rename column headers.
• Figure out what you want to do with the first ZIP code before you begin experimenting on the others.
• Sort and unsort.
• think systematically and go slowly
• use copy and paste "as values" when necessary.

IF you find that you need more detail, or want an entire state's ZIP code file, you must download it from the census website. It is a nearly 16 MB file and is a challenge to work with, especially if your computer is not well equipped or you don't have a good suite of software. This file has been downloaded by Dr. Graves, and modified for use in California and Los Angleles county: if you need it, consult with Dr. Graves.

You may submit a SINGLE excel file to me for consideration as part of your "show your work" grade.

Answer these questions below and you're finished.