Data Cleaning Basics: Fixing Messy Data

Real data arrives untidy. A workshop sign-up form collects "Austin", "austin" and "AUSTIN " as three different cities. Someone types a date as 03/04/2026 and someone else as 2026-03-04. A person signs up twice because the page was slow. None of this is anyone's fault, but every count, chart and report built on top of it will be quietly wrong.

Data cleaning is the careful work of finding and fixing such problems before the data is used. This entry gives a repeatable order of steps, a small messy example, and a few SQL statements that do the work. It assumes you can read a basic SELECT, as covered in reading data with SELECT. The ideas apply equally in a spreadsheet.

What data cleaning is, and is not

Data cleaning means making data consistent, complete enough and correct enough for the question you want to answer. It includes removing stray spaces, standardizing formats, handling blanks, merging duplicates and flagging values that look wrong.

It does not mean making the data look the way you wish it were. Deleting every row that disagrees with your expectations is not cleaning; it is editing the evidence. Good cleaning keeps a trail: you can say what changed, why and how many rows were affected. The goal is not perfect data. The goal is data that is trustworthy for a stated purpose.

Look first, and keep the original

Two habits come before any fix.

1. Keep the raw data untouched. Work on a copy, or write the cleaned result into a new table or file. If a cleaning step turns out to be wrong, you can go back to the start.
2. Profile the data. Count the rows, list the distinct values in each column, and look at the smallest and largest values. Profiling shows which problems really exist, so you fix those and not imaginary ones.

A few questions guide the look: Which columns have blanks? Which text columns have values that differ only by case or spacing? Do dates all share one format? Are there rows that appear twice?

A messy sign-up list

Here is a workshop sign-up table with five rows. The email of row 4 is empty (stored as NULL), the name in row 1 has a space before it, and seats is how many places each person booked.

Reading it with a cleaner's eye, you can already list the problems: stray space, mixed case, two date formats, one likely duplicate person, one blank email, a placeholder city, and a seat count that looks suspicious.

The usual fixes

Whitespace and case

Leading and trailing spaces are invisible and break matching, because ' Maria' and 'Maria' are different strings. Trim them. Pick one case rule for fields you compare, such as lowercase for emails, and apply it everywhere. Names are harder: capitalization rules differ between languages, so avoid forcing names into a pattern unless you know the data.

Dates and other formats

Convert dates to one format. The year-month-day form, 2026-03-04, is unambiguous and sorts correctly as text. Row 2 shows the danger of the other style: 03/04/2026 could mean March 4 or April 3. The matching duplicate in row 1 suggests March 4, but a careful cleaner confirms with the source instead of guessing silently.

Missing values

Decide what a blank means and write it one way. An empty string and NULL are different things, and a mix of both makes counts unreliable. Placeholders such as N/A, none or - should usually become NULL, because they are not real cities. Do not invent a value to fill a gap. Sometimes the right answer is to leave it empty and note that it is missing.

Duplicates

Rows 1 and 2 are probably the same person, once spacing, case and email case are normalized. Decide a rule: keep the earliest row, or merge details. If duplicates disagree, such as two different seat counts, ask the source. Never delete duplicates before you have normalized, since the match only appears after tidying.

Values that look wrong

Row 5 books 20 seats while everyone else books one or two. It could be a typo for 2, or a real group booking. Flag it, do not fix it. A cleaner does not know the answer. Mark the row, ask whoever owns the data, and record the reply.

Cleaning with SQL

The examples use functions found in PostgreSQL and most other systems. Names and details vary a little, so check yours.

First, normalize the two suspect rows, in a query that only reads:

SELECT id, TRIM(name) AS name, LOWER(email) AS email
FROM signups
WHERE id IN (1, 2)
ORDER BY id;

TRIM removes spaces from both ends by default, and LOWER converts to lowercase. The emails now match exactly. Capitalizing names is system-specific: PostgreSQL has INITCAP, but SQLite does not.

Next, find duplicate emails after normalizing:

SELECT LOWER(TRIM(email)) AS email, COUNT(*) AS copies
FROM signups
WHERE email IS NOT NULL
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

Rows 3 and 5 appear once each, and row 4 is excluded because its email is NULL. The IS NOT NULL test is needed because, as PostgreSQL's documentation explains, comparing anything with = NULL gives NULL (unknown), not true.

Profiling counts shows the blank effect:

SELECT COUNT(*) AS total_rows, COUNT(email) AS rows_with_email
FROM signups;

COUNT(*) counts rows, while COUNT(email) skips NULLs. If the blank had been stored as an empty string, it would have been counted as a value, which is why consistent handling of blanks matters.

Finally, a statement that changes data. Run it only on a copy, and look at what it will touch first:

SELECT id, city FROM signups WHERE city = 'N/A';
-- Result: one row, id 5. If that is what you expect, then:
UPDATE signups SET city = NULL WHERE city = 'N/A';

An UPDATE without a WHERE clause changes every row in the table, so never run one until you have checked the condition with a SELECT.

Check your work and write it down

After cleaning, repeat the profile. Do the row counts add up? Are the distinct values in each column what you expect? Compare a few rows by hand against the original.

Then keep a short log: what you changed, how many rows each step touched, and which questions you left open. Anyone using the data later can then trust it, or challenge it, with evidence.

The cheapest fix is the one that happens earlier. Rules in the database, such as marking a column NOT NULL or UNIQUE, stop some bad entries at the door. Drop-down lists on forms stop many spelling variants. Cleaning is never fully avoidable, but good intake shrinks it.

Common mistakes

• Cleaning the only copy. If a rule was wrong, there is no way back.
• Deleting rows that look odd. Outliers can be true. Flag them and ask.
• Guessing ambiguous dates. Confirm the format with the source before converting.
• Filling blanks with zero or a made-up value. That changes averages and totals. Leave it empty and say so.
• Removing duplicates before normalizing. The near-duplicates survive because they still look different.
• Running an update without checking its condition. Preview with SELECT, then change.
• Fixing the data but not the source. If the form keeps producing the same mess, the work repeats every week.

FAQ

How long does data cleaning take?

It depends on how messy the data is, how big it is and how clear your question is. A tidy export may need minutes, while a form that has collected free text for years can take much longer.

Should I clean in a spreadsheet or in SQL?

Either. Spreadsheets are fine for small files and one-off jobs. SQL is better for large tables and for steps you want to repeat exactly. Spreadsheet-friendly exports are covered in CSV vs JSON.

What is the difference between NULL and an empty string?

NULL means no value is recorded. An empty string is a real value that happens to contain no characters. They behave differently in counts and comparisons, so choose one convention.

Can cleaning remove real information?

Yes, if done carelessly. Merging distinct people with similar names, or trimming meaningful text, loses facts. Keep the original and log each rule.

Do I need special software?

No. A spreadsheet or a free database tool is enough to learn the habits. The habits matter more than the tool. The rest of the Data and Databases area shows where clean data gets used.