Introduction to Data Transformation and Cleaning

Models only learn from the data you provide. If that data is inaccurate, inconsistent, or full of errors, the model will be unreliable. In short: bad data equals bad models. Data cleaning and transformation are essential steps that prevent poor-quality data from undermining the entire machine learning pipeline. Data cleaning identifies and fixes problems such as missing values, inconsistent formats, duplicates, outliers, and type mismatches. Once cleaned, data is transformed to meet algorithm requirements (scaling, encoding, aggregation, parsing, binning), producing a reliable foundation for feature engineering and modeling.

The image outlines the process of data cleaning, highlighting issues such as missing values, inconsistent formats, duplicates, and outliers in datasets.

Clean, well-documented data is the first and most important step toward building reliable ML systems. Invest time here to avoid compounding issues downstream.

The image explains data cleaning, detailing issues like missing values, inconsistent formats, duplicates, outliers, and data type mismatches in datasets.

Quick reference — common problems and actions

Problem	Typical fixes
Missing values	Drop rows/columns, impute (mean/median/mode), or model-based imputation
Duplicates	Remove exact duplicates or dedupe based on key subset
Inconsistent formats	Parse and normalize (e.g., dates to ISO 8601)
Outliers	Detect with IQR/domain thresholds; cap, transform, or remove
Type mismatches	Strip non-numeric characters and cast to numeric/datetime
Categorical inconsistencies	Map variants/typos to a controlled vocabulary

Common cleaning techniques (expanded)

Missing values: remove or impute (median is robust for skewed data).
Duplicates: deduplicate using full-row or subset-based logic.
Outliers: apply domain thresholds, IQR, or winsorization.
Type conversions: ensure numeric, datetime, and categorical dtypes are correct.
Standardization: normalize inconsistent string formats (dates, currencies).
Controlled vocabulary: normalize typos and synonyms to canonical category values.

Missing values Missing values can distort statistics and models. Choose between removing affected rows/columns (simpler, destructive) or imputing (preserves sample size). Imputation methods include mean, median, mode, forward/backfill, or model-based techniques.

Dropping rows with missing values is irreversible for that dataset copy and can bias results if missingness is not random. Prefer imputation or analyze missingness patterns first.

Example — drop or impute with pandas:

import pandas as pd

df = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie"],
    "Age": [29, None, 45]
})

# Drop rows with any missing values
df_dropped = df.dropna()

# Fill missing Age with median (robust to skew)
median_age = df["Age"].median()
df_filled = df.copy()
df_filled["Age"] = df_filled["Age"].fillna(median_age)

The image shows a comparison of tables labeled "Before" and "After" addressing missing values in the "Age" column, where a missing age value is filled with the median (37).

Inconsistent formats Canonicalizing formats (especially dates and numeric strings) prevents parsing errors and ensures consistent sorting, filtering, and aggregation. Use robust parsing functions that can coerce invalid values to null for later inspection. Example — parse dates and format as ISO 8601 with pandas:

df["timestamp"] = pd.to_datetime(df["timestamp"], errors="coerce", infer_datetime_format=True)
# To format as ISO string:
df["timestamp_iso"] = df["timestamp"].dt.strftime("%Y-%m-%dT%H:%M:%S")

Duplicates Duplicates inflate counts and skew model training. Remove exact duplicates or deduplicate using a subset of columns (e.g., id + timestamp), depending on the domain.

# Remove exact duplicate rows
df_unique = df.drop_duplicates()

# Remove duplicates based on subset of columns (e.g., id and timestamp)
df_unique_subset = df.drop_duplicates(subset=["id", "timestamp"])

# In-place single-line usage
df.drop_duplicates(inplace=True)

The image shows a table highlighting spelling/typo inconsistencies for country names associated with employee IDs. It suggests mapping these values to a controlled vocabulary for uniformity.

Typos and inconsistent categorical values Create a controlled vocabulary (mapping table) to normalize synonyms, abbreviations, and typos into a single canonical value. This improves feature consistency and reduces cardinality. Example mapping:

mapping = {
    "US": "United States",
    "USA": "United States",
    "United States of America": "United States",
    "U.S.": "United States"
}
df["country_clean"] = df["country"].replace(mapping)

Outliers Outliers can be legitimate or erroneous. Detect with domain thresholds, Z-score, or IQR. Handling strategies include removing, capping, or transforming the values. Example — winsorization by clipping to 1st and 99th percentiles:

low, high = df["value"].quantile([0.01, 0.99])
df["value_clipped"] = df["value"].clip(lower=low, upper=high)

The image shows a table with "Before" and "After" columns illustrating how a temperature outlier (999°C) for SensorID S2 is replaced with a more realistic value (25°C). It suggests flagging and replacing extreme values using domain thresholds.

Data type mismatches Numeric values sometimes contain currency symbols, commas, or other non-numeric characters. Strip those characters and cast to numeric or datetime types. Coerce invalid parses to NaN for later handling. Example — clean dollar amounts and convert to numeric:

import pandas as pd

# Remove non-numeric characters and convert to numeric (coerce errors to NaN)
df["price_clean"] = pd.to_numeric(
    df["price"].astype(str)
        .str.replace(r"[^\d\.\-]", "", regex=True)
        .replace("", pd.NA),
    errors="coerce"
)

The image shows a before-and-after comparison of a data table where non-numeric characters are stripped from a temperature column, converting it to a consistent float format.

Data transformation Cleaning fixes errors; transformation prepares features for learning algorithms. Typical transformations include scaling, encoding, aggregation, parsing, and binning.

The image describes four common data transformation techniques: scaling, encoding, aggregation, and date parsing. Each technique includes a brief explanation and an associated icon.

Key transformation techniques

Scaling / Normalization
Rescale numeric features so ones with large ranges don’t dominate models. Use Min-Max scaling to rescale to [0, 1], or standardization (Z-score) to center to mean 0 and unit variance.

Example with scikit-learn:

from sklearn.preprocessing import MinMaxScaler, StandardScaler

scaler = MinMaxScaler()
df[["feature1_scaled"]] = scaler.fit_transform(df[["feature1"]])

std = StandardScaler()
df[["feature1_std"]] = std.fit_transform(df[["feature1"]])

The image illustrates data normalization using Min-Max Scaling, converting a range from 20-100 to a standardized range of 0-1.

Encoding categorical variables
Choose encoding based on model and cardinality:
- One-hot encoding: creates binary columns per category (good for nominal features).
- Label encoding: assigns integer codes (useful for ordinal or tree-based models).

Examples:

# One-hot (pandas)
df = pd.get_dummies(df, columns=["color"])

# Label encoding (scikit-learn)
from sklearn.preprocessing import LabelEncoder
le = LabelEncoder()
df["color_label"] = le.fit_transform(df["color"].astype(str))

The image illustrates the process of encoding categorical variables by converting text labels (Red, Green, Blue) into numerical form (2, 1, 0).

Binning (discretization)
Binning converts continuous variables into discrete intervals to reduce noise or improve interpretability. Use pd.cut for fixed bins or pd.qcut for quantiles.

Example — bin ages into categories:

bins = [0, 18, 35, 60, 200]
labels = ["Teen", "Young Adult", "Adult", "Senior"]
df["age_group"] = pd.cut(df["age"], bins=bins, labels=labels, right=True)

Note: pd.cut’s right parameter controls whether the bins include the right edge; adjust right=True/False or bin edges to match your intended inclusive boundaries.

The image explains binning (discretization) of ages into categories: Teen (0-18), Young Adult (19-35), Adult (36-60), and Senior (60+), illustrating conversion of continuous variables into discrete intervals.

Binning simplifies patterns and improves interpretability at the cost of some granularity. Choose bins with domain knowledge or data-driven methods. AWS tools for cleaning and transformation AWS offers managed services that streamline cleaning and transformation within ML workflows:

Service	Purpose
AWS Glue DataBrew	Visual, no-code/low-code data cleaning and profiling
AWS Glue	Serverless ETL for automated, scalable transformations
Amazon EMR	Distributed processing (Spark/Hadoop) for large-scale transformations
SageMaker Data Wrangler	Integrated data preparation and visualization inside SageMaker

The image provides an overview of AWS tools for cleaning and transforming data, featuring AWS Glue DataBrew, AWS Glue, Amazon EMR, and Amazon SageMaker Data Wrangler.

A typical AWS data-preparation workflow

Ingest raw data into Amazon S3 as the storage layer.
Use AWS Glue or AWS Glue DataBrew to profile, clean, and transform data.
For very large-scale processing, run jobs on Amazon EMR (Spark) or Glue ETL.
Load transformed data into SageMaker Data Wrangler or directly into SageMaker for feature engineering and model training.
Pass clean, transformed data to the model training environment or downstream analytics.

Summary and best practices

Start with profiling to identify missing values, duplicates, inconsistent formats, outliers, and type issues.
Prefer reproducible cleaning steps (notebooks, pipelines, or ETL jobs) so preprocessing can be audited and rerun.
Use domain rules and statistical methods (imputation, deduplication, type conversion) for cleaning.
Transform features to match algorithm expectations (scaling, encoding, binning, aggregation, parsing).
Leverage managed tools (DataBrew, Glue, EMR, SageMaker Data Wrangler) to scale and standardize preparation steps.
Document every transformation and preserve raw data to enable traceability and robust production deployments.

Watch Video

Demo Using Kinesis for Real time Data Ingestion

Feature Engineering Techniques

Prerequisites

Data Preparation for Machine Learning (ML)

ML Model Development

Deployment and Orchestration of ML Workflows

ML Solution Monitoring, Maintenance, and Security

Bringing it all together

Introduction to Data Transformation and Cleaning

Watch Video