pandas groupby and merge explained with examples

Two operations do most of the heavy lifting in real data work: summarising rows by category (groupby) and combining two tables on a shared column (merge). Both are easy to write and easy to get subtly wrong. This guide uses small tables you can paste and run.

import pandas as pd

The data

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4, 5, 6],
    "customer_id": [10, 11, 10, 12, 11, 10],
    "category": ["books", "books", "games", "games", "books", "toys"],
    "amount": [20.0, 35.5, 60.0, 45.0, 15.0, 25.0],
})

customers = pd.DataFrame({
    "customer_id": [10, 11, 12, 13],
    "name": ["Ada", "Grace", "Linus", "Margaret"],
    "city": ["London", "New York", "Helsinki", "Boston"],
})

groupby: split, apply, combine

groupbySplits a table into groups by the values in a column, applies a calculation to each group, then combines the results answers questions of the form "per X, what is Y?"

orders.groupby("category")["amount"].sum()

category
books    70.5
games   105.0
toys     25.0
Name: amount, dtype: float64

Read it in three steps: pick the grouping column, pick the column to calculate, pick the calculation.

Several statistics at once

summary = orders.groupby("category")["amount"].agg(["count", "sum", "mean"])
print(summary)

Named aggregations (clearer output)

summary = orders.groupby("customer_id").agg(
    orders=("order_id", "count"),
    total_spent=("amount", "sum"),
    biggest_order=("amount", "max"),
)
print(summary)

Naming the outputs avoids the awkward multi-level column headers you get otherwise.

Group by more than one column

orders.groupby(["customer_id", "category"])["amount"].sum()

The result has a two-level index. Flatten it back into ordinary columns with reset_index():

per_customer = (
    orders.groupby(["customer_id", "category"])["amount"]
    .sum()
    .reset_index()
)

Why reset_index() matters

After groupby, the group keys become the index, not a column. If the next step needs them as a column — a merge, a plot, a CSV export — call reset_index() or pass as_index=False:

orders.groupby("category", as_index=False)["amount"].sum()

merge: combine tables on a key

mergeJoins two DataFrames by matching values in a shared column, like a SQL JOIN attaches customer details to each order:

combined = orders.merge(customers, on="customer_id", how="left")
print(combined.head())

Each order row now carries name and city.

The four join types

In our data, customer 13 (Margaret) has no orders. An inner or left merge from orders drops her; how="right" or "outer" keeps her with empty order columns. Choose the join based on the question: "Which customers never ordered?" needs a join that keeps customers.

Different column names

orders.merge(customers, left_on="customer_id", right_on="id")

Use left_on/right_on when the key has different names. You will end up with both columns; drop one afterwards.

The mistake that duplicates rows

If the key is not unique in the right-hand table, every match multiplies the left rows:

dupes = pd.DataFrame({
    "customer_id": [10, 10, 11],
    "tag": ["vip", "newsletter", "vip"],
})

bad = orders.merge(dupes, on="customer_id", how="left")
print(len(orders), len(bad))   # 6 vs 9

Customer 10 has three orders and two tags, so you get six rows for them. Totals computed afterwards are now inflated. Guard against it with validate:

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

If customers ever contained a duplicate customer_id, pandas raises MergeError immediately instead of letting bad numbers flow downstream. Make this a habit.

Useful checks around any merge:

print(len(orders), len(combined))              # row count should usually match a left join
print(combined["name"].isna().sum())           # orders with no matching customer
print(customers["customer_id"].is_unique)      # should be True for a lookup table

Putting them together

Total spend per city, only for cities with more than one order:

result = (
    orders.merge(customers, on="customer_id", how="left")
    .groupby("city", as_index=False)
    .agg(orders=("order_id", "count"), revenue=("amount", "sum"))
    .query("orders > 1")
    .sort_values("revenue", ascending=False)
)
print(result)

Chaining each step on its own line makes the pipeline read like a recipe.

Quick troubleshooting

• KeyError: 'amount' after a groupby — the column was renamed or became the index. Print .columns and use reset_index().
• Numbers too big after a merge — duplicate keys. Use validate=.
• NaN everywhere after a merge — key types differ (10 vs "10"). Compare df["key"].dtype on both sides and cast with .astype(int).
• Merge keys have stray spaces or different case — clean with .str.strip().str.lower() first. See cleaning a messy CSV with pandas.

FAQ

What does groupby do in pandas?

groupby splits a DataFrame into groups based on the values of one or more columns, applies a calculation like sum or mean to each group, and combines the results into a new table.

What is the difference between merge and join in pandas?

merge matches on any columns you name and is the more flexible choice. join is a shortcut that matches on the index by default. For most work, use merge.

Why does my merge create duplicate rows?

The key column is not unique in one of the tables, so each match produces extra rows. Check is_unique on the lookup table and pass validate="many_to_one" to catch it.

How do I keep the group column after groupby?

Call reset_index() on the result, or use groupby(..., as_index=False) so the group keys remain regular columns.

Next reading on Sythra Articles

• Clean a messy CSV with pandas
• Read and write JSON, CSV and text files in Python
• Fixing 'could not convert string to float'