Browse cheat sheets · Python

Technology: PythonCategory: Data AnalysisDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Sum values by one column
  5. 2. Group by multiple columns
  6. 3. Calculate several aggregations at once
  7. 4. Count rows in each group
  8. 5. Count non-null values in a specific column
  9. 6. Count unique business entities
  10. 7. Keep null grouping keys intentionally
  11. 8. Group categorical columns without expanding unobserved combinations
  12. 9. Calculate percentages within a group
  13. 10. Add group totals without reducing rows
  14. 11. Filter based on group-level metrics
  15. 12. Aggregate by date
  16. 13. Aggregate by month
  17. 14. Get the first or last row per group after sorting
  18. 15. Get the row with the maximum value per group
  19. 16. Aggregate using a custom function
  20. 17. Aggregate with multiple business measures
  21. 18. Sort aggregated output deliberately
  22. 19. Avoid accidental double counting before aggregation
  23. 20. Compare row count before and after aggregation
  24. What the result means
  25. What to look for
  26. Aggregated totals are too high
  27. Some expected groups disappeared
  28. Output contains too many groups
  29. Counts differ from expected totals
  30. Average looks wrong
  31. Latest row logic returns mixed values
  32. Production notes
  33. GroupBy can be memory-intensive
  34. Prefer built-in aggregations
  35. Be explicit with business grain
  36. Grouping nulls must be intentional
  37. Aggregation can hide upstream data-quality problems
  38. Avoid groupby().apply() as a default
  39. Time grouping requires explicit timezone handling
  40. Common mistakes
  41. 1. Using count() when you mean row count
  42. 2. Using size() when you need distinct entities
  43. 3. Forgetting null group keys
  44. 4. Aggregating after a one-to-many join without checking grain
  45. 5. Averaging averages
  46. 6. Using max() to get the latest row
  47. 7. Using .apply() for simple aggregations
  48. 8. Assuming group order is meaningful
  49. Next step
  50. 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() where nunique() 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.

Next step

Use the aggregation result to decide what comes next.

  • Need to attach group totals back to rows → use transform().
  • Totals look inflated → inspect duplicates and upstream joins.
  • Expected groups are missing → inspect null grouping keys and normalization.
  • Need metrics from another DataFrame → merge after defining the target grain.
  • Need time-based summaries → use parsed datetimes and pd.Grouper.
  • Need latest or highest row per group → sort/select actual rows rather than aggregating every column.
  • GroupBy is slow or memory-heavy → reduce columns, lower cardinality, pre-filter, or move the workload to a distributed engine.
  • Output is business-critical → add reconciliation checks comparing grouped totals with source totals.

← All cheat sheets