Appendix: Try It Yourself!

A ready-made pandas, dplyr, and SQL exercise

The shared dataset

We’ll use a small sales dataset: transactions across three product categories over several months. Define it once in each language — the analysis is identical.

R

Python

---

Part 1: Group, summarise, and sort

The most common data-wrangling task: total revenue by category, sorted descending.

In R — the dplyr pipe

In Python — pandas groupby

Side-by-side comparison

Tip

Key idiom differences

Concept	dplyr (R)	pandas (Python)
Start a chain	df |> ...	(df...)
Group rows	group_by(category)	.groupby("category")
Summarise	summarise(name = fn(col))	.agg(name=("col", "fn"))
Sort descending	arrange(desc(col))	.sort_values("col", ascending=False)
Column names quoted?	Bare names: category	Always strings: "category"

---

Part 2: Filter, then group

Now: average revenue per unit, for months that aren’t February.

In R

In Python

Tip

Idiom flags

• filter() → .query() — both accept an expression, but pandas requires a string.
• mutate() → .assign() — pandas uses a lambda to reference the DataFrame mid-chain.
• .groups = "drop" in dplyr → .reset_index() in pandas — both un-nest the groups.

---

Part 3: SQL ↔︎ tidyverse cameo

The same aggregation in all three languages. Notice how SQL is the conceptual middle ground — its GROUP BY maps directly to both group_by() and .groupby().

🗄 SQL

SELECT
  category,
  SUM(revenue)  AS total_revenue,
  SUM(units)    AS total_units
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

📦 dplyr (R)

sales |>
  group_by(category) |>
  summarise(
    total_revenue = sum(revenue),
    total_units   = sum(units)
  ) |>
  arrange(desc(total_revenue))

🐍 pandas (Python)

(sales
  .groupby("category")
  .agg(
    total_revenue=("revenue","sum"),
    total_units  =("units",  "sum"))
  .sort_values("total_revenue",
               ascending=False)
  .reset_index()
  )

SQL clause	dplyr verb	pandas method
WHERE	filter()	.query()
SELECT col, fn(col)	summarise()	.agg()
GROUP BY	group_by()	.groupby()
ORDER BY ... DESC	arrange(desc(...))	.sort_values(..., ascending=False)
AS name	name = expr inside summarise()	name=("col","fn") inside .agg()

---

Match the syntax!

For each dplyr snippet, choose its pandas equivalent.

dplyr (R)	pandas (Python)
filter(month == "Jan")	-- choose -- .filter(month="Jan") .query('month == "Jan"') .where("month", "Jan")
group_by(category)	-- choose -- .groupby("category") .group("category") .aggregate("category")
summarise(total = sum(revenue))	-- choose -- .summarise(total=sum("revenue")) .sum("revenue") .agg(total=("revenue", "sum"))
arrange(desc(revenue))	-- choose -- .sort_values("revenue") .sort_values("revenue", ascending=False) .order_by("revenue", desc=True)
mutate(rev_per_unit = revenue / units)	-- choose -- .assign(rev_per_unit=lambda df: df["revenue"] / df["units"]) .mutate(rev_per_unit="revenue/units") .add_column("rev_per_unit", df["revenue"] / df["units"])
select(month, revenue)	-- choose -- .select(["month", "revenue"]) .keep("month", "revenue") [["month", "revenue"]]

---

Your turn — fill in the blanks

Here’s the complete R version of a new task: total units sold per month, keeping only months with more than 30 total units.

Now complete the pandas equivalent below and run it:

sales
  .groupby("month")
  .agg(total_units=("units", "sum"))
  .reset_index()
  .query("total_units > 30")
  .sort_values("month")

---

Concept check

In dplyr you can write filter(total_units > 30) immediately after summarise() because the pipe passes the result forward as a new data frame. In pandas, why do you need .reset_index() before .query("total_units > 30") — and what happens if you skip it?