---
title: "pandas groupby and merge explained with examples"
description: "Summarise data with groupby and combine tables with merge. Worked examples, the join types that matter, and the mistakes that silently duplicate rows."
url: https://articles.sythra.ai/articles/pandas-groupby-merge-explained
slug: pandas-groupby-merge-explained
author: "vaibhavkothari"
author_url: https://articles.sythra.ai/writers/vaibhavkothari
date_published: 2026-11-12T21:11:00.153Z
date_modified: 2026-11-12T21:11:00.153Z
topics: ["Python", "Pandas", "Data"]
reading_time_minutes: 5
publisher: "Sythra"
publisher_url: https://sythra.ai
access: free
language: en
---

# pandas groupby and merge explained with examples

> Summarise data with groupby and combine tables with merge. Worked examples, the join types that matter, and the mistakes that silently duplicate rows.

Source: https://articles.sythra.ai/articles/pandas-groupby-merge-explained · Author: vaibhavkothari · Published: 2026-11-12 · Reading time: 5 min · Topics: Python, Pandas, Data

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.

```python
import pandas as pd
```

## The data

```python
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

groupby answers questions of the form *"per X, what is Y?"*

```python
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

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

### Named aggregations (clearer output)

```python
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

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

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

```python
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`:

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

## merge: combine tables on a key

merge attaches customer details to each order:

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

Each order row now carries `name` and `city`.

### The four join types

| `how=` | Keeps |
|---|---|
| `inner` | Only keys present in **both** tables (default) |
| `left` | Every row from the left table; unmatched right values become `NaN` |
| `right` | Every row from the right table |
| `outer` | Every row from both |

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

```python
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:

```python
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`:

```python
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:

```python
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:

```python
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](https://articles.sythra.ai/articles/clean-messy-csv-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](https://articles.sythra.ai/articles/clean-messy-csv-pandas)
- [Read and write JSON, CSV and text files in Python](https://articles.sythra.ai/articles/python-read-write-files-json-csv)
- [Fixing 'could not convert string to float'](https://articles.sythra.ai/articles/could-not-convert-string-to-float-fix)

## Glossary (terms defined in this article)
- **groupby** (medium) — Splits a table into groups by the values in a column, applies a calculation to each group, then combines the results
- **merge** (medium) — Joins two DataFrames by matching values in a shared column, like a SQL JOIN