The Importance of Data Cleaning

Data cleaning, also known as data cleansing or data preprocessing, is the process of detecting and correcting (or removing) corrupt, inaccurate, or irrelevant records from a dataset. According to multiple studies, data scientists spend 60-80% of their time cleaning and preparing data.

Common Data Quality Issues:
  • Missing Values: NaN, NULL, or empty fields
  • Inconsistent Formatting: Dates, phone numbers, addresses
  • Outliers: Extreme values that skew analysis
  • Duplicates: Repeated records
  • Incorrect Data Types: Numbers stored as strings
  • Structural Errors: Typos, mislabeled categories

Data Scientist Time Allocation

70% Data Cleaning, 30% Analysis & Modeling

Data Quality Assessment

Before cleaning data, we must first assess its quality. Use the interactive table below to identify data quality issues:

ID Name Age Salary Email Join Date Department
1 John Smith NaN 55000 john@company.com 2021-05-15 Marketing
2 Jane Doe 28 62000 jane@company.com 2020-11-03 Engineering
3 Robert Johnson 45 250000 robert.j@company.com 2018-07-22 Sales
4 Mary Williams 32 48000 mary.williams@company 2022-01-14 HR
2 Jane Doe 28 62000 jane@company.com 2020-11-03 Engineering
5 Michael Brown 51 75000 michael@company.com 2015-09-30 engineering
6 Sarah Davis 23 42000 sarah.davis@company.com 03/15/2023 Marketing
Identify Issues:
Assessment Results:

Handling Missing Values

Missing data is one of the most common issues in real-world datasets. The strategy for handling missing values depends on the amount and nature of the missingness.

Deletion

Listwise: Remove entire rows with missing values

Pairwise: Remove only specific missing pairs

Use when: <5% missing, MCAR
Imputation

Mean/Median: For numerical data

Mode: For categorical data

Model-based: KNN, regression, MICE

Use when: 5-30% missing
Prediction

Machine Learning: Predict missing values

Advanced Methods: Deep learning, matrix completion

Use when: >30% missing, complex patterns
Missing Data Simulation

Adjust the parameters to see how different missing data mechanisms affect analysis:

Missing Data Visualization

Original vs Imputed Values
Statistics:

Bias: 0.0

Variance: 0.0

MSE: 0.0

Outlier Detection & Treatment

Outliers are data points that significantly differ from other observations. They can be legitimate (true extreme values) or errors (data entry mistakes).

Detection Methods:
1
Statistical Methods

Z-score: Points beyond ±3 standard deviations

IQR Method: Q1 - 1.5*IQR to Q3 + 1.5*IQR

2
Visual Methods

Box Plots: Visualize distribution and outliers

Scatter Plots: Identify outliers in relationships

3
Machine Learning

Isolation Forest: Tree-based anomaly detection

Local Outlier Factor: Density-based detection

Treatment Strategies:
Removal

Remove outlier records entirely

Careful: May lose information
Capping

Replace with min/max threshold values

Preserves distribution
Transformation

Log, square root, or Box-Cox transforms

Reduces skewness
Binning

Convert to categorical ranges

Simplifies analysis
Outlier Detection Simulation

Generate data with outliers and apply different detection methods:

Results:

Detected outliers: 0

Treatment effect: None

Data Transformation

Transforming data into appropriate formats and scales is crucial for analysis and modeling.

Numerical Transformations:
Method Formula Use Case
Standardization z = (x - μ)/σ Algorithms assuming normality
Normalization x' = (x - min)/(max - min) Neural networks, image data
Log Transform x' = log(x + 1) Right-skewed distributions
Box-Cox x' = (x^λ - 1)/λ General variance stabilization
Categorical Encoding:
One-Hot Encoding Label Encoding Ordinal Encoding Target Encoding Frequency Encoding
Encoding Guidelines:
  • One-Hot: Nominal data, few categories (<10)
  • Label: Tree-based models only
  • Ordinal: Naturally ordered categories
  • Target: High cardinality, avoid data leakage
Transformation Pipeline Builder

Build a data cleaning pipeline by selecting steps:

Data Cleaning Pipeline

Drag and drop steps to build your pipeline
Pipeline Steps:

Hands-On: Complete Data Cleaning Pipeline

In this comprehensive exercise, you'll clean a messy dataset from start to finish. The dataset contains customer information with multiple quality issues.

Complete Data Cleaning Exercise
Step-by-Step Guide:
1
Load & Explore

Understand data structure and issues

2
Missing Values

Impute age using appropriate method

3
Format Standardization

Fix emails, dates, and department names

4
Outlier Treatment

Cap extreme purchase values

5
Categorical Encoding

One-hot encode department

6
Feature Scaling

Scale numerical features appropriately

7
Final Validation

Verify cleaning results

Exercise Controls:
Hints
  • Use df['age'].fillna(df['age'].median(), inplace=True) for missing ages
  • Check emails with df['email'].str.contains('@')
  • For outliers: Calculate Q1, Q3, IQR = Q3 - Q1, then cap values
  • Use pd.get_dummies(df['department']) for one-hot encoding
Output:
Complete Solution:
# COMPLETE SOLUTION
import pandas as pd
import numpy as np
from sklearn.impute import KNNImputer
from sklearn.preprocessing import StandardScaler
import warnings
warnings.filterwarnings('ignore')

# Load data
data = {...}  # Same as above
df = pd.DataFrame(data)

# 1. Handle missing values in age
df['age'].fillna(df['age'].median(), inplace=True)

# 2. Fix email format
df = df[df['email'].str.contains('@', na=False)]

# 3. Standardize join_date format
def fix_date(date_str):
    try:
        if '/' in date_str:
            from datetime import datetime
            return datetime.strptime(date_str, '%m/%d/%Y').strftime('%Y-%m-%d')
        else:
            return date_str
    except:
        return np.nan

df['join_date'] = df['join_date'].apply(fix_date)

# 4. Standardize department names
df['department'] = df['department'].str.title()

# 5. Handle outliers in last_purchase
Q1 = df['last_purchase'].quantile(0.25)
Q3 = df['last_purchase'].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR
df['last_purchase'] = np.where(df['last_purchase'] > upper_bound, upper_bound, 
                               np.where(df['last_purchase'] < lower_bound, lower_bound, 
                                       df['last_purchase']))

# 6. One-hot encode department
df = pd.get_dummies(df, columns=['department'], prefix='dept')

# 7. Scale numerical features
scaler = StandardScaler()
numerical_cols = ['age', 'income', 'satisfaction_score', 'last_purchase']
df[numerical_cols] = scaler.fit_transform(df[numerical_cols])

print("Cleaning completed successfully!")

Module 3 Quiz: Data Cleaning

Test your understanding of data cleaning concepts and best practices.

1. Which missing data mechanism assumes that the probability of missingness depends on observed data but not on the missing values themselves?
MCAR (Missing Completely At Random)
MNAR (Missing Not At Random)
MAR (Missing At Random)
MARX (Missing At Random with eXplanatory)
2. In the IQR method for outlier detection, what is the typical multiplier used for the "whiskers"?
1.0
1.5
2.0
2.5
3. Which of the following is NOT a recommended method for handling missing data when more than 40% of values are missing in a column?
Consider removing the entire column
Use advanced imputation methods like MICE
Treat missingness as a separate category
Fill all missing values with the column mean
4. When should you use one-hot encoding instead of label encoding for categorical variables?
When the categories have no ordinal relationship
When you have high cardinality (>50 categories)
When using tree-based models exclusively
When you want to minimize dimensionality
5. What is the primary purpose of feature scaling in data preprocessing?
To reduce the dataset size
To handle missing values
To ensure features contribute equally to distance-based algorithms
To remove outliers from the data
6. Which transformation is most appropriate for handling right-skewed data with zeros?
Standardization (z-score)
Log transformation with a small constant
Min-max normalization
Box-Cox transformation without modification
7. What is the main risk of using mean imputation for missing values?
It increases computational complexity
It requires too much domain knowledge
It only works for normally distributed data
It reduces variance and distorts relationships
Your Score: 0/7
Detailed Explanations:
  1. MAR (Missing At Random): The missingness depends on observed data but not on the missing values themselves, making it easier to handle than MNAR.
  2. 1.5: The standard IQR method uses 1.5×IQR to define outliers. Values beyond Q1 - 1.5×IQR or Q3 + 1.5×IQR are considered outliers.
  3. Fill all missing values with the column mean: When too much data is missing, mean imputation can severely distort the data distribution and relationships.
  4. When categories have no ordinal relationship: One-hot encoding avoids imposing artificial ordinal relationships that don't exist.
  5. Ensure features contribute equally: Algorithms like KNN and SVM use distance metrics, so features on different scales would dominate.
  6. Log transformation with a small constant: Log(x+1) or log(x+ε) handles zeros while reducing right skewness.
  7. Reduces variance and distorts relationships: Mean imputation artificially reduces variance and can distort correlations between variables.

Data Cleaning Resources & Tools

Python Libraries
  • pandas: df.dropna(), df.fillna(), df.replace()
  • numpy: np.nan, np.isnan(), np.clip()
  • scikit-learn: SimpleImputer, StandardScaler, OneHotEncoder
  • feature-engine: Advanced transformers for missing data, outliers, encoding
  • pyjanitor: Clean API for data cleaning pipelines
Recommended Reading
Data Cleaning Checklist

Use this comprehensive checklist for your data cleaning projects:

Download Resources
Cleaning Templates

Python templates for common cleaning tasks

Cheat Sheet

PDF reference for data cleaning methods

Sample Datasets

Practice datasets with various quality issues