Transform data with Pandas. Master pandas groupby for split-apply-combine, agg for multiple aggregations, transform for same-shape results, pivot_table for reshaping, and apply for custom functions in this pandas groupby guide.

“Split-apply-combine is the bread and butter of data analysis.”

Hadley Wickham

Last Updated: July 2026 | Tested on: Python 3.14.6, Pandas 2.3.3 | Difficulty: Advanced | Reading Time: 13 minutes

“What is the average salary by department?” “Which region had the highest total sales last quarter?” “For each customer, what percentage of their orders came back as returns?” Every one of these questions follows the same shape: split the data into groups, run a calculation on each group, then combine the answers into one table. Pandas calls this pattern split-apply-combine, and pandas groupby is the tool that does all three steps for you.

Think about sorting a basket of laundry. You toss the clothes into piles by color (split), you weigh each pile (apply), then you write the weights on one sheet (combine). You never weigh the whole mixed basket at once. groupby() does exactly this with rows of data: it sorts them into piles by a column, runs your calculation on each pile, and hands back a tidy result.

Here is why it matters in real work. Say a data analyst named Rahul once had to calculate department-level statistics for 50,000 employees. Without groupby, he would have to write nested loops that filter the rows for each department by hand. With groupby, it is one line: df.groupby("dept")["salary"].mean(). That single line does the splitting, the averaging, and the combining for him.

Tip: click the diagram to open it full screen, then use the zoom and pan controls for a closer look.

The diagram illustrates the split-apply-combine pattern that powers groupby(): Pandas splits the DataFrame into groups based on a column’s values, applies an aggregation function (sum, mean, count, etc.) to each group independently, and combines the results into a new DataFrame. This three-step pattern is the same concept behind the GROUP BY clause in SQL (Structured Query Language) and MapReduce in distributed computing. Once you see it as split-apply-combine, even complex multi-column groupby operations become intuitive.

Prerequisites

You should be comfortable building a DataFrame and selecting columns first. If those words feel shaky, work through the Pandas data cleaning tutorial before this one. You will also want pandas installed: the code here was tested on Pandas 2.3.3 running on Python 3.14.6. One thing to know up front: Pandas 2.3.3 turns on Copy-on-Write by default, so the examples below never touch the old chained-assignment tricks. They just work the modern way.

GroupBy Basics: Split-Apply-Combine

Let us start with a tiny employee table and ask it a few questions. The first call, df.groupby("dept")["salary"].mean(), reads almost like English: group by department, look at salary, take the mean. After that we layer on .agg() to run several calculations at once, and named aggregations to give the result columns friendly names.

📄 groupby_basics.py: the fundamental pattern

import pandas as pd

df = pd.DataFrame({
    "name": ["Rahul", "Niranjan", "Viraj", "Pravin", "Sardar", "Prathamesh"],
    "dept": ["Eng", "DS", "Eng", "Mkt", "DS", "Eng"],
    "salary": [75000, 82000, 71000, 65000, 78000, 91000],
    "experience": [3, 5, 2, 4, 3, 7]
})

# Basic groupby + aggregation
print("Average salary by department:")
print(df.groupby("dept")["salary"].mean().round(0))
print()

# Multiple aggregations with .agg()
stats = df.groupby("dept")["salary"].agg(["mean", "min", "max", "count"])
print(f"Department salary stats:\n{stats}\n")

# Multiple columns, multiple functions
multi = df.groupby("dept").agg({
    "salary": ["mean", "sum"],
    "experience": ["mean", "max"]
}).round(0)
print(f"Multi-column agg:\n{multi}\n")

# Named aggregations (cleaner column names)
named = df.groupby("dept").agg(
    avg_salary=("salary", "mean"),
    total_salary=("salary", "sum"),
    headcount=("name", "count"),
    max_exp=("experience", "max")
).round(0)
print(f"Named aggregations:\n{named}")

▶ Output

Average salary by department:
dept
DS     80000.0
Eng    79000.0
Mkt    65000.0
Name: salary, dtype: float64

Department salary stats:
         mean    min    max  count
dept
DS    80000.0  78000  82000      2
Eng   79000.0  71000  91000      3
Mkt   65000.0  65000  65000      1

Multi-column agg:
       salary         experience
         mean     sum       mean max
dept
DS    80000.0  160000        4.0   5
Eng   79000.0  237000        4.0   7
Mkt   65000.0   65000        4.0   4

Named aggregations:
      avg_salary  total_salary  headcount  max_exp
dept
DS       80000.0        160000          2        5
Eng      79000.0        237000          3        7
Mkt      65000.0         65000          1        4

What happened here: Four flavors of the same idea. The plain .mean() gives you one number per department. The .agg(["mean", "min", "max", "count"]) call runs four functions at once and lines them up as columns. The dictionary form lets you pick different functions for different columns (salary gets mean and sum, experience gets mean and max), which is why the header has two levels. Notice the salary mean shows as 80000.0 with a decimal point: .round(0) rounds the value but keeps it a float, it does not turn it into an int.

The last style, named aggregations, is the one to reach for in real code. Writing avg_salary=("salary", "mean") spells out exactly what each result column means, so the next person who reads your code (often you, three months later) does not have to decode a MultiIndex.

Transform & Filter

Aggregation shrinks each group to a single row. Sometimes you do not want it smaller, you want the group answer glued back onto every original row. That is what transform does: same number of rows in, same number out. A quick way to picture it: agg is the final score of a cricket match, one number per team, while transform writes that team total next to every single player so you can see how each one compares.

📄 transform_filter.py: same-shape results and group filtering

import pandas as pd

df = pd.DataFrame({
    "name": ["Rahul", "Niranjan", "Viraj", "Pravin", "Sardar", "Prathamesh"],
    "dept": ["Eng", "DS", "Eng", "Mkt", "DS", "Eng"],
    "salary": [75000, 82000, 71000, 65000, 78000, 91000]
})

# transform() returns same-shape result (broadcast back to rows)
df["dept_avg"] = df.groupby("dept")["salary"].transform("mean")
df["vs_dept_avg"] = df["salary"] - df["dept_avg"]
print(f"Transform (salary vs department average):\n{df}\n")

# filter() keeps or drops whole groups
# Keep only departments with more than 1 employee
big_depts = df.groupby("dept").filter(lambda x: len(x) > 1)
print(f"Departments with more than 1 employee:\n{big_depts}")

▶ Output

Transform (salary vs department average):
         name dept  salary  dept_avg  vs_dept_avg
0       Rahul  Eng   75000   79000.0      -4000.0
1    Niranjan   DS   82000   80000.0       2000.0
2       Viraj  Eng   71000   79000.0      -8000.0
3      Pravin  Mkt   65000   65000.0          0.0
4      Sardar   DS   78000   80000.0      -2000.0
5  Prathamesh  Eng   91000   79000.0      12000.0

Departments with more than 1 employee:
         name dept  salary  dept_avg  vs_dept_avg
0       Rahul  Eng   75000   79000.0      -4000.0
1    Niranjan   DS   82000   80000.0       2000.0
2       Viraj  Eng   71000   79000.0      -8000.0
4      Sardar   DS   78000   80000.0      -2000.0
5  Prathamesh  Eng   91000   79000.0      12000.0

What happened here: The transform("mean") call computed each department average, then broadcast it back so every row carries its own department average in a new dept_avg column. Subtracting gives vs_dept_avg, telling us at a glance that Prathamesh earns 12,000 above the Engineering average while Viraj sits 8,000 below it. Because we are on Pandas 2.3.3 with Copy-on-Write, assigning those new columns is safe and clean, no SettingWithCopyWarning to worry about.

The filter() step works on whole groups, not single rows: the lambda function returns True or False for each group, and only groups that pass survive. Marketing has just Pravin, so the entire Marketing group is dropped. Note that filter keeps the original row index (you can see row 3 is gone), which is handy when you need to trace results back to the source data.

Pivot Tables

A pivot table is the same split-apply-combine idea wearing a spreadsheet outfit. Instead of a long list of groups stacked as rows, it spreads one column across the top as headers and another down the side, then fills the grid with your aggregated numbers. If you have ever built a pivot in Excel by dragging fields into Rows and Columns boxes, this is the code version of that.

📄 pivot_table.py: reshape data like Excel pivot tables

import pandas as pd
import numpy as np

sales = pd.DataFrame({
    "quarter": ["Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", "Q3"],
    "region": ["North", "South", "West", "North", "South", "West", "North", "South", "West"],
    "revenue": [50000, 45000, 38000, 55000, 48000, 42000, 60000, 52000, 47000],
    "deals": [10, 8, 7, 12, 9, 8, 14, 11, 9]
})

# Basic pivot table
pivot = pd.pivot_table(sales, values="revenue", index="quarter",
                        columns="region", aggfunc="sum")
print(f"Revenue by Quarter x Region:\n{pivot}\n")

# With margins (totals)
pivot_totals = pd.pivot_table(sales, values="revenue", index="quarter",
                               columns="region", aggfunc="sum", margins=True)
print(f"With totals:\n{pivot_totals}\n")

# Multiple values and aggregations
multi_pivot = pd.pivot_table(sales, values=["revenue", "deals"],
                              index="quarter", aggfunc={"revenue": "sum", "deals": "mean"})
print(f"Multi-value pivot:\n{multi_pivot}")

▶ Output

Revenue by Quarter x Region:
region   North  South   West
quarter
Q1       50000  45000  38000
Q2       55000  48000  42000
Q3       60000  52000  47000

With totals:
region    North   South    West     All
quarter
Q1        50000   45000   38000  133000
Q2        55000   48000   42000  145000
Q3        60000   52000   47000  159000
All      165000  145000  127000  437000

Multi-value pivot:
             deals  revenue
quarter
Q1        8.333333   133000
Q2        9.666667   145000
Q3       11.333333   159000

What happened here: The first pivot turned quarters into rows and regions into columns, with summed revenue filling each cell. Reading across a row gives you a quarter split by region; reading down a column tracks one region across quarters. Adding margins=True tacks on an All row and an All column with the row and column totals, so the bottom-right 437000 is the grand total of every cell. The last pivot shows that you can aggregate each value differently in one call: revenue is summed while deals is averaged, which is why Q1 shows 133000 total revenue but 8.33 average deals.

One caution: numpy is imported here only to make the example self-contained; this particular snippet does not need it directly.

Common Mistakes

Mistake 1: forgetting that groupby is lazy

📄 groupby_lazy.py: a groupby object is not your answer yet

import pandas as pd
df = pd.DataFrame({"dept": ["A", "B", "A"], "val": [1, 2, 3]})

# This does NOT compute anything yet, groupby is lazy
grouped = df.groupby("dept")
print(type(grouped))  # a DataFrameGroupBy object, not your answer

# You must call an aggregation to get results
print(grouped["val"].sum())  # now it computes

▶ Output

<class 'pandas.core.groupby.generic.DataFrameGroupBy'>
dept
A    4
B    2
Name: val, dtype: int64

Why this trips people up: calling groupby("dept") on its own does not give you sums or means. It hands back a DataFrameGroupBy object, basically a promise that says “I know how to split these rows, tell me what to calculate.” Think of it like a recipe card you have written but not cooked yet. Nothing lands on the plate until you call something like .sum(), .mean(), or .agg(). Beginners often print the grouped object, see that cryptic class name, and assume something broke. It did not. You just have not asked for a calculation yet.

Mistake 2: fighting the MultiIndex after grouping by two columns

When you group by more than one column, the result comes back with a MultiIndex (two or more index levels stacked together). Beginners then try to slice it like a normal column and get confused errors. The fix is almost always reset_index(), which flattens those index levels back into plain columns you can filter and merge like usual.

✅ Flatten a grouped result back to plain columns

import pandas as pd
df = pd.DataFrame({
    "dept": ["Eng", "Eng", "DS", "DS"],
    "level": ["Jr", "Sr", "Jr", "Sr"],
    "salary": [60000, 90000, 65000, 95000]
})

# Group by two columns: the result has a MultiIndex
grouped = df.groupby(["dept", "level"])["salary"].mean()
print("MultiIndex Series:")
print(grouped)

# reset_index() turns the index levels back into columns
flat = grouped.reset_index()
print("\nFlat DataFrame you can filter and merge:")
print(flat)

▶ Output

MultiIndex Series:
dept  level
DS    Jr       65000.0
      Sr       95000.0
Eng   Jr       60000.0
      Sr       90000.0
Name: salary, dtype: float64

Flat DataFrame you can filter and merge:
  dept level   salary
0   DS    Jr  65000.0
1   DS    Sr  95000.0
2  Eng    Jr  60000.0
3  Eng    Sr  90000.0

Why this matters: the grouped Series on top is correct, but its two-level index makes it awkward to filter or join. After reset_index() you get an ordinary DataFrame with dept and level as real columns, which is exactly what you want before merging with another table or feeding into a chart. Whenever a grouped result looks indented and hard to slice, reach for reset_index().

Practice Exercises

1. Exercise 1: Group sales by region, calculate total revenue.
2. Exercise 2: Use agg() for sum, mean, count on groups.
3. Exercise 3: Build customer segmentation with custom functions and pivots.

Conclusion

You now have the full split-apply-combine toolkit. Use groupby with .agg() and named aggregations to summarise each group, transform to glue group-level numbers back onto every row, filter to keep or drop whole groups, and pivot_table to reshape everything into a spreadsheet-style grid. You also met the two traps that catch most beginners: a groupby object is lazy until you ask it to calculate, and grouping by two columns hands you a MultiIndex that reset_index() flattens in one line.

Next up is combining DataFrames with merge, join, and concat, so you can pull grouped results together with other tables. For the full path from the basics all the way to machine learning, head back to the Python + AI/ML tutorial series home.

Frequently Asked Questions

Interview Questions on Pandas GroupBy

These come from real screens and onsites. Practice answering before you read each answer.

Q: What are the three steps of split-apply-combine, and which pandas calls perform each?

Split partitions the rows into groups by one or more keys, which is the groupby call. Apply runs a function on each group independently, such as mean, sum, a named aggregation, transform, or filter. Combine stitches the per-group results back into a single Series or DataFrame. You only choose the apply step; groupby handles the split and the combine for you.

Q: Your groupby aggregation on a 10-million-row DataFrame is slow and memory spikes. What do you check first?

First check whether you are using .apply() or a lambda where a built-in aggregation would do the job. Built-ins like "mean" and "sum" run in optimized C, while apply falls back to a Python loop over each group. Next, select only the columns you actually need before grouping so pandas is not carrying extra data through the operation. If the group key is a categorical, pass observed=True so pandas does not materialize every unused category combination, and pass sort=False to skip sorting the keys when order does not matter.

Q: After df.groupby(“dept”).mean() on a table with both text and numeric columns, you get an error. What is going on?

In modern pandas (2.0 and later, including 3.0) reducing a mixed-type frame no longer silently drops text columns, and mean cannot be computed on strings. Either select the numeric columns first, for example df.groupby("dept")[["salary", "experience"]].mean(), or pass numeric_only=True. Naming the columns explicitly is the cleaner habit because it documents exactly what you meant to average.

Q: What is the difference between groupby().apply() and groupby().agg()?

agg receives one column (a Series) at a time and must return a single reduced value per group, which makes it fast and predictable. apply receives the whole group as a DataFrame, so it can look across several columns and return a scalar, a Series, or an entire DataFrame. Reach for apply only when agg and transform cannot express the logic, because it is more flexible but slower.

Q: A per-customer totals report is missing some customers whose key column contains blanks. Which groupby default explains this?

By default groupby drops rows whose group key is NaN (dropna=True), so blank or missing keys silently disappear from the output. Pass dropna=False to keep a group for the missing keys, or clean and fill the key column before grouping. This is a common cause of totals that do not add up to the full dataset.

Q: What does as_index=False do, and how does it relate to reset_index()?

By default groupby puts the group keys into the result’s index. Passing as_index=False keeps them as ordinary columns instead, giving you a flat DataFrame directly. It produces the same shape you would get by calling reset_index() on the default result, just without the extra step.

Go deeper: when you outgrow this post, pandas official documentation is the next stop.

Related Posts

Previous: Pandas: Data Cleaning, Missing Values and Duplicates

Next: Pandas Merge, Join, Concat: Combining DataFrames

Series Home: Python + AI/ML Tutorial Series