Python Foundation for Data Engineers

Chapter 15 — Transforming Data: GroupBy, Merge & Apply

Lesson 15.1 — GroupBy: Aggregating Data by Category

Here's a question that comes up constantly in real data engineering work: "what's the total revenue, broken down by source system?" Or by region, or by product, or by customer. Almost every real business question boils down to the same shape — take a big pile of rows, split it into groups, and total something up within each group.

Back in Week 1, Chapter 5, you'd have solved this with a loop and a running total, one group at a time. Pandas gives you a single, purpose-built tool for exactly this pattern: .groupby().

python
import pandas as pd

sales = pd.DataFrame({
    "source_system": ["CRM", "ERP", "CRM", "Billing", "ERP", "CRM"],
    "total": [149.50, 89.99, 24.00, 310.25, 45.00, 210.00],
})

revenue_by_system = sales.groupby("source_system")["total"].sum()
print(revenue_by_system)
Output / Note

source_system Billing 310.25 CRM 383.50 ERP 134.99 Name: total, dtype: float64

Read that line in three steps, the way pandas actually processes it: .groupby("source_system") splits the table into separate groups, one per unique value in that column. ["total"] picks out the column you actually want to total up. .sum() adds it up, within each group separately. Three ideas, one line, and notice: no loop, no manually building up a dictionary of running totals the way you would have in Week 1.

.sum() is just one option. Plenty of other summaries work exactly the same way:

python
print(sales.groupby("source_system")["total"].mean())
print(sales.groupby("source_system")["total"].count())
Output / Note

source_system Billing 310.250000 CRM 127.833333 ERP 67.495000 Name: total, dtype: float64 source_system Billing 1 CRM 3 ERP 2 Name: total, dtype: int64

.mean() gives the average order value per system. .count() tells you how many orders came from each — genuinely useful for spotting a system that's gone suspiciously quiet, a real, common thing to watch for in pipeline monitoring.

You can compute several summaries at once with .agg(), which is genuinely how most real groupby work looks:

python
summary = sales.groupby("source_system")["total"].agg(["sum", "mean", "count"])
print(summary)
Output / Note
             sum        mean  count

source_system Billing 310.25 310.250000 1 CRM 383.50 127.833333 3 ERP 134.99 67.495000 2

One line, three separate statistics, all broken out by group, in a single readable table. This is genuinely close to what a real "revenue by source" report looks like, in a real pipeline, in production.

Try It Yourself: Using the sales DataFrame above, add a region column with values ["East", "West", "East", "West", "East", "West"]. Group by region instead of source_system, and print the sum, mean, and count of total for each region using .agg().

Lesson 15.2 — Merge & Join: Combining Two DataFrames

Real data almost never lives in one single table. Customer details live in one system, order details in another, product details in a third. Answering a real question — "what did our priority customers actually buy?" — usually means combining tables together. That's what .merge() is for.

python
import pandas as pd

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4],
    "customer_id": [101, 102, 101, 103],
    "total": [149.50, 89.99, 24.00, 310.25],
})

customers = pd.DataFrame({
    "customer_id": [101, 102, 103],
    "name": ["Priya Shah", "Raj Kumar", "Amit Verma"],
    "is_priority": [True, False, True],
})

merged = orders.merge(customers, on="customer_id")
print(merged)
Output / Note

order_id customer_id total name is_priority 0 1 101 149.50 Priya Shah True 1 2 102 89.99 Raj Kumar False 2 3 101 24.00 Priya Shah True 3 4 103 310.25 Amit Verma True

Read .merge(customers, on="customer_id") like a sentence: "for each row in orders, find the matching row in customers where customer_id is equal, and stitch the two together." This is genuinely the exact same idea as the dictionary lookups from Week 1, Chapter 4 — matching records by a shared key — except now it works across two full tables at once, matching every row automatically.

Now you can answer the real question directly:

python
priority_orders = merged[merged["is_priority"] == True]
print(priority_orders[["name", "total"]])
Output / Note
    name   total

0 Priya Shah 149.50 2 Priya Shah 24.00 3 Amit Verma 310.25

One merge, one filter, and you've answered a genuine business question that started out spread across two separate tables.

There's an important detail worth understanding before you merge two tables in real work: what happens to rows that don't find a match? By default, .merge() only keeps rows that matched on both sides — called an inner join. Sometimes you want to keep every row from one side regardless, filling in blanks for anything that didn't match — a left join:

python
orders2 = pd.DataFrame({
    "order_id": [1, 2, 3],
    "customer_id": [101, 102, 999],
    "total": [149.50, 89.99, 45.00],
})

left_merged = orders2.merge(customers, on="customer_id", how="left")
print(left_merged)
Output / Note

order_id customer_id total name is_priority 0 1 101 149.50 Priya Shah True 1 2 102 89.99 Raj Kumar False 2 3 999 45.00 NaN NaN

Notice customer 999 doesn't exist in the customers table at all — with how="left", that order is still kept, with NaN filling in where the customer details should be, instead of quietly disappearing. That NaN is genuinely valuable information here — it's telling you, honestly, "this order references a customer we don't actually have on file," which is exactly the kind of data quality problem worth catching, not hiding.

The default, how="inner", would have silently dropped that order entirely — completely correct in some situations, and a quiet, dangerous data loss in others. Choosing between them deliberately, the same honest judgment call from Chapter 14, is a real, important part of the job.

Try It Yourself: Add a fourth customer to customers, customer_id 104, who has placed no orders at all. Merge customers with orders using how="left" from the customers' side (customers.merge(orders, on="customer_id", how="left")), and observe what happens to that customer's row — this is exactly how you'd find "customers who've never ordered anything," a genuinely common real request.

Lesson 15.3 — apply vs. Vectorized: Why apply Is (Usually) a Smell

Let's revisit the core lesson from Chapter 11, now with a real pandas tool that tempts people into forgetting it: .apply().

Here's the setup — say you want to apply a custom discount rule to every order:

python
import pandas as pd

df = pd.DataFrame({
    "total": [149.50, 89.99, 24.00, 310.25]
})

def apply_discount(total):
    if total > 100:
        return total * 0.9
    return total

df["discounted"] = df["total"].apply(apply_discount)
print(df)
Output / Note

total discounted 0 149.50 134.5500 1 89.99 89.9900 2 24.00 24.0000 3 310.25 279.2250

This works, and it looks reasonable — .apply() runs your function once per row, and collects the results into a new column. But here's the honest problem: underneath, .apply() is genuinely still looping, one row at a time, the same slow way you learned to avoid back in Chapter 11 — it just hides the loop behind a tidier-looking line of code. On a small table like this one, it's completely fine. On a real table with millions of rows, it can be dramatically slower than a properly vectorized version.

Here's the same logic, written the vectorized way, using a tool you actually already met — back in Chapter 12:

python
import numpy as np

df["discounted"] = np.where(df["total"] > 100, df["total"] * 0.9, df["total"])
print(df)
Output / Note

total discounted 0 149.50 134.5500 1 89.99 89.9900 2 24.00 24.0000 3 310.25 279.2250

Same result, but np.where() evaluates the whole column at once — "wherever this condition is true, use this value; otherwise, use that one" — with no row-by-row looping underneath at all. This is genuinely the same shift in thinking as np.select from Chapter 12's hands-on, just for a simpler, two-outcome situation.

Here's an honest, practical rule of thumb, worth remembering more than any specific syntax: if your logic is simple math or a simple condition, reach for a vectorized tool — np.where, np.select, or plain column arithmetic — before reaching for .apply(). Save .apply() for situations where the logic genuinely can't be expressed as simple vectorized operations — calling an external function, complex string parsing, that kind of thing. .apply() isn't wrong to use; it's just worth pausing on, the same "pause before you loop" instinct from Chapter 11, before defaulting to it out of habit.

Try It Yourself: Given prices = pd.DataFrame({"price": [45, 120, 15, 300]}), write the same rule — "apply a 15% discount to anything over 100, otherwise leave it unchanged" — two ways: once using .apply() with a small function, and once using np.where(). Confirm both produce identical results.

Lesson 15.4 — Hands-On: Revenue by Source System

Let's bring this chapter together on the natural next step after Chapter 14's cleaning work: taking a cleaned dataset and actually answering a real business question with it.

We'll start from a cleaned version of the sales data from Chapter 14's hands-on exercise, with a source_system column added — genuinely how this would flow in a real pipeline, one stage feeding into the next:

python
import pandas as pd

sales_clean = pd.DataFrame({
    "order_id": [10432, 10433, 10434, 10435, 10436],
    "customer": ["Priya Shah", "Raj Kumar", "Amit Verma", "Unknown Customer", "Neha Gupta"],
    "total": [149.50, 89.99, None, 310.25, 45.00],
    "source_system": ["CRM", "ERP", "CRM", "Billing", "ERP"],
})

Your task:

  1. Drop rows with a missing total — you can't total up revenue you don't actually know.
  2. Group by source_system, and calculate total revenue and order count for each.
  3. Sort the result so the highest-revenue system appears first.
  4. Print a clean summary.

Here's the shape to build from:

python
valid_sales = sales_clean.dropna(subset=["total"])

summary = valid_sales.groupby("source_system")["total"].agg(["sum", "count"])
summary = summary.rename(columns={"sum": "revenue", "count": "order_count"})
summary = summary.sort_values("revenue", ascending=False)

print(summary)
Output / Note
          revenue  order_count

source_system Billing 310.25 1 CRM 149.50 1 ERP 134.99 2

Notice Amit Verma's order, the one with a missing total, correctly never made it into this report at all — dropna(subset=["total"]) filtered it out before the grouping even happened, exactly the deliberate, honest choice from Chapter 14's closing exercise, rather than letting it silently count as zero and understate nothing while also representing nothing real.

One more genuinely useful step — what percentage of total revenue does each system represent:

python
summary["pct_of_total"] = (summary["revenue"] / summary["revenue"].sum() * 100).round(1)
print(summary)
Output / Note
          revenue  order_count  pct_of_total

source_system Billing 310.25 1 52.7 CRM 149.50 1 25.4 ERP 134.99 2 22.9

That's a genuinely complete, presentable revenue-by-source report — cleaned input, honest handling of a missing value, grouped totals, sorted by importance, and a percentage breakdown, built in a handful of lines. This is very close to the real shape of a report a data engineer would hand off to an analyst or a dashboard, and it flows directly out of the cleaning work from the previous chapter — exactly the kind of connected, end-to-end thinking this whole course has been building toward.

Try It Yourself: Add a region column to sales_clean (any made-up values), and build a second summary table grouped by both source_system and region together — hint: .groupby() accepts a list of column names, not just one. Print the result and see how the grouping changes shape with two levels instead of one.