Pandas groupby(): Aggregate and Check Sales Reports
Use groupby() to compare records by product, store or another key. Choose the measure and operation explicitly, include missing keys when required and reconcile counts and totals before exporting a report.
Group records, then choose an operation 🔝
groupby() returns a GroupBy object; call an operation such as sum(), agg() or get_group() to obtain a result. Use aggregation for one summary per group, transform() for a result aligned with the source rows and filter() to retain qualifying groups. Select measure columns explicitly rather than adding numeric IDs. Current grouping options include by, level, as_index, sort, group_keys, observed and dropna; omit removed axis and squeeze arguments.
Create the sales sample 🔝
One row represents a sale. qty is the number of units in that sale; sale_id, c_id and p_id are identifiers. The embedded table includes the fields needed for every example. The existing sales.csv download is an optional source; compare its schema before substituting it.
import pandas as pd
import numpy as np
def sales_data():
return pd.DataFrame({"sale_id": [1, 2, 3, 4, 5, 6, 7, 8, 9], "c_id": [2, 2, 1, 4, 2, 3, 2, 3, 2], "p_id": [3, 4, 3, 2, 3, 3, 2, 2, 3], "product": ["Monitor", "CPU", "Monitor", "RAM", "Monitor", "Monitor", "RAM", "RAM", "Monitor"], "qty": [2, 1, 3, 2, 3, 2, 3, 2, 2], "store": ["ABC", "DEF", "ABC", "DEF", "ABC", "DEF", "ABC", "DEF", "ABC"]})
sales = sales_data()
print(sales)
assert sales["sale_id"].is_unique
# Optional file input, after checking its columns:
# sales = pd.read_csv("https://www.plus2net.com/python/download/sales.csv")Expected output
sale_id c_id p_id product qty store
0 1 2 3 Monitor 2 ABC
1 2 2 4 CPU 1 DEF
2 3 1 3 Monitor 3 ABC
3 4 4 2 RAM 2 DEF
4 5 2 3 Monitor 3 ABC
5 6 3 3 Monitor 2 DEF
6 7 2 2 RAM 3 ABC
7 8 3 2 RAM 2 DEF
8 9 2 3 Monitor 2 ABCInspect the first available values in each group 🔝
first() takes the first non-missing value independently for each column by default. With complete rows the result looks like the first matching record; with missing values it can combine values from different records. Use head(1) when you need actual first rows.
sales = sales_data()
print(sales.groupby("product").first())
example = pd.DataFrame({"product": ["Pen", "Pen"], "qty": [None, 2], "price": [5, None]})
print("First available values:")
print(example.groupby("product").first())
print("Actual first row:")
print(example.groupby("product").head(1))
assert example.groupby("product").first().loc["Pen", "qty"] == 2
assert pd.isna(example.groupby("product").head(1).iloc[0]["qty"])Expected output
sale_id c_id p_id qty store
product
CPU 2 2 4 1 DEF
Monitor 1 2 3 2 ABC
RAM 4 4 2 2 DEF
First available values:
qty price
product
Pen 2.0 5.0
Actual first row:
product qty price
0 Pen NaN 5.0Group by product and store 🔝
A list of columns creates one group for each observed combination. first() makes the operation explicit; merely constructing the GroupBy object does not print a grouped table.
sales = sales_data()
result = sales.groupby(["product", "store"]).first()
print(result)
assert result.shape[0] == 5
assert result.loc[("Monitor", "ABC"), "sale_id"] == 1Expected output
sale_id c_id p_id qty
product store
CPU DEF 2 2 4 1
Monitor ABC 1 2 3 2
DEF 6 3 3 2
RAM ABC 7 2 2 3
DEF 4 4 2 2Keep grouping keys as columns and choose key order 🔝
as_index=False returns grouping keys as columns for ordinary aggregations. sort=False avoids sorting group keys; it does not sort the rows inside a group. observed is specified explicitly here for a stable reporting policy.
sales = sales_data()
flat = sales.groupby(["product", "store"], as_index=False).first()
print(flat)
first_seen = sales.groupby("product", as_index=False, sort=False, observed=True).first()
print("First-seen group order:")
print(first_seen)
assert first_seen["product"].tolist() == ["Monitor", "CPU", "RAM"]Expected output
product store sale_id c_id p_id qty
0 CPU DEF 2 2 4 1
1 Monitor ABC 1 2 3 2
2 Monitor DEF 6 3 3 2
3 RAM ABC 7 2 2 3
4 RAM DEF 4 4 2 2
First-seen group order:
product sale_id c_id p_id qty store
0 Monitor 1 2 3 2 ABC
1 CPU 2 2 4 1 DEF
2 RAM 4 4 2 2 DEFRetrieve all rows from a group 🔝
Use a single column name for a single grouping key and the matching value with get_group(). For multiple keys, use a tuple. A missing group raises KeyError; check the available groups before requesting it.
sales = sales_data()
monitor = sales.groupby("product").get_group("Monitor")
print(monitor)
print("Monitor in ABC:")
print(sales.groupby(["product", "store"]).get_group(("Monitor", "ABC")))
assert monitor["sale_id"].tolist() == [1, 3, 5, 6, 9]Expected output
sale_id c_id p_id product qty store
0 1 2 3 Monitor 2 ABC
2 3 1 3 Monitor 3 ABC
4 5 2 3 Monitor 3 ABC
5 6 3 3 Monitor 2 DEF
8 9 2 3 Monitor 2 ABC
Monitor in ABC:
sale_id c_id p_id product qty store
0 1 2 3 Monitor 2 ABC
2 3 1 3 Monitor 3 ABC
4 5 2 3 Monitor 3 ABC
8 9 2 3 Monitor 2 ABCSum quantities for products and stores 🔝
sum() adds units, not the number of sales. Use an explicit numeric measure. Reconcile the grouped quantity with the source before using the report.
sales = sales_data()
by_product = sales.groupby("product")[["qty"]].sum()
by_store = sales.groupby(["product", "store"])[["qty"]].sum()
print(by_product)
print("Product/store quantities:")
print(by_store)
assert by_product["qty"].to_dict() == {"CPU": 1, "Monitor": 12, "RAM": 7}
assert by_store["qty"].sum() == sales["qty"].sum() == 20Expected output
qty
product
CPU 1
Monitor 12
RAM 7
Product/store quantities:
qty
product store
CPU DEF 1
Monitor ABC 10
DEF 2
RAM ABC 3
DEF 4Calculate maximum, minimum, mean and standard deviation 🔝
Choose the qty column before applying reductions. std() defaults to sample standard deviation with ddof=1, so the CPU group with one observation has a missing result. That is different from a measured spread of zero.
sales = sales_data()
grouped = sales.groupby("product")["qty"]
print("Maximum:")
print(grouped.max())
print("Minimum:")
print(grouped.min())
print("Mean:")
print(grouped.mean())
print("Sample standard deviation:")
print(grouped.std())
assert grouped.max().loc["Monitor"] == 3
assert grouped.min().loc["Monitor"] == 2
assert grouped.mean().loc["Monitor"] == 2.4
assert pd.isna(grouped.std().loc["CPU"])Expected output
Maximum:
product
CPU 1
Monitor 3
RAM 3
Name: qty, dtype: int64
Minimum:
product
CPU 1
Monitor 2
RAM 2
Name: qty, dtype: int64
Mean:
product
CPU 1.000000
Monitor 2.400000
RAM 2.333333
Name: qty, dtype: float64
Sample standard deviation:
product
CPU NaN
Monitor 0.547723
RAM 0.577350
Name: qty, dtype: float64Distinguish row count from available quantities 🔝
size() counts rows. count() counts non-missing values in the selected column. They match in the complete sales sample but differ when a quantity is missing. min_count=1 avoids presenting an all-missing quantity sum as zero.
sales = sales_data()
sales["qty"] = sales["qty"].astype("Int64")
sales.loc[1, "qty"] = pd.NA
summary = sales.groupby("product").agg(sales_rows=("sale_id", "size"), known_qty=("qty", "count"))
summary["units"] = sales.groupby("product")["qty"].sum(min_count=1)
print(summary)
assert summary.loc["CPU", "sales_rows"] == 1
assert summary.loc["CPU", "known_qty"] == 0
assert pd.isna(summary.loc["CPU", "units"])Expected output
sales_rows known_qty units
product
CPU 1 0 <NA>
Monitor 5 5 12
RAM 3 3 7Apply a custom group rule and compare a reduction 🔝
Select qty before applying this custom rule. A built-in sum followed by a comparison produces the same result more directly. group_keys controls index keys for applicable apply results; it does not choose the grouping columns. Functions should not mutate their input groups.
sales = sales_data()
custom = sales.groupby("product")["qty"].apply(lambda values: values.sum() > 2)
built_in = sales.groupby("product")["qty"].sum().gt(2)
print(custom)
assert custom.equals(built_in)
assert custom.to_dict() == {"CPU": False, "Monitor": True, "RAM": True}Expected output
product
CPU False
Monitor True
RAM True
Name: qty, dtype: boolApply different functions to different columns 🔝
This price column represents a unit price per sale. The simple mean averages sale prices equally; it is not revenue and is not a quantity-weighted price. A dictionary maps measure columns to their aggregation.
sales = sales_data()
sales["price"] = [30, 20, 40, 20, 30, 40, 20, 30, 40]
rules = {"qty": "sum", "price": "mean"}
summary = sales.groupby(["product", "store"], as_index=False).agg(rules)
print(summary)
assert summary.loc[summary["product"].eq("Monitor") & summary["store"].eq("ABC"), "price"].iloc[0] == 35Expected output
product store qty price
0 CPU DEF 1 20.0
1 Monitor ABC 10 35.0
2 Monitor DEF 2 40.0
3 RAM ABC 3 20.0
4 RAM DEF 4 25.0Return several statistics or use named output columns 🔝
Lists of aggregations create hierarchical output column labels. Named aggregation provides flat, descriptive column names that are convenient for CSV reports.
sales = sales_data()
sales["price"] = [30, 20, 40, 20, 30, 40, 20, 30, 40]
multi = sales.groupby(["product", "store"]).agg({"qty": ["sum"], "price": ["mean", "max", "min"]})
named = sales.groupby(["product", "store"], as_index=False).agg(units=("qty", "sum"), mean_price=("price", "mean"), maximum_price=("price", "max"), minimum_price=("price", "min"))
print(multi)
print("Named outputs:")
print(named)
assert named["units"].sum() == 20Expected output
qty price
sum mean max min
product store
CPU DEF 1 20.0 20 20
Monitor ABC 10 35.0 40 30
DEF 2 40.0 40 40
RAM ABC 3 20.0 20 20
DEF 4 25.0 30 20
Named outputs:
product store units mean_price maximum_price minimum_price
0 CPU DEF 1 20.0 20 20
1 Monitor ABC 10 35.0 40 30
2 Monitor DEF 2 40.0 40 40
3 RAM ABC 3 20.0 20 20
4 RAM DEF 4 25.0 30 20Include missing grouping keys when appropriate 🔝
dropna=True omits missing grouping keys by default. dropna=False includes them. This controls grouping keys, not whether missing measure values are counted. Empty strings are ordinary keys until normalised.
sales = sales_data()
sales.loc[0, "store"] = None
without_missing = sales.groupby(["product", "store"])["qty"].sum()
with_missing = sales.groupby(["product", "store"], dropna=False)["qty"].sum()
print(with_missing)
assert without_missing.sum() == 18
assert with_missing.sum() == 20Expected output
product store
CPU DEF 1
Monitor ABC 8
DEF 2
NaN 2
RAM ABC 3
DEF 4
Name: qty, dtype: int64Choose observed categorical groups explicitly 🔝
Pandas 3 defaults observed to True. Use observed=False if a defined category with no rows must still appear in the report. Specify your intent so a version change does not alter the report layout.
sales = sales_data()
sales["product"] = sales["product"].astype(pd.CategoricalDtype(["CPU", "Monitor", "RAM", "Keyboard"]))
observed = sales.groupby("product", observed=True).size()
defined = sales.groupby("product", observed=False).size()
print("Observed products:")
print(observed)
print("All defined products:")
print(defined)
assert "Keyboard" not in observed.index
assert defined.loc["Keyboard"] == 0Expected output
Observed products:
product
CPU 1
Monitor 5
RAM 3
dtype: int64
All defined products:
product
CPU 1
Monitor 5
RAM 3
Keyboard 0
dtype: int64Attach a group total to each original row 🔝
transform() returns a result aligned with the input rows. Use it to calculate a sale’s share of its product’s units without joining a reduced summary back to the table. Check zero denominators before dividing in other datasets.
sales = sales_data()
sales["product_units"] = sales.groupby("product")["qty"].transform("sum")
sales["unit_share_percent"] = sales["qty"].div(sales["product_units"]).mul(100)
print(sales[["sale_id", "product", "qty", "product_units", "unit_share_percent"]])
assert len(sales) == 9
assert sales.loc[0, "product_units"] == 12
assert np.allclose(sales.groupby("product")["unit_share_percent"].sum(), 100)Expected output
sale_id product qty product_units unit_share_percent
0 1 Monitor 2 12 16.666667
1 2 CPU 1 1 100.000000
2 3 Monitor 3 12 25.000000
3 4 RAM 2 7 28.571429
4 5 Monitor 3 12 25.000000
5 6 Monitor 2 12 16.666667
6 7 RAM 3 7 42.857143
7 8 RAM 2 7 28.571429
8 9 Monitor 2 12 16.666667Retain groups meeting a rule 🔝
filter() returns source rows from qualifying groups, not a summary table. This rule retains products selling more than two units in total. A group total filter differs from selecting individual sales whose qty exceeds two.
sales = sales_data()
selected = sales.groupby("product").filter(lambda group: group["qty"].sum() > 2)
print(selected)
assert selected["product"].unique().tolist() == ["Monitor", "RAM"]
assert len(selected) == 8Expected output
sale_id c_id p_id product qty store
0 1 2 3 Monitor 2 ABC
2 3 1 3 Monitor 3 ABC
3 4 4 2 RAM 2 DEF
4 5 2 3 Monitor 3 ABC
5 6 3 3 Monitor 2 DEF
6 7 2 2 RAM 3 ABC
7 8 3 2 RAM 2 DEF
8 9 2 3 Monitor 2 ABCBuild a revenue and weighted-price report 🔝
For this synthetic sales report, revenue is qty multiplied by unit price. The weighted unit price is total revenue divided by total units. Report sales rows and unique customers separately. Confirm required amounts, non-negative quantities and unique sale IDs before aggregation.
sales = sales_data()
sales["price"] = [30, 20, 40, 20, 30, 40, 20, 30, 40]
assert sales["sale_id"].is_unique
assert sales[["qty", "price"]].notna().all().all()
assert sales["qty"].ge(0).all() and sales["price"].ge(0).all()
sales["revenue"] = sales["qty"] * sales["price"]
report = sales.groupby("product", as_index=False, dropna=False).agg(sales_rows=("sale_id", "size"), customers=("c_id", "nunique"), units=("qty", "sum"), revenue=("revenue", "sum"))
report["weighted_unit_price"] = report["revenue"].div(report["units"].where(report["units"].ne(0)))
print(report)
assert report["units"].sum() == 20
assert report["revenue"].sum() == sales["revenue"].sum() == 610
assert report.loc[report["product"].eq("Monitor"), "revenue"].iloc[0] == 430Expected output
product sales_rows customers units revenue weighted_unit_price
0 CPU 1 1 1 20 20.000000
1 Monitor 5 3 12 430 35.833333
2 RAM 3 3 7 160 22.857143Reconcile totals before exporting 🔝
Sort the flat report by revenue and export with index=False. Customer counts per product can overlap; do not add them to infer unique customers across the entire extract. Check the combined-table reporting exercise and date grouping exercise for related practice.
export = report.sort_values("revenue", ascending=False)
print(export.to_csv(index=False).rstrip())
print("Unique customers across all sales:", sales["c_id"].nunique())
assert export["sales_rows"].sum() == len(sales)
assert sales["c_id"].nunique() == 4
# Optional Colab export:
# export.to_csv("product_sales_summary.csv", index=False)Expected output
product,sales_rows,customers,units,revenue,weighted_unit_price
Monitor,5,3,12,430,35.833333333333336
RAM,3,3,7,160,22.857142857142858
CPU,1,1,1,20,20.0
Unique customers across all sales: 4Common questions 🔝
Why is a group missing? Check dropna and observed. Why is count smaller than the group size? The selected measure has missing values. Why is first() not a complete original row? It selects non-missing values per column; use head(1) for actual rows. Why did a grouped total change after a merge? Check repeated join keys; use merge() validation. How do I group an index level? Use level with a named or numbered index level; do not specify both by and level. How do I create a matrix? Use pivot_table(). How do I count categories only? Use value_counts(). How do I group numeric bands? Create them with cut() first.
Exercise: add a RAM sale and update the summary 🔝
Add a RAM sale with quantity 1 and unit price 25. Predict RAM units, RAM revenue and total revenue. Keep the original sales table unchanged and recompute from an extended copy.
Exercise solution 🔝
The added sale contributes one unit and revenue 25. RAM reaches eight units and revenue 185; overall revenue reaches 635.
extra = pd.DataFrame({"sale_id": [10], "c_id": [4], "p_id": [2], "product": ["RAM"], "qty": [1], "store": ["ABC"], "price": [25]})
extended = pd.concat([sales.drop(columns="revenue"), extra], ignore_index=True)
extended["revenue"] = extended["qty"] * extended["price"]
answer = extended.groupby("product").agg(units=("qty", "sum"), revenue=("revenue", "sum"))
print(answer)
assert answer.loc["RAM", "units"] == 8
assert answer.loc["RAM", "revenue"] == 185
assert answer["revenue"].sum() == 635
assert len(sales) == 9Expected output
units revenue
product
CPU 1 20
Monitor 12 430
RAM 8 185Practice in Google Colab 🔝
Open in Google Colab View on GitHub
Run the examples in order, change sales quantities, prices and grouping options, then reconcile the summaries. Save your own copy to keep edits. All sample tables are included in the notebook.
Continue with the Data Cleaning hub, where(), loc, at, NumPy where(), fillna() and iloc.
Reference: Pandas DataFrame.groupby documentation.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Subscribe to our YouTube Channel here
Python Video Tutorials