HowToCodePage

Grouping, joining and reshaping

split-apply-combine with groupby, merging tables like SQL, and moving between long and wide layouts.

groupby: split, apply, combine

df.groupby("team")["score"].mean()
df.groupby(["team", "level"])["score"].agg(["count", "mean", "max"])

summary = df.groupby("team").agg(
    n=("score", "size"),
    avg=("score", "mean"),
    best=("name", lambda s: s.value_counts().index[0]),
).reset_index()

df.groupby("team")["score"].transform("mean")   # same length as df
MethodShape of result
aggOne row per group
transformSame number of rows — adds a column
filterKeeps whole groups that pass a test
applyArbitrary function per group (slowest)
💡
transform is underused and often the cleanest answer: "add each row's group average as a new column" is a transform, not a re-merge.

Merging like SQL

orders.merge(customers, on="customer_id", how="left")
orders.merge(customers, left_on="cust", right_on="id", how="inner")
pd.concat([jan, feb], ignore_index=True)     # stack rows
pd.concat([a, b], axis=1)                    # side by side, aligned on index
  • how: inner, left, right, outer — mirroring SQL joins.
  • Check validate="one_to_many" to have pandas fail loudly on an unexpected relationship.
  • After a left join, count nulls in the right-hand key to see how many rows failed to match.
⚠️
A join on non-unique keys silently multiplies rows — the same fan-out trap as in SQL. Compare len(df) before and after, and pass validate= so the relationship is asserted rather than assumed.

Long and wide

# wide -> long
long = df.melt(id_vars=["name"], value_vars=["math", "science"],
               var_name="subject", value_name="score")

# long -> wide
wide = long.pivot(index="name", columns="subject", values="score")
wide.reset_index(inplace=True)

# safe aggregation when keys repeat
long.pivot_table(index="name", columns="subject", values="score", aggfunc="mean")

Plotting libraries and most statistical models expect long data; humans read wide tables more easily. melt and pivot are the two operations between those worlds.

FAQ

Why did my merge produce more rows than I started with?
The join key is not unique on the right-hand side, so each match duplicates the left row. Verify with df["key"].is_unique or pass validate=.
How do I avoid the index after groupby?
Add .reset_index(), or pass as_index=False to the groupby call when you want a normal column back.

Selecting, filtering and cleaning Joins

Last refreshed 2026-09-18.