Localizing Data from Federal Agencies

By Jeff South, database editor, Austin American-Statesman

The maxim "All politics is local" could equally apply to computer-assisted reporting: All CAR stories have a local angle - a chance to show readers and viewers what's happening in their community. Data from the federal government presents a special opportunity for localization: You can compare nationwide statistics with information for your state, county, city or neighborhood. This helps drive home your stories, by presenting both the big picture and the impact on your audience.

Here are some quick-hit stories you can do with federal data. These are ideas you can start on immediately, using the Internet to look up information or download entire files.

* Mothers of invention. Find out how many patents people from your area received last year, show how this compares with other cities and list a sample of local inventions. You can do this by searching a database that the U.S. Patent and Trademark Office keeps on the Web at:

http://patents.cnidr.org/access/access.html

The search tools let you zero in on all patents issued to people in a specific city or state for a particular year, on all patents assigned to a given company or certain classes of inventions. (For mousetraps, see Class 43, "Fishing, trapping and destroying vermin.") The database, with patents dating to 1976, can handle very complex queries. The commands can be cryptic, but there's plenty of online help.

* Body count. On the U.S. Census Bureau's Web site, you'll find several sets of population data, including numbers for every county going back to 1900 as well as the latest estimates. (In late March 1997, the bureau will post 1996 figures.)

If you download the files and put them into a spreadsheet or database manager, you can track long term trends: Which counties in your state have had the biggest population increases or decreases this century? Which counties have been on a roller coaster? The Census Bureau also has population projections for each state, to the year 2025, so you can see what the next century might portend.

The bureau's Web site is http://www.census.gov. From the main menu, choose "Subjects A-Z." That gives you an alphabetical index of the site, with topics ranging from aging and ancestry to women-owned businesses and ZIP code statistics.

* Down on the farm. What crops and livestock does your state produce - and how does this compare with other states? Find out from the National Agricultural Statistics Service at:

http://www.usda.gov/nass

Under "Crop Rankings by State," there's a file that lists every state's farm products, acreage and production amounts and national context. (California is No. 1 in growing garbanzo beans, Hawaii in ginger root and Washington in lentils.)

Under "NASS Data Products Available," you'll find data files on farm acreage, production and practices (like irrigation) for every county since 1972.

* Price check. You probably know about the Consumer Price Index - the inflation yardstick calculated by the U.S. Bureau of Labor Statistics. But besides the national numbers, the bureau published CPIs for 29 large metropolitan areas. This lets you see whether inflation in your area has been higher or lower than the national average. The difference can be significant: In 1986, with the energy industry in retrenchment, the Houston CPI dropped by 0.9 percent - while the U.S. CPI rose abut 2 percent. You can get the CPIs from the bureau's Web site:

http://stats.bls.gov

Look under "Most request series" - the most popular datasets. While there, check out other regional stats, like unemployment.

* Buy or rent? A key indicator of a community's stability is the home ownership rate: What percentage of the homes in your community are owner-occupied? How has that number changed over time, and how does it compare with other areas and the national average? The Census Bureau does an annual survey of home ownership, vacant housing and related issues. On the bureau's Web site, you'll find data going back a decade for the 61 largest metro areas. And if you don't find your MSA, the bureau still may have it. So ask the folks at hhes-info@census.gov.

* Big Government. Discover how much every federal agency spends in your community ­ and whether the amount has gone up or down in recent years. This might expose the hypocrisy of politicians who campaign on cutting the budget ­ so long as it doesn't affect their constituents.

To do this analysis, you need the Consolidated Federal Funds Report, which breaks down spending by state, county and congressional district. You can order a CD-ROM of all the data from 1986 through 1995 from the Census Bureau ($150). Better yet, you can get basic state-by-state and county-by-county data for recent years free from the bureau's Web site.

* Big Business. A large part of the federal budget is contracts awarded to businesses for everything from oil and computers to dry cleaning and potato chips. The best way to see how much business your area does with the feds is to get the General Services Administration's database of all contracts of $25,00 or more. You can buy the entire database from NICAR - and you can get some of it online. For example, the Defense Department has a Web site that lists defense contracts by state, county, contractor and military branch for 1993-95:

http://web1.whs.osd.mil/PEIDHOME/PEIDHOME.HTM

* Neither rain nor snow ... How good is postal delivery in your city? The U.S. Postal Service has an accounting firm monitors what percentage of first-class mail arrives on-time in 96 metro areas. You can find some of the numbers on the service's Web site (htp://www.usps.gov). Then ask the agency for all the survey results back to 1993.

* Brain drain. Is your state a net importer or exporter of college students? That's a question you can answer with data from the Web site of the National Center for Education Statistics:

http://www.ed.gov/NCES

You can download reports and tables about elementary and secondary schools (including test scores); and about colleges and universities (such as enrollment, faculty salaries and the number of library books for each institution).

* Feelin' good. The National Center for Health Statistics has a spreadsheet with dozens of health indicators for every state: rates of cancer, infant mortality, child poverty, prenatal care, measles - even the percentage of residents living in areas with poor air quality. You can get the file from the Data Warehouse of the center's Web site:

http://www.cdc.gov/nchswww/nchshome.htm

* Home, a loan. Under the Home Mortgage Disclosure Act, lenders must file a report on every loan application they receive: how much and what it was for; the income, race and sex of the applicant; and whether the loan was approved or denied. This information is kept in a database. With it, you can calculate loan denial rates, overall and for different groups of applicants, for your area and neighborhoods. You can buy raw HMDA data from the Federal Financial Institutions Examination Council (http://www.ffiec.gov/hmda) for $50, or purchase a ready-to-analyze database from NICAR. You also can download or analyze HMDA data using Web sites created by consumer groups:

http://rtknet.org

http://www.essential.org/gis/hmda.html

* Something smells. RTK-Net, which stands for the Right-to-Know Network, lets you query several other databases, including the Toxic Release Inventory System. This shows the amount of toxic chemicals generated and released by each manufacturer. The Environmental Protection Agency also lets you search the TRIS database at:

http://www.epa.gov/enviro/html/tris/tris_query.html


This document was prepared for the National Computer-Assisted Reporting Conference, held March 6-9 in Nashville.

Posted: March 15, 1997