• Emily Singerhouse

Data Cleaning: How To

Data cleaning fixes and removes incorrect and incomplete data within a dataset to promote the quality of data. This process has many benefits and nuances, but can be a relatively easy process. To achieve your goals, it is critical to have a data cleaning strategy.


Here is a step-by-step guide on how to clean your data.


Data Cleaning in Six Steps

Step 1: Think of the bigger picture

Work with your team on outlining the goals and expectations of your data to ensure your process fits with your project. You can gather your partners together and brainstorm what you hope to do with this data. Typically, this is done at the start of a project before data collection. If you have already done this, revisit this discussion and see if anything has changed.


Step 2: Standardize your process

Ensure you and your team have a written agreement of your data cleaning strategy. This strategy needs to show the following:

  • At what point you enter the data to clean it to reduce the risk of duplicity

  • The parameters of what data needs to be cleaned

  • A process to keep track of recurring errors in the data

Step 3: Remove duplicate or irrelevant observations

Remove duplicates or irrelevant observations from your data set. Irrelevant observations mean the data doesn’t match the project needs and goals. This typically happens when you import your data from an external source. Ensure you confirm that the results are duplicitous before you remove anything.


Step 4: Fix structural errors

Structural errors look like strange naming conventions, typos, or incorrect capitalization. A spellchecker can be used to find misspelled words and values that are not used consistently. For example: “N/A” and “Not Applicable” both appear, but they should be analyzed the same.


Step 5: Handle missing data

Flag the data that is missing. If you have missing data, keep your data set as is until you have considered all possible reasons why the data is missing. Some sets might have missing values; therefore, you must pay close attention to what is missing and determine if that is telling you something about the data. You must consider that you could lose the integrity of the data if you delete observations with missing information. Ask yourself: Are you operating from an assumption of what should or shouldn’t be missing?


Step 6: Validation Review

At the end of the data cleaning process, review the data and verify cleanliness. Ask yourself these questions:

  1. Does the data make sense?

  2. Does the data follow the appropriate rules of data validation?

  3. Does it prove or disprove the working theory, or bring any insight to light?

  4. Can you find trends in the data to help you form your next theory? If not, is that because of a data quality issue?

After you are done cleaning data, you can move on to the remaining stages of your project, like data analysis.