AskAiml
Sign in Get started

pandas patterns that actually scale

Method chaining, categoricals, groupby-agg, and the SettingWithCopy warning explained.

📊 Module 5 8 min read Not started

Why this matters

Pandas is the standard for tabular data in Python — ETL, analytics, ML feature engineering. The API is large; this lesson covers the 20% you'll use 80% of the time, plus the patterns that separate "I read the docs" from "I move fast in real datasets."

Learning objectives

  1. Create, index, and filter DataFrames.
  2. Use groupby, merge, pivot, melt.
  3. Handle missing data, types, dates.
  4. Avoid the most common slow / wrong patterns.
  5. Know when to switch to Polars or Spark.

1. Series and DataFrame

python
import pandas as pd
import numpy as np

# Series — 1D, labelled
s = pd.Series([1, 2, 3], index=["a", "b", "c"], name="vals")

# DataFrame — 2D, labelled rows + columns
df = pd.DataFrame({
    "name": ["Ada", "Bob", "Cara"],
    "age":  [30, 25, 35],
    "city": ["NY", "SF", "LA"],
})
df.head(); df.tail(); df.info(); df.describe(); df.dtypes
df.shape; df.columns; df.index

The index is meaningful — it's how rows are addressed. Set it deliberately:

python
df = df.set_index("name")
df.loc["Ada"]                       # row by label
df.reset_index()                    # turn index back into a column

2. Reading / writing

python
df = pd.read_csv("data.csv", parse_dates=["timestamp"], dtype={"id": "int32"})
df = pd.read_parquet("data.parquet")
df = pd.read_excel("file.xlsx", sheet_name="Sheet1")
df = pd.read_json("data.jsonl", lines=True)
df = pd.read_sql("SELECT * FROM t", conn)

df.to_csv("out.csv", index=False)
df.to_parquet("out.parquet")        # much faster + smaller than CSV
df.to_json("out.jsonl", orient="records", lines=True)

Parquet is the right format for storage — 10× smaller, 10× faster, types preserved. CSV only for human readability / interchange with non-Python tools.

3. Selecting and filtering

python
df["age"]                           # column → Series
df[["name", "age"]]                 # columns → DataFrame
df.age                              # attribute (only when col is a valid identifier)

# By position
df.iloc[0]                          # first row (Series)
df.iloc[0:3]                        # first 3 rows
df.iloc[0:3, [0, 2]]                # 2-D position-based

# By label
df.loc["Ada"]
df.loc["Ada":"Cara", ["age", "city"]]

# Boolean mask
df[df.age > 28]
df[(df.age > 28) & (df.city == "NY")]     # parens!
df.query("age > 28 and city == 'NY'")     # string query (sometimes clearer)

# `.isin`
df[df.city.isin(["NY", "LA"])]

loc vs iloc:

  • loc[label, label] — label-based.
  • iloc[position, position] — integer-based.
  • df[col] — column selector (label).
  • df[mask] — boolean row filter.

Avoid chained assignment (df[df.x > 0]["y"] = 5) — it triggers SettingWithCopyWarning and may or may not work. Use df.loc[df.x > 0, "y"] = 5.

4. Modifying columns

python
df["age_squared"] = df["age"] ** 2
df["full_name"] = df["first"] + " " + df["last"]
df = df.assign(
    high=lambda d: d.age > 30,
    decade=lambda d: (d.age // 10) * 10,
)

df.drop(columns=["age_squared"], inplace=False)   # always prefer non-inplace
df.rename(columns={"age": "years"})
df.astype({"age": "int32"})

.assign(...) is the chainable way to add columns — keeps your code as a pipeline.

5. Missing data

python
df.isna().sum()                    # count NaNs per column
df.dropna()                        # drop rows with any NaN
df.dropna(subset=["age"])
df.fillna(0)
df.fillna({"age": df.age.median(), "city": "unknown"})
df.ffill()                          # forward-fill
df.bfill()                          # back-fill
df.interpolate()                    # linear by default

For booleans / ints with NA, use the nullable dtypes:

python
df["age"] = df["age"].astype("Int64")     # capital I — allows pd.NA
df["ok"] = df["ok"].astype("boolean")

6. groupby — split / apply / combine

python
df.groupby("city").size()
df.groupby("city")["age"].mean()
df.groupby("city").agg(
    avg_age=("age", "mean"),
    max_age=("age", "max"),
    n=("age", "count"),
)

# Multiple keys
df.groupby(["city", "department"]).agg(...)

# Apply a custom function
df.groupby("city")["age"].apply(lambda s: s.quantile(0.95))

# Transform — same shape as input (broadcast group stat back to each row)
df["age_z"] = df.groupby("city")["age"].transform(lambda s: (s - s.mean()) / s.std())

# Filter — keep entire groups passing a predicate
df.groupby("city").filter(lambda g: len(g) > 10)

agg for collapsed aggregations. transform when you need a column the same length as the input. filter when you want to keep/drop groups.

For speed, prefer built-in names ("mean", "sum") over apply(...) — they hit C code.

7. Merging / joining

python
pd.merge(left, right, on="user_id", how="inner")          # inner join
pd.merge(left, right, left_on="uid", right_on="user_id", how="left")
pd.merge(left, right, how="outer")
pd.merge(left, right, how="cross")                          # Cartesian

# By index
left.join(right, how="left")

# Concat (stacking)
pd.concat([df1, df2], axis=0)         # rows
pd.concat([df1, df2], axis=1)         # cols (align on index)

how: "inner", "left", "right", "outer", "cross".

Set validate="one_to_many" or "one_to_one" to catch duplicate-key bugs.

8. Reshaping: pivot, melt, stack, unstack

python
# Pivot (long → wide)
df.pivot(index="date", columns="city", values="temp")

# Pivot with aggregation
df.pivot_table(index="date", columns="city", values="temp", aggfunc="mean")

# Melt (wide → long)
df.melt(id_vars=["date"], value_vars=["NY", "SF", "LA"], var_name="city", value_name="temp")

# Stack / unstack (hierarchical index)
df.set_index(["date", "city"]).unstack("city")

Most "I want to reshape this table" tasks are pivot_table or melt. Reach for them before reaching for loops.

9. Date and time

python
df["ts"] = pd.to_datetime(df["ts"])
df["ts"].dt.year, df["ts"].dt.dayofweek, df["ts"].dt.month_name()

# Index by datetime
df = df.set_index("ts").sort_index()
df["2026-01":"2026-03"]               # slice by string
df.resample("D").mean()               # daily mean
df.resample("W").sum()                # weekly sum
df.rolling("7D").mean()               # 7-day rolling
df.shift(1)                            # lag
df.tz_localize("UTC").tz_convert("America/New_York")

For lossy aggregations, always specify the resample rule explicitly.

10. String ops

python
df["name"].str.upper()
df["email"].str.split("@", n=1, expand=True)
df["text"].str.contains(r"\d+", regex=True)
df["text"].str.replace(r"\s+", " ", regex=True)
df["text"].str.len()
df["text"].str.extract(r"(\d+)-(\d+)")

All string accessors live under .str. Vectorised; faster than apply.

11. Apply, map, vectorisation

python
# Bad
df["squared"] = df["x"].apply(lambda x: x*x)     # Python loop

# Good
df["squared"] = df["x"] ** 2                      # vectorised

# Bad
df["clean"] = df["text"].apply(lambda s: s.strip().lower())

# Good
df["clean"] = df["text"].str.strip().str.lower()

# Bad
for i, row in df.iterrows():
    df.loc[i, "y"] = compute(row.a, row.b)        # row-by-row mutation

# Good
df["y"] = compute_vectorised(df["a"], df["b"])    # vector inputs

apply and iterrows are the two biggest sources of slow pandas. Avoid both.

If you genuinely need row-wise logic that doesn't vectorise, use numpy broadcasting or Polars (Lesson 5.3) which expresses these operations beautifully.

12. Categorical and efficient dtypes

python
df["city"] = df["city"].astype("category")        # huge memory + speed win for low-cardinality strings
df["age"] = df["age"].astype("int32")             # int32 instead of int64 cuts memory in half
df["amount"] = df["amount"].astype("float32")     # if precision allows

For large DataFrames, type tuning saves GB of RAM.

df.memory_usage(deep=True) shows actual memory per column.

13. Method chaining (pandas pipelines)

python
result = (
    pd.read_csv("orders.csv", parse_dates=["placed_at"])
      .query("status == 'paid' and amount > 0")
      .assign(day=lambda d: d.placed_at.dt.date)
      .groupby("day")["amount"].sum()
      .rename("daily_revenue")
      .reset_index()
)

The chained style reads top-to-bottom like SQL. Use df.pipe(my_func) to plug in your own functions:

python
def add_features(d): return d.assign(decade=lambda x: (x.age // 10) * 10)

result = df.pipe(add_features).groupby("decade").mean()

14. When pandas isn't the right tool

Dataset sizeTool
< 1 GB in memorypandas
1-50 GB single machinepolars (faster, lazy, multicore) or DuckDB
> 50 GBDuckDB, Dask, Ray, Spark
StreamingPandas + chunks, Polars streaming, Kafka + Flink

Polars (Lesson 5.3) is often 5-30× faster than pandas with a similar API. If you're starting a new analytics project in 2026, strongly consider Polars first.

15. Worked example: e-commerce analytics

python
import pandas as pd

orders = pd.read_csv("orders.csv", parse_dates=["placed_at"])
users  = pd.read_csv("users.csv")

# Merge
df = orders.merge(users, on="user_id", how="left", validate="many_to_one")

# Feature engineering
df = df.assign(
    day=lambda d: d.placed_at.dt.date,
    is_weekend=lambda d: d.placed_at.dt.dayofweek >= 5,
    aov=lambda d: d.revenue / d.units,
)

# Aggregate
daily = (
    df.query("status == 'paid'")
      .groupby("day")
      .agg(
          revenue=("revenue", "sum"),
          orders=("order_id", "count"),
          aov=("aov", "mean"),
      )
      .sort_index()
)

# 7-day moving averages
daily["revenue_7d"] = daily.revenue.rolling(7).mean()
daily["orders_7d"] = daily.orders.rolling(7).mean()

# Output
daily.to_parquet("daily_revenue.parquet")

Self-contained ETL in 20 lines, no loops.

Hands-on lab (3 hours)

  1. Load a CSV; print info(), describe(), isna().sum().
  2. Filter rows with multiple conditions; both [] mask and .query().
  3. Add features with .assign(...) (lag of a column, weekday flag).
  4. Group by two columns; aggregate three metrics; reset index.
  5. Pivot from long to wide; melt back; confirm equality.
  6. Compute 28-day rolling mean of a daily series.
  7. Compare apply(lambda) vs vectorised on 1M rows. Time both.
  8. Convert a string column to category; observe memory drop.
  9. Bonus: rewrite the same pipeline in Polars (next lesson preview).

Common pitfalls

  1. SettingWithCopyWarning — solved by df.loc[mask, "col"] = ....
  2. apply over rows (iterrows, apply(axis=1)) when vectorisation works.
  3. Implicit type changes (int → float when NaN appears). Use nullable Int64.
  4. Forgetting parse_dates= on read; date columns silently stay strings.
  5. Missing validate= on merges → silent duplication.
  6. Using inplace=True (deprecated, will be removed). Use df = df.foo(...).
  7. Reading 5 GB CSV → swap. Chunk with chunksize= or switch to Polars/DuckDB.

Self-check

  1. Difference between loc and iloc.
  2. When use apply vs vectorised ops?
  3. merge vs concat?
  4. transform vs agg?
  5. Why is Parquet preferred over CSV for storage?

References

Sign in to save your progress and earn badges.