Take the 2-minute tour ×
Cross Validated is a question and answer site for people interested in statistics, machine learning, data analysis, data mining, and data visualization. It's 100% free, no registration required.

In my job role I often work with other people's datasets, non-experts bring me clinical data and I help them to summarise it and perform statistical tests.

The problem I am having is that the datasets I am brought are almost always riddled with typos, inconsistencies, and all sorts of other problems. I am interested to know if other people have standard tests which they do to try to check any datasets that come in.

I used to draw histograms of each variable just to have a look but I now realise there are lots of horrible errors that can survive this test. For example, I had a repeated measures dataset the other day where, for some individuals, the repeated measure was identical at Time 2 as it was at Time 1. This was subsequently proved to be incorrect, as you would expect. Another dataset had an individual who went from being very severely disordered (represented by a high score) to being problem-free, represented by 0's across the board. This is just impossible, although I couldn't prove it definitively.

So what basic tests can I run on each dataset to make sure that they don't have typos and they don't contain impossible values?

Thanks in advance!

share|improve this question
2  
Great question. I suspect it will be difficult to give general answers because the checks will depend on the specifics of the data set. –  mark999 Jun 7 '11 at 8:36
2  
@mark999 I agree. I'll be interested to read the answers to this question. There are some general strategies, but I find that a lot of checking is about building domain specific expectations, both about what the data should look like, and some of the common errors that can arise. –  Jeromy Anglim Jun 7 '11 at 8:42
    
Readers here will also be interested in the following thread: Quality assurance and quality control (qa/qc) guidelines for a database. –  gung Jul 25 '13 at 2:55
add comment

5 Answers

up vote 59 down vote accepted

It helps to understand how the data were recorded.

Let me share a story. Once, long ago, many datasets were stored only in fading hardcopy. In those dark days I contracted with an organization (of great pedigree and size; many of you probably own its stock) to computerize about 10^5 records of environmental monitoring data at one of its manufacturing plants. To do this, I personally marked up a shelf of laboratory reports (to show where the data were), created data entry forms, and contracted with a temp agency for literate workers to type the data into the forms. (Yes, you had to pay extra for people who could read.) Due to the value and sensitivity of the data, I conducted this process in parallel with two workers at a time (who usually changed from day to day). It took a couple of weeks. I wrote software to compare the two sets of entries, systematically identifying and correcting all the errors that showed up.

Boy were there errors! What can go wrong? A good way to describe and measure errors is at the level of the basic record, which in this situation was a description of a single analytical result (the concentration of some chemical, often) for a particular sample obtained at a given monitoring point on a given date. In comparing the two datasets, I found:

  • Errors of omission: one dataset would include a record, another would not. This usually happened because either (a) a line or two would be overlooked at the bottom of a page or (b) an entire page would be skipped.

  • Apparent errors of omission that were really data-entry mistakes. A record is identified by a monitoring point name, a date, and the "analyte" (usually a chemical name). If any of these has a typographical error, it will not be matched to the other records with which it is related. In effect, the correct record disappears and an incorrect record appears.

  • Fake duplication. The same results can appear in multiple sources, be transcribed multiple times, and seem to be true repeated measures when they are not. Duplicates are straightforward to detect, but deciding whether they are erroneous depends on knowing whether duplicates should even appear in the dataset. Sometimes you just can't know.

  • Frank data-entry errors. The "good" ones are easy to catch because they change the type of the datum: using the letter "O" for the digit "0", for instance, turns a number into a non-number. Other good errors change the value so much it can readily be detected with statistical tests. (In one case, the leading digit in "1,000,010 mg/Kg" was cut off, leaving a value of 10. That's an enormous change when you're talking about a pesticide concentration!) The bad errors are hard to catch because they change a value into one that fits (sort of) with the rest of the data, such as typing "80" for "50". (This kind of mistake happens with OCR software all the time.)

  • Transpositions. The right values can be entered but associated with the wrong record keys. This is insidious, because the global statistical characteristics of the dataset might remain unaltered, but spurious differences can be created between groups. Probably only a mechanism like double-entry is even capable of detecting these errors.

Once you are aware of these errors and know, or have a theory, of how they occur, you can write scripts to troll your datasets for the possible presence of such errors and flag them for further attention. You cannot always resolve them, but at least you can include a "comment" or "quality flag" field to accompany the data throughout their later analysis.

Since that time I have paid attention to data quality issues and have had many more opportunities to make comprehensive checks of large statistical datasets. None is perfect; they all benefit from quality checks. Some of the principles I have developed over the years for doing this include

  1. Whenever possible, create redundancy in data entry and data transcription procedures: checksums, totals, repeated entries: anything to support automatic internal checks of consistency.

  2. If possible, create and exploit another database which describes what the data should look like: that is, computer-readable metadata. For instance, in a drug experiment you might know in advance that every patient will be seen three times. This enables you to create a database with all the correct records and their identifiers with the values just waiting to be filled in. Fill them in with the data given you and then check for duplicates, omissions, and unexpected data.

  3. Always normalize your data (specifically, get them into at least fourth normal form), regardless of how you plan to format the dataset for analysis. This forces you to create tables of every conceptually distinct entity you are modeling. (In the environmental case, this would include tables of monitoring locations, samples, chemicals (properties, typical ranges, etc.), tests of those samples (a test usually covers a suite of chemicals), and the individual results of those tests. In so doing you create many effective checks of data quality and consistency and identify many potentially missing or duplicate or inconsistent values.

    This effort (which requires good data processing skills but is straightforward) is astonishingly effective. If you aspire to analyze large or complex datasets and do not have good working knowledge of relational databases and their theory, add that to your list of things to be learned as soon as possible. It will pay dividends throughout your career.

  4. Always perform as many "stupid" checks as you possibly can. These are automated verification of obvious things such that dates fall into their expected periods, the counts of patients (or chemicals or whatever) always add up correctly, that values are always reasonable (e.g., a pH must be between 0 and 14 and maybe in a much narrower range for, say, blood pH readings), etc. This is where domain expertise can be the most help: the statistician can fearlessly ask stupid questions of the experts and exploit the answers to check the data.

Much more can be said of course--the subject is worth a book--but this should be enough to stimulate ideas.

share|improve this answer
2  
(+1) Nice response! –  chl Jun 7 '11 at 14:49
4  
excellent addendum to your database QA/QC guidelines –  David Jun 7 '11 at 20:20
2  
One follow up question- the subject is worth a book- is there a book? –  Chris Beeley Jun 9 '11 at 8:24
3  
+1 - wonderful answer whuber. I wish you had a blog :) (I would have loved to add your writing to r-bloggers.com) –  Tal Galili Sep 22 '11 at 7:33
2  
You should write the book that the subject is worth! –  Zach Nov 29 '11 at 19:30
show 2 more comments

@whuber makes great suggestions; I would only add this: Plots, plots, plots, plots. Scatterplots, histograms, boxplots, lineplots, heatmaps and anything else you can think of. Of course, as you've found there are errors that won't be apparent on any plots but they're a good place to start. Just make sure you're clear on how your software handles missing data, etc.

Depending on the context you can get creative. One thing I like to do With multivariate data is fit some kind of factor model/probabilistic PCA (something that will do multiple imputation for missing data) and look at scores for as many components as possible. Data points which score highly on the less important components/factors are often outliers you might not see otherwise.

share|improve this answer
2  
+1 Plotting is for statistics what voting is for Chicago: something everybody does early and often. ;-) –  whuber Jun 7 '11 at 19:23
add comment

Big things I tend to check:

  1. Variable type - to see that a number is numeric, and not factor/character (might indicate some problem with the data that was entered)
  2. Consistent value levels - to see that a variable with the name "t1" didn't find it self again with the name "t1 " or "t 1"
  3. Outliers - see that the ranges of value make sense. (did you get a blood pressure value of 0? or minus?). Here we sometimes find out that someone encoded -5 as missing value, or something like that.
  4. Linear restrictions. I don't use that, but some find that they wish to have restructions on the dependencies of some columns (columns A, B must add to C, or something like that). For this you can have a look at the deducorrect package (I met the speaker, Mark van der Loo, in the last useR conference - and was very impressed with his package)
  5. too little randomness. Sometimes values got to be rounded to some values, or truncated at some point. These type of things are often more clear in scatter plots.
  6. Missing values - making sure that the missing is not related to some other variable (missing at random). But I don't have a rule of thumb to give here.
  7. Empty rows or rows with mostly no-values. These should be (usually) found and omitted.

Great question BTW - I hope to read other people's experience on the matter.

share|improve this answer
add comment

When you have measures along time ("longitudinal data") it is often useful to check the gradients as well as the marginal distributions. This gradient can be calculated at different scales. More generally you can do meaningful transformations on your data (fourier, wavelet) and check the distributions of the marginals of the transformed data.

share|improve this answer
add comment

A few I always go through:

  1. Are there the number of records there are supposed to be? For example, if you pulled your data from another source, or its a sub-set of someone elses data, do your numbers look reasonable. You'd think this would be covered, but you'd...be suprised.
  2. Are all your variables there? Do the values of those variables make sense? For example, if a Yes/No/Missing variable is coded "1,2,3" - what does that mean?
  3. Where are your missing values? Are there some variables that seem overburdened with missing information? Are there certain subjects with massive numbers of missing values.

Those are the first steps I go through to make sure a dataset is even ready for something like exploratory data analysis. Just sitting down, roaming about the data some going "Does that...seem right?"

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.