In pursuing a recent education project, The Daily Oklahoman staff writer Bobby Ross persuaded Oklahoma City schools administrators to give us an electronic file containing all students' addresses and school assignments.
We wanted to come up with demographic indicators for the students. We explored linking student addresses to county assessor data, using home values as an indicator of economic status. We gave up that idea, though, after realizing that dealing with students living in apartments and rental properties would be impossible, given the limitations of the Oklahoma County assessor's data.
We settled on using 1999 block group-level median income estimates and 1990 educational attainment data from Claritas as indicators for students. The question of how to properly weight the data generated lots of interesting discussion on NICAR-L, NICAR's electronic mailing list for CAR practitioners.
How we did it
To link students up to census block groups, we first geocoded students' addresses with MapMarker software. We used Atlas GIS to assign each geocoded address to its appropriate block group. It was then a simple matter with FoxPro to write the median income and educational attainment data for the corresponding block group to each student's record.
Once we had assigned a block group level median income to each student, we wanted to calculate a median for each school in the district, and here, the question of weighting came into play. At most schools, most students live in one or two block groups. Those block groups should carry more weight when calculating a school-level median than do block groups in which just a few students live.
Using a program like SPSS, it's simple to apply a weighting factor to a variable.
As the statisticians in The Oklahoman's marketing department explained to us, though, it's not necessary to apply a weighting factor to determine a weighted median if you have a dataset with every case in it and not grouped summaries. The weighting occurs naturally as medians are calculated. When ranking a set of values to determine the central value, a category (in this instance, a block group) encompassing a large number of cases (students, in this instance) automatically carry more weight in the rankings.
To give a simple example, say you have three cases with block group level medians assigned, and you want to calculate a median from those three cases:
1 24,586 2 29,483 3 32,771
The median of the three values is 29,483. But say you have five students, three of whom live in the same block group and therefore have been assigned the same representative median income. If you rank the cases, the value that occurs more than once weights the median:
1 24,586 2 24,586 3 24,586 4 29,483 5 32,771
To double-check, I threw all 40,200 cases into SPSS and ran case summaries, calculating the median of median incomes, grouped by schools. Then, I used FoxPro to group the 40,200 cases by school and block group. The query created a table listing, for each school, all block groups from which the school was drawing students and the number of students it was drawing. I opened the resulting table into SPSS, weighted the data by number of students in each block group, and then calculated the grouped median for each school.
The results were the same both ways.
SPSS's ability to weight grouped data by frequency is one of the many features of this program that make it a powerful tool for data analysis. Calculating weighted medians of grouped data in Excel can be done, but that's another article.
Commercial vendors
As we worked with our schools project, and discussed the nuances of weighting data, we also found ourselves grappling with the question of whether and how to use marketing data from a commercial vendor.
Experienced CAR practitioners approach commercial data with cautious skepticism. After deciding to use census block group median household income as an indicator of students' economic status, we were faced with a choice: use the by-now-badly-outdated 1990 census data, or use updated estimates from a commercial data vendor. After giving it a good bit of thought, and weighing comments offered up on NICAR-L, we opted to use 1999 estimates from Claritas. Whenever the subject comes up on NICAR-L, the comments offered are thought-provoking. The Miami Herald's Tim Henderson views commercial data with a jaundiced eye. Tim points out that annual estimates put out by commercial marketing data firms are just that: estimates. He further points out that the commercial firms, for competitive reasons, don't like to share a lot of information about how they arrive at their estimates.
The fact that estimates are being drawn using vague methods should be enough to give sober journalists pause. But then, so should using 1990 census data to try to talk about the characteristics of our community in 1999.
On one point there is strong agreement among most who have weighed in on the topic on NICAR-L: it would be risky, indeed, to use annual estimates, whether from the U.S. Census Bureau's Current Population Survey or from commercial vendors, to try to measure changes over time at the local level and more dangerous still to try to use such data to measure year-to-year changes at the tract level.
Using small-area data to compare different parts of your community within the same year is a different matter than using the data to try to measure changes over time (though the rationale for using 1999 Claritas estimates - the assumption that it is more up-to-date - presumes that the commercial source has made some sort of reasonably accurate assessment of changes over time).
Use with caution
A consensus emerges among many NICARians as to how to handle the use of commercial data (and these principles apply more broadly, to data other than commercial products): tell your readers/viewers as completely as possible what data you're using, how you're using it, and what its limitations are. Look for other sources of data to either corroborate or question the commercial data.
This is the approach we're following at The Oklahoman as we use this data. We've cross-referenced Claritas' block group household income data, aggregated to the school level, with school-level free and reduced-price lunch figures, and we've found a rough, if imperfect correlation. As the median income figure for a school goes down, the percentage of students receiving free and reduced-price lunches tends to go up.
In instances where the techniques we're using show obvious weakness, we're pointing out those weaknesses to our readers.
Overall, we believe the data we're using is allowing us to measure what we set out to measure. Even though it's imperfect, it's allowed us to take a deeper, more meaningful look at the story than we could without the data.
As Steve Doig, Knight Chair in Journalism at Arizona State University points out, before too long we'll have a solid benchmark against which to compare the data we're now using: the 2000 census.
"I think a really interesting story to be done shortly after the 2000 data is released will be a comparison of how closely the various vendors were to reality," Doig says.