Split-apply-combine is the heart of data analysis: group rows by a key, compute a statistic per group, and combine the results. Then join and reshape tables.
Why: the most common analysis question is "what is the average/count/sum PER category" — groupby splits rows into groups, applies an aggregation, and combines the answers into a table. When: any time you would say "per" (per user, per day, per class). Where: this mirrors SQL GROUP BY exactly.
import pandas as pd
df = pd.DataFrame({
"role": ["eng", "eng", "sci", "sci", "sci"],
"salary": [90, 110, 95, 105, 120],
"years": [3, 6, 4, 5, 8],
})
print(df.groupby("role")["salary"].mean())
# role
# eng 100.0
# sci 106.67
# Several aggregations at once:
print(df.groupby("role").agg(
avg_salary=("salary", "mean"),
headcount=("salary", "size"),
max_years=("years", "max"),
))Why: real data is spread across tables (users, orders, products) and you merge them on a shared key — the pandas equivalent of a SQL JOIN. When: use merge to combine columns from two DataFrames; choose the how (inner/left/right/outer) to control which rows survive. Where: check row counts before and after — an unexpected key duplication silently multiplies rows.
users = pd.DataFrame({"user_id": [1, 2, 3], "name": ["Ada", "Linus", "Grace"]})
orders = pd.DataFrame({"user_id": [1, 1, 3], "total": [50, 20, 80]})
# Left join: keep every user, attach their orders (NaN if none):
merged = users.merge(orders, on="user_id", how="left")
print(merged)
# Total spend per user:
print(merged.groupby("name")["total"].sum())Why: the same data can be "long" (one row per observation) or "wide" (categories spread across columns), and different tasks need different shapes — pivot and melt convert between them. When: pivot to make a summary table; melt to turn columns back into rows for plotting or modeling. Where: pivot_table also aggregates when keys repeat.
long = pd.DataFrame({
"month": ["Jan", "Jan", "Feb", "Feb"],
"role": ["eng", "sci", "eng", "sci"],
"salary": [90, 95, 100, 105],
})
wide = long.pivot_table(index="month", columns="role", values="salary")
print(wide) # rows = month, columns = role
back = wide.reset_index().melt(id_vars="month", value_name="salary")
print(back) # back to long form