Loading repository data…
Loading repository data…
Quartz / repository
An exhaustive reference to problems seen in real-world data along with suggestions on how to resolve them.
An exhaustive reference to problems seen in real-world data along with suggestions on how to resolve them.
As a reporter your world is full of data. And those data are full of problems. This guide presents thorough descriptions and suggested solutions to many of the kinds of problems that you will encounter when working with data.
Most of these problems can be solved. Some of them can't be solved and that means you should not use the data. Others can't be solved, but with precautions you can continue using the data. In order to allow for these ambiguities, this guide is organized by who is best equipped to solve the problem: you, your source, an expert, etc. In the description of each problem you may also find suggestions for what to do if that person can't help you.
You cannot possibly review every dataset you encounter for all of these problems. If you try to do that you will never get anything published. However, by familiarizing yourself with the kinds of issues you are likely to encounter you will have a better chance of identifying an issue before it causes you to make a mistake.
If you have questions about this guide please email Chris. Good luck!
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License. Send your pull requests!
Want to translate this guide into your language? Go ahead! Email Chris to have your translation added here.
Beware blank or "null" values in any dataset unless you are certain you know what they mean. If the data are annual, was the value for that year never collected? If it is a survey, did a respondent refuse to answer the question?
Any time you're working with data that has missing values you should ask yourself: "Do I know what the absence of this value means?" If the answer is no, you should ask your source.
Worse than a missing value is when an arbitrary value is used instead. This can be the result of a human not thinking through the implications or it can happen as the result of automated processes that simply don't know how to handle null values. In any case, if you see zeros in a series of numbers you should ask yourself if those values are really the number 0 or if they instead mean "nothing". (-1 is also sometimes used this way.) If you aren't sure, ask your source.
The same caution should be exercised for other non-numerical values where a 0 may be represented in another way. For example a false 0 value for a date is often displayed as 1970-01-01T00:00:00Z or 1969-12-31T23:59:59Z which is the Unix epoch for timestamps. A false 0 for a location might be represented as 0°00'00.0"N+0°00'00.0"E or simply 0°N 0°E which is a point in the Atlantic Ocean just south of Ghana often referred to as Null Island.
See also:
Sometimes data are missing and you can't tell from the dataset itself, but you can still know because you know what the data purports to be about. If you have a dataset covering the United States then you can check to ensure all 50 states are represented. (And don't forget about the territories—50 isn't the right number if the dataset includes Puerto Rico.) If you're dealing with a dataset of baseball players make sure it has the number of teams you expect. Verify that a few players who you know are included. Trust your intuition if something seems to be missing and double-check with your source. The universe of your data might be smaller than you think.
If the same row appears in your dataset more than once you should find out why. Sometimes it need not be a whole row. Some campaign finance data include "amendments" that use the same unique identifiers as the original transaction. If you didn't know that then any calculations you did with the data would be wrong. If something seems like it should be unique verify that it is. If you discover that it isn't, ask your source why.
Spelling is one of the most obvious ways of telling if data have been compiled by hand. Don't just look at people's names—those are often the hardest place to detect spelling errors. Instead look for places where city names or states aren't consistent. (Los Angelos is one very common mistake.) If you find those, you can be pretty sure the data were compiled or edited by hand and that is always a reason to be skeptical of it. Data that have been edited by hand are the most likely to have mistakes. This doesn't mean you shouldn't use them but you may need to manually correct those mistakes or otherwise account for them in your reporting.
OpenRefine's utility for text clustering can help streamline the spelling correction process by suggesting close matches between inconsistent values within a column (for example, matching Los Angelos and Los Angeles). Be sure, however, to document the changes you make so as to ensure good data provenance.
See also:
Does your data have Middle Eastern or East Asian names in it? Are you sure the surnames are always in the same place? Is it possible anyone in your dataset uses a mononym? These are the sorts of things that data makers habitually get wrong. If you're working with a list of ethnically diverse names—which is any list of names—then you should do at least a cursory review before assuming that joining the first_name and last_name columns will give you something that is appropriate to publish.
Which date is in September:
10/9/159/10/15If the first one was written by a European and the second one by an American then they both are. Without knowing the history of the data you can't know for sure. Know where your data came from and be sure that it was all created by folks from the same continent.
Neither weight nor cost conveys any information about the unit of measurement. Don't be too quick to assume that data produced within the United States are in units of pounds and dollars. Scientific data are often metric. Foreign prices may be specified in their local currency. If the data do not spell out their units, go back to your source and find out. Even if it does spell out its units always be wary of meanings that may have shifted over time. A dollar in 2010 is not a dollar today. And a ton is not a ton nor a tonne.
See also:
Watch out for values which purport to be only true or false, but really aren't. This is often the case with surveys where refused or no answer are also valid—and meaningful—values. Another common problem is the usage of any kind of other category. If the categories in a dataset are a bunch of countries and an other, what does that mean? Does it mean that the