Section 4 · Analysis: Group, Join, Reshape
Ranking and Binning
16 min3 min readPython exercise
Lesson at a glance
Rank customers by spend, measure how concentrated revenue is, and turn a continuous number into segments with cut (fixed boundaries) and qcut (equal-sized groups).
No video here — just the real thing: a written lesson, runnable examples, a quiz and practice.
“Who are our best customers?” is a question every sales or marketing team asks, and the honest answer has two parts: a ranked list, and how much the top of that list matters. At Cartwheel the answer is striking, and it shapes how you should read every revenue number, including the Q4 jump.
One row per customer
Start with a customer-level table: one row per customer, with orders and spend, built with the groupby from Lesson 4.1.
import pandas as pd
items = pd.read_csv("order_items.csv")
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount"])
orders = orders.merge(items.groupby("order_id", as_index=False)["revenue"].sum(), on="order_id", validate="one_to_one")
orders = orders[orders["status"] != "cancelled"]
cust = orders.groupby("customer_id").agg(orders=("order_id", "count"), spend=("revenue", "sum"))
print(len(cust), "customers with a non-cancelled order")
print(cust["spend"].describe().round(0))503 customers. The median spent 551 dollars, the mean 888, the top one 5,370. When the mean sits far above the median, a few large values are pulling it up; Lesson 5.1 returns to what that means for reporting averages.
Ranking
import pandas as pd
items = pd.read_csv("order_items.csv")
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount"])
orders = orders.merge(items.groupby("order_id", as_index=False)["revenue"].sum(), on="order_id", validate="one_to_one")
orders = orders[orders["status"] != "cancelled"]
cust = orders.groupby("customer_id").agg(orders=("order_id", "count"), spend=("revenue", "sum"))
cust["rank"] = cust["spend"].rank(ascending=False, method="min")
cust["percentile"] = cust["spend"].rank(pct=True)
print(cust.sort_values("rank").head(5).round(2))
top20 = cust.nlargest(int(len(cust) * 0.2), "spend")
print(len(top20), "customers =", round(top20["spend"].sum() / cust["spend"].sum(), 3), "of revenue")rank gives every row its position, with 1 for the highest spend because of ascending=False. Ties are where the methods differ: "min" gives tied customers the same rank and skips the next numbers, like a sports table, "dense" never skips, "first" breaks ties by row order, and the default "average" gives them the mean rank. pct=True turns ranks into percentiles between 0 and 1, which compare across groups of different sizes.
The last line is the concentration figure: the top 100 customers, a fifth of the base, generate 55.5% of revenue. That is less extreme than the textbook 80/20 rule, but still means losing a handful of big customers would hurt more than losing a hundred small ones.
Ranking within groups
Country managers want their own top customers, not a global list where the largest markets crowd everyone else out. rank works on a grouped column too, restarting at 1 inside each group:
import pandas as pd
items = pd.read_csv("order_items.csv")
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
customers = pd.read_csv("customers.csv")
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount"])
orders = orders.merge(items.groupby("order_id", as_index=False)["revenue"].sum(), on="order_id", validate="one_to_one")
orders = orders[orders["status"] != "cancelled"]
cust = orders.groupby("customer_id", as_index=False).agg(spend=("revenue", "sum"))
cust = cust.merge(customers[["customer_id", "country"]], on="customer_id", validate="one_to_one")
cust["rank_in_country"] = cust.groupby("country")["spend"].rank(ascending=False, method="first")
print(cust[cust["rank_in_country"] == 1].sort_values("spend", ascending=False).round(0))Filtering on rank_in_country == 1 keeps each country’s top spender, eight rows in all; keeping ranks of 3 or less would give a top-three list per country. method="first" guarantees exactly one row per rank even when two customers tie, which is what you want for a fixed-length list. This “rank within group, then filter” pattern answers a whole family of questions: the best-selling product per category, the first order of each customer, the latest ticket per order.
cut: bins with boundaries you choose
Marketing wants spend bands they can name in a campaign. You choose the edges:
import numpy as np
import pandas as pd
items = pd.read_csv("order_items.csv")
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount"])
orders = orders.merge(items.groupby("order_id", as_index=False)["revenue"].sum(), on="order_id", validate="one_to_one")
orders = orders[orders["status"] != "cancelled"]
cust = orders.groupby("customer_id").agg(orders=("order_id", "count"), spend=("revenue", "sum"))
cust["band"] = pd.cut(cust["spend"], bins=[0, 250, 500, 1000, 2000, np.inf],
labels=["<250", "250-500", "500-1k", "1k-2k", "2k+"])
print(cust.groupby("band", observed=True)["spend"].agg(["count", "sum"]).round(0))Each edge pair defines a bin, closed on the right by default: (250, 500] includes 500 but not 250. The 57 customers in the 2k+ band, 11% of the base, brought in 173,407 dollars, more than any other band. cut returns a categorical column whose categories keep your order, so the table lists the bands from smallest to largest rather than alphabetically.
qcut: equal-sized groups
When you want groups of equal size rather than fixed boundaries, qcut cuts at quantiles:
import pandas as pd
items = pd.read_csv("order_items.csv")
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount"])
orders = orders.merge(items.groupby("order_id", as_index=False)["revenue"].sum(), on="order_id", validate="one_to_one")
orders = orders[orders["status"] != "cancelled"]
cust = orders.groupby("customer_id").agg(orders=("order_id", "count"), spend=("revenue", "sum"))
cust["quartile"] = pd.qcut(cust["spend"], q=4, labels=["Q1 low", "Q2", "Q3", "Q4 high"])
share = cust.groupby("quartile", observed=True)["spend"].sum() / cust["spend"].sum()
print(share.round(3))
print(pd.qcut(cust["orders"], q=4, duplicates="drop").value_counts().sort_index())The top quartile of customers brings 63.2% of revenue, the bottom quartile 2.8%. The last line shows qcut’s limit: a third of customers placed exactly one order, so the two lowest quartile edges are both 1, and plain qcut raises ValueError: Bin edges must be unique. duplicates="drop" merges the repeated edges, leaving fewer, unequal groups. For counts with many ties, cut with hand-picked edges is usually clearer.
In the exercise you segment customers yourself. That completes the analysis toolkit; Section 5 turns it on real questions, starting with what an average can and cannot tell you.
Key takeaways
rank(ascending=False, method="min")numbers rows by value; themethoddecides how ties are numbered, andpct=Truegives percentile ranks.pd.cutassigns values to bins with boundaries you choose; usenp.infas the last edge so nothing falls outside.pd.qcutmakes bins with (nearly) equal numbers of rows, such as quartiles; when many values tie, passduplicates="drop".- Both return a categorical column, so group by it with
observed=Trueand sort by the category order, not alphabetically. - A concentration figure such as “the top 20% of customers bring 55% of revenue” is often the most useful single number you can give a business.