Advanced Data Wrangling and Transformation (part 2: 3.4–3.6)
Open in the Padhaiko app3.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
- Inspecting True Memory (
memory_usage(deep=True)): Always passdeep=Truetodf.info(memory_usage="deep")ordf.memory_usage(deep=True). Withoutdeep=True, Pandas reports only the 8-byte pointer size forobjectcolumns instead of the actual heap-allocated Pythonstrobjects. - Numeric Downcasting:
- Default
int64/float64take 8 bytes per element. If an integer column represents ages (0..120) or flags (0..1), downcasting touint8/int8(1 byte) orint32(4 bytes) saves 50%–87.5% RAM. pd.to_numeric(s, downcast="integer")ordowncast="float"selects the smallest safe dtype automatically (notefloat16lacks hardware support on many CPUs and loses precision; preferfloat32).
- Default
- Categorical Encoding (
categorydtype): Replacing repeating strings ("Kathmandu","Pokhara","Lalitpur") withcategorystores small integer codes (int8/int16) plus one dictionary of unique labels, cutting memory by up to 90% and speeding upgroupby(). - Column Selection & Sparse Types: Load only needed columns via
usecols=[...], and usepd.arrays.SparseArraywhen >90% of entries are identical (e.g.,0).
| Dtype | Bytes per Value | RAM for 1,000,000 Rows | Numeric Range / Notes |
|---|---|---|---|
int64 / float64 | 8 B | 8.0 MB | Default Pandas numeric types |
int32 / float32 | 4 B | 4.0 MB | Integers ±2.14 billion; ~7 significant float digits |
int16 | 2 B | 2.0 MB | Signed integers -32,768 to 32,767 |
int8 / uint8 / bool | 1 B | 1.0 MB | Small counters, flags, booleans |
object (str) | ~50–80 B | ~50–80 MB | Pointer (8 B) + per-string Python object overhead |
category (10 uniques) | 1 B + table | ~1.0 MB | Integer codes + tiny lookup array |
Efficient Data Processing (Vectorization vs. Row Loops)
| Aspect | Row-by-Row Loops (.iterrows / .apply) | Vectorized NumPy / Pandas Operations |
|---|---|---|
| Execution Engine | Python interpreter loop per row (high bytecode/boxing overhead) | Compiled C/Cython loops over contiguous NumPy memory buffers |
| Speed | Slowest baseline (1x) | Typically 50x–500x faster than iterrows() |
| Idiomatic Example | for i, r in df.iterrows(): ... | df["net"] = np.where(df["qty"] > 10, df["amt"] * 0.9, df["amt"]) |
Additional speed practices:
- Prefer
df.itertuples(index=False)overiterrows()if a Python loop is unavoidable (avoids building a newSeriesper row). - Use method chaining with
.assign(),.pipe(), and.query()while avoidingSettingWithCopyWarningby modifying copies explicitly (.copy()) or using.loc[mask, col] = val. - Save intermediate tabular datasets in Parquet (
.to_parquet()) or Feather rather than CSV: Parquet preserves exact dtypes, compresses columns efficiently, and supports selective column reads.
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
Properties of a Reusable Data-Cleaning Pipeline
- 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.
- 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.
- Composable
.pipe()Chains orsklearn.pipeline.Pipeline: Pandasdf.pipe(fn, ...)chains modular cleaning functions cleanly, whilescikit-learn'sBaseEstimator+TransformerMixinintegrates cleaning directly with model training and cross-validation. - 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.
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.
+-----------------------+ +---------------------------+ +-------------------------+
| 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 |
+-----------------------+ +---------------------------+ +-------------------------+| Component | Primary Responsibilities | Key Design Concerns |
|---|---|---|
| 1. Ingestion (Extract) | Connect to files, databases, APIs, or streams; parse raw formats; check source availability | Batch 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 aggregates | Vectorized 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:
- Bronze (Raw): Exact copy of ingested records with ingestion timestamps (replayable audit log).
- Silver (Cleaned / Conformed): Deduplicated, typed, schema-validated tables stored efficiently (e.g., Parquet).
- Gold (Curated / Feature Marts): Business-level aggregations or ML-ready feature tables ready for dashboards and models.
Worked Examples
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).
- Calculate the RAM (in MiB, where
1 MiB = 1,048,576 bytes) before and after optimizingstatus_codetouint8andcountrytocategory(neglecting the tiny 12-entry category lookup table). - 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, holds0..255):5,000,000 * 1 = 5,000,000 bytes ≈ 4.77 MiB.country(categorywith 12 unique labels →int8codes, 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).
df["status_code"] = df["status_code"].astype("uint8")
df["country"] = df["country"].astype("category")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.
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):
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"] = discountsVectorizing 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:
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 outTracing Output on Sample Rows:
- Row 1 (
qty=5, price=50):gross = 5 * 50 = 250. Since250 >= 200,final_total = 250 * 0.9 = 225.0. - Row 2 (
qty=2, price=40):gross = 2 * 40 = 80. Since80 < 200,final_total = 80.0.
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.
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:
- Ingestion: Read daily raw JSONL/CSV order dumps incrementally using a
datepartition watermark and validate required columns (order_id,user_id,amount,timestamp). - Transformation: Process large files in chunks (or lazy Polars scans), deduplicate by
order_id, downcast numeric IDs/amounts, convert low-cardinalitystatus/currencytocategory, and apply.pipe()cleaning functions. - 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.
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)
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).
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%).
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):
.iterrows()(Slowest): Constructs a brand-newpd.Seriesobject for every single row inside a Python loop, incurring heavy object creation and type-boxing overhead..itertuples()(Faster Loop): Yields lightweightnamedtuplerows without creatingSeriesobjects, typically 10x–30x faster thaniterrows().- Vectorized NumPy/Pandas Operations (Fastest): Executes compiled C/Cython loops directly over contiguous typed memory arrays without Python interpreter dispatch per element.
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.
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 asf(df)(no cumulative side effects like multiplying values twice or duplicating rows).
Composable .pipe() Implementation:
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)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.
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:
- Ingestion: Extracts raw data from files, APIs, or databases, performs initial schema checks, and handles retries/incremental watermarks.
- Transformation: Cleans, deduplicates, joins, downcasts dtypes, and engineers analytical/ML features.
- 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.
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).
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:
- Preserved Schema and Dtypes: Parquet stores column metadata (
int32,category/dictionary encoding,datetime64[ns, UTC]), eliminating fragile type re-inference on reload. - 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.
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).