Grouping, joining and reshaping

Split, apply, combine

Most business questions are about groups. "Which city sells the most?" means: split the rows by city, apply a sum to each group, and combine the answers into one small table. This pattern is called split-apply-combine, and in pandas it is df.groupby("city")["amount"].sum().

sales = pd.DataFrame({
    "city": ["Pune", "Delhi", "Pune", "Mumbai", "Delhi"],
    "amount": [450, 1200, 780, 2100, 150],
})
print(sales.groupby("city")["amount"].sum())
# city
# Delhi     1350
# Mumbai    2100
# Pune      1230
# Name: amount, dtype: int64

Several answers at once with agg

sales.groupby("city")["amount"].agg(["count", "sum", "mean"])
#         count   sum    mean
# city
# Delhi       2  1350   675.0
# Mumbai      1  2100  2100.0
# Pune        2  1230   615.0

Joining tables with merge

Data is often split across tables. Orders have a customer_id; the customers table has the name and city for each id. pd.merge(orders, customers, on="customer_id", how="left") brings them together. This is exactly a SQL JOIN.

Pivot tables

orders.pivot_table(index="city", columns="month", values="amount", aggfunc="sum", fill_value=0)
#        Jan   Feb   Mar
# Delhi  1200  0     150
# Pune   450   780   0

Check every result

After a groupby or merge, always run two quick checks. First, do the totals still add up? The sum of the per-city totals must equal the sum of the whole column. Second, does the row count make sense? A left merge must have at least as many rows as the left table, and exactly as many when the right-hand keys are unique. These checks take seconds and catch most join and grouping bugs before your numbers reach a manager.

To group by more than one column, pass a list: df.groupby(["city", "month"])["amount"].sum() gives one row per city and month. Add .reset_index() to turn the group labels back into normal columns, which is easier to save or merge later.

The same idea in plain Python

from collections import defaultdict

totals = defaultdict(int)
for city, amount in [("Pune", 450), ("Delhi", 1200), ("Pune", 780)]:
    totals[city] += amount
print(dict(totals))

Common mistakes

• Grouping by a column with messy spellings, so "Pune" and "pune" become two groups. Fix: clean first.
• Using an inner join by accident and silently losing customers with no orders. Fix: think about which rows you must keep; often you want a left join.
• Averaging averages: the mean of city averages is not the overall mean when cities have different numbers of orders. Fix: compute from the raw rows or weight by count.
• Forgetting that groupby drops rows where the key is missing (NaN) by default. Fix: fill the key first or pass dropna=False.