Data Wrangling & Reshaping

ds1-4 left the coffee-shop sales table clean: a consistent store_id, real datetime values, uniform product names, no duplicates. This chapter combines and reshapes that same cleaned table — and for a reader who already knows SQL (mysql2, mysql3), most of what follows is genuinely familiar material wearing pandas' own syntax.

groupby — Pandas' Own GROUP BY

mysql_foundations_07 (mysql2's own Aggregate Functions and GROUP BY chapter) covered grouping rows and computing an aggregate per group in SQL. Pandas' groupby does the exact same conceptual job:

df.groupby("product")["revenue"].sum()
# SQL equivalent: SELECT product, SUM(revenue) FROM sales GROUP BY product;

df.groupby("product").agg(total_revenue=("revenue", "sum"), orders=("order_id", "count"))
# SQL equivalent: SELECT product, SUM(revenue) AS total_revenue, COUNT(order_id) AS orders
#                 FROM sales GROUP BY product;

.agg() runs multiple aggregations at once, each labeled — the same shape as writing several aggregate functions in one SQL SELECT list.

merge — Pandas' Own JOIN

Introducing a second, small table — a store lookup, mapping store_id to a human-readable name and city:

merged = pd.merge(df, stores_df, on="store_id", how="left")
# SQL equivalent: SELECT * FROM sales LEFT JOIN stores ON sales.store_id = stores.store_id;

That "UNKNOWN" store_id from ds1-4 — kept, not dropped, precisely because a "left" join was used — now has no match in stores_df, and its store_name/city columns come back as NaN rather than the row disappearing. This is exactly the missing-data situation ds1-4 already covered, arriving again from a genuinely different source.

concat — Stacking, Not Matching

merge combines two tables based on matching key values. concat does something structurally different: it glues tables together along an axis, with no matching logic at all — useful for a case like combining July's sales file with August's, where both files share identical columns and just need to be stacked into one longer table.

full_year = pd.concat([july_df, august_df])   # stacked vertically, row-wise

Pivot Tables — Reshaping Long to Wide

A reader who's built a pivot table in a spreadsheet already knows this concept — pandas' own version does the same reshaping, in code:

pd.pivot_table(merged, index="date", columns="product", values="revenue", aggfunc="sum")

This is genuinely just groupby plus a reshape, combined into one call: it groups by two things at once (date and product), aggregates revenue within each combination, and then lays the result out as a real table — one row per date, one column per product — rather than the single flat column of grouped results groupby alone produces.

Putting It Together

merged = pd.merge(df, stores_df, on="store_id", how="left")
summary = merged.groupby(["store_name", "product"]).agg(
    total_revenue=("revenue", "sum"),
    orders=("order_id", "count"),
)

One merge, one grouped aggregation — a real per-store, per-product summary table, the exact shape of analysis ds1-9's own EDA chapter will lean on directly to actually spot patterns in a dataset, rather than just describing the mechanics of getting there.

Hands-On Exercises