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

Use Different Aggregations per Column in a pandas Pivot

Sum 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.
Pivots & summariespandas

A useful summary rarely applies one function to everything. For each region you want total revenue, total units, the average and the weighted price, the number of distinct customers, and the dates of the first and last orders. Excel's pivot table lets you set "Summarize value field by" separately for each field; pandas does the same with an aggfunc dictionary or, more readably, with named aggregation. This guide, part of Creating Pivot Tables from Excel Data, shows both, the weighted-average trap, and a total row that follows the same rules as the groups.

One order list, a different rule for each summary column Order rows with region, customer, date, units, price and revenue are summarised per region. Revenue and units are summed, customers are counted distinctly, the first and last order dates are taken with min and max, and the price is shown both as a simple and a weighted average. A B C D 1 Region Customer Units Price 2 North A 10 2.00 3 North B 5 3.00 4 North A 8 2.50 5 South C 20 1.50 6 South A 4 4.00 7 South D 6 3.00 A B C D 1 Region revenue customers wavg_price 2 North 55.00 2 2.39 3 South 64.00 3 2.13 before after agg per column

Prerequisites

Bash
pip install pandas openpyxl
Python
import pandas as pd

orders = pd.DataFrame({
    "Region": ["North", "North", "North", "South", "South", "South"],
    "Customer": ["A", "B", "A", "C", "A", "D"],
    "Date": pd.to_datetime(["2026-01-05", "2026-02-10", "2026-03-01",
                            "2026-01-20", "2026-02-02", "2026-03-15"]),
    "Units": [10, 5, 8, 20, 4, 6],
    "Price": [2.0, 3.0, 2.5, 1.5, 4.0, 3.0],
})
orders["Revenue"] = orders["Units"] * orders["Price"]
orders.to_excel("orders.xlsx", index=False)
df = pd.read_excel("orders.xlsx")

Step 1 — An aggfunc dictionary in pivot_table

pivot_table accepts a dictionary that maps each value column to its function. A list applies several functions to one column:

Python
pt = df.pivot_table(index="Region", values=["Revenue", "Units", "Price"],
                    aggfunc={"Revenue": "sum", "Units": ["sum", "max"], "Price": "mean"})
print(pt)
print(pt.columns.tolist())
# [('Price', 'mean'), ('Revenue', 'sum'), ('Units', 'max'), ('Units', 'sum')]

This works, and it is the closest match to Excel's value fields. Two things make it awkward in reports: the columns come back sorted alphabetically rather than in the order you listed them, and they have two levels — ('Units', 'max') — which must be flattened before writing a clean sheet.

Step 2 — Named aggregation with groupby

When there is no column dimension — one row per region, no months across the top — groupby().agg() with named aggregation is clearer. Each keyword is the output column name, each value a pair of source column and function:

Python
summary = df.groupby("Region").agg(
    revenue=("Revenue", "sum"),
    units=("Units", "sum"),
    customers=("Customer", "nunique"),
    first_order=("Date", "min"),
    last_order=("Date", "max"),
    avg_price=("Price", "mean"),
)
print(summary)

The columns appear in the order written, with the names you chose, in a single level. nunique answers "how many different customers", which a pivot count of rows cannot: North has three orders but only two customers. min and max on dates give the first and last order without sorting.

Which function answers which question sum answers how much in total, mean answers what a typical row looks like, nunique answers how many different values, min and max on dates answer when something first and last happened, and count answers how many rows have a value. Function North example How much in total? sum revenue 55.00 How many different customers? nunique 2 (from 3 orders) When was the latest order? max on Date 2026-03-01 What does a typical order cost? mean price 2.50 How many rows have a value? count 3

Step 3 — Weighted averages are a ratio, not a mean

avg_price is the plain mean of the price column: every order counts equally, whether it was for 5 units or 20. The average price actually paid per unit is total revenue divided by total units. Compute it from sums after aggregating:

Python
summary["wavg_price"] = summary["revenue"] / summary["units"]
print(summary[["avg_price", "wavg_price"]].round(3))
#         avg_price  wavg_price
# North       2.500       2.391
# South       2.833       2.133

In South, the large 20-unit order at 1.50 pulls the true average well below the plain mean. The same rule covers every ratio in a report — margin percentage, conversion rate, cost per unit: sum the numerator and the denominator per group, then divide. Averaging a column of ratios is almost never what a reader expects.

Step 4 — Custom functions for counts with a condition

Named aggregation accepts any function that takes a Series and returns one value. Count large orders or sum only one kind of value without adding helper columns:

Python
extra = df.groupby("Region").agg(
    large_orders=("Units", lambda s: (s >= 10).sum()),
    repeat_customers=("Customer", lambda s: (s.value_counts() > 1).sum()),
)
print(extra)
#         large_orders  repeat_customers
# Region
# North              1                 1
# South              1                 0

Lambdas are convenient but slow on large frames, because pandas calls them once per group in Python. For big data, build a boolean column first — df["large"] = df["Units"] >= 10 — and aggregate it with "sum", which runs vectorised. Conditional counting in general is covered in COUNTIF and COUNTIFS Equivalent in pandas.

Step 5 — A total row that follows the same rules

Adding summary.sum() as a total row would add up the distinct customer counts (a customer active in two regions counted twice), sum the average prices and fail on dates. Instead, run the same aggregation over the whole frame:

Python
RULES = dict(
    revenue=("Revenue", "sum"),
    units=("Units", "sum"),
    customers=("Customer", "nunique"),
    first_order=("Date", "min"),
    last_order=("Date", "max"),
)

def summarise(frame, by):
    out = frame.groupby(by).agg(**RULES)
    out["wavg_price"] = out["revenue"] / out["units"]
    return out

by_region = summarise(df, "Region")
total = summarise(df.assign(Region="Total"), "Region")
report = pd.concat([by_region, total])
print(report[["revenue", "customers", "wavg_price"]].round(2))

North has 2 customers and South 3, yet the total row says 4, not 5: customer A bought in both regions and is counted once overall. Keeping the rules in one dictionary and the derived ratio in one function guarantees that the groups and the total are computed identically. pivot_table(margins=True) does the same thing for you with an aggfunc dictionary — its All row is aggregated from the raw rows — but named aggregation gives you the ratio columns and readable names as well.

Adding up the summary versus re-aggregating the raw rows Summing the summary rows double counts a customer who buys in two regions, adds averages together and cannot add dates. Aggregating the raw rows again with the same rules gives a correct distinct count, a true weighted price and the real first and last dates. summary.sum() distinct counts added up averages added together dates cannot be summed fine for plain sums only Same rules on all rows customers counted once weighted price from totals real first and last dates one definition, two uses

Step 6 — Different aggregations with columns across

When the report needs months across the top as well, go back to pivot_table, then flatten the two-level column labels into single names before writing:

Python
df["Month"] = df["Date"].dt.strftime("%Y-%m")
wide = df.pivot_table(index="Region", columns="Month", values=["Revenue", "Customer"],
                      aggfunc={"Revenue": "sum", "Customer": "nunique"}, fill_value=0)
wide.columns = [f"{measure} {month}" for measure, month in wide.columns]
print(wide.columns.tolist()[:3])   # ['Customer 2026-01', 'Customer 2026-02', 'Customer 2026-03']

fill_value=0 is safe here because both measures are counts or sums: a region with no orders in a month really did have zero revenue and zero customers. Leave it out when one of the measures is an average or a date, where zero would be a wrong answer rather than an empty one; fill those gaps per column after flattening instead. Name the flattened columns so that readers can tell the measures apart; "Customer 2026-01" is better renamed to "Customers 2026-01" with a mapping when the report is written.

Step 7 — Write it with a format per column

Each measure needs its own number format in Excel — currency, whole numbers, dates:

Python
from openpyxl import load_workbook

report.to_excel("summary.xlsx", index_label="Region")
wb = load_workbook("summary.xlsx")
ws = wb.active
formats = {"revenue": "#,##0.00", "units": "#,##0", "customers": "0",
           "first_order": "yyyy-mm-dd", "last_order": "yyyy-mm-dd", "wavg_price": "0.000"}
header = [c.value for c in ws[1]]
for name, fmt in formats.items():
    col = header.index(name) + 1
    for (cell,) in ws.iter_rows(min_row=2, min_col=col, max_col=col):
        cell.number_format = fmt
wb.save("summary.xlsx")

Looking up each column by its header name, rather than by letter, keeps the formatting correct when a new measure is added to RULES. Formats change only how numbers are displayed; the stored values keep full precision, so anyone who reuses the sheet in formulas still gets the exact weighted price rather than the three decimals on screen.

Common pitfalls

  • count versus size. "count" counts non-missing values in a column; "size" counts rows. They differ whenever the column has gaps.
  • nunique across groups. Distinct counts do not add up; a customer in two regions is one customer overall, as the total row in Step 5 shows.
  • "first" and "last" depend on row order. ("Date", "first") returns whichever row happens to come first, which changes when the sheet is sorted differently. Use min and max for dates, or sort explicitly before aggregating when you need the first order's other details.
  • Mean of a mean. Averages of subgroup averages ignore group sizes. Recompute from raw rows.

Conclusion

Give each summary column the function that answers its question: sums for totals, nunique for distinct counts, min and max for first and last dates, and ratios of sums for weighted averages. Use named aggregation for readable single-level results, pivot_table with an aggfunc dictionary when you also need columns across, and compute totals by applying the same rules to all rows rather than adding up the summary.

Frequently asked questions

How do I use a different aggregation for each column in pivot_table? Pass aggfunc a dictionary that maps each value column to a function name, such as {"Revenue":"sum","Price":"mean"}. A list as the dictionary value applies several functions to that column and produces two-level column labels.

What is named aggregation? A groupby().agg() form where each keyword argument is an output column name and its value is a tuple of source column and function, such as revenue=("Revenue","sum"). It produces flat, readable column names in one step.

How do I calculate a weighted average price per group? Aggregate the parts separately — the sum of revenue and the sum of units — and divide afterwards. Averaging the price column gives each order equal weight regardless of its size.

How do I add a total row with the same aggregations? Run the same aggregation on the whole frame, for example by grouping on a constant column, and append it to the grouped result. That keeps distinct counts and averages correct in the total row.

More on pivots & summaries

More on pivots & summaries →