Data Preprocessing: Cleaning, Integration, Transformation & Reduction

What is Data Preprocessing?

Data preprocessing is the first and most crucial step in data mining and warehousing. It involves preparing raw data to make it consistent, accurate, and useful for analysis. Without preprocessing, even the best algorithms will fail because:

• Raw data is messy (missing values, noise, inconsistencies).
• Different sources (e.g., eSewa transactions vs. Daraz orders) use different formats.
• Business questions require specific transformations (e.g., converting dates to quarters for NEPSE stock analysis).

Key Idea:

"Garbage in, garbage out (GIGO)." Preprocessing ensures the input data is high-quality, so mining results are reliable.

Why Preprocess Data? (Real-World Pain Points)

1. eSewa’s Failed Payments

eSewa processes millions of transactions daily, but:

• Inconsistent formats: Some users enter dates as 2023/12/31, others as 31-12-2023.
• Missing data: Phone numbers may be empty for some users.
• Noise: Typos in merchant IDs (e.g., MERCH123 vs. MERCH_123). Result: Without preprocessing, eSewa’s fraud detection model would flag false positives, hurting user trust.

2. Daraz’s Out-of-Stock Errors

Daraz’s inventory system suffers from:

• Duplicate entries: Same product listed twice due to manual uploads.
• Incomplete data: Some listings lack price or stock_quantity.
• Dirty data: Product names like "iPhone 13 Pro (128GB) [Old Stock]" vs. "iPhone 13 Pro 128GB". Result: Customers see "Out of Stock" even when items are available, increasing cart abandonment.

3. NTC’s Traffic Route Optimization

NTC uses GPS data from buses to optimize routes, but:

• Missing timestamps: Some buses don’t log exact departure times.
• Sensor noise: GPS coordinates jump due to signal drops.
• Inconsistent units: Speed recorded in km/h and m/s in different logs. Result: Without cleaning, the route-planning algorithm would suggest inefficient paths, wasting fuel.

The 4 Key Steps of Data Preprocessing

Preprocessing follows a pipeline (visualized below). Each step refines data for the next.

flowchart LR
    A["Raw Data"] --> B["Data Cleaning"]
    B --> C["Data Integration"]
    C --> D["Data Transformation"]
    D --> E["Data Reduction"]
    E --> F["Cleaned Data Ready for Mining"]

Step 1: Data Cleaning (Fixing Errors)

Definition: Removing or correcting inaccuracies, inconsistencies, and missing values in data. Why? Dirty data leads to wrong conclusions. Example: If NEPSE’s stock data has missing close_price values, trend analysis fails.

Common Cleaning Techniques

Worked Example: Cleaning eSewa Transaction Data

Dirty Data Sample:

Steps:

1. Standardize dates: Convert all to YYYY-MM-DD (ISO format).
   • 2023/12/31 → 2023-12-31
   • 31-12-2023 → 2023-12-31
2. Handle missing Amount: Impute with median amount (e.g., 750 NPR).
3. Fix Merchant_ID: Standardize to MERCH123 (remove underscores).
4. Handle missing User_Phone: Remove the row (critical for fraud detection).

Cleaned Data:

Step 2: Data Integration (Combining Sources)

Definition: Merging data from multiple sources (e.g., eSewa transactions + bank records) into a unified view. Why? Business decisions need holistic data. Example: Ncell wants to analyze customer churn but needs both call logs and billing data.

Integration Techniques

Worked Example: Merging Ncell and NTC Data

Goal: Analyze bus route delays vs. Ncell network drops in Kathmandu. Data Sources:

1. NTC Bus GPS Logs (timestamps, location, bus ID).
2. Ncell Network Tower Data (call drops, signal strength).

Steps:

1. Key Alignment: Both datasets have timestamp and location (latitude/longitude).
2. Join on location: For each bus stop, check if Ncell had drops.
3. Result: A new table showing delays correlated with network issues.

erDiagram
    BUS_STOP ||--o{ GPS_LOG : "has"
    BUS_STOP ||--o{ NETWORK_DROP : "has"
    GPS_LOG {
        timestamp string
        bus_id string
        delay_minutes int
    }
    NETWORK_DROP {
        timestamp string
        location string
        drop_count int
    }

Step 3: Data Transformation (Structuring Data)

Definition: Converting data into forms suitable for mining. Includes:

• Aggregation (summarizing data).
• Normalization (scaling values).
• Discretization (converting continuous to categories).

Common Transformations

Worked Example: Transforming NEPSE Stock Data

Raw Data:

Steps:

1. Discretize Close price:

   • Low: < 2530
   • Medium: 2530–2570
   • High: > 2570 → 2023-01-01 → Medium, 2023-01-02 → High.

2. Normalize Volume (if available):

   • Original: Volume = [1000000, 1500000]
   • Normalized: (x - min) / (max - min) → 1000000 → 0, 1500000 → 1.

3. Aggregate to weekly trends:

   • Calculate avg_close_price per week.

Step 4: Data Reduction (Dimensionality Reduction)

Definition: Reducing data size or dimensions while preserving key information. Why? Large datasets slow down mining. Example: Pathao’s GPS logs have 100+ columns, but only a few affect traffic predictions.

Techniques

Worked Example: Reducing Daraz Order Data

Original Dataset: 100 columns (user_id, product_id, price, category, reviews, etc.). Goal: Predict customer churn (will they return?).

Steps:

1. Attribute Subset Selection:

   • Keep only:
     • total_spend (last 3 months)
     • avg_order_value
     • days_since_last_order
     • category_preferences (e.g., electronics vs. groceries).

2. Numerosity Reduction:

   • Original: 1M orders.
   • Sample: 100K orders (randomly selected).

3. Result: Faster training for churn prediction models.

Real-World Applications in Nepal

Common Pitfalls and How to Avoid Them

1. Over-Cleaning:

   • Problem: Removing too much data (e.g., deleting all rows with missing age).
   • Fix: Use imputation (fill missing values) instead of deletion.

2. Incorrect Joins:

   • Problem: Joining tables on wrong keys (e.g., user_id vs. phone_number).
   • Fix: Always verify primary/foreign key relationships.

3. Loss of Information:

   • Problem: Discretizing age into Young/Middle/Aged loses granularity.
   • Fix: Use bin sizes wisely (e.g., 10-year ranges).

4. Scaling Issues:

   • Problem: Normalizing price (range: 10–1,000,000) with age (18–80) distorts data.
   • Fix: Normalize per feature separately.

Exam Tip: How This Unit is Tested

1. Theory Questions (30%):

   • Define data cleaning, integration, transformation, and reduction.
   • Explain why preprocessing is essential (use the GIGO principle).
   • Compare imputation vs. deletion for missing values.

2. Scenario-Based Questions (40%):

   • Given a dirty dataset, describe steps to clean/transform it. Example: "eSewa’s transaction data has inconsistent dates and missing amounts. How would you preprocess it?"
   • Design a preprocessing pipeline for a real-world case (e.g., NEPSE stock analysis).

3. Practical Questions (30%):

   • SQL queries to clean data (e.g., UPDATE, COALESCE for missing values).
   • Python/Pseudocode for transformations (e.g., normalization, discretization).
   • Interpretation: Given a transformed dataset, explain how it improves mining results.

High-Scoring Answers Include:

✅ Step-by-step reasoning (e.g., "First clean missing values, then standardize dates..."). ✅ Real-world examples (tie answers to eSewa, Daraz, NEPSE, etc.). ✅ Visuals (show a preprocessing pipeline or data cleaning steps). ✅ Pros/Cons (e.g., "Imputation preserves data size but may introduce bias.").

Summary Checklist

Before mining, ensure your data is: ✔ Clean: No missing/noisy values. ✔ Integrated: Merged from all sources. ✔ Transformed: Formatted for analysis (e.g., dates as numbers). ✔ Reduced: Only essential features/rows.

Final Thought:

"Preprocessing is like preparing ingredients before cooking. Even the best chef can’t make a great dish with rotten tomatoes!" — Adapted from data mining wisdom.