Data Cleaning | A Guide with Examples & Steps

Data cleaning involves spotting and resolving potential data inconsistencies or errors to improve your data quality. An error is any value (e.g., recorded weight) that doesn’t reflect the true value (e.g., actual weight) of whatever is being measured.

In this process, you review, analyse, detect, modify, or remove ‘dirty’ data to make your dataset ‘clean’. Data cleaning is also called data cleansing or data scrubbing.

Why does data cleaning matter?

In quantitative research, you collect data and use statistical analyses to answer a research question. Using hypothesis testing, you find out whether your data demonstrate support for your research predictions.

Errors are often inevitable, but cleaning your data helps you minimise them. If you don’t remove or resolve these errors, you could end up with a false or invalid study conclusion.

With inaccurate or invalid data, you might make a Type I or II error in your conclusion. These types of erroneous conclusions can be practically significant with important consequences, because they lead to misplaced investments or missed opportunities.

Dirty vs clean data

Dirty data include inconsistencies and errors. These data can come from any part of the research process, including poor research design, inappropriate measurement materials, or flawed data entry.

Clean data meet some requirements for high quality while dirty data are flawed in one or more ways. Let’s compare dirty with clean data.

Valid data

Valid data conform to certain requirements for specific types of information (e.g., whole numbers, text, dates). Invalid data don’t match up with the possible values accepted for that observation.

Without valid data, your data analysis procedures may not make sense. It’s best to use data validation techniques to make sure your data are in the right formats before you analyse them.

Accurate data

In measurement, accuracy refers to how close your observed value is to the true value. While data validity is about the form of an observation, data accuracy is about the actual content.

Complete data

Complete data are measured and recorded thoroughly. Incomplete data are statements or records with missing information.

Reconstructing missing data isn’t easy to do. Sometimes, you might be able to contact a participant and ask them to redo a survey or an interview, but you might not get the answer that you would have otherwise.

Consistent data

Clean data are consistent across a dataset. For each member of your sample, the data for different variables should line up to make sense logically.

Unique data

In data collection, you may accidentally record data from the same participant twice.

It’s important to review your data for identical entries and remove any duplicate entries in data cleaning. Otherwise, your data might be skewed.

Uniform data

Uniform data are reported using the same units of measure. If data aren’t all in the same units, they need to be converted to a standard measure.

How do you clean data?

Every dataset requires different techniques to clean dirty data, but you need to address these issues in a systematic way. You’ll want to conserve as much of your data as possible while also ensuring that you end up with a clean dataset.

Data cleaning is a difficult process because errors are hard to pinpoint once the data are collected. You’ll often have no way of knowing if a data point reflects the actual value of something accurately and precisely.

In practice, you may focus instead on finding and resolving data points that don’t agree or fit with the rest of your dataset in more obvious ways. These data might be missing values, outliers, incorrectly formatted, or irrelevant.

You can choose a few techniques for cleaning data based on what’s appropriate. What you want to end up with is a valid, consistent, unique, and uniform dataset that’s as complete as possible.

Data cleaning workflow

Generally, you start data cleaning by scanning your data at a broad level. You review and diagnose issues systematically and then modify individual items based on standardised procedures. Your workflow might look like this:

1. Apply data validation techniques to prevent dirty data entry.
2. Screen your dataset for errors or inconsistencies.
3. Diagnose your data entries.
4. Develop codes for mapping your data into valid values.
5. Transform or remove your data based on standardised procedures.

Not all of these steps will be relevant to every dataset. You can carefully apply data cleaning techniques where necessary, with clear documentation of your processes for transparency.

By documenting your workflow, you ensure that other people can review and replicate your procedures.

Data validation

Data validation involves applying constraints to make sure you have valid and consistent data. It’s usually applied even before you collect data, when designing questionnaires or other measurement materials requiring manual data entry.

Different data validation constraints help you minimise the amount of data cleaning you’ll need to do.

Data-type constraints: Values can only be accepted if they are of a certain type, such as numbers or text.

Range constraints: Values must fall within a certain range to be valid.

Mandatory constraints: A value must be entered.

Data screening

Once you’ve collected your data, it’s best to create a backup of your original dataset and store it safely. If you make any mistakes in your workflow, you can always start afresh by duplicating the backup and working from the new copy of your dataset.

Data screening involves reviewing your dataset for inconsistent, invalid, missing, or outlier data. You can do this manually or with statistical methods.

Step 1: Straighten up your dataset

These actions will help you keep your data organised and easy to understand.

• Turn each variable (measure) into a column and each case (participant) into a row.
• Give your columns unique and logical names.
• Remove any empty rows from your dataset.

Step 2: Visually scan your data for possible discrepancies

Go through your dataset and answer these questions:

• Are there formatting irregularities for dates, or textual or numerical data?
• Do some columns have a lot of missing data?
• Are any rows duplicate entries?
• Do specific values in some columns appear to be extreme outliers?

Make note of these issues and consider how you’ll address them in your data cleaning procedure.

Step 3: Use statistical techniques and tables/graphs to explore data

By gathering descriptive statistics and visualisations, you can identify how your data are distributed and identify outliers or skewness.

1. Explore your data visually with boxplots, scatterplots, or histograms
2. Check whether your data are normally distributed
3. Create summary (descriptive) statistics for each variable
4. Summarise your quantitative data in frequency tables

You can get a rough idea of how your quantitative variable data are distributed by visualising them. Boxplots and scatterplots can show how your data are distributed and whether you have any extreme values. It’s important to check whether your variables are normally distributed so that you can select appropriate statistical tests for your research.

If your mean, median, and mode all differ from each other by a lot, there may be outliers in the dataset that you should look into.

Data diagnosing

After a general overview, you can start getting into the nitty-gritty of your dataset. You’ll need to create a standard procedure for detecting and treating different types of data.

Without proper planning, you might end up cherry-picking only some data points to clean, leading to a biased dataset.

Here we’ll focus on ways to deal with common problems in dirty data:

• Duplicate data
• Invalid data
• Missing values
• Outliers

De-duplication

De-duplication means detecting and removing any identical copies of data, leaving only unique cases or participants in your dataset.

If duplicate data are left in the dataset, they will bias your results. Some participants’ data will be weighted more heavily than others’.

Invalid data

Using data standardisation, you can identify and convert data from varying formats into a uniform format.

Unlike data validation, you can apply standardisation techniques to your data after you’ve collected it. This involves developing codes to convert your dirty data into consistent and valid formats.

Data standardisation is helpful if you don’t have data constraints at data entry or if your data have inconsistent formats.

String-matching methods

To standardise inconsistent data, you can use strict or fuzzy string-matching methods to identify exact or close matches between your data and valid values.

A string is a sequence of characters. You compare your data strings to the valid values you expect to obtain and then remove or transform the strings that don’t match.

Strict string-matching: Any strings that don’t match the valid values exactly are considered invalid.

Fuzzy string-matching: Strings that closely or approximately match valid values are recognised and corrected.

After matching, you can transform your text data into numbers so that all values are consistently formatted.

Fuzzy string-matching is generally preferable to strict string-matching because more data are retained.

Missing data

In any dataset, there’s usually some missing data. These cells appear blank in your spreadsheet.

Missing data can come from random or systematic causes.

• Random missing data include data entry errors, inattention errors, or misreading of measures.
• Non-random missing data result from confusing, badly designed, or inappropriate measurements or questions.

Dealing with missing data

Your options for tackling missing data usually include:

• Accepting the data as they are
• Removing the case from analyses
• Recreating the missing data

Random missing data are usually left alone, while non-random missing data may need removal or replacement.

With deletion, you remove participants with missing data from your analyses. But your sample may become smaller than intended, so you might lose statistical power.

Alternatively, you can use imputation to replace a missing value with another value based on a reasonable estimate. You use other data to replace the missing value for a more complete dataset.

It’s important to apply imputation with caution, because there’s a risk of bias or inaccuracy.

Outliers

Outliers are extreme values that differ from most other data points in a dataset. Outliers can be true values or errors.

True outliers should always be retained, because these just represent natural variations in your sample. For example, athletes training for a 100-metre Olympic sprint have much higher speeds than most people in the population. Their sprint speeds are natural outliers.

Outliers can also result from measurement errors, data entry errors, or unrepresentative sampling. For example, an extremely low sprint time could be recorded if you misread the timer.

Detecting outliers

Outliers are always at the extreme ends of any variable dataset.

You can use several methods to detect outliers:

• Sorting your values from low to high and checking minimum and maximum values
• Visualising your data in a boxplot and searching for outliers
• Using statistical procedures to identify extreme values

Dealing with outliers

Once you’ve identified outliers, you’ll decide what to do with them in your dataset. Your main options are retaining or removing them.

In general, you should try to accept outliers as much as possible unless it’s clear that they represent errors or bad data.

It’s important to document each outlier you remove and the reasons so that other researchers can follow your procedures.

Frequently asked questions about data cleaning

What is the definition of data cleaning?

Data cleaning involves spotting and resolving potential data inconsistencies or errors to improve your data quality. An error is any value (e.g., recorded weight) that doesn’t reflect the true value (e.g., actual weight) of something that’s being measured.

In this process, you review, analyse, detect, modify, or remove ‘dirty’ data to make your dataset ‘clean’. Data cleaning is also called data cleansing or data scrubbing.

Why does data cleaning matter?

Data cleaning is necessary for valid and appropriate analyses. Dirty data contain inconsistencies or errors, but cleaning your data helps you minimise or resolve these.

Without data cleaning, you could end up with a Type I or II error in your conclusion. These types of erroneous conclusions can be practically significant with important consequences, because they lead to misplaced investments or missed opportunities.

When do you clean data?

Data cleaning takes place between data collection and data analyses. But you can use some methods even before collecting data.

For clean data, you should start by designing measures that collect valid data. Data validation at the time of data entry or collection helps you minimize the amount of data cleaning you’ll need to do.

After data collection, you can use data standardisation and data transformation to clean your data. You’ll also deal with any missing values, outliers, and duplicate values.

What is the difference between clean and dirty data?

Clean data are valid, accurate, complete, consistent, unique, and uniform. Dirty data include inconsistencies and errors.

Dirty data can come from any part of the research process, including poor research design, inappropriate measurement materials, or flawed data entry.