Data Preprocessing: Cleaning, Transforming & Feature Engineering

TAKEAWAYS:

• Dirty data is the enemy: Noise, missing values, and inconsistencies must be handled before analysis (e.g., eSewa’s duplicate transaction records).
• Integration merges sources: Combining transaction logs (Daraz) or sensor data (NTC power grids) requires careful schema alignment.
• Transformation reshapes data: Normalization, aggregation, and encoding (e.g., converting Kathmandu traffic routes into numerical features) unlock patterns.
• Reduction keeps what matters: Dimensionality reduction (PCA) speeds up mining—critical for NEPSE stock trend analysis with 100+ indicators.
• Discretization simplifies: Converting continuous data (e.g., Khalti’s transaction amounts) into bins improves rule clarity.
• Automation is key: Tools like Python’s pandas or SQL’s CASE WHEN handle preprocessing at scale (e.g., Pathao’s ride-demand forecasting).

1. Why Preprocessing? The Dirty Data Problem

Raw data is messy:

• Noise: Typos in customer names (e.g., "Kathmandu" vs. "KathmanduU").
• Missing values: Ncell call-duration records with blank entries.
• Inconsistencies: Dates formatted as DD/MM/YYYY vs. MM-DD-YYYY in bank loans.
• Irrelevant data: A Daraz order’s shipping address may not predict customer churn.

Visual: The preprocessing pipeline (before mining):

flowchart LR
    A["Raw Data\n(e.g., eSewa transactions)"] --> B["Cleaning\n(Handle noise/missing values)"]
    B --> C["Integration\n(Merge datasets)"]
    C --> D["Transformation\n(Scale, encode, aggregate)"]
    D --> E["Reduction\n(Drop/extract features)"]
    E --> F["Discretization\n(Bin continuous data)"]
    F --> G["Ready for Mining\n(e.g., fraud detection)"]

Real-world tie-in:

• eSewa’s fraud detection: Preprocessing flags duplicate payments by cleaning transaction IDs and merging user profiles.
• NTC’s power outage prediction: Sensor data is cleaned (removing spikes), integrated with weather logs, and binned into "high-risk" time slots.

2. Data Cleaning: Fixing the Mess

A. Handling Missing Values

Methods:

Worked Example: Problem: A dataset of Pathao ride requests has 10% missing fare_amount values. The mean fare is ₹120, median is ₹100. Solution:

# Using median (less sensitive to outliers)
df['fare_amount'].fillna(100, inplace=True)

Why median? Pathao’s fares are skewed (some rides are ₹50, others ₹500). Median preserves the central tendency better.

B. Noise Reduction

Techniques:

• Binning: Group continuous values (e.g., age → "18-25", "26-35").
• Smoothing: Replace outliers with percentiles (e.g., cap Ncell call durations at 99th percentile).
• Clustering: Use DBSCAN to detect and remove anomalies (e.g., Daraz orders with impossible shipping times).

Visual: Outlier treatment in NEPSE stock prices:

graph LR
    A["Raw Prices\n(₹100-₹5000)"] --> B["Detect Outliers\n(>₹3000 = 1% of data)"]
    B --> C["Cap at 99th Percentile\n(₹2800)"]
    C --> D["Cleaned Data\n(₹100-₹2800)"]

Real Picture:

3. Data Integration: Merging Datasets

Challenges:

• Schema mismatches: eSewa’s user_id vs. Khalti’s customer_code.
• Temporal gaps: Daraz order data vs. inventory logs.
• Duplicate records: NTC’s power consumption data with the same meter ID.

Solutions:

Worked Example: Problem: Merge Daraz order data (100K rows) with customer reviews (50K rows) to predict churn. Steps:

1. Align keys: Both tables have customer_id but different formats (e.g., "CUST123" vs. "123").
2. Join:

   SELECT o.order_id, c.review_rating, o.amount_spent
   FROM orders o
   INNER JOIN customers c ON o.customer_id = REPLACE(c.customer_id, 'CUST', '')
   WHERE c.review_rating IS NOT NULL;
   

3. Result: 45K merged rows for churn analysis.

4. Data Transformation: Reshaping for Patterns

A. Normalization/Scaling

Why? Algorithms like K-means assume features are on similar scales (e.g., age vs. income). Methods:

Visual: Min-Max scaling on Daraz prices:

graph TD
    A["Raw Prices\n(₹100, ₹5000)"] --> B["Min-Max\n(₹100→0, ₹5000→1)"]
    B --> C["Scaled Data\n(0 to 1)"]

B. Encoding Categorical Data

Problem: Algorithms need numbers (e.g., "Kathmandu" vs. "Lalitpur" as 0/1). Methods:

Worked Example: Problem: Encode customer locations for a Pathao demand prediction model. Solution (One-Hot):

from sklearn.preprocessing import OneHotEncoder
encoder = OneHotEncoder(sparse=False)
locations_encoded = encoder.fit_transform([["Kathmandu"], ["Lalitpur"], ["Bhaktapur"]])
# Output: [[1,0,0], [0,1,0], [0,0,1]]

C. Aggregation

Example: Summarize Ncell call records by hour to predict peak usage.

SELECT
    HOUR(call_time) AS hour_of_day,
    COUNT(*) AS call_count
FROM call_records
GROUP BY HOUR(call_time)
ORDER BY hour_of_day;

Output:

5. Data Reduction: Less Is More

A. Dimensionality Reduction

Goal: Reduce features while retaining 95% variance (e.g., 100 stock indicators → 10). Methods:

Visual: PCA on NEPSE data (3D → 2D):

B. Discretization

Why? Convert continuous data into bins (e.g., "low/medium/high risk"). Example: Bin Khalti transaction amounts into risk categories:

Code:

import pandas as pd
df['risk_level'] = pd.cut(df['amount'],
                          bins=[0, 1000, 5000, float('inf')],
                          labels=['Low', 'Medium', 'High'])

6. Real-World Applications

A. eSewa: Fraud Detection

1. Cleaning: Remove duplicate transactions using GROUP BY transaction_id.
2. Integration: Merge user profiles with payment logs.
3. Transformation: Encode user locations (one-hot) and normalize amounts.
4. Reduction: Use PCA to reduce 20 features to 5 for faster fraud scoring.

B. Daraz: Recommendation System

1. Cleaning: Fill missing product ratings with the user’s average rating.
2. Integration: Join order history with product categories.
3. Transformation: Encode categories (one-hot) and scale prices.
4. Discretization: Bin order amounts into "small/medium/large" for rule mining.

C. NTC: Power Outage Prediction

1. Cleaning: Smooth sensor data with a moving average (remove noise).
2. Integration: Merge weather data with outage logs.
3. Transformation: Encode weather conditions (e.g., "rainy"=1, "sunny"=0).
4. Reduction: Use feature selection to keep only top 10 predictors.

Exam Tip

What examiners test:

1. Definitions: Know the difference between cleaning, integration, and transformation.
2. Methods: Be ready to apply:
   • Missing value techniques (e.g., mean vs. prediction).
   • Encoding schemes (label vs. one-hot).
   • Dimensionality reduction (PCA vs. feature selection).
3. SQL/Python: Expect questions on:
   • Cleaning with COALESCE or fillna().
   • Joins for integration.
   • Aggregation (GROUP BY, COUNT).
4. Real-world links: Relate preprocessing to:
   • Fraud detection (eSewa).
   • Recommendations (Daraz).
   • Predictive maintenance (NTC).
5. Pitfalls: Avoid:
   • Over-cleaning (losing meaningful missingness).
   • Leakage (e.g., using future data to impute missing values).

Sample Exam Question: "A dataset of Pathao rides has missing fare amounts and categorical locations. Describe how you would preprocess it for a churn prediction model. Include SQL/Python snippets for cleaning and encoding."

Model Answer Outline:

1. Cleaning: Handle missing fares with median, remove outliers.
2. Integration: Join ride data with user profiles.
3. Transformation: One-hot encode locations, normalize fare amounts.
4. Reduction: Use PCA if >50 features.
5. Discretization: Bin fares into "low/medium/high" for rule mining.

Key Formula to Remember: For Min-Max Scaling: $$x^{\prime }=\frac{x-\min (X)}{\max (X)-\min (X)}$$

Real Picture: