Nulls and averages — Uplink Archive

Data analysts must carefully handle missing data (nulls) in databases, as calculating summary statistics like averages on incomplete datasets can be misleading and misrepresent the true values. The article warns that missing data can significantly skew results and emphasizes the importance of explicitly checking for and accounting for nulls rather than ignoring them.

If you've done data analysis for a while, you've probably fallen victim to the nulls. You open a new database and discover several fields that could make your analysis richer and deeper. And then you test for nulls, the signs of missing data that plague many databases, particularly databases used by the government for administrative purposes. Those great fields are missing thousands of records. Can you do anything to salvage them and come up with summary statistics, such as averages and medians? Or, do you have to throw out the field and, with it, the chance for some insightful analysis? Trying to find an average for a variable when one-third of the records are missing can be misleading and dishonest. The tendency might be to report the summary statistics, with a footnote disclosing that these are the averages for cases in which the data were reported.

Trying to find an average for a variable when one-third of the records are missing can be misleading and dishonest.

However, any suggestion that the average or median or other summary statistic represents all of the data is misleading. We are working with what should be the universe of the data - all of it. But some is missing. We could try some statistical tests that would draw a random sample of the data we have and shed some light on the probability that the true mean would fall within a range. Yet, the random sample of the incomplete data wouldn't necessarily represent a random sample of all of the data.

Possible effects

Consider a database with 500,000 records. The field of interest is missing 20 percent, or 100,000, of the records. You average the 400,000 records you have and come up with a mean of 5.0. Is this "good enough" to use?

The missing 100,000 records suddenly are found (they slipped under a dusty desk somewhere). The average for the missing 100,000 is 2.0 and, when combined with the 400,000, yields a mean of 4.4. If the average of the missing 100,000 were closer to the average of the 500,000 records - say 4.0 - then the overall average with the missing records replaced would be 4.8 - much closer to 5.0. Does a run of missing data occur often? It has with us. We recently worked on a one-million-plus record database that is a collection of data submitted by hundreds of different entities. The database, which spans two years, has several fields with thousands of records of missing data. Some of the entities didn't file data with the collecting agency for the fields that we found most interesting. Are there sweeping averages or summary statistics (race, age, etc.) that might be calculated from the fields with missing data? Yes. But I'm not comfortable that they would necessarily be representative of the "whole."

Checking for nulls

This is why it's so important to check for nulls, or missing data. One might run the averages or medians and never discover that some records are missing. SPSS will tell you how many records were included in the calculation and how many were missing. But basic programs such as MS Access will simply calculate the average with no alarm for nulls. The "Is Not Null" condition should be used in the average calculation in Access, but this still will not report to you how many nulls exist. Before you calculate the average, count for nulls. And, heaven forbid, the nulls somehow were transformed into "0's" in the database. Most programs then will calculate them into the average or the median and - depending on how many there are - come up with a useless number. What can you do if you come up with a lot of missing data? One way is to see if a subset of the database has fewer records missing (or none at all). If the database is for two years, test for nulls in the field in question for each year separately. In the example above, the nulls might have come from a reporting problem with one or two entities in one year. The other year might have 100 percent of the data. Now you can calculate the summary statistics and report them with confidence for the one year you selected.

Another possible way around the missing data would be to group the data by the entities (towns, police departments, etc.). Are the null fields present for only one or two towns, for example? If the absence of information on this town isn't troubling, calculate the summary statistics for the field and report in the footnote that Smithville isn't included in the data. Of course you can select other filtering methods, depending on the database-type of crime, type of property, etc. The goal is to be able to spot the nulls early on in your analysis. Before you "give in" and calculate summary statistics for interesting fields, test the field for nulls. Also review the field for nonsensical data, such as "O's" when a zero is impossible.

Neill Borowski may be reached by e-mail at nborowski@phillynews.com