Now that we understand what data cleaning is for and what methods and approaches there are to shape up our dataset, there is still the question of what cleaning can and can’t catch.

A general rule for cleaning a dataset where each column is a variable and the rows represent the records is:

• if the number of incorrect or missing values in a row is greater than the number of correct values, it is recommended to exclude that row.
• if the number of incorrect or missing values in a column is greater than the number of correct values in that column, it is recommended to exclude that column.

It should be made clear that exclusion is not the same as deletion! If you decide that you don’t want to include a row or column in your analysis or visualization, you should set them aside in a separate dataset rather than deleting them altogether. Once data are deleted, you can’t retrieve them any longer, even if you realize later on that there was a way to fill in the missing values. Unless you are absolutely certain that you will not use a record or variable again, do not just delete it.

In the last few chapters, we have talked about several different processes for data cleaning and have seen the types of problems they can help identify and fix. When we’re searching for errors and mistakes, we are able to detect potential problems such as:

• inconsistent labels, misspellings, and errors in punctuation;
• outliers, invalid values, and extreme values;
• data that aren’t internally consistent within the dataset (e.g. 200 lbs. of morphine);
• lack or excess of data;
• odd patterns in distributions; and
• missing values.

What we haven’t talked a lot about yet is what data cleaning can’t catch. There may be incorrect values that are nevertheless both within the acceptable range for the data and that make complete sense. For example, if someone enters the number 45 instead of 54 into your dataset and your valid range of numbers is 0-100, it will be unlikely that you’ll catch that error unless that field is one that you’re cross-checking with another field or you’re verifying the information with an outside source record.

Similar to that, you may be receiving information from an online survey form and the person filling it out may have selected the button for “Strongly Agree” when they actually meant to select “Strongly Disagree.” Again, unless this answer is somehow cross-checked with another variable or source, you will have no easy way to detect this error. Sometimes this type of error is more critical than others. If a person selects “Strongly Agree” instead of “Agree” on an opinion survey, that is unlikely to have the same impact on the results as if someone accidentally marks the wrong gender on a form for a research study where you are using gender as a grouping category for treatment assignments.

Data cleaning also can’t tell if a missing value is truly missing (i.e. the question was accidentally skipped or the data were not collected for some reason) or the question was purposely skipped (i.e. the participant declined to answer) unless “Prefer not to answer” was an answer choice. This may be relevant in some cases (particularly in demographics), though in others, you may decide to just treat both as missing data. This is why, as mentioned before, you need to include a “Prefer not to answer” choice for any question of a personal nature where you want to know if the data are truly missing, since some people may actively choose to not answer questions about race/ethnicity, income, political affiliation, sexual orientation, etc.