Data Analytics & Business Intelligence · Chapter 2 · ~15 min · Beginner
Data Cleaning: Spotting Dirty Data & Fixing Errors
Discover why data analysts spend 80% of their time cleaning data before doing any analysis: learn how to spot missing values, remove duplicate records, standardize text typos, and impute missing fields safely.
- Understand the 'Garbage In, Garbage Out' rule of analytics
- Identify the 4 classic dirty data defects in raw datasets
- Learn when to drop missing rows vs when to impute with Median/Mean
- Clean and standardize real records in the interactive workbench
1The 80/20 Rule: Why Clean Data Matters
There is a well-known industry saying: "Garbage In, Garbage Out."
If your raw dataset has duplicate customers, missing revenue numbers, or misspelled country names, even the most expensive AI model or beautiful executive chart will give you completely wrong answers!
In professional industry surveys, data analysts report spending up to 80% of their time finding and cleaning dirty data, and only 20% actually building charts and sharing insights. Cleaning data is the superpower that makes all real analysis trustworthy.
What is meant by the phrase 'Garbage In, Garbage Out' in data analysis?
2The 4 Classic Dirty Data Defects
Whenever you open a raw CSV file or SQL dump, you should immediately inspect for these 4 common issues:
NULL or Blank Cells
Users skipped optional fields (like phone number or age) during sign-up.
Repeated Rows
Accidental double-clicks or merged marketing lists producing identical rows.
Typos & Aliases
The same country entered as "USA", "usa", "United States", and "U.S.".
Impossible Numbers
Human typos like Age: -5 or Year: 2099.
What risk occurs if duplicate customer purchase rows are not removed prior to calculating quarterly revenue?
3Handling Missing Values: Drop vs. Impute
When you encounter missing cells (NULL / NaN), you have two main strategies:
Strategy A: Dropping Rows (Deleting)
Simply delete any row that has missing data.
• Best when: Missing values account for less than 1-2% of a massive dataset, and deleting them won't skew the results.
Strategy B: Imputation (Filling)
Replace the blank cell with a smart estimated number (typically the Median or Mean of that column).
• Best when: Dropping rows would throw away too much valuable customer transaction data.
What is 'data imputation' in the context of data cleaning?
4Standardizing Text & Categorical Aliases
Computers are literal. If your table has categories like:
A computer will treat those as 5 completely different countries! Your bar chart will have 5 tiny bars instead of one big bar.
Standardization fixes this in three quick steps:
- Trim Whitespace: Remove accidental spaces at the beginning or end (
" USA ".trim()$\rightarrow$"USA"). - Consistent Casing: Convert all strings to UPPERCASE or Title Case.
- Map Aliases: Replace variations with an authoritative standard code (
"United States" $\rightarrow$ "USA").
5Hands-On: Clean a Real Dataset Live
Use the interactive workbench below to fix 3 common defects in a customer dataset. Watch the Cleanliness Score reach 100%:
Interactive Data Cleaning Workbench
Click cleaning operations to fix data defects:
| id | customer name | age | country | spend ($) | data status |
|---|---|---|---|---|---|
| 101 | Alice Johnson | 28 | USA | $120 | Verified Clean |
| 102 | Bob Smith | NULL (MISSING) | "usa" (TYPO) | $45 | Dirty Data |
| 103 | Carol Davis | 34 | Canada | $210 | Verified Clean |
| 101 | Alice Johnson | 28 | USA | $120 | Duplicate Row |
| 104 | David Kim | 24 | "united states" (TYPO) | $85 | Dirty Data |
Click all 3 cleaning operations above to achieve a 100% verified clean dataset.
6Chapter Knowledge Challenge
Test your data cleaning intuition on real-world datasets before moving to Chapter 3:
You have 10,000 transaction records. Exactly 8 rows are identical duplicates caused by a network retry. What is the correct cleaning action?
Why is imputing missing salaries with the column Median usually better than the Mean?
7Recap
- The Garbage In, Garbage Out principle means data cleaning is required before any analysis can be trusted.
- Common defects include Missing values, Duplicate rows, Inconsistent casing/typos, and Outliers.
- Handle missing values by dropping rows when rare, or imputing with the column Median when you need to preserve sample size.
- Always trim whitespace and map text aliases to ensure categories group together properly in charts.
Congratulations on completing Chapter 2! Mark as complete to update your syllabus: