How to aggregate columns with named groupby outputs in Pandas (with Code)

Editorial · Published 4 November 2026

The Short Answer (TL;DR)

Pass output_name=(input_column, aggregation) pairs to DataFrameGroupBy.agg to create explicitly named summary columns. State missing-key and categorical-group policies, and distinguish the number of rows from the number of observed measurements.

import pandas as pd
df = pd.DataFrame({"group":["A","A","B"],"value":[2.,4.,8.]})
print(df.groupby("group",as_index=False).agg(rows=("value","size"),observed=("value","count"),mean_value=("value","mean")))

Detailed Implementation

This example uses synthetic data and does not need a network connection. Install the recorded package versions in a separate Python environment before running it.

python -m pip install numpy==2.5.4 pandas==3.0.6

from pathlib import Path
import numpy as np
import pandas as pd

def summarize(frame):
    required = {"region","revenue","units"}
    if not frame.columns.is_unique or not required.issubset(frame.columns):
        raise ValueError("Use unique columns including region, revenue and units.")
    for column in ["revenue","units"]:
        if not pd.api.types.is_numeric_dtype(frame[column]) or pd.api.types.is_bool_dtype(frame[column]):
            raise ValueError("Revenue and units must be numeric measurements.")
        if not np.isfinite(frame[column].dropna().to_numpy(dtype=float)).all():
            raise ValueError("Observed measurements must be finite.")
    return frame.groupby("region",as_index=False,dropna=False,observed=True,sort=False).agg(
        rows=("revenue","size"),
        observed_revenue=("revenue","count"),
        revenue_total=("revenue",lambda s:s.sum(min_count=1)),
        revenue_mean=("revenue","mean"),
        units_total=("units","sum"),
    )

# Synthetic revenue in currency units and counts of physical units.
data = pd.DataFrame({
    "region":pd.Categorical(["A","A","B","B","C",None,"A",None],categories=["A","B","C","D"]),
    "revenue":pd.array([10,30,20,None,None,5,0,15],dtype="Float64"),
    "units":pd.array([1,3,2,4,1,1,0,2],dtype="Int64"),
},index=[8,8,2,1,4,4,3,3])
result = summarize(data)
report = result.to_string(index=False,na_rep="NA") + "\n"
Path("named-group-summary.txt").write_text(report,encoding="utf-8")
print(report)

There are four output groups, eight input rows and six observed revenues. Observed revenue totals sum to 80, physical-unit totals sum to 14, and the all-missing C revenue summary remains NA.

What does each output column mean?

The synthetic result has one row for A, B, C and the missing region key. A has three measurements, revenue total 40 and observed mean 40/3. B has two rows but only one observed revenue, so its mean is 20 rather than 10. C has one row and no observed revenue; its total and mean remain missing. The missing-key group has revenue total 20 and mean 10. All eight input rows are represented, including the measured zero.

Why keep counts next to the mean?

For these data, revenue counts across groups add to six observed measurements, while row counts add to eight. Dividing a group total by its row count would silently treat absent revenues as zero. The helper uses a minimum count of one for the revenue sum so an entirely unobserved group is distinguishable from a measured total of zero. Its units column contains a recorded count on every row; a different missing-units policy would need to be stated separately.

Which groups are included?

The call explicitly retains missing grouping keys, includes only observed categorical levels and uses first-appearance group order. Category D is declared but has no rows, so it does not produce a summary. These choices are part of the data contract, not incidental formatting. The output row order can change when the input is rearranged; comparisons should align by group key. Duplicate input index labels are harmless here because aggregation groups by the region column.

How do named outputs help downstream reporting?

Each keyword names an output and each pair selects an input column plus reduction. The chosen names distinguish revenue currency units, a mean revenue per observed row and physical-unit counts. A flat, explicit output schema is easier to join or export than relying on generated multi-level labels. Keep the grouping key as a column with as_index=False. Summarization reduces row detail, so preserve the source table if later auditing needs individual observations.

Can group means be averaged again?

The observed overall revenue mean is 80/6, approximately 13.333 currency units. Averaging the three available group means equally gives approximately 14.444 because their observed counts differ. To combine these observed means, weight each by its own observed revenue count. Neither calculation corrects missing-data bias or establishes a population effect; this example is a descriptive summary with no sampling or causal model.

Mathematical Context

Let O_g be the rows in group g with an observed revenue, and let n_g be their count. The reported mean is the sum of revenue over O_g divided by n_g when n_g is positive. A group with no observed revenues has an undefined mean. The row count includes rows outside O_g. Revenue sums have currency units, revenue means have currency units per observed row, and row counts are dimensionless. Combining group means with weights n_g recovers the observed overall mean; equal group weights answer a different question.

\[ \bar{x}_g=\frac{1}{n_g}\sum_{i\in O_g}x_i,\quad n_g=|O_g|>0 \]

Reproducibility

Executed with Python 3.12.14; numpy 2.5.4, pandas 3.0.6. Automated checks confirm the stated output properties.

Primary documentation

Related guides

Before summarizing a table enriched with reference records, validate a many-to-one merge to prevent duplicate reference keys from multiplying rows and changing group totals.

If the same measurement is spread across several source columns, reshape wide data to long format before grouping by the resulting variable and its identifiers.