Browse cheat sheets · Python
GroupBy and Aggregations in pandas
Group pandas data by business keys, calculate reliable aggregations, preserve row-level context with transform, and avoid common mistakes with nulls, duplicates, and mixed aggregation logic.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Sum values by one column
- 2. Group by multiple columns
- 3. Calculate several aggregations at once
- 4. Count rows in each group
- 5. Count non-null values in a specific column
- 6. Count unique business entities
- 7. Keep null grouping keys intentionally
- 8. Group categorical columns without expanding unobserved combinations
- 9. Calculate percentages within a group
- 10. Add group totals without reducing rows
- 11. Filter based on group-level metrics
- 12. Aggregate by date
- 13. Aggregate by month
- 14. Get the first or last row per group after sorting
- 15. Get the row with the maximum value per group
- 16. Aggregate using a custom function
- 17. Aggregate with multiple business measures
- 18. Sort aggregated output deliberately
- 19. Avoid accidental double counting before aggregation
- 20. Compare row count before and after aggregation
- What the result means
- What to look for
- Aggregated totals are too high
- Some expected groups disappeared
- Output contains too many groups
- Counts differ from expected totals
- Average looks wrong
- Latest row logic returns mixed values
- Production notes
- GroupBy can be memory-intensive
- Prefer built-in aggregations
- Be explicit with business grain
- Grouping nulls must be intentional
- Aggregation can hide upstream data-quality problems
- Avoid groupby().apply() as a default
- Time grouping requires explicit timezone handling
- Common mistakes
- 1. Using count() when you mean row count
- 2. Using size() when you need distinct entities
- 3. Forgetting null group keys
- 4. Aggregating after a one-to-many join without checking grain
- 5. Averaging averages
- 6. Using max() to get the latest row
- 7. Using .apply() for simple aggregations
- 8. Assuming group order is meaningful
- Next step
- Related deep dives
Problem
You need to summarize row-level data into business metrics such as:
- revenue by customer
- orders by country
- average duration by status
- daily totals by product
- maximum transaction per account
- unique users by channel
- percentages within a group
- group-level values while preserving the original row grain
The challenge is not just calling groupby().
You need to make sure:
- the grouping keys are correct
- null group keys are handled intentionally
- duplicates do not distort totals
- aggregations match the business grain
- output columns remain understandable
- group-level metrics are joined back safely when needed
Quick answer
For one metric:
summary = (
df.groupby("customer_id", as_index=False)
.agg(total_revenue=("revenue", "sum"))
)
For several metrics:
summary = (
df.groupby(["country", "channel"], as_index=False)
.agg(
orders=("order_id", "nunique"),
customers=("customer_id", "nunique"),
revenue=("revenue", "sum"),
avg_order_value=("revenue", "mean"),
)
)
For a group-level metric that must remain on every original row:
df["customer_total_revenue"] = (
df.groupby("customer_id")["revenue"]
.transform("sum")
)
Use agg() when you want fewer rows.
Use transform() when you want to preserve the original row count.
Code / commands
1. Sum values by one column
summary = (
df.groupby("customer_id", as_index=False)
.agg(total_revenue=("revenue", "sum"))
)
This produces one row per customer_id.
Use named aggregation so the output column has a meaningful name.
2. Group by multiple columns
summary = (
df.groupby(
["country", "channel"],
as_index=False,
)
.agg(
revenue=("revenue", "sum"),
orders=("order_id", "nunique"),
)
)
The output grain is now:
country + channel
Make sure that grain matches the business question.
3. Calculate several aggregations at once
summary = (
df.groupby("customer_id", as_index=False)
.agg(
total_revenue=("revenue", "sum"),
avg_revenue=("revenue", "mean"),
max_revenue=("revenue", "max"),
order_count=("order_id", "nunique"),
first_order=("order_date", "min"),
last_order=("order_date", "max"),
)
)
Named aggregation is usually clearer than older patterns that create MultiIndex columns.
4. Count rows in each group
Use .size() when you want row count.
counts = (
df.groupby("status", dropna=False)
.size()
.reset_index(name="row_count")
)
This counts rows regardless of nulls in other columns.
5. Count non-null values in a specific column
Use .count() when you want non-null values in one column.
counts = (
df.groupby("status", as_index=False)
.agg(non_null_email_count=("email", "count"))
)
Important:
size() -> counts rows
count() -> counts non-null values
Do not use them interchangeably.
6. Count unique business entities
summary = (
df.groupby("country", as_index=False)
.agg(
unique_customers=("customer_id", "nunique"),
unique_orders=("order_id", "nunique"),
)
)
Use nunique() when repeated rows for the same entity should count once.
7. Keep null grouping keys intentionally
By default, pandas drops NA group keys.
To keep them:
summary = (
df.groupby(
"country",
dropna=False,
as_index=False,
)
.agg(revenue=("revenue", "sum"))
)
This is important when missing dimension values are operationally meaningful.
8. Group categorical columns without expanding unobserved combinations
When grouping categoricals, be explicit about whether you want only observed combinations.
summary = (
df.groupby(
["country", "channel"],
observed=True,
as_index=False,
)
.agg(revenue=("revenue", "sum"))
)
observed=True is useful when categorical columns define many theoretically possible combinations but only some appear in the data.
9. Calculate percentages within a group
Example: each product’s share of category revenue.
category_total = (
df.groupby("category")["revenue"]
.transform("sum")
)
df["category_revenue_pct"] = (
df["revenue"] / category_total
)
Use transform() because the result must align row-for-row with the original DataFrame.
10. Add group totals without reducing rows
df["customer_total_revenue"] = (
df.groupby("customer_id")["revenue"]
.transform("sum")
)
df["customer_order_count"] = (
df.groupby("customer_id")["order_id"]
.transform("nunique")
)
This is useful for filtering or comparing individual rows against group totals.
11. Filter based on group-level metrics
Keep customers whose total revenue exceeds 10,000:
customer_total = (
df.groupby("customer_id")["revenue"]
.transform("sum")
)
filtered = df.loc[
customer_total > 10_000
]
This preserves detail rows for qualifying customers.
12. Aggregate by date
Make sure the date column is parsed first:
df["event_time"] = pd.to_datetime(
df["event_time"],
errors="coerce",
)
Then group by date:
daily = (
df.groupby(
df["event_time"].dt.date,
as_index=False,
)
.agg(revenue=("revenue", "sum"))
)
For time-series work, pd.Grouper is often cleaner:
daily = (
df.groupby(
pd.Grouper(
key="event_time",
freq="D",
)
)
.agg(revenue=("revenue", "sum"))
.reset_index()
)
13. Aggregate by month
monthly = (
df.groupby(
pd.Grouper(
key="event_time",
freq="MS",
)
)
.agg(
revenue=("revenue", "sum"),
orders=("order_id", "nunique"),
)
.reset_index()
)
freq="MS" groups by month-start boundaries.
This keeps a proper datetime column instead of converting to strings.
14. Get the first or last row per group after sorting
Suppose you want the latest record per customer.
latest = (
df.sort_values(
["customer_id", "event_time"]
)
.groupby(
"customer_id",
as_index=False,
)
.tail(1)
)
This keeps the full row.
Do not use:
groupby().agg("max")
to mean “latest row” across multiple columns.
That can combine values from different rows.
15. Get the row with the maximum value per group
idx = (
df.groupby("customer_id")["revenue"]
.idxmax()
)
highest_orders = df.loc[idx]
This preserves the actual row associated with the maximum value.
Be careful if the target column can be entirely null within a group.
16. Aggregate using a custom function
def revenue_range(s: pd.Series) -> float:
return s.max() - s.min()
summary = (
df.groupby("customer_id", as_index=False)
.agg(
revenue_range=("revenue", revenue_range)
)
)
Use custom Python functions only when built-in aggregations cannot express the logic.
Built-ins are usually faster and easier to maintain.
17. Aggregate with multiple business measures
summary = (
df.groupby(
["merchant_id", "event_date"],
as_index=False,
)
.agg(
transactions=("transaction_id", "nunique"),
customers=("customer_id", "nunique"),
gross_amount=("amount", "sum"),
avg_amount=("amount", "mean"),
max_amount=("amount", "max"),
)
)
This pattern is common in reporting and data-engineering workloads.
Define the output grain before writing the code.
18. Sort aggregated output deliberately
summary = (
df.groupby("country", as_index=False)
.agg(revenue=("revenue", "sum"))
.sort_values(
"revenue",
ascending=False,
)
)
Do not assume group order carries business meaning.
Sort explicitly when the output will be reviewed or published.
19. Avoid accidental double counting before aggregation
Suppose the input contains duplicate order rows because of a prior join.
Validate first:
duplicate_orders = df.duplicated(
subset=["order_id"],
keep=False,
)
print(df.loc[duplicate_orders].head(20))
If the intended grain is one row per order, aggregate only after fixing or intentionally handling the duplication.
GroupBy cannot correct a wrong input grain automatically.
20. Compare row count before and after aggregation
print("Input rows:", len(df))
print("Output rows:", len(summary))
Validate expected number of groups:
expected_groups = (
df[["country", "channel"]]
.drop_duplicates()
.shape[0]
)
print("Expected groups:", expected_groups)
print("Actual groups:", len(summary))
If dropna=False or categorical grouping is involved, make sure the validation logic uses the same semantics.
What the result means
| Pattern | Meaning |
|---|---|
groupby("customer_id") |
Defines one group per customer key. |
as_index=False |
Keeps grouping keys as normal output columns. |
agg() |
Reduces each group into one or more summary values. |
transform() |
Returns group-derived values aligned to the original row count. |
size() |
Counts rows in each group. |
count() |
Counts non-null values. |
nunique() |
Counts distinct non-null values by default. |
dropna=False |
Keeps NA grouping keys as their own group. |
observed=True |
With categorical groupers, returns observed category combinations rather than all possible ones. |
pd.Grouper() |
Groups datetime-like data into time buckets. |
What to look for
Aggregated totals are too high
Common causes:
- duplicate rows
- one-to-many joins upstream
- wrong business grain
- repeated events
- using
count()wherenunique()was required
Validate the input before changing the aggregation.
Some expected groups disappeared
Check whether grouping keys contain nulls.
By default, null group keys may be excluded unless you use:
dropna=False
Output contains too many groups
Check:
- whitespace differences
- casing differences
- inconsistent codes
- timestamp precision
- extra grouping columns
Example:
"US"
"us"
"US "
are different group keys unless normalized.
Counts differ from expected totals
Ask what you are counting:
- rows?
- non-null values?
- distinct orders?
- distinct customers?
Choose size, count, or nunique deliberately.
Average looks wrong
Possible causes:
- duplicate rows
- nulls
- weighting differences
- wrong grain
- averaging already-aggregated averages
Never average subgroup averages unless the weighting is mathematically correct.
Latest row logic returns mixed values
Using max() across columns does not reconstruct the latest row.
Sort and select the actual row instead.
Production notes
GroupBy can be memory-intensive
Large group cardinality can consume significant memory.
Risk increases with:
- many grouping columns
- high-cardinality keys
- many aggregations
- object/string-heavy columns
- large intermediate DataFrames
Select only necessary columns before grouping.
Prefer built-in aggregations
These are generally faster:
"sum"
"mean"
"min"
"max"
"count"
"nunique"
than arbitrary Python functions.
Use .apply() only when group logic genuinely requires custom behavior.
Be explicit with business grain
Before grouping, write the intended output grain conceptually:
one row per customer per day
Then your grouping keys should represent exactly that grain.
Grouping nulls must be intentional
Dropping null dimension values silently can distort totals.
Use:
dropna=False
when missing keys should remain visible.
Aggregation can hide upstream data-quality problems
A final total may look reasonable even when duplicate rows or dirty keys exist.
Validate:
- row count
- key uniqueness
- duplicate business keys
- null keys
before trusting the result.
Avoid groupby().apply() as a default
apply() is powerful but often slower and harder to reason about.
Prefer:
agg()transform()filter()- vectorized operations
when they can express the same logic.
Time grouping requires explicit timezone handling
If timestamps come from multiple zones, normalize them before daily/monthly grouping.
Otherwise, records near midnight can fall into the wrong reporting period.
Common mistakes
1. Using count() when you mean row count
count() ignores nulls.
Use:
groupby(...).size()
for actual row count.
2. Using size() when you need distinct entities
Use nunique() when the metric is unique customers, orders, sessions, or other business entities.
3. Forgetting null group keys
Default behavior can exclude them.
Use dropna=False when those rows must remain visible.
4. Aggregating after a one-to-many join without checking grain
This is a common source of inflated totals.
Validate duplicates and join cardinality first.
5. Averaging averages
Example:
average(region averages)
is not generally equal to the overall average.
Use weighted calculations when subgroup sizes differ.
6. Using max() to get the latest row
max() works column by column.
It does not preserve row relationships.
Sort and take the actual row.
7. Using .apply() for simple aggregations
It is usually slower and less clear than built-in operations.
8. Assuming group order is meaningful
Sort explicitly if order matters.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.