Data cleaning, also called data cleansing, means finding and then fixing or removing records in a dataset, table or database that are corrupt, inaccurate or irrelevant. You replace, modify or delete the bad parts, standardise formats and fill gaps, so the cleaned data lines up with similar data elsewhere in your systems.
Someone mistypes an entry, a file gets damaged on its way between systems, or two teams define the same field in different ways. Data cleaning is the work of putting that right.
What data cleaning does to a dataset
- It finds the incomplete, incorrect or inaccurate parts of the data.
- It replaces, modifies or deletes those parts.
- It harmonises formats, for example making every row use the same date format.
- It can make data more complete by adding related information, a practice called data enhancement.
- It leaves the dataset consistent with other similar datasets in the system.
How cleaning a dataset plays out in practice
Errors arrive from three directions: people entering data wrongly, corruption while the data is being sent or stored, and different data dictionary definitions across systems. Each produces a different kind of mess, which is why cleaning is a set of jobs rather than one.
A small business with a customer spreadsheet is a good example. One row has a date written day first, another month first. A postcode is missing. An address is spelled two ways for the same customer. Cleaning that sheet means picking one date format and applying it everywhere, filling or flagging the missing postcode, and settling on one version of the address.
Corruption in transmission or storage is worth remembering, because nobody typed anything wrong. Check data that has been moved between systems, not just data people typed.
Differing data dictionary definitions are the third source. One system might hold a customer's country as a full name while another uses a short code. If two systems define the same field differently, putting their records side by side shows the mismatch, and harmonising formats is how that gets settled.
There are two ways to do it. You can work interactively, using data wrangling tools to look at the data and fix it as you go. Or you can use batch processing, often with scripts, which suits large volumes or a clean-up you'll repeat.
The same thinking applies whatever the source. Data pulled together by scraping data from websites needs it as much as anything typed in by hand.
Before you change a single cell, set your house rules: one date layout, one way of writing names and one spelling for repeated values. Write them down.
The catch: cleaning isn't validation
This is where people get muddled. Data validation and data cleaning sound like the same thing, but they work at different moments. Validation rejects bad data at the point of entry, so it never gets in. Cleaning corrects data that's already been collected, usually in batches.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
So cleaning is a repair job, not a gate. If the same errors keep turning up every time you clean, the fix may belong at the point of entry instead, so the bad data never arrives in the first place.
How to try data cleaning on your own data
- Pick one dataset or table to start with, ideally one people rely on.
- Look for the incomplete, incorrect or inaccurate parts: blanks, typos and values that make no sense.
- Decide for each one whether to replace it, modify it or delete it.
- Harmonise formats, such as getting every date into the same layout.
- Add related information where it makes the records more complete.
- Check the result is consistent with other similar data in your systems.
Work on a copy rather than the only version you have. If a change goes wrong, you can compare against the original and try again.
Once it's clean, protect it. A tidy dataset is worth looking after, so read up on keeping your important business data safe.
Frequently asked questions
Is data cleansing different from data cleaning?
No. They're two names for the same process of finding and fixing or removing bad records.
Who needs to clean their records?
Anyone whose work rests on a spreadsheet or database, from a business owner with a customer list to a student with a project dataset.
Does cleaning always mean deleting data?
No. Removal is one option, but a bad value can also be swapped for the right one or corrected where it stands.
Why does data cleaning matter for a business?
Incorrect data has real consequences. Inconsistent addresses, for instance, can mean resending mail or even losing customers.
Should I clean data by hand or with a script?
Both are used: interactively with data wrangling tools, or in batches, often with scripts. If you're new to it, fixing things by hand first teaches you what kinds of errors your data tends to have, which is what you'd need to know before scripting a batch clean-up.
