🐍 Python for Data Engineers · Chapter 11

Pandas: the 15 operations that cover 90%

Pandas is a small language hiding inside Python. Learn its fifteen verbs and stop writing loops.

Study this lesson free in the app →

The basics

Pandas is a Python library for working with tables in memory: a DataFrame is a spreadsheet where every column has a type, and you transform whole columns at once instead of looping over rows.

The problem it solves: a Python for loop over a million rows does a slow object lookup per row. df.fare * 1.05 runs one tight loop in C over contiguous memory — roughly 100× faster, and far harder to get wrong. Pandas also gives you the verbs analysts need daily: filter, group, join, dedupe, fill missing values.

The mental model: describe the result, not the steps. Think "do this to the column", never "do this to each row". Every aggregation is split → apply → combine: split rows by key, compute per group, stitch the results back together — the same model SQL and Spark use.

It fits in RAM up to a few GB; past that use DuckDB, Polars, Spark or the warehouse.

Explained

# what people write first
total = 0
for i in range(len(df)):
    if df.iloc[i]["status"] == "completed":
        total += df.iloc[i]["fare"]

# what Pandas is for
total = df.loc[df.status == "completed", "fare"].sum()

The second is one line, and on a million rows it is roughly 100× faster — because the loop runs in C over contiguous memory instead of in Python over objects.

The spreadsheet that learned to think

A DataFrame is a spreadsheet with two upgrades.

First, every column knows its type. A date column is dates, not text that looks like dates — so sorting works, arithmetic works, and 2026-01-09 never lands before 2026-01-10.

Second, you describe the result instead of the steps. In a spreadsheet you drag a formula down 40,000 rows. In Pandas you say df.fare * 1.05 and mean all of them, and the loop happens in C where you cannot get it wrong.

That shift — from "do this to each row" to "do this to the column" — is the entire skill. Everything else is vocabulary.

The 15 verbs, on RideFlow data

import pandas as pd

# 1. read — always set dtypes and parse dates at the door
df = pd.read_csv("rides.csv", parse_dates=["event_time"],
                 dtype={"ride_id": "string", "city": "category"})

# 2. look before you leap
df.info(); df.describe(); df.head()

# 3. select columns / 4. filter rows (boolean mask)
df[["ride_id", "city", "fare"]]
df[(df.status == "completed") & (df.fare > 0)]      # & not and, brackets required

# 5. .loc — label-based select + filter in one
df.loc[df.city == "Shillong", ["ride_id", "fare"]]

# 6. new column, vectorised
df["fare_with_tax"] = df.fare * 1.05

# 7. conditional column without a loop
df["band"] = pd.cut(df.fare, [0, 100, 500, float("inf")],
                    labels=["low", "mid", "high"])

# 8. groupby + agg — the workhorse
daily = (df.groupby(["city", df.event_time.dt.date])
           .agg(rides=("ride_id", "nunique"),
                revenue=("fare", "sum"),
                avg_fare=("fare", "mean"))
           .reset_index())

# 9. sort / 10. top N
daily.sort_values("revenue", ascending=False).head(10)

# 11. merge (a SQL join, with validation)
rides = df.merge(drivers, on="driver_id", how="left", validate="many_to_one")

# 12. dedupe — keep the latest event per ride
df.sort_values("event_time").drop_duplicates("ride_id", keep="last")

# 13. missing values — explicitly, never silently
df.fare = df.fare.fillna(0)
df = df.dropna(subset=["ride_id"])

# 14. window per group — running total per city
df["cum_revenue"] = df.groupby("city").fare.cumsum()

# 15. write — partitioned parquet, not CSV
df.to_parquet("rides/", partition_cols=["city"], index=False)

Step through a groupby

Split → apply → combine. Every groupby in every tool works this way, including Spark and SQL.

The warning everyone ignores

completed = df[df.status == "completed"]
completed["fare_with_tax"] = completed.fare * 1.05

This prints SettingWithCopyWarning, and sometimes the new column silently doesn't appear.

Why vectorised beats apply beats loop

Three ways to add tax to a million rows:

| Approach | Time | Why | | --- | --- | --- | | for i in range(len(df)) with .iloc | ~60 s | a Python object lookup per row, twice | | df.fare.apply(lambda f: f * 1.05) | ~0.6 s | still a Python function call per row | | df.fare * 1.05 | ~0.005 s | one C loop over a contiguous NumPy array |

apply is a loop wearing better clothes. It is a legitimate escape hatch for genuinely custom logic, but it is not vectorisation, and interviewers listen for whether you know the difference.

Most "I need apply" cases have a vectorised form:

• if/else → np.where(cond, a, b)
• multiple conditions → np.select([c1, c2], [v1, v2], default=v3)
• binning → pd.cut
• lookup by key → .map(dict) or a merge
• string work → .str accessor (df.city.str.lower())
• date parts → .dt accessor (df.event_time.dt.hour)

The join that tripled revenue

Finance reports ₹4.2 crore for July. Your pipeline says ₹12.6 crore. Exactly 3×.

The code:

rides.merge(drivers, on="driver_id", how="left")

The cause: the drivers table has three rows per driver — one per vehicle they have registered. A left join against a non-unique key duplicates the left row once per match. Every ride is now three rides, and every fare is counted three times.

This is a fan-out join, and it is the single most common way real revenue numbers go wrong.

The guard rail is one argument:

rides.merge(drivers, on="driver_id", how="left", validate="many_to_one")
# MergeError: Merge keys are not unique in right dataset

validate= turns a silent tripling into a loud crash. Use it on every merge you would be embarrassed to get wrong — and always check len(df) before and after a join.

When Pandas is the wrong tool

Pandas loads everything into RAM, and typically needs 5–10× the file size while working (intermediate copies, object overhead, index structures). A 2 GB CSV can need 15 GB.

Honest thresholds:

• < 1 GB — Pandas, no hesitation
• 1–10 GB — Pandas with care: dtype on read, usecols to skip columns, category for low-cardinality strings, chunked reading. Or reach for Polars or DuckDB, both of which query files bigger than RAM on one machine
• > 10 GB, or it must run on a schedule — Spark/Databricks, or push the transform into the warehouse where the data already lives

Cheap wins that often move the threshold:

df = pd.read_csv(f, usecols=["ride_id", "city", "fare"],   # read fewer columns
                 dtype={"city": "category"})                # 10x less memory for repeated strings
df.memory_usage(deep=True).sum() / 1e9                      # measure, don't guess

And the structural answer: don't move data to the compute, move the compute to the data. If it lives in Snowflake, aggregate in Snowflake and bring back the small result.

Words to know

In two minutes

• A DataFrame is a typed spreadsheet; the skill is thinking in columns, not rows.
• Vectorised column maths runs in C and is ~100× faster than a row loop; apply is still a Python loop per row.
• Fix dtypes at the door: parse dates, make fares numeric, ids strings, repeated strings category.
• groupby is split → apply → combine; call reset_index() so the keys become columns again.
• A merge against a non-unique key fans out rows and multiplies revenue; use validate= and compare row counts.
• Filter then assign → .copy(), or assign through .loc[mask, col]; chained indexing silently does nothing.
• errors="coerce" and NaN-skipping sum() turn loud failures into silent zeros — pair them with a null-rate check.
• Pandas needs 5–10× the file size in RAM; past a few GB use usecols/dtypes, DuckDB/Polars, or push the work to the warehouse.

Test yourself

There are 9 questions, 3 puzzles and 7 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.