Cleaning & transforming data
Pivots & summaries
Pivot tables, group-bys, running totals and rankings — in pandas and in the workbook.
Start with the full guides
The long-form treatment of pivots & summaries, with the reasoning the shorter pages assume.
- GuideCreating Pivot Tables from Excel Data with PandasBuild pivot tables from Excel data in Python: ingest and clean a workbook, aggregate with pandas pivot_table, add totals, filter, and export a formatted report.
- GuideBuilding Finance and Operations Reports in Excel with PythonThe month-end pack, the aging report, the commission statement and the stock reorder list share one shape: exports in, reference data joined, a period chosen, numbers calculated, totals tied back to the source and exceptions surfaced first. This guide builds that shape once — a budget-versus-actual report from a ledger export — and shows how each finance and operations report reuses it.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toAdd a Native Excel Pivot Table with PythonShip a real, refreshable Excel pivot table rather than a static grid — write the source as a table, drive Excel through xlwings on Windows, and the cross-platform alternatives.
- How-toAdd Subtotals and Grand Totals to a pandas Pivot Tablepandas' margins=True adds only a grand total. Add Excel-style subtotal rows for each outer group of a multi-level pivot, compute them from the raw data so averages stay correct, and write them to Excel as bold rows or live SUBTOTAL formulas.
- How-toBin Values into Ranges Like Excel FREQUENCY with pd.cutGroup order values, ages or delivery times into bands such as "0-10", "10-50" and "over 100". Use pd.cut with explicit edges and labels, decide which side of each band is closed, stop values outside the edges from silently becoming NaN, match Excel's FREQUENCY counts exactly, and use qcut for equal-sized groups.
- How-toBuild a Cohort Retention Table from Excel DataTurn an order export into the classic retention triangle: assign each customer to the month of their first order, count how many come back in each following month, convert counts to percentages, compare cohorts, weight an average retention curve correctly, and write a colour-scaled table to Excel.
- How-toBuild a Crosstab from Excel Data with pandasCount how often categories occur together in an Excel sheet with pandas.crosstab — frequency tables, row and column percentages, totals, sums instead of counts, banded numbers, categories with zero counts and missing values that silently drop rows — then write the result to Excel with percentage formats.
- How-toBuild a Funnel Conversion Table from Event DataTurn a raw event log exported to Excel into a conversion funnel with pandas: one timestamp per user and step, an ordered funnel that only counts steps reached after the previous one within a time window, step-to-step and overall conversion, drop-off counts, a breakdown by channel, median time between steps, and an Excel sheet with data bars.
- How-toBuild a Product Co-occurrence Matrix from Order LinesFind out which products are bought together from an order-lines sheet: build an order × product incidence table, multiply it by its transpose to count co-occurrences, read the diagonal as order counts, rank pairs by support, confidence and lift, avoid the pitfalls of quantities and duplicate lines, and write a heatmap matrix and a top-pairs list to Excel.
- How-toBuild an RFM Customer Segmentation from Excel DataSegment customers by recency, frequency and monetary value from an Excel order history: compute the three measures per customer, score them 1 to 5 with quantiles that survive ties, map score combinations to named segments such as Champions and At risk, summarise each segment, and write a workbook with a recency-by-frequency grid.
- How-toCalculate Customer Lifetime Value from Order HistoryEstimate what a customer is worth from an Excel order export with pandas: historical value to date, cumulative margin by months since first order for each cohort, the simple formula of average order value times frequency times margin times expected lifetime, why customers who joined recently must not be compared with old ones, a breakdown by acquisition channel, and a summary sheet with the assumptions written down.
- How-toCalculate Moving Averages with pandas rollingSmooth daily sales with a 7-day moving average, per store, the way an Excel AVERAGE over a sliding range does. Learn why rolling(7) and rolling("7D") differ when days are missing, what min_periods does to the first rows, trailing versus centred windows, and how to roll within groups without scrambling rows.
- How-toCalculate Percent of Total per Group in pandasShow 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%.
- How-toCompare Two Periods Side by Side in a PivotBuild the classic "this period versus last period" table from Excel sales data with pandas: choose comparable periods (same month last year, month to date versus the same days last month), pivot them side by side, compute change and percentage change that cope with new and discontinued products, rank the biggest movers, show each line's contribution to the total change, and write it to Excel with coloured arrows.
- How-toCount Distinct Values per Group in pandasCount unique customers per region, distinct products per order or active users per month — the "Distinct Count" that Excel pivot tables only offer through the Data Model. Use nunique, decide how blanks, case and spacing count, get several distinct counts at once, and avoid adding distinct counts across groups.
- How-toCreate a Pivot Table from Excel with PandasCreate a pivot table from an Excel file with pandas: read the workbook, aggregate with pivot_table using index, columns, values and aggfunc, then export the result.
- How-toExport a Pandas Pivot Table to Excel (Formatted)Build a pandas pivot_table, flatten its MultiIndex, export with to_excel, then add a styled header, number formats, and a grand-total row with openpyxl.
- How-toGet the Top N Rows per Group in pandasList the three best-selling products in each region, the largest invoice per customer, or the top five reps per quarter. Use sort_values with groupby().head(), decide what happens to ties with rank, rank aggregated totals rather than raw rows, and fold everything else into an "Other" line.
- How-toNumber Rows Within Each Group in pandasAdd a running number that restarts for every customer, region or order — the pandas equivalent of Excel's COUNTIF($A$2:A2,A2) and SQL's ROW_NUMBER() OVER (PARTITION BY ...): cumcount in a chosen order, ranks that handle ties, numbering consecutive runs, numbering the groups themselves, and writing the result to Excel.
- How-toPivot Text Values Instead of Numbers in pandasTurn a long list of statuses, shift codes or comments into a grid — projects by week, employees by day — when the values are text, not numbers: pivot for unique combinations, the "duplicate entries" error and how to resolve it, pivot_table with first, last or joined values, picking the latest status by timestamp, and colouring the grid by value in Excel.
- How-toRun a Pareto Analysis on Excel Data with pandasFind 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.
- How-toRun an ABC-XYZ Inventory ClassificationClassify stock items by how much they matter and how predictable they are with pandas: ABC classes from cumulative share of consumption value, XYZ classes from the coefficient of variation of monthly demand, the nine-cell ABC-XYZ matrix with item counts and value shares, a stocking policy per cell, items with too little history, and an Excel matrix sheet a planner can use.
- How-toSort Pivot Rows in a Custom Order with pandasPivot tables sort labels alphabetically — Apr, Aug, Dec, Feb… and L, M, S, XL — which is never what a report wants. Impose a business order with ordered Categoricals or reindex, keep empty categories visible, push "Other" and totals to the end, sort by value within a fixed group order, and make sure the order survives the trip to Excel.
- How-toTurn a Pivot Table into a Heatmap in ExcelMake a pandas pivot readable at a glance by colouring it in Excel: a sequential colour scale for volumes, a diverging scale centred on zero for changes, totals left out of the scale, a separate scale per row when rows differ in size, numbers hidden for a pure heatmap, and the same rules in openpyxl and xlsxwriter.
- How-toUnpivot a Wide Excel Sheet with pandas meltTurn a month-per-column spreadsheet into tidy rows — pd.melt, choosing id_vars, parsing the column names into real dates, and handling multi-level headers.
- How-toUse Different Aggregations per Column in a pandas PivotSum revenue, average price, count distinct customers and find the last order date in one summary table. Use pivot_table's aggfunc dictionary or groupby named aggregation, compute weighted averages correctly, add a total row that uses the same rules, and write readable column names to Excel.
- How-toBuild a Customer Churn Report from Subscription DataTurn monthly subscription data into a churn report with pandas: customers gained and lost each month, logo churn rate, an MRR bridge from new, expansion, contraction and churned revenue, net revenue retention, and a cohort retention table written to Excel with a colour scale, with checks that every month's bridge adds up.
- How-toRefresh Excel Pivot Tables and Queries with PythonRecalculate a workbook, refresh pivot caches and Power Query connections, and save the result — with xlwings or pywin32 on Windows, plus the headless alternatives.
- How-toAVERAGEIFS, MAXIFS and MINIFS Equivalents in pandasTranslate Excel's AVERAGEIFS, MAXIFS and MINIFS to pandas — boolean masks for one-off results, groupby for tables, transform for per-row columns — and reproduce Excel's handling of blanks, text, wildcards, date ranges and the empty case where MAXIFS returns 0.
- How-toFORECAST, TREND, SLOPE and RSQ Equivalents in PythonReproduce Excel's linear trend functions — SLOPE, INTERCEPT, RSQ, CORREL, STEYX, TREND and FORECAST.LINEAR — with NumPy, matching the spreadsheet to many decimal places. Fit a trend per product, use dates as the x-axis, project the next periods, and know where a straight line stops being a forecast.
- How-toExcel GROUPBY and PIVOTBY Function Equivalents in pandasExcel 365's GROUPBY and PIVOTBY functions summarise a table with one formula. Translate them to pandas: groupby with a grand total row, sorting by the value column, filtering rows before grouping, several aggregations at once, subtotals for two levels, pivot_table with row and column totals, and PERCENTOF shares — plus why a workbook that relies on them will not calculate outside Excel 365.
- How-toRANK and PERCENTILE Formulas in pandasMatch RANK.EQ with method='min' and ascending=False, rank within groups in one call, and replace LARGE, PERCENTILE and QUARTILE with nlargest, quantile and qcut.
- How-toRunning Totals and Year-Over-Year Growth in pandascumsum for running totals, shift and pct_change for period comparisons, rolling for moving averages — and why a year-over-year join beats shift when months go missing.
- How-toSTDEV.S, STDEV.P, VAR.S and VAR.P in pandas and NumPypandas .std() and NumPy std() give different answers for the same numbers — one matches Excel's STDEV.S, the other STDEV.P. Match each Excel function exactly with ddof, choose between sample and population deliberately, handle single-value groups, and compute per-group spread, coefficients of variation and z-scores.
- How-toSUBTOTAL and AGGREGATE Equivalents in pandasReproduce Excel's SUBTOTAL and AGGREGATE in pandas: totals that skip the subtotal rows already in a sheet, the 1–11 versus 101–111 function numbers that include or ignore hidden rows, and AGGREGATE's option to ignore error values — reading which rows are subtotals and which are hidden with openpyxl, and checking every result against LibreOffice.
- How-toSUMPRODUCT and Weighted Averages in pandasTranslate Excel's SUMPRODUCT to pandas — total value from quantity times price, conditional sums with boolean arrays, and weighted averages such as average price weighted by units. Covers weighted averages per group, missing weights, and why the plain mean of averages gives the wrong answer.
- How-toTEXTJOIN Equivalent in pandas (Join Values per Group)Combine many rows into one cell — every product a customer bought, "North, South, East" — the way Excel's TEXTJOIN does. Join per group with groupby and str.join, skip blanks like ignore_empty, convert numbers, de-duplicate and sort within each group, respect Excel's cell length limit, and split the result back into rows.
- How-toTurn a Pivot Table Export Back into Tidy DataSomeone sends a copy-pasted pivot table instead of the data behind it. Rebuild one row per observation with pandas: combine the two header rows into year and quarter, forward-fill the row labels that pivot layouts leave blank, drop subtotal and grand-total rows, unpivot the value columns, convert the numbers, and prove the result by recomputing the totals the export showed.
- How-toHow Missing Values Change Sums, Averages and CountsBlank cells quietly change summary figures, and pandas and Excel do not always agree: what SUM, AVERAGE, COUNT and COUNTA do with blanks, text placeholders and zeros, how pandas sum, mean, count and size compare, why an all-blank group sums to 0, min_count, averages over blanks versus zeros, and a reconciliation that proves both tools give the same answers.
- How-toRoll Up Totals Through a Parent-Child HierarchySum amounts posted to the bottom of a cost-centre, account or product hierarchy up through every level with pandas: a parent-child table turned into each node's path to the top, totals for every ancestor in one join, depth and indented labels, amounts posted to non-leaf nodes, a check that the top equals the sum of all postings, and an indented Excel report with subtotals at every level.
- How-toClean and Aggregate Excel Data with Polars ExpressionsClean a messy Excel export with Polars expressions — trim and normalise text, parse amounts typed with currency symbols and thousands separators, find the values that failed, add conditional columns, then aggregate with group_by, window shares and a pivot, and write the summary back to Excel.
- How-toJoin and Pivot Excel Data with PolarsCombine and reshape workbook data with Polars: read several sheets, join them with validate to catch duplicate keys, find unmatched rows with an anti join, group and aggregate, pivot quarters into columns, unpivot back, run the pipeline lazily, and write a formatted result with write_excel — with the pandas equivalents alongside.
- How-toUse Polars Window Functions on Excel DataAdd group-level figures to every row of an Excel table without collapsing it: Polars over() for share of group total, rank within group, running totals, month-on-month change and top N per group, the order_by and mapping_strategy options that control how windows behave, and the same calculations side by side with pandas transform.
- How-toReconcile Excel Totals Against Control Totals with PythonProve that an Excel import or report is complete by reconciling it with control totals — record counts, amount totals and hash totals supplied by the source system — overall and per group, at every stage of a pipeline, with money compared in cents and a reconciliation sheet that shows exactly where figures diverge.
- How-toCalculate Month-to-Date and Year-to-Date Totals with pandasCompute MTD, QTD and YTD figures as of a report date, running period-to-date columns that reset each month or year, the same-period-last-year comparison that cuts last year at the same day, fiscal year-to-date with an April start, and a tidy summary table for an Excel report — including the leap-day and incomplete-data traps.
- How-toGroup Excel Data by Fiscal Year and ISO Week with pandasAssign every Excel row to a fiscal year, fiscal period and ISO week with pandas — any fiscal start month, labels such as FY2026/27, period numbers 1–12, ISO years that differ from calendar years around New Year, Excel's WEEKNUM rule, and complete weekly tables with no missing weeks.
- How-toGroup Excel Rows by Month and Quarter with pandasAggregate Excel data into monthly and quarterly totals with pandas — Grouper vs to_period, filling empty periods, fiscal years, and writing the result back as a formatted report.
- How-toRound Timestamps into Time Slots with pandasBucket call-centre calls, orders or sensor readings into 15-minute, hourly or shift slots: floor, ceil and round with dt accessors, count and sum per slot, show empty slots as zero with a full slot grid, build a weekday-by-hour heatmap table, assign custom slots such as shifts, and compare with Excel's FLOOR formula and its floating-point edge cases.
- How-toAggregate a Huge Excel File Without Loading ItSum, count, average and find the minimum and maximum per group in an Excel sheet with hundreds of thousands of rows while holding only one row at a time: a streaming group-by over openpyxl read-only or python-calamine rows, statistics that can and cannot be computed in one pass, an exact median with bounded memory where values are discrete, and a check against pandas on a sample.
- How-toAllocate Shared Costs Across Departments with PythonSpread rent, IT, HR and finance costs over the departments that use them: choose a driver per cost pool, calculate shares, allocate with rounding that still adds up to the cent, compare the direct method with the step-down method for service departments, and write an allocation workbook with formulas finance can audit.
- How-toBuild a Customer Profitability ReportFind out which customers actually make money with pandas: start from revenue and product margin, then subtract the costs that differ by customer — discounts and rebates, delivery, the cost of handling many small orders and returns, and the cost of slow payment — build a profit waterfall for each customer and a whale curve for the portfolio, and write a ranked Excel report.
- How-toBuild a Discount Leakage ReportFind out how much margin is lost to discounts beyond policy with pandas: compare every invoice line's discount with the approved maximum for the customer tier and product group, value the excess, separate approved exceptions from unapproved ones, show who and which customers account for it, measure how widely discounts vary for the same product and tier, and write the findings to Excel.
- How-toBuild a Headcount and Attrition Report in Excel with PythonTurn an HR export of hire and leave dates into a monthly headcount and attrition report: point-in-time headcount at each month end, hires and leavers, attrition against average headcount, a rolling twelve-month rate, voluntary versus involuntary exits, first-year leavers, and a department breakdown in Excel with a chart.
- How-toBuild a Price-Volume-Mix AnalysisExplain why revenue changed between two periods by splitting the change into price, volume and mix effects with pandas: per-product price and quantity, the standard PVM formulas, separate lines for new and discontinued products, a check that the effects add up exactly to the change, a breakdown by product, and an Excel report with a bridge table.
- How-toBuild a Product Margin ReportWork out what each product and customer really earns from sales lines with pandas: net revenue after discounts and returns, the standard cost in force on each sale date with merge_asof, freight allocated by weight, gross margin by product, category and customer, loss-making lines, a price-versus-cost explanation of margin change, and an Excel report with margin colour coding.
- How-toBuild a Profit and Loss Statement from a General LedgerTurn a general ledger export into a monthly profit and loss statement in Excel: check the ledger balances, map account numbers to P&L lines with a chart of accounts, flip signs so revenue and costs read naturally, add gross, operating and net profit with margins, and write a workbook with collapsible account detail and live subtotal formulas.
- How-toBuild a Returns Analysis ReportAnalyse product returns properly with pandas: link each return to the sale it came from, calculate return rates by the month of sale rather than the month of return, compare products and channels, rank the reasons customers give, find the few products that cause most refund value, allow for returns still to come in recent months, and write the analysis to Excel.
- How-toBuild a Sales Commission Report with Python and ExcelCalculate commissions from a month of closed deals: tiered rates applied the way the plan defines them — marginal like tax bands or on the whole amount once a tier is reached — clawbacks for cancelled deals, reps close to the next tier flagged, a summary for payroll and one statement sheet per rep showing the working.
- How-toBuild a Sales Pipeline Report from a CRM ExportTurn a CRM opportunity export into a pipeline report: value and weighted value by stage, expected close by month, coverage of the remaining quota, deals past their close date or with no recent activity, and what changed since last week by comparing two snapshots — written to Excel for the Monday sales meeting.
- How-toBuild a Staff Utilisation Report from TimesheetsMeasure how much of your team's available time goes on billable work with pandas: capacity from contracted hours minus holidays and leave, billable and non-billable hours from timesheet codes, utilisation against a target per role, a person-by-week heatmap with a colour scale, flags for missing timesheets and overload, and a formatted Excel report.
- How-toBuild a Trial Balance from a General LedgerProduce a month-end trial balance in Excel from general ledger journal lines with pandas: opening balances plus period movements to closing balances for every account, debit and credit columns, a check that the ledger balances, flags for accounts on their wrong side and unmapped or suspense accounts, a prior-month comparison, and an openpyxl layout with live totals.
- How-toBuild a Weekly Sales Report in Excel with PythonBuild the Monday-morning sales report from daily transactions: the week just ended against the previous week and the same ISO week last year, like-for-like stores only, year to date, an eight-week trend with sparklines, the biggest movers, and a one-page Excel layout with arrows and conditional formatting.
- How-toBuild an Accounts Receivable Aging Report with PythonTurn an open-invoice export into the aging report credit control works from: balances bucketed into current, 1–30, 31–60, 61–90 and over 90 days past due as at the period end, one row per customer with totals, credit notes netted, customers over their credit limit flagged and the total tied back to the ledger.
- How-toBuild an Aged Creditors ReportBuild an aged creditors (accounts payable ageing) report from an open payables export with pandas and openpyxl: age every open invoice, credit note and unallocated payment by due date into current, 1–30, 31–60, 61–90 and over-90-day buckets, show credits as negatives, mark disputed items, summarise by supplier, reconcile to the payables control account and highlight what is overdue.
- How-toBuild an Inventory Reorder Report with Python and ExcelDecide what to order this week from a stock export and recent sales: average daily usage, reorder points from lead time and safety stock, stock already on order counted, order quantities rounded up to pack sizes and minimums, days of cover, stock-out risks flagged and a purchase list grouped by supplier.
- How-toBuild an Inventory Turnover and Days on Hand ReportMeasure how hard stock is working with pandas: annual cost of goods sold against average inventory value for inventory turnover, days on hand from current stock and recent usage, slow-moving and dead stock classification, the cash tied up in each class, a category summary against targets, and an Excel report that shows where to act.
- How-toCreate a Histogram in Excel with PythonShow how values are distributed — order sizes, delivery times, invoice amounts — as a histogram in an Excel report. Choose bin edges with numpy, count values per bin, label bins the way readers expect, draw them as touching columns with openpyxl or xlsxwriter, and keep a live FREQUENCY version for data that changes.
- How-toWrite a MultiIndex DataFrame to Excel with pandasWrite pivot tables and grouped results with MultiIndex rows or columns to Excel — merged or repeated labels, the extra index-name row, the index=False error, flattening for filters, and reading it back.
Follow it through a track
Prefer an ordered route? These tracks cover pivots & summaries along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Pivots & summaries is part of Cleaning & transforming data.