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.
Same table as Lesson 01. pd and df are already loaded.
| region | rep | sales | |
|---|---|---|---|
| 0 | East | Ana | 120.0 |
| 1 | West | Ben | 80.0 |
| 2 | East | Cho | NaN |
| 3 | North | Dev | 200.0 |
| 4 | West | Eli | 150.0 |
Part 1 · Learn
The syntax, and what each piece returns
ASort rows
| Syntax | Returns |
|---|---|
df.sort_values("sales") | A new DataFrame sorted ascending (smallest first) by default, with NaN last. Each row keeps its original label. |
…, ascending=False | Sorts 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
| Syntax | Returns |
|---|---|
.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
| Syntax | Returns |
|---|---|
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
| Syntax | Returns |
|---|---|
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.
2 · Top two deals
Set top2 to the two biggest deals, biggest first.
Hint
See A, which has both pieces you need.
4 · Best region by average deal
Set best_region to the name of the region with the highest average sales.
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.
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.
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