Code
import pandas as pd
url = 'https://eds-217-essential-python.github.io/data/openaq_goleta_measurments.csv'
goleta = pd.read_csv(url)
pm25 = goleta[goleta['parameter'] == 'pm25'].copy()
pm25.shape(734, 15)
A panda, putting things in order. MidJourney 5
“Which one is the worst?” is probably the most common question anyone asks of a dataset. So is “what are the top ten?” Both are the same operation: put the rows in order, then take the ones at the front.
This session gives you that operation as a named pattern, and gives you two small companions for the case where you want the label of the winner rather than the row.
By the end of this session you will be able to:
- reorder a table with
.sort_values(), in either direction - write the top-N sentence,
df.sort_values('col', ascending=False).head(n) - explain why a ranking is meaningless until you have filtered first
- find the label of the largest or smallest value with
.idxmax()and.idxmin() - look up a single value with
.loc[row_label, column_label]
Getting Started
Create a new notebook from the Command Palette (Create: New Jupyter Notebook), and confirm its kernel reads eds217_2026.
Save your notebook (
Ctrl + S, orCmd + Son macOS) as:Session_3C_Sorting_and_Ranking.ipynbAdd a title cell (Markdown), updating the date to today:
# Day 3: Session 3C - The Top-N Sentence
[Session Webpage](https://eds-217-essential-python.github.io/course-materials/interactive-sessions/3c_sorting_and_ranking.html)
Date: 09/02/2026- Read in the Goleta air quality data and rebuild the
pm25subset from the last session:
Putting a table in order
.sort_values() takes the name of a column and reorders the whole table by it:
Code
pm25.sort_values('value').head()| location_id | location_name | parameter | value | unit | datetimeUtc | datetimeLocal | timezone | latitude | longitude | country_iso | isMobile | isMonitor | owner_name | provider | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1691 | 1186 | Goleta | pm25 | -4.0 | µg/m³ | 2024-07-31T11:00:00+00:00 | 2024-07-31T04:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1716 | 1186 | Goleta | pm25 | -3.0 | µg/m³ | 2024-08-01T12:00:00+00:00 | 2024-08-01T05:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1428 | 1186 | Goleta | pm25 | -3.0 | µg/m³ | 2024-07-20T12:00:00+00:00 | 2024-07-20T05:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1429 | 1186 | Goleta | pm25 | -3.0 | µg/m³ | 2024-07-20T13:00:00+00:00 | 2024-07-20T06:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1713 | 1186 | Goleta | pm25 | -3.0 | µg/m³ | 2024-08-01T09:00:00+00:00 | 2024-08-01T02:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
Smallest first, which is the default. Those negative readings are the ones you flagged yesterday, and here they are at the bottom of the ranking where you would expect them.
To go the other way, pass ascending=False:
Code
pm25.sort_values('value', ascending=False).head()| location_id | location_name | parameter | value | unit | datetimeUtc | datetimeLocal | timezone | latitude | longitude | country_iso | isMobile | isMonitor | owner_name | provider | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1376 | 1186 | Goleta | pm25 | 22.0 | µg/m³ | 2024-07-18T08:00:00+00:00 | 2024-07-18T01:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1712 | 1186 | Goleta | pm25 | 22.0 | µg/m³ | 2024-08-01T08:00:00+00:00 | 2024-08-01T01:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1873 | 1186 | Goleta | pm25 | 21.0 | µg/m³ | 2024-08-08T08:00:00+00:00 | 2024-08-08T01:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1930 | 1186 | Goleta | pm25 | 20.0 | µg/m³ | 2024-08-10T17:00:00+00:00 | 2024-08-10T10:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1863 | 1186 | Goleta | pm25 | 19.0 | µg/m³ | 2024-08-07T22:00:00+00:00 | 2024-08-07T15:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
Now the largest values are on top. ascending= is a keyword argument, the same kind of argument as index_col= from Monday: you pass it by name, after the column.
Sorting works on text columns too, alphabetically:
Code
goleta.sort_values('parameter').head(3)| location_id | location_name | parameter | value | unit | datetimeUtc | datetimeLocal | timezone | latitude | longitude | country_iso | isMobile | isMonitor | owner_name | provider | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1186 | Goleta | o3 | 0.025 | ppm | 2024-07-12T01:00:00+00:00 | 2024-07-11T18:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 469 | 1186 | Goleta | o3 | 0.014 | ppm | 2024-08-01T11:00:00+00:00 | 2024-08-01T04:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 470 | 1186 | Goleta | o3 | 0.013 | ppm | 2024-08-01T12:00:00+00:00 | 2024-08-01T05:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
Note
🐍 .sort_values() returns a new DataFrame in the new order and leaves the original alone. Look at pm25.head() after all that sorting and the rows are exactly where they were.
Code
pm25.head(3)| location_id | location_name | parameter | value | unit | datetimeUtc | datetimeLocal | timezone | latitude | longitude | country_iso | isMobile | isMonitor | owner_name | provider | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1228 | 1186 | Goleta | pm25 | 3.0 | µg/m³ | 2024-07-12T01:00:00+00:00 | 2024-07-11T18:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1229 | 1186 | Goleta | pm25 | 8.0 | µg/m³ | 2024-07-12T02:00:00+00:00 | 2024-07-11T19:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1230 | 1186 | Goleta | pm25 | 6.0 | µg/m³ | 2024-07-12T03:00:00+00:00 | 2024-07-11T20:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
✏️ Test your knowledge
Sort pm25 by datetimeLocal instead of by value. What does the first row give you? Say in one sentence what sorting by a timestamp column does.
The top-N sentence
Sorting gives you the whole table in order. Almost always you want the front of it, so pair the sort with .head():
Code
pm25.sort_values('value', ascending=False).head(10)| location_id | location_name | parameter | value | unit | datetimeUtc | datetimeLocal | timezone | latitude | longitude | country_iso | isMobile | isMonitor | owner_name | provider | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1376 | 1186 | Goleta | pm25 | 22.0 | µg/m³ | 2024-07-18T08:00:00+00:00 | 2024-07-18T01:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1712 | 1186 | Goleta | pm25 | 22.0 | µg/m³ | 2024-08-01T08:00:00+00:00 | 2024-08-01T01:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1873 | 1186 | Goleta | pm25 | 21.0 | µg/m³ | 2024-08-08T08:00:00+00:00 | 2024-08-08T01:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1930 | 1186 | Goleta | pm25 | 20.0 | µg/m³ | 2024-08-10T17:00:00+00:00 | 2024-08-10T10:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1863 | 1186 | Goleta | pm25 | 19.0 | µg/m³ | 2024-08-07T22:00:00+00:00 | 2024-08-07T15:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1838 | 1186 | Goleta | pm25 | 19.0 | µg/m³ | 2024-08-06T20:00:00+00:00 | 2024-08-06T13:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1534 | 1186 | Goleta | pm25 | 17.0 | µg/m³ | 2024-07-24T22:00:00+00:00 | 2024-07-24T15:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1592 | 1186 | Goleta | pm25 | 17.0 | µg/m³ | 2024-07-27T08:00:00+00:00 | 2024-07-27T01:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1840 | 1186 | Goleta | pm25 | 16.0 | µg/m³ | 2024-08-06T23:00:00+00:00 | 2024-08-06T16:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
| 1839 | 1186 | Goleta | pm25 | 16.0 | µg/m³ | 2024-08-06T21:00:00+00:00 | 2024-08-06T14:00:00-07:00 | America/Los_Angeles | 34.445301 | -119.827797 | NaN | NaN | NaN | Unknown Governmental Organization | AirNow |
That is the top-N sentence:
df.sort_values('column', ascending=False).head(n)Read it left to right as three instructions: sort by this column, biggest first, and give me the first n. Drop ascending=False and the same sentence gives you the bottom N instead.
You will write this line more often than any other line this week. Learn it as one unit rather than as three separate methods, because you will almost never want one without the others.
Often you only care about a couple of columns, so select them after ranking:
Code
pm25.sort_values('value', ascending=False).head(10)[['datetimeLocal', 'value', 'unit']]| datetimeLocal | value | unit | |
|---|---|---|---|
| 1376 | 2024-07-18T01:00:00-07:00 | 22.0 | µg/m³ |
| 1712 | 2024-08-01T01:00:00-07:00 | 22.0 | µg/m³ |
| 1873 | 2024-08-08T01:00:00-07:00 | 21.0 | µg/m³ |
| 1930 | 2024-08-10T10:00:00-07:00 | 20.0 | µg/m³ |
| 1863 | 2024-08-07T15:00:00-07:00 | 19.0 | µg/m³ |
| 1838 | 2024-08-06T13:00:00-07:00 | 19.0 | µg/m³ |
| 1534 | 2024-07-24T15:00:00-07:00 | 17.0 | µg/m³ |
| 1592 | 2024-07-27T01:00:00-07:00 | 17.0 | µg/m³ |
| 1840 | 2024-08-06T16:00:00-07:00 | 16.0 | µg/m³ |
| 1839 | 2024-08-06T14:00:00-07:00 | 16.0 | µg/m³ |
✏️ Test your knowledge
Write the top-N sentence to find the five cleanest hours in pm25, that is, the five lowest values. Then say why the answer you get is not very useful, and what you would have to do about it first.
Filter first, then rank
Here is what happens if you rank without filtering:
Code
goleta.sort_values('value', ascending=False).head(5)[['parameter', 'value', 'unit']]| parameter | value | unit | |
|---|---|---|---|
| 1086 | pm10 | 40.0 | µg/m³ |
| 795 | pm10 | 38.0 | µg/m³ |
| 796 | pm10 | 36.0 | µg/m³ |
| 1084 | pm10 | 36.0 | µg/m³ |
| 1105 | pm10 | 36.0 | µg/m³ |
Every row at the top is pm10, because particulates are reported in micrograms per cubic metre and ozone is reported in parts per million. A number in µg/m³ will beat a number in ppm every time, and the ranking tells you nothing except which column has the bigger units.
The two sentences you learned today are meant to be used in that order:
Code
goleta[goleta['parameter'] == 'pm10'].sort_values('value', ascending=False).head(5)[
['datetimeLocal', 'value', 'unit']
]| datetimeLocal | value | unit | |
|---|---|---|---|
| 1086 | 2024-08-05T17:00:00-07:00 | 40.0 | µg/m³ |
| 795 | 2024-07-24T14:00:00-07:00 | 38.0 | µg/m³ |
| 1105 | 2024-08-06T14:00:00-07:00 | 36.0 | µg/m³ |
| 1084 | 2024-08-05T15:00:00-07:00 | 36.0 | µg/m³ |
| 796 | 2024-07-24T15:00:00-07:00 | 36.0 | µg/m³ |
Filter to one comparable thing, then rank it. That sequence is most of practical data analysis, and forgetting the first half is one of the most common ways to publish a wrong number.
A ranking is only as good as the group it ranks
Before you report a “top ten”, ask yourself what the ten are being compared against, and whether every row in the table belongs in that comparison. Yesterday’s units problem and today’s ranking problem are the same problem, arriving twice.
✏️ Test your knowledge
Filter goleta to ozone, then rank it to find the five highest ozone readings. What are the values, and what unit are they in?
Finding the label instead of the row
Sometimes you don’t want the row. You want to know which one it was: which day, which station, which food.
.idxmax() gives you the index label of the largest value in a column:
Code
pm25['value'].idxmax()np.int64(1376)That number is a row label, not a value and not a position. Hand it to .loc[] with a column name and you get the one thing you were after:
Code
worst_hour = pm25['value'].idxmax()
pm25.loc[worst_hour, 'datetimeLocal']'2024-07-18T01:00:00-07:00'.loc[row_label, column_label] reads as “the value at this row, in this column”. You saw it this morning on the Toolik data, and it is the natural partner to .idxmax().
.idxmin() does the same for the smallest value:
Code
cleanest_hour = pm25['value'].idxmin()
print(cleanest_hour)
print(pm25.loc[cleanest_hour, 'datetimeLocal'])
print(pm25.loc[cleanest_hour, 'value'])1691
2024-07-31T04:00:00-07:00
-4.0.idxmax() reports one winner, even when there are two
Two hours in this dataset are tied at the maximum of 22 µg/m³, and .idxmax() names only one of them. It does not warn you that the other exists.
Whenever the answer to your question would change if there were a tie, use the top-N sentence and read the result, rather than trusting a single label:
Code
pm25.sort_values('value', ascending=False).head(3)[['datetimeLocal', 'value']]| datetimeLocal | value | |
|---|---|---|
| 1376 | 2024-07-18T01:00:00-07:00 | 22.0 |
| 1712 | 2024-08-01T01:00:00-07:00 | 22.0 |
| 1873 | 2024-08-08T01:00:00-07:00 | 21.0 |
✏️ Test your knowledge
Use .idxmax() and .loc[] on the ozone subset to find the timestamp of the highest ozone reading. Then check your answer with the top-N sentence.
Putting it together
The two sentences of Day 3, in one workflow:
Code
pm10 = goleta[goleta['parameter'] == 'pm10'].copy()
worst_ten = pm10.sort_values('value', ascending=False).head(10)
worst_ten[['datetimeLocal', 'value', 'unit']]| datetimeLocal | value | unit | |
|---|---|---|---|
| 1086 | 2024-08-05T17:00:00-07:00 | 40.0 | µg/m³ |
| 795 | 2024-07-24T14:00:00-07:00 | 38.0 | µg/m³ |
| 1105 | 2024-08-06T14:00:00-07:00 | 36.0 | µg/m³ |
| 1084 | 2024-08-05T15:00:00-07:00 | 36.0 | µg/m³ |
| 796 | 2024-07-24T15:00:00-07:00 | 36.0 | µg/m³ |
| 1103 | 2024-08-06T12:00:00-07:00 | 35.0 | µg/m³ |
| 1195 | 2024-08-10T09:00:00-07:00 | 34.0 | µg/m³ |
| 848 | 2024-07-26T19:00:00-07:00 | 34.0 | µg/m³ |
| 1126 | 2024-08-07T12:00:00-07:00 | 34.0 | µg/m³ |
| 1100 | 2024-08-06T09:00:00-07:00 | 33.0 | µg/m³ |
Code
worst = pm10['value'].idxmax()
print('Worst pm10 hour:', pm10.loc[worst, 'datetimeLocal'])
print('Value:', pm10.loc[worst, 'value'], pm10.loc[worst, 'unit'])Worst pm10 hour: 2024-08-05T17:00:00-07:00
Value: 40.0 µg/m³Key points
.sort_values('col')reorders a table, smallest first.ascending=Falsereverses it.- The top-N sentence is
df.sort_values('col', ascending=False).head(n). Learn it as one unit. - Sorting returns a new DataFrame. Assign it to a name to keep it.
- Filter first, then rank. A ranking across categories that are not comparable is not an answer.
.idxmax()and.idxmin()give the index label of the extreme value, not the value..loc[row_label, column_label]looks up a single value.