Lesson 02 · Data Science → AI/ML Engineering

Sort and group

"Who's top?" and "how much per team?" are the two questions you'll get asked most often.

≈ 15 minLearn → Practice → ReviewBuilds on: Lesson 01

Same table as Lesson 01. pd and df are already loaded.

regionrepsales
0EastAna120.0
1WestBen80.0
2EastChoNaN
3NorthDev200.0
4WestEli150.0

Part 1 · Learn

The syntax, and what each piece returns

ASort rows

SyntaxReturns
df.sort_values("sales")A new DataFrame sorted ascending (smallest first) by default, with NaN last. Each row keeps its original label.
…, ascending=FalseSorts largest first instead. NaN still goes last.
.head(n)The first n rows (5 if you don't give a number).

BPick one row, then one value

SyntaxReturns
.iloc[0]The row at position 0, meaning whichever row is first right now, e.g. after sorting. It returns a Series.
.iloc[-1]The last row by position.
row["rep"]One value from that row, e.g. the string 'Ben'.
df.loc[3, "rep"]Looks up a value by label: the row labelled 3, column rep. Labels don't change when you sort; positions do.

CFind where the max or min is

SyntaxReturns
df["sales"].max()The biggest value (200.0).
df["sales"].idxmax()The label of the row that holds the biggest value (3). .idxmin() works the same way for the smallest.

So df.loc[df["sales"].idxmax(), "rep"] means "the rep on the row where sales is highest". You get it without sorting.

DGroup, then aggregate

SyntaxReturns
df.groupby("region")["sales"].sum()A Series with one value per region. Its index is the region names, sorted alphabetically.
….mean() .count() .max()Any aggregation from Lesson 01 works per group, and they all still skip NaN.
….sum().idxmax()Because the result is indexed by region, idxmax() returns the region name.

Read it as split → apply → combine: split the rows by region, sum each group, then combine the results into one Series.[1]

Part 2 · Practice

Now write it yourself

1 · Weakest rep

Set weakest to the name (a string) of the rep with the lowest sales. Use sorting.

Hint

A, then B. Where does Cho's NaN end up?

2 · Top two deals

Set top2 to the two biggest deals, biggest first.

Hint

See A, which has both pieces you need.

3 · Totals per region

Set totals to the total sales per region.

Hint

See D.

4 · Best region by average deal

Set best_region to the name of the region with the highest average sales.

Hint

Group with .mean() as in D. That gives you a Series of averages indexed by region name. Which tool from C returns the label of the biggest value?

Stretch · Top rep without sorting

Set top_rep to the name of the rep with the highest sales, without using sort_values.

Hint

See C, which shows the exact pattern.

Part 3 · Remember

Spaced review

Next, do the practice set until this lesson's skills are mastered (3 clean solves each). Then its 7 cards join your Daily review from the next day.

Use it at work tomorrow

The next time someone asks you "which X is top?" or "how much per Y?", write the sort_values or groupby line yourself before reaching for AI.

Primary source

Python for Data Analysis ch. 10: Data Aggregation and Group Operations, by Wes McKinney. Read §10.1 on how groupby works. Sorting is in §5.2 Sorting and Ranking.

Ask me anything. If the difference between loc and iloc, or between a value and a label, is fuzzy, ask me. It's the most common point of confusion in pandas.

[1] pandas docs, Group by: split-apply-combine