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
Finding the largest and smallest values in a column is one of the most common question anyone asks of a dataset. Sorting records to see the largest or smalles is another common code pattern. Both analyses are fundamentally the same operation: put the rows in order, then take the ones at the front.
This afternoon will learn to sort and display our data. We will also add two small additiona code patterns for the case where you want the label of the winner rather than the whole 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 pattern,
df.sort_values('col', ascending=False).head(n) - explain why a ranking can be meaningless if you have not 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 the file. In the Explorer, hover over the EDS217 heading and click New File…, then type the name in full, extension included:
Session_3C_Sorting_and_Ranking.ipynbCheck the kernel. The Kernel Selector in the notebook’s action bar should read Python 3.11.15 (Conda: eds217). If it reads anything else, click it, choose Change Kernel, and pick the eds217 entry.
Add a title cell. Click + Markdown in the action bar, and give it this content, with today’s date:
# Day 3: Session 3C - The Top-N Pattern
[Session Webpage](https://eds-217-essential-python.github.io/course-materials/interactive-sessions/3c_sorting_and_ranking.html)
Date: 09/02/2026Save with
Ctrl + S(Cmd + Son macOS), and keep saving as you go.Read in the Goleta air quality data and rebuild the
pm25subset you made in this morning’s 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 |
Sorting puts the smallest values first, which is what happens by default. The negative readings you flagged yesterday are the smallest of all, so they come out at the head of the table, which is where you would expect them once the order runs upward.
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 yesterday: 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 pattern
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 |
The line you just wrote is the top-N pattern:
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 pattern gives you the bottom N instead.
Learn the whole line as one unit rather than as separate pieces, because you will almost never want to sort a table without immediately taking the front of the result.
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 pattern to find the five cleanest hours in pm25, that is, the five lowest values. Then say why the answer you get may not be very useful, and what you would have to fix in the data first.
Filter first, then rank
Ranking the whole table, without filtering it first, gives you this:
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 almost always beat a number in ppm, so the ranking tells you nothing except which pollutant is reported in the bigger units.
The filter pattern and the top-N pattern are meant to be used together, and in one particular 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. Running the two patterns in that order covers a great deal of practical data analysis, and skipping the filter is how you end up reporting a number that answers a different question from the one you asked.
A ranking is only as good as the group it ranks
Before you report a “top ten”, ask yourself what those ten rows are being compared against, and whether every row in the table belongs in that comparison. Yesterday’s units problem and today’s ranking problem come from the same mistake, which is putting numbers that measure different things next to each other and treating them as one set.
✏️ Test your knowledge
Filter goleta to ozone, whose parameter value is o3, then rank it to find the five highest ozone readings. What are the values, and what unit are they in? Keep the result, because the next exercise uses it.
Finding the label instead of the row
Sometimes you don’t want the row. You want to know which one it was: which hour, which station, which pollutant.
.idxmax() gives you the index label of the largest value in a column:
Code
pm25['value'].idxmax()np.int64(1376)The number you get back 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. Nothing in the output tells you that the second one is there.
Whenever the answer to your question would change if there were a tie, use the top-N pattern 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 you just made to find the timestamp of the highest ozone reading. Then check your answer with the top-N pattern.
Putting it together
The two patterns 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 pattern 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 does not answer the question you asked.
.idxmax()and.idxmin()give the index label of the extreme value, not the value..loc[row_label, column_label]looks up a single value.