Pandas — groupby, merge, reshape

The bread-and-butter operations that interviewers probe for performance and semantic understanding.

groupby — split-apply-combine

df = pd.DataFrame({
    "team": ["A", "A", "B", "B", "B"],
    "score": [10, 20, 30, 40, 50],
})

df.groupby("team")["score"].sum()
# team
# A    30
# B   120

The three modes:

agg — one value per group

df.groupby("team").agg(
    total=("score", "sum"),
    avg=("score", "mean"),
    n=("score", "count"),
)

Named aggregation (Pandas 0.25+) returns a clean DataFrame with named columns.

transform — same shape, value per group broadcast back

df["team_avg"] = df.groupby("team")["score"].transform("mean")
# Each row gets its team's mean

transform keeps the original row count. Use for: per-group normalization, ranking within group, fill missing per group.

apply — arbitrary per-group function

def top_2(group):
    return group.nlargest(2, "score")

df.groupby("team").apply(top_2)

Most flexible, slowest. Use only when agg / transform don’t fit.

groupby performance

• as_index=False to keep group keys as columns (not as the index):

  df.groupby("team", as_index=False).agg(total=("score", "sum"))

• Sort=False if you don’t need sorted groups (faster):

  df.groupby("team", sort=False)["score"].sum()

• Multiple keys:

  df.groupby(["country", "team"]).agg(...)

• observed=True with categorical group keys avoids cartesian-product of unused categories:

  df.groupby("team", observed=True).sum()

For huge group counts (millions of groups), Pandas slows dramatically. Polars or DuckDB win there.

merge — joins

orders = pd.DataFrame({"user_id": [1, 2, 1, 3], "amount": [100, 200, 50, 300]})
users = pd.DataFrame({"user_id": [1, 2, 3], "name": ["A", "B", "C"]})

merged = orders.merge(users, on="user_id", how="left")

Match on differently-named columns

orders.merge(users, left_on="user_id", right_on="id")

Many-to-many gotcha

If both sides have duplicate keys, you get a cartesian product per matching key. A 100-row orders × 5 duplicates in users = 500 rows.

orders.merge(users, on="user_id", validate="many_to_one")    # raises if not 1:1 on right

validate options: one_to_one, one_to_many, many_to_one, many_to_many. Use to catch surprise expansions.

Indicator

df.merge(other, on="key", how="outer", indicator=True)
# Adds _merge column: "left_only", "right_only", "both"

Useful for “find rows missing on one side.”

join — index-based merge

orders.set_index("user_id").join(users.set_index("user_id"))

join joins on the index by default. If both DataFrames share an index, this is fast and clean. Otherwise, merge is more flexible.

concat — stack

pd.concat([df1, df2])                        # vertical, default
pd.concat([df1, df2], axis=1)                # horizontal (column-wise)
pd.concat([df1, df2], ignore_index=True)     # reset index after stacking

Aligns on the non-concat axis. concat([df1, df2]) aligns columns; missing columns become NaN. concat([df1, df2], axis=1) aligns rows by index.

pivot and pivot_table

df = pd.DataFrame({
    "date": ["2024-01", "2024-01", "2024-02", "2024-02"],
    "team": ["A", "B", "A", "B"],
    "score": [10, 20, 30, 40],
})

df.pivot(index="date", columns="team", values="score")
#            A    B
# 2024-01   10   20
# 2024-02   30   40

pivot requires unique (index, columns) combinations. If you have duplicates, use pivot_table:

df.pivot_table(index="date", columns="team", values="score", aggfunc="sum")

pivot_table aggregates duplicates with aggfunc (default mean).

melt — pivot inverse

wide = pd.DataFrame({"id": [1, 2], "Jan": [10, 20], "Feb": [30, 40]})
wide.melt(id_vars="id", var_name="month", value_name="value")
#    id month  value
# 0   1   Jan     10
# 1   2   Jan     20
# 2   1   Feb     30
# 3   2   Feb     40

Wide → long format. Standard prep for plotting with seaborn / time-series operations.

stack / unstack

df.stack()        # columns → rows (one level)
df.unstack()      # rows → columns

For MultiIndex DataFrames; rotates between long-form and wide-form on hierarchical indices.

Window functions

df["rolling_avg"] = df["score"].rolling(window=3).mean()
df["expanding_sum"] = df["score"].expanding().sum()
df["ewm_avg"] = df["score"].ewm(span=10).mean()

• rolling(window) — fixed window.
• expanding() — from start to current.
• ewm() — exponentially weighted.

Time-aware windows:

df["rolling_7d"] = df.set_index("date")["score"].rolling("7D").mean()

"7D" means 7 calendar days. Handles uneven sampling correctly.

resample — time-series aggregation

df = df.set_index("timestamp")
df.resample("1H").sum()           # hourly buckets
df.resample("1D").agg({"price": "ohlc", "volume": "sum"})   # OHLC + sum

Same syntax as groupby but with time aliases ('D', 'H', '15min', 'W-MON', etc.).

Common gotchas

• merge with multi-row matches explodes rows. Use validate=... to catch.
• groupby on categorical with empty groups — observed=False (default) creates rows for every category combination even if data has zero rows. Use observed=True.
• pivot with duplicates — raises. Use pivot_table if you have duplicates.
• In-place reshape doesn’t exist for most. Reassign: df = df.melt(...).
• concat along axis=0 with mismatched columns — unions columns, fills missing with NaN. Sometimes desired, sometimes a bug.
• groupby().apply() warning in 2.2+ — implicit grouping-key inclusion is deprecated; pass include_groups=False explicitly.

When to skip Pandas for these ops

Pandas is fine for interactive analysis on small data. Production data pipelines past a few GB usually benefit from a faster engine.

Interview angle

• “What’s the difference between agg, transform, and apply on a groupby?” — agg returns one row per group; transform returns same shape as input (broadcasts group result back to each row); apply is arbitrary per-group function, most flexible, slowest.
• “How would you compute each row’s percentile within its group?” — df.groupby("group")["value"].transform(lambda x: x.rank(pct=True)). Transform broadcasts the per-group rank back to row shape.
• “merge returned more rows than expected. What happened?” — duplicate keys on one or both sides → cartesian product per match. Use validate="many_to_one" etc. to catch. Or deduplicate first with .drop_duplicates(subset=key).
• “pivot vs pivot_table?” — pivot requires unique (index, columns) pairs; raises on duplicates. pivot_table aggregates duplicates via aggfunc (default mean).
• “How do you do a rolling 7-day average on uneven time series?” — set datetime index, then df.rolling("7D").mean(). Time-aware window respects calendar gaps; integer-window .rolling(7) would average the last 7 rows regardless of time.
• “resample vs groupby for time-series?” — resample is a time-aware shortcut for groupby on a datetime index. Same semantics, friendlier syntax for time frequencies.
• “How would you detect rows that exist in df1 but not in df2?” — df1.merge(df2, how="left", indicator=True).query('_merge == "left_only"'). Or set difference on a key column.