Data Cleaning: Purification & Preparation

1. Introduction: Transforming Raw Noise into Machine Learning Fuel

How accurately a machine learning model generalizes in production depends heavily—often up to 80%—on the hygiene and integrity of its training data. In real-world enterprise environments, raw data arrives messy and corrupted. Network timeouts, human typing blunders, database migration glitches, and inconsistent API schemas inevitably contaminate incoming datasets.

Consider an e-commerce sales dataset analyzed by Byteion. Upon initial inspection, several severe issues emerge:

• Several premium laptop entries have missing (None / NaN) prices.
• Network retries caused identical customer transactions to be recorded twice.
• Ordered quantity is represented inconsistently as numbers ("2") and words ("two").
• A single product category is recorded inconsistently as "Fashion", "fashion", and whitespace-padded " Fashion ".

Passing such corrupted records directly to a machine learning estimator triggers either outright runtime exceptions (ValueError: Input contains NaN) or causes the model to learn distorted, biased relationships. The systematic engineering methodology developed to remediate these defects is Data Cleaning.

Crucially, data cleaning does not mean deleting all unusual observations. Outliers frequently represent legitimate, high-value phenomena (such as large enterprise bulk purchases or credit card fraud). Every modification or record removal must be anchored in clear domain reasoning.

2. The 6 Core Dimensions of Data Quality

In enterprise data engineering, datasets are evaluated against 6 standardized dimensions of quality:

3. The 5 Core Pathologies of Data Cleaning

Data cleaning workflows primarily address five recurring categories of data corruption:

4. Missing Values: Statistical Character & Imputation

Before selecting an imputation strategy, you must diagnose why the data is missing. Modern mathematical statistics establishes three distinct mechanisms of missingness:

4.1 Auditing Missing Data in pandas

import pandas as pd
import numpy as np

# Audit raw counts and percentage missing per column
print(df.isna().sum())
missing_pct = (df.isna().mean() * 100).round(2)
print(missing_pct)

4.2 When is Row Deletion (dropna) Justified?

Dropping rows via df.dropna() or df.dropna(subset=['price']) is safe only when:

• Missing rows comprise less than 3% to 5% of the total dataset.
• The missingness mechanism is verified to be MCAR (Completely At Random).
• Otherwise, dropping incomplete rows substantially reduces statistical power and skews model representations.

4.3 Imputation Strategies

5. Duplicate Records: Composite Keys & Smart Filtering

Duplicate rows harm predictive models by artificially doubling the gradient weight of repeated instances during optimization.

# 1. Audit total duplicate rows
print("Total Duplicates:", df.duplicated().sum())

# 2. View all duplicate pairs simultaneously
print(df[df.duplicated(keep=False)])

# 3. Deduplicate across a composite key, preserving the first instance
df_cleaned = df.drop_duplicates(subset=["order_id", "product", "price"], keep="first")

6. Inconsistent Data & Text Sanitization

To a machine, "Fashion", "fashion", and whitespace-padded " Fashion " are three completely distinct entities. The standard text cleaning pipeline involves:

# 1. Strip leading and trailing whitespace
df["category"] = df["category"].astype("string").str.strip()

# 2. Normalize casing to lowercase
df["category"] = df["category"].str.lower()

# 3. Map synonym variants to a canonical token
mapping = {
    "accessory": "accessories",
    "accessories": "accessories",
    "electronics": "electronics",
    "fashion": "fashion"
}
df["category"] = df["category"].map(mapping)

6.1 Sanitizing Formatted Currency Strings

When numerical prices arrive formatted as "55,000 BDT", direct float casting throws errors. We strip commas and units, then call pd.to_numeric(errors='coerce'):

df["price"] = (
    df["price"]
    .astype("string")
    .str.replace(",", "", regex=False)
    .str.replace("BDT", "", regex=False)
    .str.strip()
)
# errors="coerce" transforms unparseable entries (e.g., "N/A") cleanly into NaN
df["price"] = pd.to_numeric(df["price"], errors="coerce")

7. Data Type Casting & Domain Validation

Strict data typing enables temporal extraction and prevents silent mathematical degradation:

8. Outlier Remediation: Tukey Fences & Winsorization

Engineers utilize three primary strategies for handling outliers:

Q1 = df["quantity"].quantile(0.25)
Q3 = df["quantity"].quantile(0.75)
IQR = Q3 - Q1
lower_fence = Q1 - 1.5 * IQR
upper_fence = Q3 + 1.5 * IQR

# Capping: Clamping extreme values to the Tukey fences without deleting rows
df["quantity_capped"] = np.clip(df["quantity"], lower_fence, upper_fence)

9. Data Leakage: The Cardinal Sin in Machine Learning

During data cleaning and preprocessing, the most dangerous failure mode is Data Leakage.

10. Complete Practical Python Demonstration

Here is a complete, production-grade pandas data cleaning pipeline:

import pandas as pd
import numpy as np

# 1. Synthesize noisy real-world dataset
df = pd.DataFrame({
    "order_id": ["O01", "O02", "O03", "O03", "O04", "O05"],
    "category": [" Electronics ", "electronics", "Accessories", "Accessories", "FASHION", "Fashion"],
    "price": ["55,000", "22,000", "2,500", "2,500", "1,800 BDT", "N/A"],
    "quantity": ["1", "2", "3", "3", "two", "4"],
    "order_date": ["2026-09-01", "2026-09-02", "2026-09-03", "2026-09-03", "invalid_date", "2026-09-05"]
})

# 2. Store pristine raw backup
raw_backup = df.copy(deep=True)

# 3. Text sanitization (whitespace stripping and lowercasing)
df["category"] = df["category"].astype("string").str.strip().str.lower()

# 4. Numeric field sanitization
df["price"] = df["price"].astype("string").str.replace(",", "", regex=False).str.replace("BDT", "", regex=False).str.strip()
df["price"] = pd.to_numeric(df["price"], errors="coerce")
df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")

# 5. Timestamp conversion
df["order_date"] = pd.to_datetime(df["order_date"], format="%Y-%m-%d", errors="coerce")

# 6. Deduplication on composite keys
df = df.drop_duplicates(subset=["order_id", "category", "price"], keep="first")

# 7. Imputation using robust medians
df["price"] = df["price"].fillna(df["price"].median())
df["quantity"] = df["quantity"].fillna(df["quantity"].median())

print("\n--- Cleaned DataFrame ---")
print(df)

11. Summary of Key Learnings