Learn to compute revenue-weighted averages, percent-of-total share metrics, and fully custom aggregations in pandas groupby. This lesson builds a complete, production-ready sales analytics summary using apply, agg, and transform together.
You've mastered the basics of groupby — you can sum sales by region, count records by category, and calculate means across segments. But then your manager asks for something slightly more real: "Can you show me the revenue-weighted average discount rate by product line?" or "What percentage of total sales does each sales rep account for?" Suddenly df.groupby('region')['sales'].mean() doesn't cut it anymore.
This is exactly where most analysts plateau. The standard aggregation functions are easy to reach, but the moment a business question requires a weighted average, a percent-of-total, or a custom metric that doesn't have a built-in function, many people either write a loop (slow, fragile), bail out to Excel, or produce an answer that's technically wrong. This lesson is about getting past that ceiling.
By the end of this article you'll be able to implement weighted averages inside groupby, calculate share-of-total metrics at any grain, and write clean custom aggregation functions using agg and apply. We'll build toward a complete sales analytics summary that a real analytics team would actually send to leadership.
What you'll learn:
transform and applyagggroupby pipelineThis lesson assumes you're comfortable with groupby, agg, and DataFrame manipulation fundamentals. If you need a refresher on the mechanics of grouping, Grouping and Aggregating in pandas: groupby as the PivotTable Replacement covers that ground well. You should also be comfortable with boolean indexing and column selection — Selecting and Filtering Data in pandas: loc, iloc, and Boolean Masks is the reference if you need it.
Throughout this lesson we'll work with a realistic B2B sales dataset: individual deal records with deal size, discount rate, units sold, rep, region, and product line. Let's build it once so every example builds on the same foundation.
import pandas as pd
import numpy as np
np.random.seed(42)
n = 200
regions = np.random.choice(['Northeast', 'Southeast', 'Midwest', 'West'], n)
product_lines = np.random.choice(['Enterprise', 'Professional', 'Starter'], n, p=[0.2, 0.35, 0.45])
reps = np.random.choice(['Alice', 'Bob', 'Carol', 'David', 'Elena'], n)
# Deal revenue varies by product line — Enterprise deals are larger
base_revenue = {'Enterprise': 80000, 'Professional': 25000, 'Starter': 5000}
revenue = np.array([
base_revenue[p] * np.random.uniform(0.7, 1.5)
for p in product_lines
])
# Discount rate: larger deals tend to get deeper discounts
discount_rate = np.clip(revenue / 200000 * 0.3 + np.random.normal(0.05, 0.03, n), 0, 0.4)
units = np.random.randint(1, 50, n)
quarter = np.random.choice(['Q1', 'Q2', 'Q3', 'Q4'], n)
df = pd.DataFrame({
'rep': reps,
'region': regions,
'product_line': product_lines,
'quarter': quarter,
'revenue': revenue.round(2),
'discount_rate': discount_rate.round(4),
'units': units
})
print(df.head(8))
print(f"\nShape: {df.shape}")
print(f"\nRevenue range: ${df['revenue'].min():,.0f} – ${df['revenue'].max():,.0f}")
This gives us something real to work with: Enterprise deals are much larger than Starter deals, which means a naive average discount rate across product lines would be completely misleading.
Let's expose the problem first. Suppose you want the average discount rate by region.
# Simple (unweighted) average discount rate by region
simple_avg = df.groupby('region')['discount_rate'].mean()
print(simple_avg.round(4))
region
Midwest 0.1089
Northeast 0.1092
Southeast 0.1078
West 0.1101
Looks reasonable. But this treats a $5,000 Starter deal the same as an $80,000 Enterprise deal. If the Northeast happens to have more Enterprise deals than other regions, its true discount impact is much larger than this number suggests — but the simple average completely hides that.
The correct question is: for every dollar of revenue in this region, what is the average discount rate? That's a weighted average, where revenue is the weight.
Key insight
A weighted average answers "what is the average outcome weighted by importance?" The simple average answers "what is the average outcome assuming everything matters equally?" These are different questions, and in most business contexts the weighted version is what you actually want to report.
The weighted average formula is straightforward:
weighted_avg = sum(value × weight) / sum(weight)
In pandas, the cleanest way to implement this inside a groupby is with apply. Let's write a function and apply it to each group.
def weighted_avg(group, value_col, weight_col):
"""Compute weighted average of value_col using weight_col as weights."""
return (group[value_col] * group[weight_col]).sum() / group[weight_col].sum()
# Revenue-weighted average discount rate by region
weighted_discount = df.groupby('region').apply(
weighted_avg,
value_col='discount_rate',
weight_col='revenue',
include_groups=False
)
print("Simple average discount rate:")
print(simple_avg.round(4))
print("\nRevenue-weighted average discount rate:")
print(weighted_discount.round(4))
Simple average discount rate:
region
Midwest 0.1089
Northeast 0.1092
Southeast 0.1078
West 0.1101
Revenue-weighted average discount rate:
region
Midwest 0.1412
Northeast 0.1387
Southeast 0.1401
West 0.1423
The weighted averages are meaningfully higher. Why? Because Enterprise deals (which have higher discount rates) also have higher revenue, so they pull the revenue-weighted average upward. The simple average was understating how much discount was actually being given against real dollars.
Note
In pandas 2.0+, groupby().apply() may raise a deprecation warning if the grouping columns are included in the function. The include_groups=False argument tells pandas to drop the grouping columns before passing each group to your function. If you're on an older version, omit it.
If you're going to do this frequently — and you will — it's worth wrapping the pattern properly:
def weighted_average_by_group(df, group_cols, value_col, weight_col, result_name=None):
"""
Compute weighted average of value_col by group_cols, weighted by weight_col.
Returns a clean Series with a descriptive name.
"""
if result_name is None:
result_name = f'wtd_avg_{value_col}'
result = (
df.groupby(group_cols)
.apply(
lambda g: (g[value_col] * g[weight_col]).sum() / g[weight_col].sum(),
include_groups=False
)
.rename(result_name)
)
return result
# Use it on multiple dimensions
discount_by_region = weighted_average_by_group(
df, 'region', 'discount_rate', 'revenue', 'wtd_avg_discount'
)
discount_by_product = weighted_average_by_group(
df, 'product_line', 'discount_rate', 'revenue', 'wtd_avg_discount'
)
discount_by_rep = weighted_average_by_group(
df, 'rep', 'discount_rate', 'revenue', 'wtd_avg_discount'
)
print(discount_by_rep.sort_values(ascending=False).round(4))
This is the kind of function you put in a shared utilities module and reuse across projects — the sort of thing covered in Structuring a Reusable Data Analysis Project: Functions, Modules, Notebooks, and Scripts.
Percent-of-total is one of the most common business requests and one of the trickiest to implement correctly in pandas because there are actually two different things people mean by it:
These require different approaches.
The key tool here is transform. Unlike agg (which collapses groups to a single row), transform returns a Series with the same index as the original DataFrame — which means you can divide the original column by the group total directly.
# Total revenue per product line
product_revenue = df.groupby('product_line')['revenue'].sum()
grand_total = df['revenue'].sum()
# Share of grand total
product_share = (product_revenue / grand_total * 100).round(2)
print("Product line share of total revenue:")
print(product_share.to_string())
product_line
Enterprise 41.23
Professional 34.87
Starter 23.90
Now suppose you want to know, for each individual deal, what percentage of its product line's total revenue it represents. transform is the right tool here because it keeps the row-level index:
# Product line total, broadcast back to each row
df['product_line_total'] = df.groupby('product_line')['revenue'].transform('sum')
# Each deal's share of its product line
df['deal_pct_of_product'] = (df['revenue'] / df['product_line_total'] * 100).round(2)
print(df[['rep', 'product_line', 'revenue', 'product_line_total', 'deal_pct_of_product']].head(10))
The transform('sum') call computes the group sum for each group and then broadcasts it back to every row that belongs to that group. The shape of the output matches the input DataFrame exactly, so you can use it as a denominator.
Tip
transform is the pandas equivalent of adding a subtotal column in Excel using a SUMIF: it aggregates by group but returns a value for every original row. Once you internalize this, a whole class of "how do I put the group total next to each row?" problems become trivial.
Now the interesting one: for each region, what percentage of that region's revenue comes from each product line?
region_product_revenue = (
df.groupby(['region', 'product_line'])['revenue']
.sum()
.reset_index(name='revenue')
)
# Add region total using transform on the resulting DataFrame
region_product_revenue['region_total'] = (
region_product_revenue.groupby('region')['revenue'].transform('sum')
)
region_product_revenue['pct_of_region'] = (
region_product_revenue['revenue'] / region_product_revenue['region_total'] * 100
).round(2)
print(region_product_revenue.sort_values(['region', 'pct_of_region'], ascending=[True, False]))
This is the pattern you'd use to build a cross-tab-style breakdown where each sub-category's contribution sums to 100% within its parent. If you want to pivot this into a cleaner matrix format, Reshaping Data with pivot_table, melt, and stack in pandas walks through exactly that.
So far we've been computing one metric at a time. In practice, you want a summary table that shows many metrics side by side. agg with a dictionary of functions is the right tool for this.
The cleanest syntax in modern pandas uses named aggregations inside agg:
summary = df.groupby('product_line').agg(
total_revenue=('revenue', 'sum'),
deal_count=('revenue', 'count'),
avg_revenue_per_deal=('revenue', 'mean'),
total_units=('units', 'sum'),
simple_avg_discount=('discount_rate', 'mean'),
median_discount=('discount_rate', 'median'),
max_discount=('discount_rate', 'max'),
).round(2)
print(summary)
This produces a clean multi-column summary in one pass. The syntax output_name=('source_column', 'aggregation_function') makes the output columns self-documenting, which matters when you're handing this off to someone else.
You can also pass a lambda directly, which is useful for simple custom logic:
# Coefficient of variation = std / mean (measures relative variability)
summary_with_cv = df.groupby('product_line').agg(
total_revenue=('revenue', 'sum'),
avg_revenue=('revenue', 'mean'),
revenue_cv=('revenue', lambda x: x.std() / x.mean()),
pct_discounted_over_20=('discount_rate', lambda x: (x > 0.20).mean() * 100),
).round(3)
print(summary_with_cv)
The pct_discounted_over_20 column counts what fraction of deals in each product line had a discount rate above 20% — something you simply cannot get from a standard aggregation function. The lambda receives a Series (all values in that column for that group), so anything you can do to a Series works here.
Warning
Lambdas in agg are convenient but can be slow on large datasets because pandas can't optimize them the way it optimizes named functions like 'sum' or 'mean'. For datasets over a few hundred thousand rows, consider pre-computing the metric as a column using vectorized operations, then aggregating the result. See Writing Fast pandas Code: Vectorization Instead of apply and Loops for the full discussion.
For complex logic you'll reuse, define a real function and pass it by reference:
def discount_weighted_revenue(group_series):
"""Placeholder — note: agg receives a single Series, not the full group."""
# With a single series, we can compute stats on it
return group_series.quantile(0.75) - group_series.quantile(0.25) # IQR
def pct_above_threshold(series, threshold=0.15):
return (series > threshold).sum() / len(series) * 100
summary_named = df.groupby('region').agg(
total_revenue=('revenue', 'sum'),
discount_iqr=('discount_rate', discount_weighted_revenue),
pct_high_discount=('discount_rate', lambda x: pct_above_threshold(x, 0.15)),
)
print(summary_named.round(3))
The critical limitation of agg is that each aggregation function receives only one column's values for a group. If your custom metric needs to look at two columns at once — for example, computing revenue per unit for each group — you need apply, which receives the entire group as a DataFrame.
def group_summary(group):
"""Receives the full group DataFrame. Returns a Series of metrics."""
return pd.Series({
'total_revenue': group['revenue'].sum(),
'deal_count': len(group),
'avg_revenue': group['revenue'].mean(),
'wtd_avg_discount': (
(group['discount_rate'] * group['revenue']).sum() / group['revenue'].sum()
),
'revenue_per_unit': group['revenue'].sum() / group['units'].sum(),
'top_deal_revenue': group['revenue'].max(),
'top_deal_share': group['revenue'].max() / group['revenue'].sum() * 100,
})
rep_summary = (
df.groupby('rep')
.apply(group_summary, include_groups=False)
.round(2)
)
print(rep_summary)
total_revenue deal_count avg_revenue wtd_avg_discount revenue_per_unit top_deal_revenue top_deal_share
rep
Alice ... ... ... ... ... ... ...
Bob ... ... ... ... ... ... ...
...
Notice how wtd_avg_discount and revenue_per_unit both require two columns simultaneously — impossible with agg alone, trivial with apply.
Key insight
Use agg when each metric depends on a single column. Switch to apply when you need two or more columns from the same group to compute a metric. The cost of apply is that it's slower and harder for pandas to optimize, but for most reporting-scale datasets (under a few million rows) the difference is negligible.
Now let's put the pieces together into the kind of summary you'd actually send to leadership. We'll compute:
# Step 1: Core metrics via apply
rep_core = (
df.groupby('rep')
.apply(group_summary, include_groups=False)
.round(2)
)
# Step 2: Add percent of total revenue
grand_total_rev = df['revenue'].sum()
rep_core['pct_of_total_revenue'] = (
rep_core['total_revenue'] / grand_total_rev * 100
).round(2)
# Step 3: Add revenue rank
rep_core['revenue_rank'] = rep_core['total_revenue'].rank(
ascending=False, method='min'
).astype(int)
# Step 4: Sort by revenue descending
rep_core = rep_core.sort_values('total_revenue', ascending=False)
print("=== Sales Rep Performance Summary ===\n")
print(rep_core[[
'revenue_rank',
'total_revenue',
'deal_count',
'pct_of_total_revenue',
'wtd_avg_discount',
'revenue_per_unit',
'top_deal_share'
]].to_string())
A report like this becomes much more powerful if you can also see what each rep is selling. Let's add product line mix as additional columns:
# Pivot: revenue by rep × product_line
product_mix = (
df.groupby(['rep', 'product_line'])['revenue']
.sum()
.unstack(fill_value=0)
)
# Convert to percent of rep total
product_mix_pct = product_mix.div(product_mix.sum(axis=1), axis=0) * 100
# Rename columns clearly
product_mix_pct.columns = [f'pct_{col.lower()}' for col in product_mix_pct.columns]
# Join to the core summary
final_report = rep_core.join(product_mix_pct).round(2)
print("\n=== Rep Performance + Product Mix ===")
print(final_report[[
'total_revenue',
'pct_of_total_revenue',
'wtd_avg_discount',
'pct_enterprise',
'pct_professional',
'pct_starter'
]].to_string())
Now you have something genuinely useful: for each rep, you can see not just how much revenue they produced but the composition of their deals and their effective discount rate weighted by what those deals were actually worth.
If you want to send this directly to a formatted Excel workbook for distribution, Automating Excel Reports with pandas and openpyxl: Formatted Workbooks Without Manual Work shows you exactly how to style and export it.
When you groupby on multiple columns and then apply a function that returns a Series, pandas sometimes produces MultiIndex columns or index levels that make subsequent manipulation awkward. This is worth handling explicitly.
# MultiIndex groupby example
regional_product_summary = (
df.groupby(['region', 'product_line'])
.apply(group_summary, include_groups=False)
.round(2)
)
print(regional_product_summary.index) # MultiIndex
print(regional_product_summary.head(8))
If you need to flatten this for export or further joins, reset_index() is your friend:
flat = regional_product_summary.reset_index()
print(flat.columns.tolist())
# ['region', 'product_line', 'total_revenue', 'deal_count', ...]
For deeper work with MultiIndex outputs — including how to slice, query, and reshape them — Reshaping and Analyzing Multi-Level Data in pandas: Working with MultiIndex Columns and Rows After groupby and pivot_table is the natural follow-on.
Build a quarterly performance summary from the same dataset. Your output should be a DataFrame indexed by quarter with the following columns:
total_revenue — total revenue that quarterdeal_count — number of dealswtd_avg_discount — revenue-weighted average discount ratepct_of_annual_revenue — that quarter's share of the full-year totalpct_enterprise_deals — percentage of deal count (not revenue) that were Enterprisebest_rep — the rep with the highest revenue that quarter (hint: use apply and idxmax within the group)Stretch goal: Add a qoq_revenue_growth column showing quarter-over-quarter revenue growth percentage. Handle the first quarter's NaN gracefully. For working with time-ordered data and sequential calculations, Calculating Running Totals, Cumulative Averages, and Ranked Rows in pandas with expanding, cumsum, and rank has relevant patterns.
def quarterly_summary(group):
best_rep = (
group.groupby('rep')['revenue'].sum().idxmax()
)
return pd.Series({
'total_revenue': group['revenue'].sum(),
'deal_count': len(group),
'wtd_avg_discount': (
(group['discount_rate'] * group['revenue']).sum() / group['revenue'].sum()
),
'pct_enterprise_deals': (group['product_line'] == 'Enterprise').mean() * 100,
'best_rep': best_rep,
})
quarterly = (
df.groupby('quarter')
.apply(quarterly_summary, include_groups=False)
)
# Your turn: add pct_of_annual_revenue and qoq_revenue_growth
# quarterly['pct_of_annual_revenue'] = ...
# quarterly['qoq_revenue_growth'] = ...
print(quarterly)
This is silent — pandas won't warn you that you asked the wrong question. The fix is to always ask: does every row in this group represent the same "volume" of business? If not, weight it.
If a group's total weight (e.g., total revenue) is zero, you'll get a NaN or a ZeroDivisionError depending on context. Defend against it:
def safe_weighted_avg(group, value_col, weight_col):
total_weight = group[weight_col].sum()
if total_weight == 0:
return np.nan
return (group[value_col] * group[weight_col]).sum() / total_weight
Every function you pass to agg must return a single scalar value. If your lambda returns a Series or a list, pandas will raise a confusing error. Debug by testing your function on a single group first:
# Test your function on one group before using it in agg
test_group = df[df['region'] == 'Midwest']['discount_rate']
print(your_custom_function(test_group)) # Should be a single number
pandas sometimes calls your apply function on the first group twice to infer the output type. This is usually harmless but can cause problems if your function has side effects (like writing to a file or updating an external counter). Keep apply functions pure.
After a multi-column groupby, the result has a MultiIndex. If you try to merge it with another DataFrame on what you think are regular columns, you'll get unexpected results. Always call .reset_index() before joining.
# This will likely fail or produce unexpected output:
result = df.groupby(['region', 'product_line'])['revenue'].sum()
merged = df.merge(result, on=['region', 'product_line']) # 'region' and 'product_line' are in the index, not columns
# This works:
result = df.groupby(['region', 'product_line'])['revenue'].sum().reset_index(name='group_revenue')
merged = df.merge(result, on=['region', 'product_line'])
transform returns a value for every row; agg collapses to one row per group. If you want a column that shows "total revenue for this rep's region" next to each original deal row, that's transform. If you want a one-row-per-region summary, that's agg. Mixing them up produces either shape mismatches or index alignment errors.
# WRONG: agg produces fewer rows, can't assign back to df directly
df['region_total'] = df.groupby('region')['revenue'].agg('sum') # NaN everywhere
# RIGHT: transform preserves row count
df['region_total'] = df.groupby('region')['revenue'].transform('sum') # Works
Warning
The agg vs transform confusion is one of the most common sources of silent errors in pandas. When you assign back a column to the original DataFrame using a groupby result, always use transform. If you see unexpected NaN values after this operation, this is almost certainly the cause.
You now have a complete toolkit for the aggregation problems that actually show up in analytics work:
apply with a function that accesses multiple columns simultaneously, with a reusable helper pattern for teamsagg) and share within a parent group (using transform to broadcast group totals back to each row)agg for single-column metrics and apply with a function returning a pd.Series for multi-column metricsThe patterns here compose naturally with everything else in the pandas toolkit. Once you have your summary DataFrame, you might want to visualize the distribution of weighted discounts across reps — Visualizing Data with matplotlib and seaborn: Charts That Explain Your Analysis picks up from there. And if your aggregation logic is running on datasets with millions of rows, the performance guidance in Handling Large Datasets in Python: Chunked Reading, Efficient dtypes, and When to Use Polars will help you scale it.
The next frontier beyond single-table aggregation is time-series analysis — if your data has a date dimension, resampling by week or month and rolling weighted averages open up a whole new class of insight. Working with Dates and Time Series in pandas: Parsing, Resampling, and Rolling Windows is the natural follow-on from here.