GroupBy and Merge

Two of the most powerful operations in Pandas. groupby does for DataFrames what GROUP BY does in SQL — aggregates rows into groups. merge does what SQL JOIN does — combines two DataFrames on a shared key. Together they handle 80% of real-world data transformation work.

Learning Objectives

• Use .groupby() with aggregation functions
• Apply multiple aggregations with .agg()
• Transform groups without collapsing rows with .transform()
• Merge DataFrames with pd.merge() (INNER, LEFT, RIGHT, OUTER)
• Concatenate DataFrames with pd.concat()

---

GroupBy — Aggregation

import pandas as pd

df = pd.read_csv("orders.csv", parse_dates=["order_date"])

# Count orders per status
print(df.groupby("status")["order_id"].count())
# status
# cancelled    3
# completed   12
# pending      5
# dtype: int64

# Total revenue per customer
revenue_by_customer = df[df["status"] == "completed"].groupby("customer_id")["total"].sum()
print(revenue_by_customer.sort_values(ascending=False).head(5))

Common aggregations

# Single aggregation
df.groupby("status")["total"].mean()
df.groupby("category")["total"].sum()
df.groupby("customer_id")["order_id"].count()

# `.size()` vs `.count()`
df.groupby("status").size()          # counts ALL rows including NaN
df.groupby("status")["total"].count() # counts non-NaN values in "total"

# Unique count per group
df.groupby("customer_id")["product"].nunique()  # unique products per customer

---

Multiple Aggregations with .agg()

# Multiple aggregations on one column
customer_stats = df[df["status"] == "completed"].groupby("customer_id")["total"].agg(
    orders="count",
    total_revenue="sum",
    avg_order="mean",
    max_order="max",
    min_order="min",
)
print(customer_stats.sort_values("total_revenue", ascending=False))

Sample output:

customer_id	orders	total_revenue	avg_order	max_order	min_order
C003	6	1638.95	273.16	389.00	179.98
C001	5	924.97	184.99	298.00	35.99

# Different aggregations on different columns
summary = df.groupby("status").agg(
    order_count=("order_id", "count"),
    total_revenue=("total", "sum"),
    avg_total=("total", "mean"),
    unique_customers=("customer_id", "nunique"),
)

Custom aggregation functions

# Custom function in agg
def range_value(x):
    return x.max() - x.min()

df.groupby("category")["total"].agg(
    revenue="sum",
    spread=range_value,      # custom function
)

---

Grouping by Multiple Columns

# Monthly revenue by category
monthly_category = (
    df[df["status"] == "completed"]
    .assign(month=df["order_date"].dt.to_period("M"))
    .groupby(["month", "category"])["total"]
    .sum()
    .reset_index()
)
print(monthly_category.head(8))

.reset_index() — flatten the grouped result

After .groupby(), the group columns become the index. Use .reset_index() to convert them back to regular columns.

# Without reset_index: customer_id is the index
result = df.groupby("customer_id")["total"].sum()
print(result.index)     # customer_id is the index

# With reset_index: customer_id is a regular column
result = df.groupby("customer_id")["total"].sum().reset_index()
print(result.columns)   # ['customer_id', 'total']

---

.transform() — Group Aggregates Without Collapsing

transform returns a Series of the same length as the original DataFrame, making it perfect for adding group-level statistics as a column.

# Add each customer's total spend as a column on every order row
df["customer_total"] = df.groupby("customer_id")["total"].transform("sum")

# Add group average — useful for comparing individual vs group
df["category_avg"] = df.groupby("category")["total"].transform("mean")
df["vs_category_avg"] = df["total"] / df["category_avg"] - 1  # % above/below avg

# Rank within group
df["rank_in_category"] = df.groupby("category")["total"].transform(
    lambda x: x.rank(ascending=False)
)

For mid-level analysts

transform is the Pandas equivalent of SQL window functions with PARTITION BY. It's how you get "each customer's contribution to total revenue" without losing the row-level detail.

---

Merge — Joining DataFrames

pd.merge() is the Pandas equivalent of SQL JOIN.

orders = pd.read_csv("orders.csv")
customers = pd.read_csv("customers.csv")
products = pd.read_csv("products.csv")

INNER JOIN (default)

# Only rows with a match in both DataFrames
result = pd.merge(orders, customers, on="customer_id")
# Or with different column names:
result = pd.merge(orders, customers, left_on="cust_id", right_on="customer_id")

LEFT JOIN

# All orders, plus customer info where it exists
result = pd.merge(orders, customers, on="customer_id", how="left")
# Rows with no matching customer get NaN for customer columns

RIGHT and OUTER JOIN

# All customers, even those without orders
result = pd.merge(orders, customers, on="customer_id", how="right")

# All rows from both tables
result = pd.merge(orders, customers, on="customer_id", how="outer")

Joining three tables

full = (
    orders
    .merge(customers, on="customer_id", how="left")
    .merge(products, on="product_id", how="left")
)
print(full.shape)
print(full.columns.tolist())

Detecting duplicates after merge

before = len(orders)
after = len(result)
if after > before:
    print(f"Warning: row count increased from {before} to {after} — possible one-to-many join")

Merge can silently multiply rows

If customers has multiple rows per customer_id (a data quality issue), joining on that column will multiply every order by the number of customer rows. Always check row counts before and after.

---

pd.concat() — Stacking DataFrames

Use concat to combine DataFrames vertically (adding more rows) or horizontally (adding more columns).

# Vertical stack — add more rows (same columns)
jan_df = pd.read_csv("orders_jan.csv")
feb_df = pd.read_csv("orders_feb.csv")
mar_df = pd.read_csv("orders_mar.csv")

q1 = pd.concat([jan_df, feb_df, mar_df], ignore_index=True)
print(q1.shape)  # combined row count

# All CSV files in a directory
from pathlib import Path
import pandas as pd

all_dfs = [pd.read_csv(f) for f in Path("monthly_exports").glob("*.csv")]
combined = pd.concat(all_dfs, ignore_index=True)

concat requires matching column names

If two DataFrames have different column names, mismatched columns fill with NaN. Check df.columns for each before concatenating, and rename if needed.

---

Practical Example — Customer Revenue Report

import pandas as pd

orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
customers = pd.read_csv("customers.csv")

# Step 1: calculate order totals and filter
completed = orders[orders["status"] == "completed"].copy()
completed["total"] = completed["quantity"] * completed["unit_price"]

# Step 2: aggregate by customer
customer_summary = (
    completed
    .groupby("customer_id")
    .agg(
        total_orders=("order_id", "count"),
        total_revenue=("total", "sum"),
        avg_order=("total", "mean"),
        last_order=("order_date", "max"),
    )
    .reset_index()
)

# Step 3: join customer details
report = customer_summary.merge(
    customers[["customer_id", "first_name", "last_name", "segment"]],
    on="customer_id",
    how="left"
)

# Step 4: rank by revenue
report["revenue_rank"] = report["total_revenue"].rank(ascending=False, method="dense")

# Step 5: sort and display
report = report.sort_values("total_revenue", ascending=False)
print(report.head(10))

---

Practice Exercises

Warm-up 1. Group orders by status and calculate the count, total revenue, and mean revenue for each status. 2. Find the top 3 customers by total spend (completed orders only). 3. Merge orders and customers on customer_id (LEFT JOIN). Check the shape before and after.

Main 4. Calculate monthly revenue by category. Show month, category, and total revenue sorted by month ascending. 5. Use .transform() to add a column showing each order's percentage of its customer's total spend. 6. Find all customers who have placed orders but don't exist in the customers table (use an OUTER JOIN + NaN check).

Stretch 7. Build a full customer report: orders count, total spend, average order value, days since last order, and a segment label (Champion if spend > £1000, Regular if > £200, New otherwise). 8. Load 3 monthly CSV files, concatenate them, then group by category and month to produce a quarterly pivot.

---

Interview Questions

[Beginner] What does .groupby() do in Pandas?

[Beginner] What is the difference between pd.merge() and pd.concat()?

[Mid-level] What is the difference between .agg() and .transform() after a groupby?

[Mid-level] After a LEFT JOIN, how do you find rows that had no match in the right table?

[Senior] A groupby on a 100M-row DataFrame is taking 3 minutes. What are your options to speed it up?

---

← Filtering Data · Next: Data Analysis Workflows →