Module 4: Data Cleaning and Preprocessing

"Data scientists spend 80% of their time cleaning data, and the other 20% complaining about cleaning data."

Learning Objectives

By the end of this module, you will be able to:

• Identify and handle missing values using multiple strategies
• Detect and treat outliers using IQR and Z-score methods
• Convert data types and standardize formats across a dataset
• Clean messy string data (whitespace, casing, typos, patterns)
• Remove duplicates and validate data integrity
• Build a reusable data cleaning pipeline

Why Data Cleaning Matters

Building analysis on dirty data is like painting a cracked wall — the finish looks fine until it peels. Real data has typos, mixed date formats, sentinel values like 999, and partial exports.

1. Handling Missing Values

Detecting Missing Values

Use .isnull() / .isna() counts per column, percent missing, and boolean masks for “rows with any gap.” Missingness patterns sometimes tell a story (e.g. optional survey fields).

Strategy 1: Drop Missing Values

dropna() with how="all" vs any, subset=[cols], or thresh= to keep columns that are “mostly full.” Dropping is honest when data is MCAR and you have volume — destructive when rare events matter.

Strategy 2: Fill Missing Values

fillna(constant), mean/median for numeric (mind skew!), mode for categories, ffill/bfill for time series. Filling is a modeling choice, not a neutral button.

Strategy 3: Interpolation

interpolate() estimates between known points — useful for ordered data; risky for categories.

2. Detecting and Handling Outliers

Outliers can be errors (negative age) or signal (CEO salary). Always investigate before deleting.

IQR Method (Interquartile Range)

Compute Q1, Q3, IQR = Q3 − Q1. Fences at Q1 − 1.5×IQR and Q3 + 1.5×IQR. Robust to skew because it uses middle half of data.

Z-Score Method

((x - \mu) / \sigma). Flag (|z| > 3) (rule of thumb). Sensitive to extreme values that inflate σ — pair with judgment.

Handling Outliers

3. Data Type Conversion

pd.to_numeric(..., errors="coerce") turns garbage into NaN instead of crashing. pd.to_datetime parses mixed date strings. ZIP codes should often stay strings (leading zeros). Booleans from "yes"/"no" need explicit mapping.

4. String Cleaning

Strip whitespace, normalize case, collapse repeated spaces with regex, standardize phone formats. .str accessor in Pandas applies string ops column-wide without Python loops.

Advanced String Operations

Extract with patterns, replace substrings, split into multiple columns when one field encodes several facts.

5. Removing Duplicates

duplicated() finds repeated rows; drop_duplicates() keeps first/last. Specify subset=[keys] when only certain columns define identity (e.g. customer_id + date).

6. Data Validation

Rules like: age ∈ [0, 150], email contains @, amounts ≥ 0. Schema tools (Great Expectations, pydantic) scale this; in small projects, boolean masks + a log of failing row IDs suffice.

Quarantine pattern: keep two DataFrames — clean (passes all rules) and rejects (with a failure_reason column). Stakeholders trust you when bad rows are visible, not silently dropped.

7. Complete Data Cleaning Pipeline

Order that usually works:

1. Load and copy data
2. Standardize strings and categories
3. Coerce types with safe converters
4. Handle missing (strategy per column)
5. Dedupe
6. Validate; quarantine bad rows
7. Document assumptions in comments or a short README

Key Example: One function-style flow: coerce salary from messy strings, drop impossible ages, dedupe on name+email.

[object Object], pandas ,[object Object], pd
,[object Object], numpy ,[object Object], np

df = pd.DataFrame({
    ,[object Object],: [,[object Object],, ,[object Object],, ,[object Object],, ,[object Object],],
    ,[object Object],: [,[object Object],, ,[object Object],, ,[object Object],, ,[object Object],],
    ,[object Object],: [,[object Object],, ,[object Object],, ,[object Object],, ,[object Object],],
})
df[,[object Object],] = df[,[object Object],].,[object Object],.strip().,[object Object],.title()
df[,[object Object],] = (
    df[,[object Object],].,[object Object],.replace(,[object Object],, ,[object Object],, regex=,[object Object],)
    .pipe(pd.to_numeric, errors=,[object Object],)
)
df = df[df[,[object Object],].between(,[object Object],, ,[object Object],)]
df = df.drop_duplicates(subset=[,[object Object],, ,[object Object],], keep=,[object Object],)

Practice Exercises

Exercise 1: Missing Value Strategy (Beginner)

Given a small dataframe, report % missing per column and apply two different fill strategies; compare summary stats.

Exercise 2: Outlier Detection Report (Intermediate)

Use IQR and Z-score on the same numeric column; list which rows each method flags and discuss differences.

Exercise 3: Address Standardization (Intermediate)

Normalize messy addresses: strip, title case, collapse spaces, standardize state abbreviations.

Exercise 4: Full Pipeline Challenge (Advanced)

End-to-end: load CSV, clean types, handle outliers, validate, output clean CSV + rejected rows log.

Exercise 5: Data Quality Scoring (Advanced)

Compute a 0–100 quality score per dataset from completeness, validity, and duplicate rate.

Mini-Project: Data Quality Dashboard

Notebook or script that profiles a dataset: missing heatmap concept (table), duplicate counts, outlier counts, validation pass rate, and a one-page summary for stakeholders.

Key Takeaways

1. Missing data strategy is a modeling choice — document it.
2. Investigate outliers before deleting; sometimes they’re the story.
3. to_numeric/to_datetime with errors="coerce" save your sanity.
4. Dedupe on business keys, not accidental row identity.
5. Validation turns “trust me” into checkable rules.

Resources for Further Learning

• Pandas: Working with missing data
• Great Expectations — data validation framework
• Janitor (pyjanitor) — cleaning helpers

Next up: Module 5 — SQL for Data Analysis — extracting and shaping data where it lives.