Data Cleaning Mastery

In This Section, You Will Learn:

• • Why data cleaning is the most important skill
• • Finding and handling missing values
• • Detecting and removing duplicates
• • Fixing data types and formats
• • Handling outliers
• • Common cleaning patterns with AI help

Why Data Cleaning is 80% of the Job

• The Reality of Real-World Data:
• • Missing values everywhere
• • Duplicate records
• • Inconsistent formats (Lahore vs lahore vs LAHORE)
• • Wrong data types (numbers stored as text)
• • Errors and typos
• • Outliers (someone entered age as 500)
• The Famous Quote:
• 'Garbage In, Garbage Out'
• If your data is dirty, your analysis will be wrong!
• The Good News:
• • Cleaning skills are highly valued
• • AI can help automate routine cleaning
• • Once mastered, it becomes fast and easy

Finding and Handling Missing Values

• Finding Missing Values:
• df.isnull().sum() → Count missing values per column
• df.info() → See non-null counts
• Strategies for Missing Values:
• 1. Remove rows with missing values:
• df.dropna() → Remove all rows with any missing value
• Use when: Very few missing values (<5%)
• 2. Fill with a specific value:
• df['column'].fillna(0) → Fill with zero
• df['column'].fillna('Unknown') → Fill with text
• 3. Fill with statistics:
• df['column'].fillna(df['column'].mean()) → Fill with average
• df['column'].fillna(df['column'].median()) → Fill with median
• Use when: Many missing values, don't want to lose rows
• 4. Forward/Backward fill:
• df['column'].fillna(method='ffill') → Use previous value
• Use when: Time series data

Detecting and Removing Duplicates

• Finding Duplicates:
• df.duplicated().sum() → Count duplicate rows
• df[df.duplicated()] → See the duplicate rows
• Removing Duplicates:
• df.drop_duplicates() → Remove exact duplicate rows
• df.drop_duplicates(subset=['email']) → Remove based on one column
• df.drop_duplicates(keep='last') → Keep last occurrence instead of first
• When to Check for Duplicates:
• • After combining multiple data sources
• • When data might have been entered twice
• • Before calculating counts or sums
• Example:
• If a customer appears twice, their total purchases will be counted twice!

Fixing Data Types and Formats

• Common Data Type Issues:
• • Numbers stored as text: '1000' instead of 1000
• • Dates stored as text: '2024-01-15' as string
• • Inconsistent categories: 'Male', 'male', 'M', 'MALE'
• Fixing Data Types:
• df['price'] = df['price'].astype(float) → Convert to number
• df['date'] = pd.to_datetime(df['date']) → Convert to date
• Standardizing Text:
• df['city'] = df['city'].str.lower() → All lowercase
• df['city'] = df['city'].str.strip() → Remove extra spaces
• df['city'] = df['city'].str.title() → Title Case
• Mapping Values:
• df['gender'] = df['gender'].replace({'M': 'Male', 'm': 'Male', 'MALE': 'Male'})

Handling Outliers

• What are Outliers?
• • Extreme values that don't fit the pattern
• • Age: 500 years (obviously wrong)
• • Price: -100 (negative price?)
• • Order: 1,000,000 items (typo?)
• Finding Outliers:
• df.describe() → Check min/max values
• df['column'].plot(kind='box') → Visual outlier detection
• Handling Outliers:
• Option 1: Remove them
• df = df[df['age'] < 120] → Keep only realistic ages
• Option 2: Cap them
• df['age'] = df['age'].clip(upper=100) → Cap at 100
• Option 3: Investigate
• • Sometimes outliers are real (e.g., rich customer)
• • Ask the business before removing!

Your Assignment: Clean a Real Dataset

• Practice Exercise:
• 1. Download a sample CSV (Kaggle has free datasets)
• 2. Load into Google Colab using pd.read_csv()
• 3. Check for missing values: df.isnull().sum()
• 4. Check for duplicates: df.duplicated().sum()
• 5. Check data types: df.info()
• 6. Fix any issues you find
• 7. Save the clean data: df.to_csv('clean_data.csv')
• Ask AI for help:
• 'I have a CSV with 10,000 sales records. Write Python code to clean it: handle missing values, remove duplicates, and fix any data type issues.'