Python for Data Engineering
Intermediate 16 min read Python for Data Engineering

pandas Essentials

Tabular data without the SQL server

pandas gives analytics-shaped dataframes with vectorized operations, joins, grouping and I/O — the tool used for local exploration and many final transformations.

pandasDataFramevectorizationgroupbyjoinCSV
Prerequisites: Python Data Structures
Before we start

What you will learn

  • Load, inspect and shape DataFrames idiomatically
  • Use vectorized ops, groupby and merge instead of row loops
  • Apply the pandas mindset (row-wise code is an anti-pattern)
Introduction

What is this?

pandas is Python's dataframe engine: tabular data with labeled columns, vectorized arithmetic, group-by aggregations, merges and a rich I/O surface. It fits comfortably in memory and is the standard for local exploration, feature prep and final small-to-medium transforms.

Motivation

Why do you need it?

  • Vectorized operations run at C speed in numpy, where a Python for-loop over rows crawls.
  • Its API made this exact mental model mainstream — and it transfers directly to PySpark DataFrames.
  • Fast to experiment with: load -> transform -> plot inside a notebook, then promote the same code to a pipeline.
Think about it

Mental model

Imagine

An elevator vs stairs

Think of

Vectorized pandas vs row loops

Both reach the same floor, but vectorized operations are the elevator — the whole block of work moves at once. Extraction thought nobody takes the stairs unless they enjoy it.

Mechanics

How it works

  • A DataFrame is a column-based 2D structure built from numpy arrays, so per-column work is C-speed.
  • Vectorized ops (`df['x'] * 2`) apply to the whole column without a Python loop.
  • `groupby().agg()` splits, applies, combines — the same shape as SQL GROUP BY.
  • `merge` implements SQL-style joins (inner/left/outer/cross) on key columns.
  • `read_csv`, `to_parquet`, `read_sql` bridge the file/database world into and out of memory.

The pandas data workflow.

Read
Clean
Transform
Aggregate/Join
Write
Hands-on

Examples

Vectorized instead of a loop

Vectorized instead of a loop
import pandas as pd
df = pd.DataFrame({"amount": [10, 5, -3, 8]})
df["is_sale"] = df["amount"].gt(0)
df["taxed"] = df["amount"].clip(lower=0) * 1.2
print(df["taxed"].sum())  # 27.6

Every line operates on the whole column at once; no row iteration, matching the SQL mental model.

Real world

In production

  • A finance export is read, cleaned of null currency rows, and written to Parquet — all vectorized, all line-logged.
  • An analytics engineer merges a daily fact with a dim in a notebook, validates with the same code that later runs in a dbt test.
  • A feature team preps a training dataframe locally, then rewrites the identical chain in PySpark for full volume.
Guidelines

Best practices

  • Vectorize: prefer column expressions and groupby over `df.iterrows()` or `df.apply(..., axis=1)`.
  • Set unambiguous dtypes up front (parse dates, coerce numbers) before analytics.
  • Validate shape at each boundary: rows-in vs rows-out counts gate correctness.
  • Freeze versions: pandas behavior changes between releases and silently shifts results.
Watch out

Common mistakes

  • `df.iterrows()` for anything large — 1000x slower than the vectorized equivalent.
  • Silent dtype coercion (object → float) turning bad values into NaN, then propagating.
  • Copy-vs-view aliasing: mutating a slice that was actually a view mutates the parent silently.
  • Loading a file way bigger than RAM and wondering where the OOM came from — use chunking or Spark.
Root cause analysis

Common issues

Error
SettingWithCopyWarning then the column 'didn't change'
Root cause

Mutating a view instead of a copy of a slice

Fix

Use `.copy()` on slices or chain operations so mutations target a fresh frame.

Error
Numbers summed as text or became NaN
Root cause

Dtype stayed object or coercion failed mid-column

Fix

Coerce with pd.to_numeric(errors='coerce') and assert the null rate afterward.

When things break

Debugging tips

  • Compare `df.dtypes` and `df.shape` right after read — most bugs are already visible there.
  • Split a failing operation and repro on 100 rows to find the exact column/row that breaks it.
  • Use `df.info()` and `df.describe()` before aggregating to catch nulls and typecasts early.
Interviews

Interview checkpoint

Practice

Hands-on lab

Clean and Aggregate a Messy Export in pandas
30 min · intermediate
In progress
Objectives
  • Read a dirty CSV into a typed DataFrame
  • Clean and normalize with vectorized operations
  • Group, aggregate and export a summary

Clean and Aggregate a Messy Export in pandas

30 min · intermediate

0/4 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which approach aggregates `df` by region fastest and most idiomatically?

2

pandas operations are vectorized, meaning they operate on whole columns without a Python loop.

Go deeper

Official documentation

pandas user guide
docs
pandas API reference
docs