Chapter 2 of 2
Course Progress0 of 2 completed (0%)

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.

Concept Check

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:

1. Missing Values

NULL or Blank Cells

Users skipped optional fields (like phone number or age) during sign-up.

2. Duplicate Records

Repeated Rows

Accidental double-clicks or merged marketing lists producing identical rows.

3. Inconsistent Text

Typos & Aliases

The same country entered as "USA", "usa", "United States", and "U.S.".

4. Invalid Values

Impossible Numbers

Human typos like Age: -5 or Year: 2099.

Concept Check

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.

Concept Check

What is 'data imputation' in the context of data cleaning?

4Standardizing Text & Categorical Aliases

Computers are literal. If your table has categories like:

["USA", "United States", "usa", " United States ", "U.S.A."]

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

Cleanliness Score: 40%

Click cleaning operations to fix data defects:

idcustomer nameagecountryspend ($)data status
101Alice Johnson28USA$120Verified Clean
102Bob SmithNULL (MISSING)"usa" (TYPO)$45Dirty Data
103Carol Davis34Canada$210Verified Clean
101Alice Johnson28USA$120Duplicate Row
104David Kim24"united states" (TYPO)$85Dirty 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:

Concept Check 1 of 2
1

You have 10,000 transaction records. Exactly 8 rows are identical duplicates caused by a network retry. What is the correct cleaning action?

Concept Check 2 of 2
2

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:

Enjoying this free lesson? Your support helps us build more free animated chapters.

Support Us