Grouping, pivoting and reshaping

Two keys, long and wide

Group by two columns and you get one row per pair. That's a long table: every combination stacked in one column. For reading, a wide table is often clearer, with one key down the side and the other across the top. unstack() turns long into wide, and pivot_table does the grouping and the reshaping in one call:

import pandas as pd

orders = pd.read_csv("https://api.gradient.test/v1/datasets/orders.csv").drop_duplicates()
orders = orders[orders["sku"] != "TEST-000"].copy()
orders["revenue"] = orders["qty"] * orders["unit_price"]
orders["month"] = orders["date"].str[:7]

long = orders.groupby(["store", "member"])["revenue"].sum()
print(long.head(4))
print(long.unstack())

wide = orders.pivot_table(index="month", columns="store", values="revenue", aggfunc="sum")
print(wide.head(3).round(0))

pivot_table(index=..., columns=..., values=..., aggfunc=...) reads like a spreadsheet pivot: rows from one column, columns from another, cells holding a summary of a third. Long tables suit further grouping and most charting code. Wide tables suit reading and side-by-side comparison. You'll move between the two constantly. stack() and melt() go back from wide to long.

transform: a group result on every row

agg gives one row per group. Sometimes you want the group's result written next to each row instead, to compare a row with its group. That's transform: same groups, same functions, but the answer has the same length as the original table.

import pandas as pd

orders = pd.read_csv("https://api.gradient.test/v1/datasets/orders.csv").drop_duplicates()
orders = orders[orders["sku"] != "TEST-000"].copy()
orders["revenue"] = orders["qty"] * orders["unit_price"]

orders["order_total"] = orders.groupby("order_id")["revenue"].transform("sum")
orders["share_of_order"] = (orders["revenue"] / orders["order_total"]).round(2)
multi = orders[orders["order_total"] > orders["revenue"]]
print(multi[["order_id", "sku", "revenue", "order_total", "share_of_order"]].head(4))

orders["store_avg_line"] = orders.groupby("store")["revenue"].transform("mean")
above = orders["revenue"] > orders["store_avg_line"]
print(round(above.mean(), 3), "of lines are above their own store's average line")

Each line now knows its order's total, so its share is one division. The second transform puts each store's average line next to every row, so "above average *for its store*" is one comparison. Without transform you'd build the group table, then join it back onto the rows by store. That join is exactly what transform does for you.

import pandas as pd

orders = pd.read_csv("https://api.gradient.test/v1/datasets/orders.csv").drop_duplicates()
orders = orders[orders["sku"] != "TEST-000"].copy()
orders["month"] = orders["date"].str[:7]

units = None
busiest = {}

Running totals

A running total, or cumulative sum, adds each value to everything before it: year-to-date revenue, say. cumsum() does it, and after a groupby it restarts for each group:

import pandas as pd

orders = pd.read_csv("https://api.gradient.test/v1/datasets/orders.csv").drop_duplicates()
orders = orders[orders["sku"] != "TEST-000"].copy()
orders["revenue"] = orders["qty"] * orders["unit_price"]
orders["month"] = orders["date"].str[:7]
orders["year"] = orders["date"].str[:4]

monthly = orders.groupby(["store", "year", "month"], as_index=False)["revenue"].sum()
monthly["year_to_date"] = monthly.groupby(["store", "year"])["revenue"].cumsum()
print(monthly[monthly["store"] == "denver"].tail(4).round(0))

as_index=False keeps the group keys as ordinary columns, so the result is a plain long table you can keep working with. Grouping the cumsum by store and year makes every store's total start again from zero each January, which is what "year to date" means.

Small groups lie

Rank the marketplace listings by rating and look at what's on top:

import pandas as pd

listings = pd.read_csv("https://api.gradient.test/v1/datasets/marketplace.csv")
listings = listings.drop_duplicates(subset="listing_id")

print(listings.nlargest(5, "rating")[["listing_id", "rating", "reviews"]])
trusted = listings[listings["reviews"] >= 30]
print(trusted.nlargest(5, "rating")[["listing_id", "rating", "reviews"]])

The raw top five are mostly listings with a handful of reviews. One delighted buyer is enough for a 5.0. An average over a few values swings much more than an average over hundreds, so the extremes of any ranking fill up with small groups, at the top and the bottom. That's chance, not quality.

The fix is a rule set before you look: rank only groups with enough data. With groupby, compute the count alongside the average, then filter: agg(rating=("rating", "mean"), n=("rating", "size")), then [lambda t: t["n"] >= 30]. Pick the threshold by asking how many values you'd need before you'd bet on the average. Then report it, so readers know what was left out.

import pandas as pd

orders = pd.read_csv("https://api.gradient.test/v1/datasets/orders.csv").drop_duplicates()
orders = orders[orders["sku"] != "TEST-000"].copy()
orders["revenue"] = orders["qty"] * orders["unit_price"]

per_order = orders.groupby("order_id").agg(
    store=("store", "first"),
    member=("member", "first"),
    total=("revenue", "sum"),
)
print(len(per_order), "orders")

summary = per_order.groupby("store").agg(member_share=("member", "mean"), avg_order=("total", "mean"))
print(summary.round(3))
print(per_order.pivot_table(index="store", columns="member", values="total", aggfunc="mean").round(2))