Data Cleaning & Preparation

The $24 million typo

In 2012, Knight Capital Group — one of the largest market makers on Wall Street — deployed a software update that contained a bug. The system started executing trades at wrong prices, buying high and selling low at machine speed. In 45 minutes, Knight Capital lost $440 million. The company nearly went bankrupt overnight.

The root cause? A configuration error — old test code that should have been removed was triggered by new data that didn't match the expected format. Dirty data met unvalidated inputs and the result was financial catastrophe.

But you don't need Wall Street to see the damage of bad data. In 2020, Public Health England lost nearly 16,000 COVID-19 test results because they were stored in an Excel file that hit the row limit of the old .xls format (65,536 rows). The data simply vanished. Contact tracing was delayed for thousands of people.

Bad data doesn't just produce wrong charts. It produces wrong decisions, lost money, and in healthcare, lost lives. Data cleaning isn't glamorous, but it's the most important step in any analysis.

The five types of dirty data

Every messy dataset suffers from some combination of these problems. Learn to spot them and you'll know what to fix.

Handling missing values

Missing data is the most common problem you'll face. The question isn't whether your data has missing values — it's what to do about them.

The decision tree for missing values

The four strategies

There Are No Dumb Questions

"Can I just replace missing values with zero?"

Almost never. Zero is a real value. If a customer's revenue field is blank, replacing it with zero means "this customer spent $0" — which is a statement about their behavior. A missing value means "we don't know what they spent." Those are very different things. Replacing blanks with zero will deflate your averages and distort your analysis.

"What if the missing data isn't random?"

That's called "systematic missingness" and it's a red flag. Example: if all customers over age 60 have blank email fields, the data isn't missing randomly — older customers may not have provided emails. Deleting those rows would remove an entire demographic from your analysis. You need to investigate WHY the data is missing before deciding how to handle it.

Handling duplicates

Duplicates inflate your numbers. If a $500 order appears twice, your revenue report is $500 too high. If a customer appears three times, your user count is inflated by two.

How duplicates happen

How to find and fix them

In SQL:

-- Find duplicates
SELECT email, COUNT(*) as occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

-- Keep only the first occurrence
DELETE FROM customers
WHERE id NOT IN (
  SELECT MIN(id) FROM customers GROUP BY email
);

In Excel/Sheets:

1. Select the data range
2. Data > Remove Duplicates (Excel) or Data > Data cleanup > Remove duplicates (Sheets)
3. Choose which columns define a "duplicate" — usually a unique identifier like email or order ID

Handling outliers

An outlier is a data point that's far from the others. The question: is it an error, or is it real?

The outlier handling process

1. Detect: Use box plots, z-scores, or IQR (interquartile range) method. Any point beyond 1.5x IQR from Q1 or Q3 is a statistical outlier.
2. Investigate: Look at the record. Does it make sense? Check the source.
3. Decide: Remove if it's an error. Cap (winsorize) if it's extreme but real. Keep and flag if it's important.

There Are No Dumb Questions

"Should I always remove outliers?"

No. Removing real outliers is data manipulation. If your dataset includes Elon Musk's net worth, removing it because it "skews the average" is hiding reality. Instead, report the median (which is robust to outliers) alongside the mean. Only remove outliers when they're clearly errors — a typo, a sensor malfunction, a test record that wasn't deleted.

"What's the difference between winsorizing and trimming?"

Trimming removes the extreme values entirely. Winsorizing replaces them with the next-closest non-extreme value. If the 99th percentile of order value is $5,000 and you have orders at $50,000 and $80,000, winsorizing sets both to $5,000. Trimming deletes them. Winsorizing is usually preferred because it preserves sample size.

Standardizing formats

The same information written three different ways creates three different groups in your analysis. This is the #1 cause of wrong GROUP BY results.

The rule: pick one standard and enforce it. For dates, use ISO 8601 (YYYY-MM-DD). For countries, use a reference table. For booleans, use TRUE/FALSE. Document the standard so everyone on the team follows it.

The data cleaning checklist

Use this checklist every time you receive a new dataset. It takes 15 minutes and saves hours.

Key takeaways

• Bad data costs real money. Trillions annually across industries. Every wrong decision built on dirty data is a preventable loss.
• Five types of dirty data: missing values, duplicates, inconsistent formatting, outliers, and wrong data types. Learn to spot all five.
• Missing values need strategy, not deletion. Choose between removing, filling, forward-filling, or flagging based on how much data is missing and whether the missingness is random.
• Not all outliers are errors. Investigate before removing. Use median instead of mean when outliers are real but extreme.
• Standardize formats ruthlessly. One country name format. One date format (ISO 8601). One boolean format. Document the standard.
• Use the 8-step checklist on every new dataset: shape, types, missing values, duplicates, ranges, categories, outliers, referential integrity.