Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Calculate Percent of Total per Group in pandas

Show each product's share of its region's sales, each region's share of the company, and a crosstab where every row adds to 100%. Use groupby transform for shares within groups, crosstab normalize for tables, keep rows with missing groups, and round shares so they still add up to exactly 100%.
Pivots & summariespandasExcel functions in pandas

"What share of North's sales is Widgets?" and "what share of the company is North?" are the questions behind half of all management reports. In Excel they are a pivot table with "Show values as % of parent row" or a =B2/SUMIF(...) column. In pandas they take one line each — once you know that groupby().transform gives you the group total on every row — plus some care over two details that make percentages lie: rows whose group is blank, and rounding that makes a column of shares add up to 99%. This page, part of Creating Pivot Tables from Excel Data with Pandas, covers shares of the grand total, shares within groups and fully normalised tables.

Three different percentages from the same sales Three stacked questions about the same data. Share of grand total: North Widgets is 500 of 2,300, or 21.7%. Share within the group: North Widgets is 500 of North's 1,000, or 50%. Group share of the total: North is 1,000 of 2,300, or 43.5%. Each divides by a different total. North Widgets sold 500 — as a percentage of what? Share of the grand total: 500 / 2,300 = 21.7% value / df["sales"].sum() Share within its region: 500 / 1,000 = 50% value / groupby("region").transform("sum") Region share of the company: 1,000 / 2,300 = 43.5% groupby("region").sum() / total

Prerequisites

Bash
pip install pandas openpyxl

sales.xlsx has eight rows of sales by region and product. One row has no region — a data quality problem that turns out to matter for the percentages.

Step 1 — Share of the grand total

The simplest percentage divides each row by the column total:

Python
import pandas as pd

sales = pd.read_excel("sales.xlsx")
sales["share_of_total"] = sales["sales"] / sales["sales"].sum()
print(sales.round(3).to_string(index=False))
print("check:", sales["share_of_total"].sum())

Keep shares as fractions (0.217) in the data and format them as percentages only when writing; that way they can be summed, compared and charted without converting back. The check line is worth keeping in real code: shares of a total always add up to 1, and anything else means rows were filtered or duplicated between computing the total and using it.

Step 2 — Share within each group with transform

For shares within a region, each row needs its own region's total. groupby().transform("sum") returns exactly that — a Series the same length as the data, holding each row's group total — so the division stays row by row:

Python
sales["region_total"] = sales.groupby("region", dropna=False)["sales"].transform("sum")
sales["share_of_region"] = sales["sales"] / sales["region_total"]
print(sales[["region", "product", "sales", "region_total", "share_of_region"]].round(3).to_string(index=False))

North's three products now show 50%, 30% and 20%, adding to 100% within North. transform is the pandas counterpart of the Excel pattern =B2/SUMIF($A:$A,A2,$B:$B), and far faster, because the group sums are computed once rather than once per row. SUMIF and SUMIFS Equivalent in pandas covers the conditional sums themselves.

Group totals repeated on every row by transform The North rows of the sales sheet with two added columns. region_total holds 1,000 on every North row, the sum of North's sales. share_of_region divides each row's sales by it: Widgets 50%, Gadgets 30%, Gizmos 20%. groupby("region")["sales"].transform("sum") A B C D E 1 region product sales region_total share 2 North Widgets 500 1000 50% 3 North Gadgets 300 1000 30% 4 North Gizmos 200 1000 20%

Step 3 — Do not let blank groups disappear

The row with no region is the trap. By default groupby drops missing keys, so a summary by region silently omits its sales:

Python
by_region = sales.groupby("region")["sales"].sum()
print(by_region.to_dict(), "sum:", by_region.sum(), "vs total:", sales["sales"].sum())

by_region = sales.groupby("region", dropna=False)["sales"].sum()
print((by_region / by_region.sum()).round(3).to_dict())

Without dropna=False, the regions add up to 2,250 while total sales are 2,300, and each region's share is computed against a total that is missing 50. With it, the blank region appears as its own line and the shares describe all the data. In a report, relabel it — sales["region"].fillna("Unassigned") — so readers see the gap instead of a blank row label.

Step 4 — Normalised tables with crosstab

For a whole table of shares — products across regions — pd.crosstab builds the grid and normalize chooses what each cell is a share of: "index" for each row's total, "columns" for each column's, "all" for the grand total:

Python
data = sales.assign(region=sales["region"].fillna("Unassigned"))
by_row = pd.crosstab(data["region"], data["product"], values=data["sales"], aggfunc="sum", normalize="index")
by_col = pd.crosstab(data["region"], data["product"], values=data["sales"], aggfunc="sum", normalize="columns")
print(by_row.round(3))
print(by_col.round(3))

Each row of the first table adds to 1 — the product mix within each region — and each column of the second adds to 1 — the regional split of each product. These are the equivalents of Excel's "% of Row Total" and "% of Column Total" pivot options. Combinations with no sales show 0.0 after normalising, which is right here — East sold no Gadgets — but check that a zero means "none" and not "no data" before publishing it. Build a Crosstab from Excel Data with pandas covers crosstabs in general.

Step 5 — Round shares so they still add to 100%

Rounded independently, shares often total 99% or 101%, which readers notice. Three equal thirds rounded to whole percentages give 33 + 33 + 33 = 99. When the displayed figures must total exactly 100, use the largest-remainder method: round everything down, then hand the missing points to the shares with the biggest remainders:

Python
import numpy as np

def round_to_100(shares, decimals=0):
    scale = 100 * 10 ** decimals
    raw = np.asarray(shares, dtype=float) * scale
    floored = np.floor(raw)
    missing = int(round(scale - floored.sum()))
    order = np.argsort(-(raw - floored))
    floored[order[:missing]] += 1
    return floored / 10 ** decimals

thirds = [1 / 3, 1 / 3, 1 / 3]
print(np.round(np.array(thirds) * 100).tolist(), "->", round_to_100(thirds).tolist())

region_share = by_region / by_region.sum()
print({k: float(v) for k, v in zip(region_share.index.fillna("Unassigned"), round_to_100(region_share, decimals=1))})

The thirds become 34, 33, 33 — one share absorbs the rounding — and the regional shares at one decimal place add to exactly 100.0. Use this only for display: the unrounded fractions stay in the data for any further calculation, and the adjusted figure is never more than one rounding unit away from its true value.

Independent rounding versus largest remainder Two panels showing three equal shares of one third. Rounding each share independently gives 33, 33 and 33, which totals 99. The largest remainder method rounds down and gives the leftover point to the largest remainder, producing 34, 33 and 33, which totals 100. Round each share 33.33 → 33 33.33 → 33 33.33 → 33 total 99 Largest remainder 33.33 → 34 33.33 → 33 33.33 → 33 total 100

Shares next to totals in a pivot table

Readers often want the amount and its share side by side, with a total row. Build the pivot with margins=True, which adds an "All" row and column, and divide by the grand total it computed:

Python
pivot = pd.pivot_table(data, index="region", values="sales", aggfunc="sum", margins=True, margins_name="Total")
pivot["share"] = pivot["sales"] / pivot.loc["Total", "sales"]
print(pivot.round(3))

The Total row shows 2,300 and a share of 1.0, which doubles as a check that nothing was lost on the way. For a product-by-region pivot, divide the whole table by the Total column to get row shares, or by the Total row to get column shares — the same results as crosstab(normalize=...), but with the totals visible in the same table.

Step 6 — Write shares with a percent format

Write the fractions as numbers and apply a percent number format, so Excel can sort and chart them while readers see percentages:

Python
from openpyxl import load_workbook

out = sales[["region", "product", "sales", "share_of_total", "share_of_region"]].fillna({"region": "Unassigned"})
out.to_excel("sales_shares.xlsx", index=False)
wb = load_workbook("sales_shares.xlsx")
ws = wb.active
for row in ws.iter_rows(min_row=2, min_col=4, max_col=5):
    for cell in row:
        cell.number_format = "0.0%"
wb.save("sales_shares.xlsx")
print(pd.read_excel("sales_shares.xlsx")["share_of_region"].round(3).tolist())

For a pivot-style layout, write the crosstab result instead and format its cells the same way. Export a Pandas Pivot Table to Excel (Formatted) covers the layout details.

Common pitfalls

  • Dropping blank groups. groupby omits NaN keys; use dropna=False or fill them.
  • Merging totals back instead of using transform. It works, but it is slower and easy to get wrong when keys have duplicates.
  • Storing percentages as 21.7 instead of 0.217. Keep fractions; format at the end.
  • Filtering after computing totals. Shares then refer to a total that includes rows no longer shown.
  • Averaging percentages across groups. The mean of each region's share of its own products is not a company-wide share; recompute from the underlying sums instead.
  • Negative values in the total. Returns or credits can make shares exceed 100% or go negative; decide whether to net them or show them separately.

Performance notes

groupby().transform("sum") uses pandas' optimised group sums and handles millions of rows in well under a second; passing a string such as "sum" is much faster than a Python lambda. crosstab builds its grid through a pivot and is equally quick for typical report sizes.

Conclusion

Divide by df[col].sum() for shares of the grand total and by groupby(...)[col].transform("sum") for shares within each group, keeping dropna=False so rows with blank groups are not silently dropped from the totals. Use pd.crosstab(..., normalize=...) for tables of row, column or overall shares, round displayed shares with the largest-remainder method when they must total exactly 100%, and write the fractions with a percent format.

Frequently asked questions

How do I get each row's share of its group total? Divide the value by the group total from groupby with transform("sum"). transform returns the group total aligned to every row, so the division happens row by row without a merge.

What is the pandas equivalent of Excel's "Show values as % of row total"? pd.crosstab with values, aggfunc="sum" and normalize="index" gives each row as fractions of its row total; normalize="columns" gives column shares and normalize="all" shares of the grand total.

Why do my rounded percentages add up to 99% or 101%? Rounding each share independently loses or gains fractions of a percent. When the displayed shares must total exactly 100%, use the largest-remainder method: round down, then give the leftover points to the shares with the largest remainders.

What happens to rows whose group is blank? groupby drops NaN keys by default, so their values vanish from the totals and every share is computed against a smaller total. Pass dropna=False, or fill the blank with a label such as "Unassigned", so the shares still describe all the data.

More on pivots & summaries

More on pivots & summaries →