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

Run a Pareto Analysis on Excel Data with pandas

Find the handful of customers or products that make up 80% of revenue. Aggregate, sort, compute cumulative shares, classify items into A, B and C with a clear rule for the item that crosses each threshold, and write the table with a native Excel Pareto chart — columns plus a cumulative line on a secondary axis.
Pivots & summariesCharts & imagespandas

The 80/20 rule is less a law than a question: which few customers, products or causes account for most of the total? A Pareto analysis answers it with a sorted table and a running share — and the answer tells a business where to spend its attention. The mechanics are short in pandas, but two details are regularly got wrong: rows must be aggregated before ranking, and the item that pushes the running total past 80% has to be counted inside the 80%, not just outside it. This page, part of Creating Pivot Tables from Excel Data with Pandas, builds the table, an ABC classification and a native Excel Pareto chart.

Customer revenue sorted, with the cumulative share A Pareto view of ten customers. Columns show revenue in thousands from Northwind at 42 down to Margie at 0.2. The cumulative share line rises steeply: 42% after one customer, 73% after two, 85% after three, then flattens towards 100%. Three customers make up more than 80% of revenue. 0 20 40 60 Northwind Contoso Fabrikam Litware Adatum Proseware Tailspin Wingtip Lucerne Margie 80% reached

Prerequisites

Bash
pip install pandas openpyxl

orders.xlsx has twenty orders for ten customers — each customer appears twice — adding up to 100,000 in revenue.

Step 1 — Aggregate before you rank

A Pareto analysis ranks items, so every row for the same customer has to be combined first. Ranking the raw orders would split a big customer across several lines and understate its share:

Python
import pandas as pd

orders = pd.read_excel("orders.xlsx")
by_customer = (orders.groupby("customer", as_index=False)["amount"].sum()
                     .sort_values("amount", ascending=False, ignore_index=True))
print(by_customer.to_string(index=False))
print("total:", by_customer["amount"].sum())

Each customer now has one row, largest first. ignore_index=True renumbers the rows 0, 1, 2… in rank order, which is handy for counting how many items a threshold needs.

Step 2 — Compute the cumulative share

Divide each amount by the total to get its share, and take the running sum of the shares:

Python
total = by_customer["amount"].sum()
by_customer["share"] = by_customer["amount"] / total
by_customer["cum_share"] = by_customer["share"].cumsum()
by_customer["rank"] = range(1, len(by_customer) + 1)
by_customer["item_share"] = by_customer["rank"] / len(by_customer)
print(by_customer.round(3).to_string(index=False))

cum_share climbs from 0.42 after Northwind to 0.73 after Contoso and 0.85 after Fabrikam, then flattens. item_share — the rank as a fraction of the item count — gives the other half of the 80/20 statement: after three customers, 30% of the customer base, the business has 85% of its revenue. Real data rarely lands on exactly 80/20, and saying "30% of customers bring 85% of revenue" is more useful than forcing the familiar numbers.

Step 3 — Count the items needed to reach 80%

The question "how many customers make up 80%?" has an off-by-one trap. Filtering on cum_share <= 0.8 returns the customers before the threshold is reached — Northwind and Contoso, 73% — and misses Fabrikam, whose revenue carries the total past 80%. Test the cumulative share before each item instead:

Python
by_customer["prev_cum"] = by_customer["cum_share"] - by_customer["share"]
top = by_customer[by_customer["prev_cum"] < 0.8]
print(len(top), "customers reach", round(top["cum_share"].iloc[-1], 3), "of revenue:", top["customer"].tolist())

wrong = by_customer[by_customer["cum_share"] <= 0.8]
print("with cum_share <= 0.8:", len(wrong), "customers,", round(wrong["cum_share"].iloc[-1], 3))

prev_cum < 0.8 says "the total so far had not yet reached 80% when this item was added", which is exactly the set of items needed to get there: three customers covering 85%. The other filter reports two customers covering 73%, which answers a different question — how many items stay under 80% — that nobody asked.

The item that crosses 80% belongs in the top group A table of the first four customers with share, cumulative share and cumulative share before the item. Northwind and Contoso have cumulative shares of 42% and 73%. Fabrikam takes it from 73% to 85%, so its previous cumulative share is below 80% and it is included. Litware starts at 85% and is not. prev_cum < 0.8 includes the crossing item A B C D 1 customer share cum_share prev_cum 2 Northwind 42% 42% 0% 3 Contoso 31% 73% 42% 4 Fabrikam 12% 85% 73% 5 Litware 6.5% 91.5% 85%

Step 4 — Classify items into A, B and C

ABC classification extends the same idea to three tiers: A items bring the first 80% of the total, B the next 15%, C the last 5%. Use the same "before this item" rule for each boundary so every item lands in the tier where it starts:

Python
import numpy as np

by_customer["class"] = np.select(
    [by_customer["prev_cum"] < 0.80, by_customer["prev_cum"] < 0.95],
    ["A", "B"], default="C")
summary = by_customer.groupby("class").agg(customers=("customer", "count"), revenue=("amount", "sum"))
summary["revenue_share"] = (summary["revenue"] / total).round(3)
print(by_customer[["customer", "amount", "cum_share", "class"]].round(3).to_string(index=False))
print(summary)

Three A customers carry 85% of revenue, two B customers the next 10%, and five C customers the last 5%. The 80/95 thresholds are a convention; inventory planners often use 70/90, and some teams set the cut-offs by count instead of value. Keep the thresholds as named constants so the rule is visible and easy to change. Excel IF Formulas as pandas Conditional Columns covers np.select in more detail.

Step 5 — Write the table and a native Pareto chart

A Pareto chart combines columns for the sorted amounts with a line for the cumulative share on a secondary axis. openpyxl builds both as native Excel charts that readers can restyle:

Python
from openpyxl import load_workbook
from openpyxl.chart import BarChart, LineChart, Reference

out = by_customer[["customer", "amount", "cum_share", "class"]]
out.to_excel("pareto.xlsx", index=False, sheet_name="Pareto")
wb = load_workbook("pareto.xlsx")
ws = wb["Pareto"]
n = len(out) + 1
for (cell,) in ws.iter_rows(min_row=2, min_col=3, max_col=3):
    cell.number_format = "0%"

bars = BarChart()
bars.x_axis.delete = False  # Excel hides axes unless this is set
bars.y_axis.delete = False
bars.title = "Revenue by customer"
bars.add_data(Reference(ws, min_col=2, min_row=1, max_row=n), titles_from_data=True)
bars.set_categories(Reference(ws, min_col=1, min_row=2, max_row=n))
bars.y_axis.title = "Revenue"

line = LineChart()
line.x_axis.delete = False
line.y_axis.delete = False
line.add_data(Reference(ws, min_col=3, min_row=1, max_row=n), titles_from_data=True)
line.y_axis.axId = 200
line.y_axis.title = "Cumulative share"
line.y_axis.number_format = "0%"
line.y_axis.scaling.max = 1
line.y_axis.crosses = "max"

bars += line
bars.width, bars.height = 22, 11
ws.add_chart(bars, "F2")
wb.save("pareto.xlsx")
print(len(load_workbook("pareto.xlsx")["Pareto"]._charts), "chart")

Giving the line its own axis id and setting it to cross at the maximum puts the cumulative scale on the right-hand side, fixed from 0 to 100%. The chart reads its data from the cells, so if the table is refreshed with next month's figures in the same place, the chart follows. Add a Combo Chart with a Secondary Axis in openpyxl explains the axis settings in depth.

The ABC classes for the ten customers A summary of the ABC classification. Class A has three customers and 85% of revenue. Class B has two customers and 10%. Class C has five customers and 5%. The thresholds were 80% and 95% of cumulative revenue. Customers Revenue share Rule A 3 85% prev_cum < 80% B 2 10% prev_cum < 95% C 5 5% the rest

Say it in one sentence

The table and chart are for analysts; the people who act on a Pareto analysis usually want one line in an email or at the top of a dashboard. Generate it from the same numbers, so it can never disagree with the table:

Python
n_top, n_all = len(top), len(by_customer)
sentence = (f"{n_top} of {n_all} customers ({n_top / n_all:.0%}) bring "
            f"{top['cum_share'].iloc[-1]:.0%} of revenue: {', '.join(top['customer'])}.")
print(sentence)

"3 of 10 customers (30%) bring 85% of revenue: Northwind, Contoso, Fabrikam." Written to a cell above the table or into the body of the report email, it states the finding in the terms readers already use, and it updates itself on the next run.

Step 6 — Run it per group

Pareto questions are often asked within a segment — which products make up 80% of each region's sales. Compute the cumulative share inside each group by sorting within groups and using groupby().cumsum():

Python
orders["region"] = ["North" if i % 3 else "South" for i in range(len(orders))]
per = (orders.groupby(["region", "customer"], as_index=False)["amount"].sum()
             .sort_values(["region", "amount"], ascending=[True, False]))
per["share"] = per["amount"] / per.groupby("region")["amount"].transform("sum")
per["prev_cum"] = per.groupby("region")["share"].cumsum() - per["share"]
print(per[per["prev_cum"] < 0.8].groupby("region")["customer"].count().to_dict())

Each region gets its own count of customers needed to reach 80% of its revenue. Calculate Percent of Total per Group in pandas explains the transform pattern used for the group shares.

Common pitfalls

  • Ranking rows instead of items. Aggregate by customer or product first.
  • cum_share <= 0.8. It excludes the item that crosses the line; use the cumulative share before the item.
  • Forcing the 80/20 story. Report the actual split — "30% of customers, 85% of revenue".
  • Negative amounts. Credit notes can make shares exceed 100% part-way; net them per item before ranking.

Performance notes

Grouping, sorting and cumsum are all vectorised; a Pareto table over millions of rows takes well under a second. The chart is the slowest part of the job and still negligible. Charts with thousands of categories are unreadable, so for long tails chart the A and B items and show C as a single bar.

Conclusion

Aggregate to one row per item, sort descending, compute each item's share and the running share with cumsum, and count the items needed for 80% using the cumulative share before each item so the crossing item is included. Extend the same rule to A/B/C classes, write the table with a native Pareto chart — columns plus a cumulative line on a secondary axis — and run it within groups with groupby().cumsum() when the question is asked per segment.

Frequently asked questions

What is a Pareto analysis? It ranks items by their contribution to a total and shows the cumulative share, to find the few items that account for most of it — often around 80% of revenue from about 20% of customers, though the actual split varies.

Is the item that crosses 80% in the top group? It should be. The items needed to reach 80% include the one that takes the running total past the line. Classify on the cumulative share before the item is added — prev_cum < 0.8 — so the crossing item counts as A.

What is ABC classification? A three-tier version of the same idea: A items make up the first 80% of the total, B items the next 15%, and C items the last 5%. The thresholds are conventions; inventory teams often use 70/20/10 instead.

How do I make a Pareto chart in Excel from Python? Write the sorted amounts and the cumulative share, then build a bar chart of the amounts and a line chart of the cumulative share on a secondary axis with openpyxl, and combine them.

More on pivots & summaries

More on pivots & summaries →