Data Preprocessing & Cleaning: Techniques, Challenges & Real-World Impact

Core Concepts & Definitions

1. What is Data Preprocessing?

Data preprocessing transforms raw data into a format suitable for mining. It includes:

• Data cleaning (handling noise, missing values)
• Data integration (merging multiple sources)
• Data transformation (normalization, discretization)
• Data reduction (dimensionality reduction, sampling)

flowchart LR
    A["Raw Data"] --> B["Data Cleaning"]
    B --> C["Data Integration"]
    C --> D["Data Transformation"]
    D --> E["Data Reduction"]
    E --> F["Mined Data"]

Why it matters: Dirty data leads to wrong insights. For example, eSewa’s fraud detection fails if transaction timestamps are inconsistent.

2. Data Cleaning Techniques

A. Handling Noisy Data

Noisy data = incorrect values due to measurement errors or data entry mistakes. Methods:

Worked Example: Binning for Daraz’s Recommendations Suppose Daraz tracks user purchase amounts (in $): [50, 120, 80, 300, 45, 600, 200] Step 1: Define bins (equal-width):

• Bin 1: 0–100
• Bin 2: 101–300
• Bin 3: 301–600 Step 2: Replace values: [50→Bin1, 120→Bin2, 80→Bin1, 300→Bin2, 45→Bin1, 600→Bin3, 200→Bin2] Outcome: Daraz can now recommend products based on spending tiers instead of raw amounts.

B. Handling Missing Values

Causes: Unrecorded data (e.g., a user skips a survey question) or system errors. Methods:

Worked Example: Missing Values in Ncell’s Customer Data Dataset:

Step 1: Check correlation. Data_Usage_GB vs. Churned shows users with >6GB rarely churn. Step 2: Impute missing value:

• Mean of non-churned users = (5.2 + 8.1)/2 = 6.65 → Assign 6.65 to User 102. Outcome: Ncell can now analyze churn risk accurately.

C. Data Transformation

1. Normalization

Convert data to a common scale (e.g., 0–1 or -1 to 1). Formulas:

• Min-Max: $x^{\prime }=\frac{x-\text{min}}{\text{max}-\text{min}}$
• Z-Score: $x^{\prime }=\frac{x-\mu }{\sigma }$

Example: Comparing Daraz’s product ratings (1–5 stars) with price ranges ($10–$500).

• Original: [4, 5, 1, 3] (ratings), [200, 500, 10, 150] (prices).
• Min-Max normalized: Ratings: [0.67, 1.0, 0.0, 0.33] Prices: [0.33, 1.0, 0.0, 0.25]

Why? Algorithms like K-means perform better with normalized data.

2. Discretization

Convert continuous data into categories (bins). Methods:

• Equal Width: Fixed range (e.g., 0–10, 10–20).
• Equal Frequency: Equal number of data points per bin.
• Cluster-Based: Use K-means to define bins.

Example: NTC’s electricity usage (kWh/day): Raw data: [50, 120, 80, 300, 45, 600, 200] Equal Width (3 bins):

• Bin 1: 0–150 → [50, 120, 80, 45]
• Bin 2: 151–350 → [300, 200]
• Bin 3: 351–600 → [600]

Outcome: NTC can now target "High Usage" customers (Bin 3) for dynamic pricing.

D. Data Reduction

1. Dimensionality Reduction

Reduce number of attributes to improve efficiency. Methods:

• PCA (Principal Component Analysis): Linear transformation.
• Feature Selection: Choose top k features (e.g., using correlation).
• Sampling: Randomly select a subset of data.

Example: eSewa’s fraud detection uses 100+ transaction features. PCA reduces it to 10 key components without losing 90% of variance.

2. Sampling

• Random Sampling: Simple but may miss trends.
• Stratified Sampling: Ensure representation of all classes (e.g., 60% normal users, 40% fraudsters in eSewa data).

3. Data Integration

Combine data from multiple sources (e.g., Daraz’s orders + user profiles). Challenges:

• Schema conflicts: Different attribute names (e.g., Age vs. Customer_Age).
• Inconsistent data: Same product listed as "iPhone 13" and "iPhone-13". Solutions:
• Use ETL tools (Extract, Transform, Load) like Talend or Apache Nifi.
• Apply data matching (e.g., fuzzy matching for names).

Example: Merging Khalti’s transaction logs with user demographic data to predict spending habits.

4. Data Preprocessing Workflow

flowchart TD
    A["Raw Data"] --> B["Data Cleaning\n(Noise, Missing Values)"]
    B --> C["Data Integration\n(Merge Sources)"]
    C --> D["Data Transformation\n(Normalization, Discretization)"]
    D --> E["Data Reduction\n(PCA, Sampling)"]
    E --> F["Mined Data\n(Ready for Analysis)"]

In the Real World

1. eSewa’s Fraud Detection

   • Idea Used: Data Cleaning (Outlier Detection) + Discretization
   • How: eSewa flags transactions where the amount deviates >3σ from a user’s typical spending (e.g., a user who usually pays $50 flags a $5,000 transfer). Discretization bins amounts into "Low," "Medium," "High" risk categories.

2. Daraz’s Recommendation Engine

   • Idea Used: Normalization + Clustering (K-means)
   • How: Daraz normalizes user purchase amounts (0–1 scale) and clusters them into segments (e.g., "Budget Buyers," "Luxury Shoppers"). Recommendations are then tailored to each cluster.

3. NTC’s Smart Metering

   • Idea Used: Time-Series Imputation + Binning
   • How: If a smart meter misses a day’s reading, NTC interpolates using the average of the previous and next day. Binning then categorizes usage into "Peak," "Off-Peak," and "Critical" for dynamic pricing.

4. Pathao’s Driver Routing

   • Idea Used: Data Integration + Missing Value Imputation
   • How: Pathao merges real-time traffic data (from GPS) with historical route data. If a driver’s location is missing for 2 minutes, it’s imputed using the last known speed and direction. This ensures accurate ETA calculations.

5. Nepal Rastra Bank’s Loan Approval

   • Idea Used: Discretization + Feature Selection
   • How: Loan applicants’ credit scores (continuous) are binned into "Poor," "Fair," "Good," "Excellent." Only the top 3 features (score, income, loan history) are used to reduce computation time.

Exam Tip

What Examiners Love to Test

1. Algorithm Steps: Be ready to show K-means++ initialization or binning with exact calculations (e.g., "After 2 iterations, centroids move to (175, 65) and (185, 75)").
2. Real-World Mapping: Always relate preprocessing to a Nepali app (e.g., "How would Daraz handle missing product ratings?").
3. Definitions with Examples:
   • "Discretization is converting continuous data to bins. Example: NTC’s electricity usage binned into ‘Low,’ ‘Medium,’ ‘High’ for pricing."
4. Comparison Tables: Memorize the pros/cons of smoothing methods (e.g., "Binning loses granularity but is simple").
5. Missing Data Scenarios: Practice imputing values in small datasets (3–5 rows) using mean/median/mode.

Common Pitfalls

• Ignoring Normalization: Forgetting to scale data before K-means leads to incorrect clusters.
• Over-Smoothing: Replacing all outliers with mean/median may hide genuine trends (e.g., a sudden spike in Khalti transactions could indicate a new promotion).
• Vague Bins: Using bins like "Low," "Medium," "High" without defining ranges (e.g., "Low = 0–100 kWh" vs. "Low = anything < average").

Marks Boosters

• Draw a Diagram: For K-means iterations, show the data points and centroid movements.
• Use Real Numbers: Examiners reward concrete examples (e.g., "For Daraz’s dataset, the mean purchase amount is $120").
• Link to Applications: Every answer should end with a Nepali app example (e.g., "This technique is used by eSewa to detect fraudulent transactions").

In the real world

• eSewa: Uses normalization and missing value imputation to clean transaction data before detecting fraud. For example, if a user’s transaction amount is missing, it fills it with the average amount for that user’s typical spending pattern (e.g., $200 for a regular user).
• NTC: Applies binning to electricity usage data to categorize customers into tiers (low, medium, high) for dynamic pricing. This helps them target high-usage customers (e.g., those using >350 kWh/day) with time-of-use discounts.
• Daraz: Employs data integration by merging order data with user profiles to personalize recommendations. For instance, if a user frequently buys electronics, Daraz’s system combines their purchase history with product reviews to suggest relevant items.