---
title: "What Is Data Cleaning? Steps, Examples and Tools"
description: "Data cleaning is finding and fixing wrong, duplicate, missing or badly formatted values so a dataset can be trusted. The steps, a worked example and the tools."
url: https://proxynet.io/blog/what-is-data-cleaning
date: 2026-11-19
author: "Acar Diveroli"
category: "Use Cases, AI"
lang: en
---

# What Is Data Cleaning? Steps, Examples and Tools

A buyer merges three supplier price lists into one sheet and asks for the average price per category. The answer for mugs comes back at 320. Nobody sells a 320 mug: one supplier writes decimals with a comma, and a cleanup step that removed commas as thousands separators turned 12,50 into 1250. The same sheet lists one mug twice because one copy has a trailing space in its name. Every formula worked. The data underneath them did not.

This post covers what data cleaning is, the steps in the order that works, the problems it fixes, a worked example we ran on a small product table, how cleaning differs from transformation and validation, spreadsheets versus scripts, machine learning and data collected from the web.

> **Note: Short answer**
>
> Data cleaning (also called data cleansing or scrubbing) is the work of finding and fixing values in a dataset that are wrong, duplicated, missing, inconsistent or in the wrong format, so the data can be trusted for analysis. A typical pass profiles the data first, fixes structural errors, standardises formats such as dates and currencies, removes duplicates, decides what to do with missing values, checks every record against written rules and documents each change. The order matters: duplicates often only become visible after formats are standardised. Cleaning never invents data; values that cannot be fixed are flagged and set aside, not guessed.

## What is data cleaning?

Data cleaning is the process of detecting and correcting errors in a dataset so that each value means what it claims to mean. A dataset here is any collection of related records, usually a table where each row is one thing (a product, a customer, a reading) and each column is one attribute of it.

The errors it targets are ordinary: a name with an extra space, a price stored as text, the same product listed twice, a date written three different ways, a field left empty. None of these stops a spreadsheet or a database from working. They change the result instead: a count is too high, an average is off, a filter misses rows.

Clean data also has a shape. Hadley Wickham's paper [Tidy Data](https://www.jstatsoft.org/article/view/v059i10) describes it in three rules: each variable is a column, each observation is a row, and each type of observational unit is a table. A sheet where one column holds both size and colour ("M / blue") breaks the first rule, and cleaning splits it.

## Why is data cleaning important?

Data cleaning is important because analysis, reports and models can only be as accurate as the data they read; errors in the input pass straight into the output, which is what the old phrase "garbage in, garbage out" means. The danger is that the output still looks normal.

A duplicate row makes a product count twice in every total. A missing value that a tool treats as zero pulls an average down. A price in pounds stored among prices in euros makes a cheap product look expensive, and a model trained on such data learns the mistakes along with the patterns.

Cleaning is the repair step; measuring how good the data is afterwards is a separate job. [What Is Data Quality?](/blog/what-is-data-quality) covers the six dimensions and the checks that score a dataset.

## What are the steps of data cleaning?

Data cleaning follows seven steps, from looking at the data to writing down what changed. The order below is deliberate: each step makes the next one easier and more reliable.

1. **Profile the data.** Before changing anything, count what is there: empty values per column, distinct values, minimum and maximum, the most common formats. A currency column with four spellings of the same two currencies tells you what to fix.
1. **Fix structural errors.** Correct the shape: split columns that hold two things, rename headers, trim spaces, fix character encoding (a "Â£" where "£" should be) and turn numbers stored as text into numbers.
1. **Standardise formats.** Pick one form per field and convert everything to it: dates as YYYY-MM-DD, the [ISO 8601 profile](https://www.w3.org/TR/NOTE-datetime) most systems read without guessing, currencies as three-letter codes such as EUR or USD (the ISO 4217 codes), one decimal separator, one unit, one spelling for each category value.
1. **Remove duplicates.** Decide which field identifies a record (a product ID, an email, an order number) and keep one row per key. Do this after standardising: "A-100" and "a-100 " are the same product, but an exact match will not see it.
1. **Handle missing values.** For each empty field choose one of three options: leave the record out, fill the value from a reliable source, or keep the record and mark the value as missing. Filling a gap with an average or a guess is a modelling decision, not cleaning, and should be written down as one.
1. **Validate against rules.** Check every record against written rules: prices above zero, dates that exist, stock status from a fixed list, a sale price no higher than the regular price. Records that fail go to a review list rather than being deleted.
1. **Document every change.** Record what was changed, by which rule and how many rows each rule touched. Keep the raw data untouched next to the cleaned copy, so any number in a report can be traced back.

Outliers, values far from the rest, belong in step 6. A price of 1,250 among prices of 10 to 30 may be a typo or a real premium product, so check it against the source before removing it.

## What problems does data cleaning fix?

Data cleaning fixes a small set of recurring problems, and most messy datasets combine several of them. The table pairs each problem with a typical example and the usual fix.

| Problem | Example | Usual fix |
|---|---|---|
| Duplicates | Same product on two rows | Deduplicate on a stable key |
| Inconsistent format | 18/11/2026 and Nov 18 2026 | Convert to one format |
| Inconsistent labels | in stock, In Stock, instock | Map to one fixed list |
| Wrong type | Price stored as text "19,90" | Parse into a number |
| Extra whitespace | "Ceramic Mug " | Trim and collapse spaces |
| Missing values | Empty price | Fill from source or flag |
| Impossible values | Price -5, date Nov 31 | Flag for review |
| Mixed units | Grams and kilograms in one column | Convert to one unit |
| Encoding errors | "Â£" instead of "£" | Re-read with the right encoding |

## A worked example: cleaning a 10-row product table

A small example shows the steps working together. We built a fictional product table for "Shop A" with ten rows and the kinds of mistakes real exports contain:

```text
sku,name,price,currency,collected,stock
A-100,Ceramic Mug ,12.50,EUR,2026-11-18,in stock
a-100,ceramic  mug,12.50,eur,18/11/2026,In Stock
A-101,Steel Bottle,"19,90",EUR,2026-11-18,in stock
A-102,Desk Lamp,,EUR,2026-11-18,out of stock
A-103,Notebook A5,4.20,GBP,Nov 18 2026,in stock
A-104,Cotton Tote,-5,EUR,2026-11-18,in stock
A-105,Phone Stand,8.99,€,2026-11-18,instock
A-106,Wall Clock,24.00,EUR,2026-11-18,in stock
A-106,Wall Clock,24.00,EUR,2026-11-18,in stock
A-107,Desk Mat,15.00,EUR,2026-11-31,in stock
```

On our test machine, a short Node.js script profiled the table, standardised it, validated it and removed duplicates in that order. Profiling found one empty price and four spellings in both the currency and the stock column. Standardising trimmed two names, upper-cased one product code, turned "19,90" into 19.90, mapped "eur" and "€" to EUR and rewrote two dates as 2026-11-18.

Validation flagged three rows instead of deleting them: A-102 has no price, A-104 has a negative price and A-107 is dated 31 November, a day that does not exist. The duplicate check found the most telling result. Before standardising, an exact comparison saw only one duplicate (the two identical A-106 rows). After trimming and upper-casing, it found a second: "a-100, ceramic  mug" is the same product as "A-100, Ceramic Mug ".

The ten rows ended as five clean rows, three rows for review and two removed duplicates. Nothing was guessed: the missing lamp price stays missing until someone checks the shop's page. For the same process written out in Python with pandas, including encoding fixes and outliers, see [How to Clean Scraped Data with Pandas](/blog/clean-scraped-data-with-pandas).

## How is data cleaning different from data transformation and validation?

Data cleaning corrects values that are wrong; data transformation changes the shape or meaning of correct data for a new use; data validation checks data against rules without changing it. The three often run in the same job, which is why the terms get mixed up.

| Term | What it does | Example |
|---|---|---|
| Data cleaning | Repairs wrong or messy values | "19,90" becomes 19.90 |
| Data transformation | Reshapes correct data for a use | Daily prices become weekly averages |
| Data validation | Tests data against rules | Flags a price below zero |
| Data mapping | Matches fields between systems | "item_price" goes to "price" |

Validation is the referee: it tells cleaning what to fix and confirms afterwards that the fix held. Transformation usually comes after cleaning, since an aggregate built on duplicates repeats their error. When data moves between systems with different field names, the matching of source fields to target fields is its own step, explained in [What Is Data Mapping?](/blog/what-is-data-mapping).

## Should you clean data in a spreadsheet or with a script?

Clean data in a spreadsheet when the table is small, the job is one-off and a person needs to see each change; use a script or a query when the data arrives again and again. The choice is about repetition more than size.

Spreadsheets handle the basics: trim functions, find and replace, filters, a "remove duplicates" command and text-to-columns. Their weakness is memory: a fix made by hand has to be made again next month, and nobody can tell afterwards which cells changed.

A script or SQL query turns each fix into a rule that runs the same way on every batch and leaves a record. Python with the pandas library is common for tables, SQL for data already in a database. Between the two sits OpenRefine, a free, open-source tool that runs on your own computer. Its [clustering feature](https://openrefine.org/docs/manual/cellediting) groups values that are similar but not identical, such as company names with different spacing or punctuation, and merges them only after you approve each group.

## How does data cleaning work for machine learning?

Data cleaning for machine learning follows the same steps but adds two concerns: wrong labels and leakage between training and test data. A model has no way to tell a mistake from a pattern, so both go straight into what it learns.

Labels are the answers a model learns from, such as "spam" or "not spam" on an email. They are often wrong more often than people assume: a [NeurIPS 2021 study](https://arxiv.org/abs/2103.14749) estimated label errors of at least 3.3% on average across the test sets of ten widely used benchmark datasets, and at least 6% in the ImageNet validation set. Checking a sample of labels by hand belongs in the cleaning plan.

Leakage happens when information that would not be available at prediction time is used while building the model, which makes test scores look better than real use. The [scikit-learn guide](https://scikit-learn.org/stable/common_pitfalls.html) gives the rule: split the data into training and test sets first, before any preprocessing, and learn values such as an average used to fill gaps from the training part only. Duplicates matter here too; the same record in both sets lets a model be tested on an answer it has already seen.

## Why does web data need extra cleaning?

Web data needs extra cleaning because pages are written for people, and data collected from many countries arrives in many local formats. One site writes a price as `1.299,00` with the euro sign after it, another as `1,299.00` with the sign in front; a date of 03/04 means March or April depending on the country.

The fix is to keep the context with every value. Store the currency code, the country the page was read from and the collection time on each row, and convert to one currency and one date format only after cleaning. Product names in several languages need a shared key, such as the product ID or barcode number, before deduplication can work.

Where you read a page from also changes what it says, because many shops show prices, currency and stock for the visitor's location. A [Residential Proxy](https://proxynet.io/residential-proxy) with country and city targeting lets a collection job read each market as a local visitor would, so the currency on the row matches the market it describes. That makes cleaning easier; it does not replace it.

Teams that would rather receive structured data than parse pages themselves can ask about managed page extraction through our [Web Scraping API](/data-scraping), available through our sales team.

## Where is data cleaning used?

- **Analytics and reporting:** sales and operations figures before they reach a dashboard.
- **E-commerce and pricing:** product catalogs, supplier price lists and competitor prices.
- **CRM and marketing:** customer and company records before segmentation.
- **Research:** survey answers with typos or answers outside the scale.
- **Machine learning:** training and test data before a model is trained.
- **System migrations:** records moved between systems with different formats.

## Common mistakes

- **Cleaning the only copy.** Keep the raw data untouched; without it, no fix can be checked or undone.
- **Deduplicating before standardising.** "A-100" and "a-100 " look different until both are trimmed and upper-cased.
- **Deleting rows that fail a rule.** Flag and count them, or you cannot see whether quality is improving.
- **Filling gaps with guesses.** An average written into an empty field looks like real data in every later report.
- **Converting currencies in place.** Keep the original amount and currency code; convert in a separate column.
- **Fixing by hand every month.** A fix you repeat should become a written rule.

## Decision guide

| Need | Recommendation |
|---|---|
| A small one-off table | Spreadsheet: trim, find and replace, remove duplicates |
| The same export every week | A script or SQL query with written rules |
| Messy names and categories | Clustering in OpenRefine, approve each merge |
| Duplicates you cannot see | Standardise first, then deduplicate on a key |
| Empty values | Fill from the source or flag; do not guess |
| Training data for a model | Split first, check a sample of labels |
| Prices from several countries | Keep currency, country and time on every row |
| Many sources, different field names | Map fields first, then clean |

## Frequently asked questions

### What are the steps of data cleaning?

Profile the data, fix structural errors, standardise formats, remove duplicates, handle missing values, validate against rules and document the changes. Standardise before deduplicating: many duplicates only match once spacing and letter case agree.

### What is the difference between data cleaning and data cleansing?

There is no practical difference: data cleaning, data cleansing and data scrubbing all mean finding and fixing wrong, duplicate, missing or badly formatted values. Some teams use "cleansing" for a recurring process built into a system and "cleaning" for a one-off job, but the work is the same.

### How do you clean data in Excel?

In Excel, use TRIM to remove extra spaces, Find and Replace for consistent spellings, Text to Columns to split fields, filters to spot odd values and Remove Duplicates on the column that identifies each record. Work on a copy, and keep the original sheet unchanged.

### Why is data cleaning important in data analysis?

Analysis repeats any error in its input: duplicates inflate totals, missing values distort averages and mixed spellings split one category into several. The results still look plausible, so the error often surfaces only after a decision rests on it.

### What is data cleaning in machine learning?

In machine learning, data cleaning means the usual steps plus two extra checks: correcting wrong labels and preventing leakage between training and test data. Split the data first and learn any fill values from the training set only, so test scores reflect how the model will behave on new data.

### Is data cleaning the same as clearing app data on a phone?

No. Clearing app data on a phone deletes the files an app has saved on that device, such as its cache, settings and sign-in, and returns it to a fresh state. Data cleaning in analysis keeps the data and corrects it; nothing is meant to be lost.

## Summary

Data cleaning makes a dataset mean what it says: one row per real thing, one format per field, no silent gaps and no impossible values. Profile first, fix the structure, standardise, then deduplicate, decide on missing values, validate against written rules and log every change while keeping the raw data. For data collected from the web, keep the currency, country and collection time on every row, and collect each market's data from that market; our [price monitoring](/price-monitoring) page explains how that works for competitor prices.
