Skip to main content
MahiDev — Learn. Build. Become AI-ready.

Lesson 19

agg, transform, and filter

Ask for several summaries at once with agg, spread a group result back over its rows with transform, and keep whole groups with filter.

Intermediate25 min
agg, transform, and filter hero Module 24 Lesson 19agg, transform, and filter hero Module 24 Lesson 19

agg gives one row per group, transform gives one value per original row, and filter keeps or drops whole groups. Choosing the right one of the three is most of what fluent grouping looks like.

Four grouped operations and the shape each one returnsFour grouped operations and the shape each one returns

Several summaries at once

Give agg a list of summary names and you get a column for each. Give it a dict and each column gets its own summary, which is how one call answers several questions.

python
import pandas as pd

learners = pd.DataFrame({
    "name": ["Avery", "Jordan", "Casey", "Riley", "Morgan", "Quinn"],
    "track": ["Python", "NumPy", "Python", "pandas", "NumPy", "pandas"],
    "city": ["Northgate", "Eastport", "Northgate", "Westfield", "Eastport", "Westfield"],
    "hours": [42, 31, 55, 24, 47, 38],
    "score": [88, 74, 91, 65, 82, 79],
})
print(learners.groupby("track")["score"].agg(["size", "min", "mean", "max"]))
print(learners.groupby("track").agg({"hours": "sum", "score": "mean"}).round(2))

Output:

text
        size  min  mean  max
track                       
NumPy      2   74  78.0   82
Python     2   88  89.5   91
pandas     2   65  72.0   79
        hours  score
track               
NumPy      78   78.0
Python     97   89.5
pandas     62   72.0

Name your own output columns

Keyword form reads best in a report: the keyword becomes the column name and the tuple says which column and which summary. Your own function works anywhere a name does.

python
import pandas as pd

learners = pd.DataFrame({
    "name": ["Avery", "Jordan", "Casey", "Riley", "Morgan", "Quinn"],
    "track": ["Python", "NumPy", "Python", "pandas", "NumPy", "pandas"],
    "city": ["Northgate", "Eastport", "Northgate", "Westfield", "Eastport", "Westfield"],
    "hours": [42, 31, 55, 24, 47, 38],
    "score": [88, 74, 91, 65, 82, 79],
})
report = learners.groupby("track").agg(
    learners=("name", "size"),
    total_hours=("hours", "sum"),
    best_score=("score", "max"),
    spread=("score", lambda column: column.max() - column.min()),
)
print(report)

Output:

text
        learners  total_hours  best_score  spread
track                                            
NumPy          2           78          82       8
Python         2           97          91       3
pandas         2           62          79      14

transform keeps the original shape

agg shrinks the table to one row per group. transform returns the group answer for every row it came from, so you can put it straight into a new column and compare each row with its own group.

python
import pandas as pd

learners = pd.DataFrame({
    "name": ["Avery", "Jordan", "Casey", "Riley", "Morgan", "Quinn"],
    "track": ["Python", "NumPy", "Python", "pandas", "NumPy", "pandas"],
    "city": ["Northgate", "Eastport", "Northgate", "Westfield", "Eastport", "Westfield"],
    "hours": [42, 31, 55, 24, 47, 38],
    "score": [88, 74, 91, 65, 82, 79],
})
learners["track_mean"] = learners.groupby("track")["score"].transform("mean")
learners["vs_track"] = (learners["score"] - learners["track_mean"]).round(2)
print(learners[["name", "track", "score", "track_mean", "vs_track"]])

Output:

text
     name   track  score  track_mean  vs_track
0   Avery  Python     88        89.5      -1.5
1  Jordan   NumPy     74        78.0      -4.0
2   Casey  Python     91        89.5       1.5
3   Riley  pandas     65        72.0      -7.0
4  Morgan   NumPy     82        78.0       4.0
5   Quinn  pandas     79        72.0       7.0

Without transform you would summarize, then merge the result back on the group column. This is the same answer in one line.

filter keeps whole groups

The function receives each group as a DataFrame and answers True or False. Every row of a True group is kept, so this asks a question about the group rather than about the row.

python
import pandas as pd

learners = pd.DataFrame({
    "name": ["Avery", "Jordan", "Casey", "Riley", "Morgan", "Quinn"],
    "track": ["Python", "NumPy", "Python", "pandas", "NumPy", "pandas"],
    "city": ["Northgate", "Eastport", "Northgate", "Westfield", "Eastport", "Westfield"],
    "hours": [42, 31, 55, 24, 47, 38],
    "score": [88, 74, 91, 65, 82, 79],
})
print(learners.groupby("track").filter(lambda group: group["score"].mean() > 78))
print(learners.groupby("city").filter(lambda group: len(group) > 1)["name"].tolist())

Output:

text
    name   track       city  hours  score
0  Avery  Python  Northgate     42     88
2  Casey  Python  Northgate     55     91
['Avery', 'Jordan', 'Casey', 'Riley', 'Morgan', 'Quinn']

Ranking inside a group

Grouped rank numbers the rows within each group, which answers questions such as who leads their own track. cumsum behaves the same way: it restarts at every group.

python
import pandas as pd

learners = pd.DataFrame({
    "name": ["Avery", "Jordan", "Casey", "Riley", "Morgan", "Quinn"],
    "track": ["Python", "NumPy", "Python", "pandas", "NumPy", "pandas"],
    "city": ["Northgate", "Eastport", "Northgate", "Westfield", "Eastport", "Westfield"],
    "hours": [42, 31, 55, 24, 47, 38],
    "score": [88, 74, 91, 65, 82, 79],
})
learners["rank_in_track"] = (
    learners.groupby("track")["score"].rank(ascending=False).astype(int)
)
learners["running_hours"] = learners.groupby("track")["hours"].cumsum()
print(learners[["name", "track", "score", "rank_in_track", "running_hours"]])

Output:

text
     name   track  score  rank_in_track  running_hours
0   Avery  Python     88              2             42
1  Jordan   NumPy     74              2             31
2   Casey  Python     91              1             97
3   Riley  pandas     65              2             24
4  Morgan   NumPy     82              1             78
5   Quinn  pandas     79              1             62

Key takeaways

  • agg returns one row per group and takes a list, a dict, or keywords

  • Keyword form names the output columns for you

  • transform returns one value per original row, ready for a new column

  • filter keeps or drops whole groups, not single rows

  • Next: Combining DataFrames

  • Previous: Grouping with groupby

Loading playground…

Check your understanding

agg, transform, and filter Quiz

What shape does agg return?

1 / 10

Previous

← Grouping with groupby

Next

Combining DataFrames →