Pandas GroupBy and Merge: The Two Skills That Cover Most Analysis Work

by

If you sat down and logged what you do in pandas over a month, two operations would dominate almost everything else: splitting data into groups and summarising it, and sticking two tables together.

Everything else is decoration. Get these two right and you can do most analysis work.

The problem is that both of them behave in ways that feel slightly odd until you understand the model underneath. This article explains that model, and then goes through the patterns you will use constantly.

Where it helps, I have shown the SQL equivalent, because a lot of people come to pandas already knowing SQL and the translation makes things click faster.

Part 1: GroupBy

The mental model: split, apply, combine

Every groupby does three things.

Split. The rows are divided into groups based on the values of one or more columns.

Apply. A function runs on each group independently.

Combine. The results are stitched back into a single object.

Once you see it this way, the confusing parts stop being confusing. The reason groupby returns something strange when you print it is that it has only done the split, and it is waiting to be told what to apply.

python

import pandas as pd

df.groupby("region")
# <pandas.core.groupby.generic.DataFrameGroupBy object at 0x...>

That is not an error. It is a set of groups with no instructions yet.

The basic aggregation

python

df.groupby("region")["net_amount"].sum()

sql

SELECT region, SUM(net_amount)
FROM orders
GROUP BY region;

Same operation. The pandas version returns a Series with region as the index, which is the first thing that trips people up. If you want a normal DataFrame with region as a column:

python

df.groupby("region", as_index=False)["net_amount"].sum()

I use as_index=False almost everywhere, because it keeps the output shaped like a table and saves a reset_index() later.

Multiple metrics at once with agg

This is where groupby gets genuinely useful.

python

summary = df.groupby("region", as_index=False).agg(
    total_sales   = ("net_amount", "sum"),
    order_count   = ("order_id", "count"),
    customers     = ("customer_id", "nunique"),
    avg_order     = ("net_amount", "mean"),
    largest_order = ("net_amount", "max"),
)

This is called named aggregation, and it is the cleanest syntax pandas has. You get sensible column names immediately instead of a multi level column index you then have to flatten.

The SQL equivalent reads almost identically:

sql

SELECT
    region,
    SUM(net_amount)            AS total_sales,
    COUNT(order_id)            AS order_count,
    COUNT(DISTINCT customer_id) AS customers,
    AVG(net_amount)            AS avg_order,
    MAX(net_amount)            AS largest_order
FROM orders
GROUP BY region;

Note nunique maps to COUNT(DISTINCT ...). That one is worth memorising because it comes up constantly and people reach for count by mistake.

Grouping by more than one column

python

monthly = df.groupby(["region", "order_month"], as_index=False).agg(
    total_sales = ("net_amount", "sum"),
)

Pass a list and you get one row per combination that exists in the data. Combinations with no rows simply do not appear, which matters if you are drawing a chart and want a zero line for empty months. To force them in:

python

monthly = (
    df.groupby(["region", "order_month"])["net_amount"]
      .sum()
      .unstack(fill_value=0)
      .stack()
      .reset_index(name="total_sales")
)

The one that changes everything: transform

agg collapses each group down to one row. transform returns a result the same length as the original data, with the group value repeated on every row of that group.

That sounds abstract, so here is why it matters. Say you want each order’s share of its region’s total:

python

df["region_total"] = df.groupby("region")["net_amount"].transform("sum")
df["share_of_region"] = df["net_amount"] / df["region_total"]

No merge needed. The group total lands on every row automatically.

This is the pandas answer to a SQL window function:

sql

SELECT
    order_id,
    net_amount,
    SUM(net_amount) OVER (PARTITION BY region) AS region_total
FROM orders;

Once you know transform, a whole category of clumsy “aggregate then merge back” code disappears. Common uses:

python

# flag orders above their region's average
df["above_region_avg"] = df["net_amount"] > df.groupby("region")["net_amount"].transform("mean")

# number each customer's orders in date order
df = df.sort_values("order_date")
df["order_seq"] = df.groupby("customer_id").cumcount() + 1

# days since that customer's previous order
df["prev_order"] = df.groupby("customer_id")["order_date"].shift(1)
df["days_since_prev"] = (df["order_date"] - df["prev_order"]).dt.days

Those last two are the pandas versions of ROW_NUMBER and LAG.

Filtering groups

To keep only groups meeting a condition, which is SQL’s HAVING:

python

big_regions = df.groupby("region").filter(lambda g: g["net_amount"].sum() > 100000)

That returns the original rows belonging to qualifying groups, not the summary. If you want the summary filtered instead, aggregate first and filter the result:

python

summary = df.groupby("region", as_index=False)["net_amount"].sum()
summary = summary[summary["net_amount"] > 100000]

The second version is faster and easier to read. Use filter only when you genuinely need the underlying rows.

The NaN trap

By default, groupby drops rows where the grouping key is NaN. Silently.

python

df.groupby("region")["net_amount"].sum()          # missing regions vanish
df.groupby("region", dropna=False)["net_amount"].sum()   # they get their own group

If your group totals do not add up to the grand total, this is the first thing to check. It catches everyone at least once.

Part 2: Merge

The mental model: keys and multiplication

A merge matches rows from two tables on one or more key columns. The output row count is not the input row count. It is the number of key matches, and that distinction is the source of nearly every merge bug.

python

result = pd.merge(
    orders,
    customers,
    on="customer_id",
    how="left",
)

The four join types, in plain terms

  • how="inner": keep only rows where the key exists in both. Default. Silently drops non matching rows.
  • how="left": keep every row from the left table, fill missing right values with NaN. What you want most of the time when enriching a main table with lookups.
  • how="right": the mirror of left. Rarely used, because you can just swap the tables and read it more easily.
  • how="outer": keep everything from both sides. Useful for reconciliation, when you want to see what exists on one side and not the other.

The default being inner is worth flagging. If you merge a customer lookup onto orders and forget to set how="left", every order with a missing or unmatched customer ID quietly disappears from your analysis.

Always check the row count

Make this a reflex:

python

before = len(orders)
result = pd.merge(orders, customers, on="customer_id", how="left")
after = len(result)

print(f"before: {before}, after: {after}")

If after is larger than before, your right hand table has duplicate keys and rows have multiplied. Every numeric total you calculate from this point is inflated.

Find the culprit:

python

print(customers["customer_id"].duplicated().sum())
customers[customers["customer_id"].duplicated(keep=False)].sort_values("customer_id").head()

Usually the fix is to deduplicate the lookup table first:

python

customers_unique = customers.drop_duplicates(subset=["customer_id"], keep="last")

Pandas can also police this for you, which is better than remembering to check:

python

result = pd.merge(
    orders,
    customers,
    on="customer_id",
    how="left",
    validate="many_to_one",
)

That raises an error if the right side is not unique on the key. Use it. It turns a silent wrong answer into a loud failure, which is always the better outcome.

Finding out what did not match

The indicator argument adds a column showing where each row came from:

python

check = pd.merge(
    orders,
    customers,
    on="customer_id",
    how="outer",
    indicator=True,
)

print(check["_merge"].value_counts())

You get counts for both, left_only and right_only. This is the fastest way to answer “how many orders have no matching customer record”, which is a data quality question you will be asked often.

When the key columns have different names

python

result = pd.merge(
    orders,
    customers,
    left_on="cust_id",
    right_on="customer_id",
    how="left",
)

You now have both columns in the output. Drop the redundant one.

Merging on multiple keys

python

result = pd.merge(
    sales_actual,
    sales_target,
    on=["region", "order_month"],
    how="left",
)

Both key columns must match in type. Merging a string "2026-03" against a datetime will produce zero matches with no warning, which feels like a bug and is one of the most common ones people hit.

Overlapping column names

When both tables have a name column, pandas adds suffixes:

python

result = pd.merge(
    orders,
    customers,
    on="customer_id",
    how="left",
    suffixes=("_order", "_customer"),
)

Set them explicitly. The defaults of _x and _y are meaningless two weeks later.

merge vs join vs concat

Quick guide, because the three get confused:

  • pd.merge: match rows on key columns. Use this for anything SQL would call a join.
  • df.join: same idea but matches on the index by default. Convenient when your data is genuinely indexed, confusing otherwise.
  • pd.concat: stack tables on top of each other or side by side without matching keys. This is SQL’s UNION ALL, not a join.

python

all_months = pd.concat([jan_df, feb_df, mar_df], ignore_index=True)

ignore_index=True rebuilds a clean index. Leave it out and you get repeated index values, which cause strange problems later.

Putting both together

A typical end to end task uses them in sequence:

python

# 1. summarise at the grain you want
monthly = df.groupby(["region", "order_month"], as_index=False).agg(
    total_sales = ("net_amount", "sum"),
    orders      = ("order_id", "nunique"),
)

# 2. bring in targets
monthly = pd.merge(
    monthly,
    targets,
    on=["region", "order_month"],
    how="left",
    validate="one_to_one",
)

# 3. derive the comparison
monthly["achievement"] = monthly["total_sales"] / monthly["target_sales"]

# 4. add a group level context column
monthly["region_share"] = (
    monthly["total_sales"] / monthly.groupby("order_month")["total_sales"].transform("sum")
)

Four steps, and between them they cover a very large share of real analysis.

The habits that prevent bugs

  • Print the row count before and after every merge
  • Use validate= so bad joins raise instead of multiplying
  • Use how="left" when enriching, not the default inner
  • Use dropna=False in groupby when totals need to reconcile
  • Reach for transform instead of aggregating and merging back
  • Check key column data types before blaming the merge

None of these are clever. They are just the difference between a number you can defend and a number you cannot.

Leave a Comment