PadhaikoAll subjects

Advanced Data Wrangling and Transformation (part 2: 3.4–3.6)

Advanced Python Programming for Data Science (ENCT 325)

Open in the Padhaiko app

3.4 Memory optimization and efficient data processing

As DataFrames scale from thousands to tens of millions of rows, naive Pandas idioms (int64, float64, Python object strings, row-wise .iterrows()) waste gigabytes of RAM and run orders of magnitude slower than vectorized native operations.

Memory Optimization Techniques in Pandas

  1. Inspecting True Memory (memory_usage(deep=True)): Always pass deep=True to df.info(memory_usage="deep") or df.memory_usage(deep=True). Without deep=True, Pandas reports only the 8-byte pointer size for object columns instead of the actual heap-allocated Python str objects.
  2. Numeric Downcasting:
    • Default int64 / float64 take 8 bytes per element. If an integer column represents ages (0..120) or flags (0..1), downcasting to uint8 / int8 (1 byte) or int32 (4 bytes) saves 50%–87.5% RAM.
    • pd.to_numeric(s, downcast="integer") or downcast="float" selects the smallest safe dtype automatically (note float16 lacks hardware support on many CPUs and loses precision; prefer float32).
  3. Categorical Encoding (category dtype): Replacing repeating strings ("Kathmandu", "Pokhara", "Lalitpur") with category stores small integer codes (int8/int16) plus one dictionary of unique labels, cutting memory by up to 90% and speeding up groupby().
  4. Column Selection & Sparse Types: Load only needed columns via usecols=[...], and use pd.arrays.SparseArray when >90% of entries are identical (e.g., 0).
Pandas Data Type Memory Footprint (per Million Values)
DtypeBytes per ValueRAM for 1,000,000 RowsNumeric Range / Notes
int64 / float648 B8.0 MBDefault Pandas numeric types
int32 / float324 B4.0 MBIntegers ±2.14 billion; ~7 significant float digits
int162 B2.0 MBSigned integers -32,768 to 32,767
int8 / uint8 / bool1 B1.0 MBSmall counters, flags, booleans
object (str)~50–80 B~50–80 MBPointer (8 B) + per-string Python object overhead
category (10 uniques)1 B + table~1.0 MBInteger codes + tiny lookup array

Efficient Data Processing (Vectorization vs. Row Loops)

AspectRow-by-Row Loops (.iterrows / .apply)Vectorized NumPy / Pandas Operations
Execution EnginePython interpreter loop per row (high bytecode/boxing overhead)Compiled C/Cython loops over contiguous NumPy memory buffers
SpeedSlowest baseline (1x)Typically 50x–500x faster than iterrows()
Idiomatic Examplefor i, r in df.iterrows(): ...df["net"] = np.where(df["qty"] > 10, df["amt"] * 0.9, df["amt"])

Additional speed practices:


3.5 Building a reusable data-cleaning pipeline

Ad-hoc notebook cells break when new monthly files arrive. A reusable data-cleaning pipeline turns cleaning rules into modular, idempotent, testable transformations that behave identically across development and production.

Core Design Principles

Definition

Properties of a Reusable Data-Cleaning Pipeline

  1. Pure & Idempotent Transforms: Each stage takes a DataFrame (and optional config/fitted state) and returns a cleaned DataFrame without mutating the caller's input unexpectedly; running the pipeline twice on clean data leaves it unchanged.
  2. Configuration over Hardcoding: Column names, valid bounds, missing-value strategies, and category mappings live in explicit parameters or config dicts/dataclasses rather than buried magic numbers.
  3. Composable .pipe() Chains or sklearn.pipeline.Pipeline: Pandas df.pipe(fn, ...) chains modular cleaning functions cleanly, while scikit-learn's BaseEstimator + TransformerMixin integrates cleaning directly with model training and cross-validation.
  4. Schema Validation & Audit Metrics: Assert required columns, check duplicate primary keys, log row counts dropped/imputed at each stage, and fail loudly on unexpected schema drift.
Python
import pandas as pd


def standardize_columns(df: pd.DataFrame) -> pd.DataFrame:
    out = df.copy()
    out.columns = [c.strip().lower().replace(" ", "_") for c in out.columns]
    return out


def drop_invalid_rows(df: pd.DataFrame, required_cols: list[str]) -> pd.DataFrame:
    return df.dropna(subset=required_cols).drop_duplicates()


def clip_outliers(df: pd.DataFrame, col: str, low: float, high: float) -> pd.DataFrame:
    out = df.copy()
    out[col] = out[col].clip(lower=low, upper=high)
    return out


def run_cleaning_pipeline(raw_df: pd.DataFrame) -> pd.DataFrame:
    return (
        raw_df
        .pipe(standardize_columns)
        .pipe(drop_invalid_rows, required_cols=["user_id", "amount"])
        .pipe(clip_outliers, col="amount", low=0.0, high=100_000.0)
    )

Notice how each helper has a single responsibility and can be unit-tested independently with small synthetic DataFrames (pytest).


3.6 Introduction to data pipeline components (Ingestion, transformation, storage)

A data-cleaning script handles one table in memory; an end-to-end data pipeline connects three architectural layers—Ingestion, Transformation, and Storage—to move data reliably from raw sources to analytics and ML consumers.

Three-Stage Data Pipeline ArchitectureText
+-----------------------+      +---------------------------+      +-------------------------+
|    1. INGESTION       | ---> |     2. TRANSFORMATION     | ---> |       3. STORAGE        |
| (Extract & Validate)  |      | (Clean, Join, Aggregate)  |      |  (Persist & Serve)      |
+-----------------------+      +---------------------------+      +-------------------------+
| - CSV / JSON / Excel  |      | - Deduplication & typing  |      | - Data Lake (Parquet)   |
| - REST / SOAP APIs    |      | - Missing-value handling  |      | - Relational DB / DW    |
| - SQL / NoSQL queries |      | - Feature engineering     |      | - Feature Store / Cache |
+-----------------------+      +---------------------------+      +-------------------------+
Core Data Pipeline Components
ComponentPrimary ResponsibilitiesKey Design Concerns
1. Ingestion (Extract)Connect to files, databases, APIs, or streams; parse raw formats; check source availabilityBatch vs. streaming cadence, incremental watermarks (fetch only new rows), schema checks, retries
2. Transformation (Clean & Shape)Deduplicate, cast dtypes, impute nulls, join lookups, compute derived features and aggregatesVectorized performance, memory bounds (chunking), idempotency, zero train/test leakage
3. Storage (Load & Serve)Persist curated outputs into queryable sinks (Parquet files, SQLite/PostgreSQL, Data Warehouse)Partitioning (e.g., by year/month), compression, ACID/atomic writes, read access patterns

Medallion (Layered) Storage Pattern

Modern data pipelines commonly organize storage into progressive quality tiers so raw source data is never lost if transformation logic changes:


Worked Examples

Example 3.4

3.4

A dataset has 5,000,000 rows with two columns: status_code (values 0..200, stored as int64) and country (12 unique countries, stored as object averaging 64 bytes per Python string).

  1. Calculate the RAM (in MiB, where 1 MiB = 1,048,576 bytes) before and after optimizing status_code to uint8 and country to category (neglecting the tiny 12-entry category lookup table).
  2. Write the Pandas optimization snippet.

Calculating Baseline Memory Usage:

  • status_code (int64, 8 bytes/row): 5,000,000 * 8 = 40,000,000 bytes ≈ 38.15 MiB.
  • country (object, 64 bytes/row): 5,000,000 * 64 = 320,000,000 bytes ≈ 305.18 MiB.
  • Total baseline RAM = 40,000,000 + 320,000,000 = 360,000,000 bytes ≈ 343.32 MiB.

Calculating Optimized Memory Usage and Writing Code:

  • status_code (uint8, 1 byte/row, holds 0..255): 5,000,000 * 1 = 5,000,000 bytes ≈ 4.77 MiB.
  • country (category with 12 unique labels → int8 codes, 1 byte/row): 5,000,000 * 1 = 5,000,000 bytes ≈ 4.77 MiB.
  • Total optimized RAM ≈ 10,000,000 bytes ≈ 9.54 MiB (a 36x reduction, saving ~333.79 MiB).
Python
df["status_code"] = df["status_code"].astype("uint8")
df["country"] = df["country"].astype("category")
Answer:

Baseline RAM is 343.32 MiB (38.15 MiB for int64 + 305.18 MiB for object). Downcasting to uint8 and category drops RAM to 9.54 MiB (4.77 MiB + 4.77 MiB), a 36x memory reduction.

Example 3.5

3.5

Refactor the following slow .iterrows() discount loop into a vectorized Pandas/NumPy pipeline function usable with df.pipe(), and verify the output on two rows: (qty=5, price=50) and (qty=2, price=40):

Python
discounts = []
for _, row in df.iterrows():
    total = row["qty"] * row["price"]
    if total >= 200:
        discounts.append(total * 0.9)
    else:
        discounts.append(total)
df["final_total"] = discounts

Vectorizing with NumPy np.where inside a .pipe() Function: Replace row-by-row Python iteration with array arithmetic and np.where(cond, true_val, false_val) inside a pure function:

Python
import numpy as np
import pandas as pd


def apply_bulk_discount(df: pd.DataFrame, threshold: float = 200.0, rate: float = 0.9) -> pd.DataFrame:
    out = df.copy()
    gross = out["qty"] * out["price"]
    out["final_total"] = np.where(gross >= threshold, gross * rate, gross)
    return out

Tracing Output on Sample Rows:

  • Row 1 (qty=5, price=50): gross = 5 * 50 = 250. Since 250 >= 200, final_total = 250 * 0.9 = 225.0.
  • Row 2 (qty=2, price=40): gross = 2 * 40 = 80. Since 80 < 200, final_total = 80.0.
Answer:

The vectorized apply_bulk_discount function uses np.where(gross >= threshold, gross * rate, gross) and returns final_total = [225.0, 80.0] without slow Python row iteration.

Example 3.6

3.6

How should a student answer an exam question asking: "Design a Python data pipeline covering Ingestion, Transformation, and Storage for daily e-commerce order logs, highlighting how you ensure idempotency and memory efficiency"?

Mapping the Three Pipeline Components:

  1. Ingestion: Read daily raw JSONL/CSV order dumps incrementally using a date partition watermark and validate required columns (order_id, user_id, amount, timestamp).
  2. Transformation: Process large files in chunks (or lazy Polars scans), deduplicate by order_id, downcast numeric IDs/amounts, convert low-cardinality status/currency to category, and apply .pipe() cleaning functions.
  3. Storage: Write raw payloads to Bronze storage and cleaned typed partitions (orders/dt=YYYY-MM-DD/part.parquet) to Silver Parquet (or upsert into SQLite/PostgreSQL).

Highlighting Idempotency and Memory Safety: Explain that idempotency is achieved via DELETE + INSERT (or overwriting a date-partitioned Parquet file atomically) and deduplication on order_id, while memory efficiency comes from chunking, projection pushdown (usecols), and dtype downcasting.

Answer:

Structure the answer around (1) Ingestion (incremental watermark extraction + schema validation), (2) Transformation (chunked/vectorized .pipe() cleaning, deduplication, dtype downcasting), and (3) Storage (partitioned Parquet or SQL upserts), emphasizing atomic partition overwrites for idempotency and bounded chunking for memory efficiency.


Exercises

Practice Exercises — Advanced Data Wrangling (Part 2)

Question 1

Why does df.info() underreport RAM for object columns unless memory_usage="deep" is passed? Also compute the memory saved (in MiB) when downcasting a 2,000,000-row integer column from int64 to int16.

Why memory_usage="deep" Is Required: An object Series stores a NumPy array of 8-byte C pointers referencing arbitrary Python objects scattered on the heap. Without memory_usage="deep", Pandas counts only 8 bytes * N for the pointer array and ignores the actual string/object sizes.

Memory Saved by Downcasting (int64 → int16):

  • int64 (8 bytes/row): 2,000,000 * 8 = 16,000,000 bytes ≈ 15.26 MiB.
  • int16 (2 bytes/row): 2,000,000 * 2 = 4,000,000 bytes ≈ 3.81 MiB.
  • Memory saved = 15.26 - 3.81 = 11.44 MiB (a 75% reduction).
Answer:

Without memory_usage="deep", Pandas counts only the 8-byte object pointers instead of the underlying Python objects. Downcasting 2,000,000 rows from int64 (15.26 MiB) to int16 (3.81 MiB) saves 11.44 MiB (75%).

Question 2

Rank .iterrows(), .itertuples(), and vectorized NumPy/Pandas operations from slowest to fastest in Pandas, and explain why vectorization achieves dramatic speedups.

Speed Ranking (Slowest to Fastest):

  1. .iterrows() (Slowest): Constructs a brand-new pd.Series object for every single row inside a Python loop, incurring heavy object creation and type-boxing overhead.
  2. .itertuples() (Faster Loop): Yields lightweight namedtuple rows without creating Series objects, typically 10x–30x faster than iterrows().
  3. Vectorized NumPy/Pandas Operations (Fastest): Executes compiled C/Cython loops directly over contiguous typed memory arrays without Python interpreter dispatch per element.
Answer:

From slowest to fastest: .iterrows() < .itertuples() < vectorized NumPy/Pandas operations. Vectorization eliminates per-row Python interpreter overhead and Series allocation by running compiled C loops over contiguous typed arrays.

Question 3

What makes a data-cleaning pipeline reusable and idempotent? Rewrite a chain of three cleaning calls—stripping whitespace on "city", filtering age >= 0, and filling missing "score" with 0—using Pandas .pipe().

Reusability and Idempotency:

  • Reusable: Functions are parameterized (no hardcoded file paths or magic columns), copy inputs cleanly, and have single responsibilities that can be unit-tested.
  • Idempotent: Applying the pipeline f(f(df)) produces the exact same result as f(df) (no cumulative side effects like multiplying values twice or duplicating rows).

Composable .pipe() Implementation:

Python
def clean_city(df):
    out = df.copy()
    out["city"] = out["city"].str.strip()
    return out

def filter_valid_age(df):
    return df[df["age"] >= 0].copy()

def fill_score(df, default=0.0):
    out = df.copy()
    out["score"] = out["score"].fillna(default)
    return out

clean_df = raw_df.pipe(clean_city).pipe(filter_valid_age).pipe(fill_score, default=0.0)
Answer:

A reusable pipeline uses parameterized, pure, testable functions, and idempotency guarantees f(f(df)) == f(df). Chaining raw_df.pipe(clean_city).pipe(filter_valid_age).pipe(fill_score, default=0.0) expresses all three stages cleanly.

Question 4

Describe the three core components of a data pipeline (Ingestion, Transformation, Storage) and explain the Bronze–Silver–Gold layered storage pattern.

Three Core Pipeline Components:

  1. Ingestion: Extracts raw data from files, APIs, or databases, performs initial schema checks, and handles retries/incremental watermarks.
  2. Transformation: Cleans, deduplicates, joins, downcasts dtypes, and engineers analytical/ML features.
  3. Storage: Persists transformed data in queryable, partitioned, compressed sinks (Parquet data lakes, relational databases, or warehouses).

Bronze–Silver–Gold Pattern:

  • Bronze: Immutable raw landing layer preserving original source payloads for replayability.
  • Silver: Cleaned, conformed, deduplicated, strongly typed tables.
  • Gold: Curated, aggregated business metrics or ML-ready feature tables.
Answer:

Ingestion extracts and validates source data; Transformation cleans, joins, and reshapes it; Storage persists partitioned outputs for downstream consumers. Bronze–Silver–Gold separates raw archival data (Bronze), cleaned typed tables (Silver), and curated analytical/ML marts (Gold).

Question 5

Why is Apache Parquet preferred over CSV for intermediate pipeline storage? If an 85.0 MB CSV dataset compresses to 12.5 MB as Parquet, compute the compression ratio (CSV size / Parquet size) and state two additional advantages of Parquet besides disk savings.

Compression Ratio Calculation: Compression ratio = 85.0 / 12.5 = 6.8x smaller on disk.

Two Additional Advantages Over CSV:

  1. Preserved Schema and Dtypes: Parquet stores column metadata (int32, category/dictionary encoding, datetime64[ns, UTC]), eliminating fragile type re-inference on reload.
  2. Columnar Projection Pushdown: Reading 2 columns out of 50 from a Parquet file reads only those 2 column chunks from disk instead of scanning every byte of all 50 columns.
Answer:

The compression ratio is 6.8x (85.0 / 12.5). Beyond disk savings, Parquet preserves exact column schemas/dtypes and supports columnar projection pushdown (reading only requested columns from disk).

← Advanced Data Wrangling and Transformation (part 1: 3.1–3.3)Applied Statistics and Exploratory Analysis →