Lecture 02: Data Preprocessing & Cleansing

Course: Introduction to Data Analytics (CE KMITL)
Lecturer: Asst. Prof. Dr. Rathachai Chawuthai, Department of Computer Engineering, Faculty of Engineering
Source Slide Deck: DA04 - Data Processing.pdf

1. Overview of Data Quality and Preprocessing

1.1 The Reality of Dirty Data

In applied machine learning and data engineering, raw data collected from physical systems, transactional databases, and human inputs is rarely clean, complete, or directly usable.

"Data cleaning is one of the three biggest problems in data warehousing." 
— Ralph Kimball

Industrial surveys consistently indicate that data scientists spend between 70% to 80% of their total project time on data collection, cleansing, integration, and feature transformation.

       +-------------------------------------------------------+
       |   Typical Distribution of Data Science Project Time   |
       +-------------------------------------------------------+
       | [Data Cleansing & Preprocessing: ~75%]  | [Model: 25%]|
       +-------------------------------------------------------+

1.2 Common Sources of Data Imperfections

Data quality issues stem from three primary failure modes:

1. Incomplete Data:
   • Attributes with missing values ($n/a$, null, or blank cells).
   • Caused by equipment malfunction, voluntary survey omissions, differing data collection schemas across historical eras, or software bugs during recording.
2. Noisy Data:
   • Measurements containing random errors, sensor drift, or extreme variance.
   • Caused by faulty instrument calibration, transmission corruption, human typographical entry errors, or optical character recognition (OCR) glitches.
3. Inconsistent Data:
   • Contradictory values or schema mismatches across records.
   • Caused by integrating disparate databases (e.g., recording age as "28" but birth date as "1980-01-01"), functional dependency violations, or conflicting naming conventions.

1.3 Core Data Preprocessing Tasks

To transform raw, imperfect inputs into reliable training representations, the data preprocessing phase is partitioned into five distinct sub-tasks:

2. Data Cleansing

2.1 Handling Missing Data

When datasets contain empty or unrecorded values, practitioners must select an appropriate handling strategy based on the proportion of missing data and the underlying mechanism of missingness.

Comparison of Missing Data Strategies

2.2 Noisy Data and Outlier Detection

An outlier is an observation that deviates so substantially from other observations as to arouse suspicion that it was generated by a different mechanism.

Outlier Treatment Policy: Drop vs. Cap

• Drop: Remove the observation entirely if it represents an unambiguous measurement error, transmission glitch, or impossible physical state (e.g., Age = 350).
• Cap (Winsorization): Replace extreme values beyond statistical thresholds with the boundary fence value itself (e.g., mapping all values $> 95\text{th percentile}$ to the 95th percentile value). This retains the sample count while eliminating extreme leverage.

2.3 Outlier Detection via the Interquartile Range (IQR) Fences

The Interquartile Range (IQR) is an exceptionally robust, non-parametric metric of statistical dispersion that is resilient against extreme values (unlike the sample standard deviation).

Mathematical Formulation

Given an ordered dataset partitioned into four equal quartiles:

• $Q_1$ (First Quartile / 25th percentile): Separates the lowest 25% of data.
• $Q_2$ (Second Quartile / Median / 50th percentile): Center of the distribution.
• $Q_3$ (Third Quartile / 75th percentile): Separates the lowest 75% of data.

The Interquartile Range ($IQR$) represents the central 50% spread:

$$IQR = Q_3 - Q_1$$

Outlier detection boundaries are defined by the Tukey Fences:

$$\text{Lower Fence} = Q_1 - 1.5 \times IQR$$

$$\text{Upper Fence} = Q_3 + 1.5 \times IQR$$

Any data point $x$ falling outside the interval $[\text{Lower Fence}, \text{Upper Fence}]$ is formally classified as an outlier:

$$x < Q_1 - 1.5 \times IQR \quad \lor \quad x > Q_3 + 1.5 \times IQR$$

(Note: In advanced exploratory diagnostics, points outside $3.0 \times IQR$ are classified as extreme outliers).

Step-by-Step Worked Numerical Example (from Lecture Deck)

Consider the following sorted dataset of 11 measurements:

$$S = \{0, 5, 11, 12, 14, 15, 16, 17, 18, 30, 32\} \quad (N = 11)$$

1. Identify Quartiles:
   • The median ($Q_2$) at index 6 is $15$.
   • Lower half: $\{0, 5, 11, 12, 14\} \implies \text{Median } Q_1 = 11$.
   • Upper half: $\{16, 17, 18, 30, 32\} \implies \text{Median } Q_3 = 18$.
2. Compute IQR:

   $$IQR = Q_3 - Q_1 = 18 - 11 = 7$$

3. Calculate Lower and Upper Fences:

   $$\text{Lower Fence} = Q_1 - 1.5 \times IQR = 11 - (1.5 \times 7) = 11 - 10.5 = 0.5$$

   $$\text{Upper Fence} = Q_3 + 1.5 \times IQR = 18 + (1.5 \times 7) = 18 + 10.5 = 28.5$$

4. Evaluate Outliers:
   • $0 < 0.5 \implies 0$ is a lower outlier.
   • $30 > 28.5 \implies 30$ is an upper outlier.
   • $32 > 28.5 \implies 32$ is an upper outlier.
5. Cleaned Dataset Result:

   $$S_{\text{clean}} = \{5, 11, 12, 14, 15, 16, 17, 18\}$$

2.4 Alternative Outlier Detection Methods

• Standard Deviation Rule (Z-Score Filtering):
  • Assumes a Gaussian distribution. Values with distance from mean exceeding $k$ standard deviations are flagged:

    $$|x - \mu| > k\sigma \quad (\text{commonly } k = 3)$$

• Percentile Thresholding:
  • Trimming or capping values outside predetermined percentiles (e.g., below the 5th percentile or above the 95th percentile).

3. Data Integration

Data integration combines data from multiple disparate sources (databases, data warehouses, flat CSV files, sensor streams) into a single, unified analytical store.

3.1 Schema Integration and the Entity Identification Problem

Real-world entities are frequently referenced using distinct column headers, keys, or conceptual representations across different corporate systems:

$$\text{Table } A.\text{cust-id} \equiv \text{Table } B.\text{cust-\#}$$

• Metadata Integration: Requires mapping metadata attributes, verifying data types, and reconciling disparate database keys.
• Data Value Conflicts: The same real-world entity may have contradictory attribute values due to:
  • Different unit scales (Metric vs. Imperial: meters vs. feet, kilograms vs. pounds).
  • Differing temporal recording snapshots (outdated address vs. updated address).
  • Differing string encodings (UTF-8 vs. ASCII).

3.2 Redundancy and Correlation Analysis

Integrating multiple tables often introduces duplicate or highly correlated attributes (e.g., storing both annual_salary and monthly_salary). Redundant attributes inflate dimensionality, increase computation, and destabilize estimation in linear models.

• Continuous Features — Pearson Correlation Coefficient:

  $$r_{X,Y} = \frac{\sum_{i=1}^n (x_i - \bar{x})(y_i - \bar{y})}{\sqrt{\sum_{i=1}^n (x_i - \bar{x})^2} \sqrt{\sum_{i=1}^n (y_i - \bar{y})^2}}$$

  If $r_{X,Y} \approx 1$ or $-1$, one of the attributes can be pruned without loss of information.
• Categorical Features — Chi-Square ($\chi^2$) Test:

  $$\chi^2 = \sum_{i=1}^r \sum_{j=1}^c \frac{(O_{ij} - E_{ij})^2}{E_{ij}}$$

  Evaluates statistical independence between categorical attributes.

4. Data Transformation & Feature Scaling

4.1 Motivation for Scaling

Continuous features naturally span widely differing numerical scales. For example:

• Age: $18$ to $80$
• Annual Income: $\$15,000$ to $\$500,000$

Algorithms whose objective functions or distance calculations compute Euclidean distance ($d = \sqrt{\sum (x_i - y_i)^2}$) — such as K-Means, K-Nearest Neighbors (KNN), Support Vector Machines (SVM), and Gradient Descent — will be overwhelmingly dominated by the high-magnitude income feature, rendering age mathematically negligible. Feature scaling places attributes onto comparable numerical ranges.

4.2 Min-Max Normalization

Min-Max normalization performs a linear transformation on the original data, mapping values into a fixed interval (typically $[0, 1]$).

Formula

$$x_{\text{norm}} = \frac{x - x_{\min}}{x_{\max} - x_{\min}}$$

To rescale onto an arbitrary range $[new_{\min}, new_{\max}]$:

$$x' = \frac{x - x_{\min}}{x_{\max} - x_{\min}} (new_{\max} - new_{\min}) + new_{\min}$$

Characteristics

• Preserves exact relative relationships among values.
• Bound within $[0, 1]$.
• Vulnerability: Highly sensitive to extreme outliers. If an outlier $x_{\max} = 10,000$ is introduced, all normal observations ($20$ to $80$) are compressed into a near-zero range $[0.002, 0.008]$. Outliers must be handled prior to Min-Max scaling.

Worked Numerical Example (from Lecture Deck)

Given an attribute with $x_{\min} = -23$ and $x_{\max} = 85$ (range $\Delta = 85 - (-23) = 108$):

4.3 Z-Score Standardization

Z-score standardization scales values based on the distribution mean ($\mu$) and standard deviation ($\sigma$). The resulting standardized distribution has zero mean ($\mu = 0$) and unit variance ($\sigma = 1$).

Formula

$$z = \frac{x - \mu}{\sigma}$$

Where:

• $\mu = \frac{1}{n} \sum_{i=1}^n x_i$ is the arithmetic mean.
• $\sigma = \sqrt{\frac{1}{n} \sum_{i=1}^n (x_i - \mu)^2}$ is the standard deviation.

Characteristics

• Centers data at $0$; does not constrain values to a bounded interval.
• Robustness: Significantly less sensitive to outliers than Min-Max normalization because standard deviation spreads the variance across all samples.
• Highly suitable for algorithms that assume normally distributed predictors (e.g., Logistic Regression, Linear Discriminant Analysis, Principal Component Analysis).

Worked Numerical Example (from Lecture Deck)

For the dataset above with sample mean $\mu \approx 20.25$ and standard deviation $\sigma \approx 35.18$:

4.4 Normalization by Decimal Scaling

Decimal scaling normalizes by shifting the decimal point of values:

$$x' = \frac{x}{10^j}$$

Where $j$ is the smallest positive integer such that $\max(|x'|) < 1$.
Example: If values range from $-986$ to $917$, the maximum absolute value is $986$. Setting $j = 3$ yields $x' = \frac{x}{1000}$, scaling the interval to $[-0.986, 0.917]$.

4.5 Data Reshaping: Tidy Data vs. Sparse Matrices

• Tidy Data Principles:
  1. Each variable forms a distinct column.
  2. Each observation forms a distinct row.
  3. Each type of observational unit forms a distinct table.
• Sparse Matrix Representations: When relationships across high-cardinality entities (e.g., users rating movies) contain predominantly empty interactions, storing explicit zeroes wastes memory.
  • Example: User-Movie Ratings:

• Stored compactly as coordinate tuples: (user_id, movie_id, rating).

5. Feature Engineering

Feature engineering is the process of using domain knowledge and statistical transformations to create new features that make machine learning algorithms work more effectively.

5.1 Binning (Discretization)

Binning converts continuous numerical values or high-cardinality categorical levels into discrete bins or grouped cohorts.

• Motivation: Mitigates minor observation noise, prevents overfitting, and enhances model regularization.
• Numerical Binning Example:
  • $0 - 30 \implies \text{Low}$
  • $31 - 70 \implies \text{Mid}$
  • $71 - 100 \implies \text{High}$
• Categorical Binning Example:
  • $\text{Spain, Italy} \implies \text{Europe}$
  • $\text{Chile, Brazil} \implies \text{South America}$

5.2 Logarithmic Transformation

The logarithmic transform is among the most vital tools for stabilizing variance and handling heavily right-skewed data (e.g., personal income, transaction volume, website traffic).

Formula

$$y = \log(x) \quad \text{or} \quad y = \log(x + 1)$$

(Note: $\log(x + 1)$ is implemented as numpy.log1p(x) to safely handle non-negative features containing zero values without encountering $\log(0) = -\infty$).

Benefits

1. Reduces Positive Skewness: Compresses long right-side tails, transforming skewed distributions into near-Gaussian shapes.
2. Normalizes Multiplicative Processes: In natural phenomena, absolute differences at high magnitudes (e.g., $\$1,000,000$ vs $\$1,050,000$) represent smaller relative impacts than at low magnitudes ($\$100$ vs $\$50,100$). The log transform linearizes these exponential relationships.
3. Diminishes Outlier Influence: Shrinks extreme values closer to the median, providing robustness to linear and distance-based estimators.

5.3 One-Hot Encoding

Machine learning models require numerical input vectors. Categorical text cannot be natively multiplied by model weights.

One-Hot Encoding maps a categorical feature with $k$ discrete categories into $k$ separate binary indicator columns containing $0$ or $1$.

Worked Example Table (from Lecture Slide 34)

Suppose a dataset records users and their associated social network accounts:

Raw Categorical Record Table:

One-Hot Encoded Binary Matrix:

Pandas Implementation

import pandas as pd

# Standard single-category dummy encoding
encoded_columns = pd.get_dummies(data['social_network'])
data = data.join(encoded_columns).drop('social_network', axis=1)

(Note: In linear regression with an intercept term, one column must be omitted ($k-1$ dummies) to prevent the dummy variable trap / exact multicollinearity).

5.4 Grouping Operations (Aggregations)

Transactional databases store multiple interaction rows per entity (e.g., one row per purchase). To prepare tidy feature matrices where each customer is represented by exactly one row, grouping operations are executed:

• Numerical Features: Aggregated using summary metrics:
  • sum(): Total lifetime customer revenue.
  • mean(): Average transaction order value.
  • std(): Consistency of purchasing behavior.
• Categorical Features: Aggregated using:
  • nunique(): Number of unique product categories purchased.
  • mode(): Most frequently visited store location.

5.5 Feature Splitting

Deconstructing complex, compound string columns into separate, actionable features:

1. Name Splitting:
   • "John Doe" $\implies \text{first\_name} = \text{"John"}$, $\text{last\_name} = \text{"Doe"}$.
2. Title and Year Parsing:
   • "Toy Story (1995)" $\implies \text{title} = \text{"Toy Story"}$, $\text{year} = 1995$.
   • The extracted year can subsequently be used for numerical regression or binned into release decades.

5.6 Date and Time Feature Extraction

Raw date strings (e.g., "2017-01-01") are non-ordinal text strings that convey minimal direct mathematical signal to estimators if left unprocessed.

Feature Extraction Techniques

1. Component Extraction: Isolating calendar attributes into separate columns:
   • Year (dt.year), Month (dt.month), Day of Month (dt.day).
2. Elapsed Time Intervals: Computing dynamic durations relative to reference benchmarks:
   • Elapsed years: $\text{Current Year} - \text{Record Year}$.
   • Elapsed months: $(\text{Current Year} - \text{Record Year}) \times 12 + (\text{Current Month} - \text{Record Month})$.
3. Cyclical and Behavioral Flags:
   • Day of week name (dt.day_name()) $\implies$ Monday, Sunday.
   • Weekend indicator: $\text{is\_weekend} \in \{0, 1\}$.
   • Seasonal indicators: Quarter, holiday indicator.

Python Code Implementation (from Lecture Slide 44)

from datetime import date
import pandas as pd

# Sample date DataFrame
data = pd.DataFrame({'date': [
    '01-01-2017',
    '04-12-2008',
    '23-06-1988',
    '25-08-1999',
    '20-02-1993'
]})

# 1. Transform string to datetime object
data['date'] = pd.to_datetime(data['date'], format="%d-%m-%Y")

# 2. Extract calendar year and month
data['year'] = data['date'].dt.year
data['month'] = data['date'].dt.month

# 3. Compute elapsed years and elapsed months from current reference
current_year = date.today().year
current_month = date.today().month

data['diff_years'] = current_year - data['date'].dt.year
data['diff_months'] = (current_year - data['date'].dt.year) * 12 + (current_month - data['date'].dt.month)

# 4. Extract weekday name
data['day_name'] = data['date'].dt.day_name()

Output Feature Table

6. Chained Processing Pipelines and Data Dictionaries

In production environments, data preprocessing operations are chained into reproducible, modular DAGs (Directed Acyclic Graphs).

6.1 Modular Pipeline Architecture

6.2 Data Dictionary Documentation Standard

To maintain data governance across pipeline stages, every generated intermediate dataset ($dx$) requires a formal Data Dictionary specification:

• Dataset Identifier: dx2_UserSocialMedia
• File System Target: /var/system/XYZ/dataset/dx2_{YYYY-MM-DD}.csv
• Generation Schedule: Daily batch execution at 02:00 UTC.
• Schema Specification:

7. Chapter Summary & Best Practices Checklist

• Cleansing First: Detect and resolve missing values and outliers before applying feature scaling.
• Outlier Fences: Use $IQR = Q_3 - Q_1$ with fences $[Q_1 - 1.5 IQR, Q_3 + 1.5 IQR]$ for non-parametric outlier filtering.
• Normalization vs. Standardization:
  • Use Min-Max ($[0, 1]$) when absolute bounded ranges are required and extreme outliers have been removed.
  • Use Z-Score ($\mu=0, \sigma=1$) when preserving variance and minimizing vulnerability to extreme values is paramount.
• Log Transformation: Apply $y = \log(x + 1)$ to right-skewed positive features to compress heavy tails and normalize variance.
• Categorical Encodings: Convert categorical strings to binary vectors using One-Hot Encoding, while avoiding multicollinearity in linear models.
• Date Deconstruction: Decompose raw timestamp strings into years, months, weekdays, and elapsed duration intervals.