Excel GROUPBY and PIVOTBY Function Equivalents in pandas

Excel 365 added two functions that replace a whole pivot table with a formula: GROUPBY(row_fields, values, function) returns a summary table that spills down the sheet, and PIVOTBY(row_fields, col_fields, values, function) returns a crosstab. Analysts love them because the summary updates as the data changes and needs no refresh. When such a workbook becomes a scheduled Python report, every one of those formulas has a pandas counterpart — and pandas is often the only way to get the same numbers into a file other people can open, because the functions only calculate in recent Excel 365. This page maps the arguments that matter onto pandas code. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas openpyxl

sales.xlsx is a flat sales table — region, category, quarter, sales — the kind of range a GROUPBY formula points at.

Step 1 — GROUPBY with its default total

=GROUPBY(A2:A13, D2:D13, SUM) groups by region, sums sales, sorts the regions ascending and adds a grand total row at the bottom (its total_depth defaults to grand totals). In pandas:

import pandas as pd

sales = pd.read_excel("sales.xlsx")

def groupby_with_total(df, by, value, func="sum", total_label="Total"):
    out = df.groupby(by, as_index=False)[value].agg(func).sort_values(by)
    total = pd.DataFrame({by: [total_label], value: [df[value].agg(func)]})
    return pd.concat([out, total], ignore_index=True)

print(groupby_with_total(sales, "Region", "Sales"))

The grand total is computed from the original rows, not by summing the group results. For SUM that makes no difference; for AVERAGE it does — the overall average is not the average of the group averages — and computing it from the rows is what Excel does too.

Step 2 — Sort by value and filter rows first

Two optional arguments change the result most often. sort_order sorts by a result column — -2 means descending by the second column, the values — and filter_array keeps only rows where a condition holds before grouping:

q2 = sales[sales["Quarter"] == "Q2"]                       # filter_array: D2:D13 = "Q2"
ranked = (q2.groupby("Region", as_index=False)["Sales"].sum()
            .sort_values("Sales", ascending=False))         # sort_order: -2
print(ranked.to_string(index=False))

Filtering before grouping is the point of filter_array: excluded rows do not reach the total either. Note East's Q2 hardware row with sales of 0 — it is kept, because zero is a value; a filter on Sales > 0 would remove it. The ranked order makes this the usual "top regions" table; for the top N per group, see Get the Top N Rows per Group in pandas.

Step 3 — Several functions and two levels

GROUPBY accepts several functions stacked side by side (HSTACK(SUM, AVERAGE, COUNT)) and several row fields, with total_depth=2 adding a subtotal per first-level group. pandas does the same with named aggregations and a subtotal loop:

stats = sales.groupby("Region").agg(Sum=("Sales", "sum"), Average=("Sales", "mean"), Count=("Sales", "count"))
print(stats.round(1))

def two_level(df, outer, inner, value):
    parts = []
    for key, part in df.groupby(outer, sort=True):
        detail = part.groupby(inner, as_index=False)[value].sum()
        detail.insert(0, outer, key)
        parts.append(detail)
        parts.append(pd.DataFrame({outer: [key], inner: ["Subtotal"], value: [part[value].sum()]}))
    parts.append(pd.DataFrame({outer: ["Total"], inner: [""], value: [df[value].sum()]}))
    return pd.concat(parts, ignore_index=True)

print(two_level(sales, "Region", "Category", "Sales").to_string(index=False))

Named aggregations give the result columns readable names, which a summary sheet needs anyway. The two-level layout — detail rows, a subtotal per region, a grand total — is what GROUPBY(A2:B13, D2:D13, SUM, , 2) produces; writing it out with bold subtotal rows is covered in Add Subtotals and Grand Totals to a pandas Pivot Table.

Step 4 — PIVOTBY is pivot_table with margins

=PIVOTBY(A2:A13, C2:C13, D2:D13, SUM) puts regions down the side, quarters across the top, and adds a total row and a total column by default:

cross = pd.pivot_table(sales, index="Region", columns="Quarter", values="Sales",
                       aggfunc="sum", fill_value=0, margins=True, margins_name="Total")
print(cross)

margins=True adds the totals, computed from the underlying rows like Excel's. fill_value=0 shows 0 where a combination has no rows (West had no Q1 sales); without it the cell is NaN, the equivalent of the blank Excel leaves. Combinations that never occur at all are simply absent from both outputs.

Step 5 — PERCENTOF and shares of the total

Excel added PERCENTOF to use as the function in GROUPBY and PIVOTBY: each group's share of the total. In pandas, divide by the total — or use normalize in crosstab:

share = sales.groupby("Region")["Sales"].sum() / sales["Sales"].sum()
print(share.map("{:.1%}".format))
col_share = pd.crosstab(sales["Region"], sales["Quarter"], values=sales["Sales"],
                        aggfunc="sum", normalize="columns")
print(col_share.round(3))

normalize="columns" gives each region's share within each quarter, the equivalent of PIVOTBY's relative_to set to columns; normalize="index" gives shares within each row, and normalize="all" shares of the grand total. More ways to compute shares are in Calculate Percent of Total per Group in pandas.

Step 6 — Write the summary as values

A workbook that contains GROUPBY formulas only calculates in Excel 365: older Excel versions show #NAME?, and LibreOffice did not evaluate them in testing (version 26.2 returned #NAME? for both functions). Writing the pandas results as values works everywhere:

with pd.ExcelWriter("sales_summary.xlsx", engine="xlsxwriter") as xw:
    groupby_with_total(sales, "Region", "Sales").to_excel(xw, sheet_name="By region", index=False)
    cross.to_excel(xw, sheet_name="Region x quarter")
    bold = xw.book.add_format({"bold": True, "top": 1})
    last = len(sales["Region"].unique()) + 1
    xw.sheets["By region"].set_row(last, None, bold)
with pd.ExcelFile("sales_summary.xlsx") as xf:
    print(xf.sheet_names)

The total row is bolded with a top border so it reads like the formula's output. If recipients do have Excel 365 and want a live formula, keep the data sheet in the file and let them add GROUPBY themselves; the Python report remains the version of record.

Step 7 — One helper for many summaries

A workbook converted from Excel 365 often has a dozen GROUPBY formulas that differ only in the field, the function and the filter. Capture the pattern once, so each summary is one call and the defaults match Excel's — ascending sort, a grand total, optional filter:

def excel_groupby(df, by, value, func="sum", sort_desc_by_value=False, where=None, total=True):
    data = df if where is None else df[where(df)]
    out = data.groupby(by, as_index=False)[value].agg(func)
    out = out.sort_values(value if sort_desc_by_value else by, ascending=not sort_desc_by_value)
    if total:
        label = {by: "Total"} if isinstance(by, str) else {by[0]: "Total"}
        out = pd.concat([out, pd.DataFrame([{**label, value: data[value].agg(func)}])], ignore_index=True)
    return out

print(excel_groupby(sales, "Category", "Sales", "mean").round(1).to_string(index=False))
print(excel_groupby(sales, "Region", "Sales", sort_desc_by_value=True,
                    where=lambda d: d["Category"] == "Garden").to_string(index=False))

The first call is GROUPBY(B2:B13, D2:D13, AVERAGE): average sales per category with an overall average of all rows underneath. The second is a filtered, value-sorted summary of garden sales by region, GROUPBY(A2:A13, D2:D13, SUM, , , -2, B2:B13="Garden"). Passing the filter as a function of the DataFrame keeps the helper reusable across tables. Each summary can then be written to its own sheet in one loop, which is the shape most monthly packs take — see Write Multiple DataFrames to One Excel File.

Performance notes

groupby and pivot_table handle millions of rows in well under a second for sums and counts; the Python-level loop in two_level runs once per outer group, which is fine for regions and products and slow only for tens of thousands of groups — for those, compute subtotals with a second groupby on the outer key and merge. In the spreadsheet, by contrast, each GROUPBY recalculates whenever its source range changes, so a workbook with many of them over a large table becomes sluggish; moving the summaries into the scheduled Python job is often what prompts the conversion in the first place.

Common pitfalls

• Averaging averages. Compute totals for AVERAGE, MEDIAN and the like from the rows, not from group results.
• Filtering after grouping. filter_array removes rows before aggregation; filtering the summary changes nothing about the total.
• Missing combinations. Use fill_value=0 only where "no rows" really means zero.
• Mixed-type keys. A region typed as North and North forms two groups in both tools; strip text first.
• Shipping live formulas. GROUPBY and PIVOTBY show #NAME? outside Excel 365.

Conclusion

GROUPBY is groupby().agg() plus a total row computed from the underlying data; its sort_order and filter_array arguments are sort_values and a boolean filter applied first; several functions are named aggregations; and total_depth=2 is a subtotal per outer group. PIVOTBY is pivot_table with margins=True, and PERCENTOF is a division by the total or crosstab(normalize=...). Write the results as values, because workbooks that depend on these functions only calculate in Excel 365.

Frequently asked questions

What is the pandas equivalent of Excel GROUPBY? Group by the row field, select the value column and aggregate it with the function, sort by the row field, with a total row appended that is calculated from all rows. Use named aggregations for several functions at once.

What is the pandas equivalent of Excel PIVOTBY? pd.pivot_table(df, index=row_field, columns=col_field, values=value, aggfunc=function, margins=True). The margins add the row and column totals that PIVOTBY shows by default.

How do I replicate GROUPBY's filter_array in pandas? Filter the DataFrame with a boolean condition before grouping, for example dfdf"Quarter" == "Q2".groupby("Region"). Excluded rows then do not contribute to the groups or the total.

Do GROUPBY and PIVOTBY work in older Excel or LibreOffice? No. They are Excel 365 functions; older Excel versions show #NAME?, and LibreOffice did not evaluate them in testing. Write the summary as values from pandas for files that others open.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions and their pandas counterparts.
• Create a Pivot Table from Excel with pandas — pivot_table in depth.
• SUMIF and SUMIFS Equivalent in pandas — single conditional totals.
• UNIQUE, SORT and FILTER Equivalents in pandas — the other dynamic array functions.