CH
Coding Hub
Data AnalyticsIntermediate9 min read read

Data Cleaning Techniques: Handling Missing Values & Outliers

A practical guide to data quality, deduplication, imputation, and anomaly detection using Python.

Coding Hub Technical Editorial Team(Software Engineering & Education)
•
Updated 2026-03-15

Overview & Context

Data scientists and analysts routinely report spending up to 80% of their working time finding, cleaning, and organizing messy data. No machine learning algorithm or visual dashboard can overcome inaccurate, duplicate, or corrupted inputs. Master data cleaning techniques to ensure your insights rest on a foundation of verified statistical truth.

What You Will Learn
Understand the direct business costs of dirty data ('Garbage In, Garbage Out')
Learn strategies for handling missing values: dropping vs statistical imputation
Identify and remove duplicate records without losing transactional context
Detect and treat extreme numerical outliers using the Interquartile Range (IQR) method
Table of Contents

1. The 4 Forms of Dirty Data

1. **Missing Data:** Empty cells, null pointers, or sentinel placeholders (like `-999` or `'N/A'`).

2. **Duplicate Records:** Identical transactions submitted multiple times due to network retries.

3. **Inconsistent Formatting:** Mixed date notations (`2026-01-15` vs `15/01/2026`) or mixed categorical text casing (`'Remote'`, `'remote'`, `'REMOTE'`).

4. **Outliers:** Numerical values that deviate so severely from other observations that they distort statistical averages (e.g. entering an age of `200` instead of `20`).

2. Strategies for Handling Missing Values

Deletion vs Imputation

When data is missing, you have two primary options:

- **Listwise Deletion (`dropna()`):** Drop the row. This is safe only when the percentage of missing records is tiny (< 3%) and missing at random.

- **Imputation (`fillna()`):** Fill the missing cell with a calculated substitute. For symmetric numerical data, use the mean. For skewed data with outliers, use the median. For categorical data, use the mode (most frequent item).

3. Identifying & Removing Duplicates

Always verify uniqueness on candidate primary key columns. In Python Pandas, use `df.duplicated()` to inspect matches, and `df.drop_duplicates()` to retain only the first instance.

4. Outlier Detection with the IQR Method

The Interquartile Range (Tukey's Fences)

The Interquartile Range (IQR) measures statistical dispersion, calculated as the difference between the 75th percentile ($Q3$) and the 25th percentile ($Q1$):

$$\text{IQR} = Q3 - Q1$$

Values falling below $Q1 - 1.5 \times \text{IQR}$ or above $Q3 + 1.5 \times \text{IQR}$ are classified as outliers.

5. Complete Python Data Cleaning Script

Here is a complete, production-ready script using Pandas to clean an academic score dataset:

python
import pandas as pd
import numpy as np

# Sample dirty student dataframe
data = {
    "student_id": [101, 102, 103, 103, 104, 105],
    "student_name": ["Alice Smith", "Bob Jones", "Carol White", "Carol White", "David Lee", "Eva Green"],
    "semester_gpa": [8.5, np.nan, 7.8, 7.8, 9.2, 18.5], # 18.5 is an invalid outlier on a 10.0 scale!
    "department": ["Computer Science", "data science", "Data Science", "Data Science", "CS", "Computer Science"]
}

df = pd.DataFrame(data)

# Step 1: Remove duplicate rows
df = df.drop_duplicates()

# Step 2: Standardize categorical text casing and abbreviations
df["department"] = df["department"].str.strip().str.title()
df["department"] = df["department"].replace({"Cs": "Computer Science"})

# Step 3: Handle outliers (cap GPA to maximum valid 10.0)
df.loc[df["semester_gpa"] > 10.0, "semester_gpa"] = np.nan

# Step 4: Impute missing GPA values using the median
median_gpa = df["semester_gpa"].median()
df["semester_gpa"] = df["semester_gpa"].fillna(median_gpa)

print("Cleaned Dataset:")
print(df)
Key note: This script systematically removes duplicates, standardizes department naming, handles scale outliers, and imputes missing GPA values cleanly.

Summary & Key Takeaways

  • 1Always inspect missing data distributions before choosing deletion or imputation.
  • 2Use the median rather than the mean when data contains extreme skewness or outliers.
  • 3Detect numerical outliers using the 1.5 * IQR rule.
  • 4Standardize categorical strings with `.str.strip()` and `.str.title()`.
Frequently Asked Questions

Should I always delete outliers?

No. Never delete outliers blindly. Outliers often represent genuine, critical business events—such as credit card fraud attempts or viral social traffic spikes. Only delete outliers that represent proven data entry or sensor errors.

What is the difference between NaN and Null?

In Python and Pandas, `NaN` (Not a Number) represents missing floating-point numeric data. `None` is Python's native singleton object representing the absence of a value. In SQL, missing values are universally denoted as `NULL`.