I have a data set that consists of 15 age values.

I want to clean the data before doing anything further. I have a few questions about data cleaning and data integrity.

What is the best treatment if the dataset contains an illegal value (like '${\rm F1}$', e.g.)? I thought taking the mean of the 14 remaining values and doing a mean substitution might be a good idea.

If there are values that cannot be right, such as $-99$ (no one can be $-99$ years old), is the best course of action a subjective treatment? Or should I use a statistical method, such as mean substitution? It seems logical to me to turn the $-99$ to a $+99$, and treat it as a data entry error.

Can $0$ be an age? The data set contains a $0$ value.

I am mostly looking to know if there is any formalised approach to dealing with these type of problems.

  • 1
    $\begingroup$ If it is an illegal value, then it is a data entry error. If you're specifically talking about age and you have reason to believe that the clerk meant to type 99 instead of -99, then it's fine to make that subjective correction if you know you're dealing with elderly subjects. However if the subject in question could be 9 years old because your data set encompasses people of all ages, then you can't do that. To infer the age of subjects is a difficult thing, though. For age you can't do mean substitution. 0 years can be an age if you're talking about newborns. $\endgroup$ – rocinante Mar 18 '14 at 1:29
  • 2
    $\begingroup$ Note that $-99$ is often an automatic value that is used by some software (SPSS, I think) to mark missing data. $\endgroup$ – gung - Reinstate Monica Mar 18 '14 at 1:48
  • $\begingroup$ As far as I remember, $-99$ is not "automatic", but one can specify values that are to be considered missing in SPSS and $-99$ (and $-98$ if one wants to differentiate between e.g. "don't know" and "don't want to answer") are very conventional candidates for such missing value codes. Alternative common values are be $-999$ and $-998$, depending on the range of the variable in question. This should be documented in the codebook, so there should be no need to guess. In fact, if there is no codebook explaining this, then that is a very bad sign about the quality of the data. $\endgroup$ – Maarten Buis Mar 18 '14 at 8:31

You should use common sense. Know how the data was recorded. Know the properties of the sample. Know any missing value codes. Know about the logical range of values both in general (e.g., age can't be less than zero) and for the application (if it's a study on adults, you shouldn't have age values less than around 16, 17, 18 or so). A few general categories of invalid data:

  • Official missing data values: In some cases, a dataset will have an official set of values to indicate missing data. Common values include -9, -99, -999, NA, 99, etc. In these cases, you need to ensure that the software you use, is aware that such values are missing. In SPSS, there is a field to specify, missing data values. In R you can often specify this when reading data, or you can replace such values with NA.
  • Invalid values that provide no information regarding true value: Some values are clearly invalid, and do not suggest what is the true value. This judgement is not intrinsic to the value. Instead it relates to the application. For example, to me "F1" as an age value has no meaning. But perhaps in your application it could mean something. In the case where the value has no meaning, the value recorded is equivalent to missing, so just replace value with missing.
  • Invalid values that suggest true value: In other cases, the value will be invalid, but may suggest the true value. For example, you have a value of -43 and you are pretty sure that it is actually 43 and that the minus sign was a typo. Many of these cases take judgement and knowledge about what might have generated the error. Sometimes you can use additional information to inform your replacement of the hopefully correct value.

Whether a value of 0 or 99 is a legitimate value for age would depend on the nature of the dataset. You should know enough about the data to know whether it includes babies or the very old. Histograms of age may also be informative. If there are spikes at 0 and 99 with all the rest of the data between 18 and 60, this would suggest that the 0, and 99 data is invalid.

At the end of this process, your problem of invalid data will typically be transformed into one of missing data. At that point there are a wide range of issues related to assessment, deletion, imputation and so forth.

| cite | improve this answer | |
  • $\begingroup$ Thanks Jeromy. The question was based on a conceptual idea, and I was questioning there exists any formalised practices in data integrity on how to deal with these problems with a mechanical approach. In my example, the points refer just to ages with a -99 as a data error, along with the 'F1'. $\endgroup$ – chiRal Mar 18 '14 at 2:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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