A panda, counting things by the hour. MidJourney 5
Three air quality monitors sit within about fifteen kilometres of each other on the South Coast: one in Goleta, one in Santa Barbara, and one on the roof of the CNSI building on the UCSB campus. Between the 10th of July and the 11th of August 2024 they recorded 4,942 hourly measurements of ozone and particulate matter.
The three stations are close enough together to be breathing the same air. They are far enough apart, and different enough in what they measure, that comparing them is useful.
You have seen one of these files before. On Wednesday you filtered and ranked the Goleta station one parameter at a time. Today you have all three stations, and you have the pattern that lets you ask about all of them at once.
OpenAQ, an open global air quality data platform. Data supplied to OpenAQ by AirNow. https://openaq.org
Everything below is built from these:
grouped = df.groupby('key') # split
grouped['col'].mean() # apply and combine
df.groupby('key')['col'].mean() # the same pattern, one line
df.groupby('key')['col'].agg(['count', 'mean']) # several summaries at once
df.groupby('key').agg({'a': 'mean', 'b': 'count'}) # different summaries, different columnsAnd the pattern from Wednesday that does most of the work:
df.sort_values('column', ascending=False).head(n) # the top-N patternCreate a new notebook named EOD_Day5_Air_Quality.ipynb.
Add a title cell:
# Day 5 EOD: Three Stations, One Atmosphere
Date: 09/04/2026Two lines here use tools you have not been taught. Both of them are setup. Everything after this block is Day 5 material, and you should recognize all of it.
Stacking the files. The three stations are in three separate CSVs with identical columns. pd.concat() takes a list of DataFrames and stacks them into one, top to bottom. The site column does not exist in the files; you are adding it first, so that the stacked table still records which rows came from where.
goleta['site'] = 'Goleta'
santa_barbara['site'] = 'Santa Barbara'
cnsi['site'] = 'CNSI'
aq = pd.concat([goleta, santa_barbara, cnsi], ignore_index=True)ignore_index=True renumbers the rows 0 to 4,941 instead of restarting at 0 three times.
Pulling the hour out. datetimeLocal is text that looks like 2024-07-11T18:00:00-07:00. Counting from zero, characters 11 and 12 are the hour. Square brackets on .str slice out a piece of every string in the column, the same way they slice a piece out of a list:
aq['hour'] = aq['datetimeLocal'].str[11:13]The result is text, not a number: '06', '14'. Text turns out to be convenient here, because zero-padded hours sort into the same order as the numbers would.
We cover joining tables and dates on Tuesday. Next week you will write both the stacking and the hour extraction yourself.
π Cheatsheet: pandas DataFrames Β· Cheatsheet: time series
import pandas as pd
base = 'https://eds-217-essential-python.github.io/data/'
goleta = pd.read_csv(base + 'openaq_goleta_measurments.csv')
santa_barbara = pd.read_csv(base + 'openaq_santa_barbara_measurments.csv')
cnsi = pd.read_csv(base + 'openaq_CNSI_measurments.csv')
goleta['site'] = 'Goleta'
santa_barbara['site'] = 'Santa Barbara'
cnsi['site'] = 'CNSI'
aq = pd.concat([goleta, santa_barbara, cnsi], ignore_index=True)
aq['hour'] = aq['datetimeLocal'].str[11:13]
aq.shape(4942, 17)Answer each question with code, then write the answer in a markdown cell underneath, in a complete sentence with the numbers in it.
How many readings came from each site?
How many distinct parameters does each site measure? Use the dictionary form of .agg() to report the reading count and the parameter count side by side.
One of the three stations is not like the others. Name it, say what it does not do, and say in one sentence what that means for any comparison you will make across all three stations.
How many readings of each parameter are there across the whole table? Which parameter is the best measured? Which is the worst?
Run the line below and read the output:
aq.groupby('parameter')['unit'].nunique()parameter
o3 1
pm10 1
pm25 1
Name: unit, dtype: int64Ozone is reported in parts per million and the two particulate measurements in micrograms per cubic metre. Never group across the parameter column without filtering to one parameter first. A mean of ozone and PM10 together does not mean anything (last Tuesdayβs units problem).
Build a table of just the PM2.5 readings, ending the line with .copy(), and call it pm25. How many rows?
Which site has the highest mean PM2.5? Write the split-apply-combine pattern, then rank the result.
The three site means are within half a microgram of each other. Before you decide that means anything, use .agg() with a list to report the count, mean, min, max and standard deviation of PM2.5 at each site in one call.
In a markdown cell: The means are nearly identical and the maxima are not. What is different about these three stations, and which of the five columns in your table told you? Would you describe the South Coast as having one air quality or three?
Now do the same for PM10. Which site is higher, and by how much? Note which site is missing from your answer and why.
Use the filter pattern and the split-apply-combine pattern together: how many hours did each site record a PM2.5 value above 12 Β΅g/mΒ³, which was the US annual standard until it was tightened to 9 in 2024?
In a markdown cell: your answer to question 10 has a site missing from it entirely. Is that because the air there was clean, or for another reason? Look back at your answer to question 7.
Ask for the minimum PM2.5 at each site.
Two of the three sites report a negative concentration of particulate matter, which is not a thing that can happen. Count how many negative readings each site has.
In a markdown cell: the site with no negative readings is also the site with the smallest standard deviation and the lowest maximum. Propose one explanation that accounts for all three of those facts at once. Say what you would need to confirm it with this data.
Would you drop the negative readings before computing site means? Compute both versions and report the difference, then say in one sentence which you would publish and why.
Everything so far has grouped by station, which is a question about where. The rest of the practice groups by hour instead, which is a question about when.
Build a table of just the ozone readings, ending the line with .copy(), and call it o3. Then group it by hour and report the count and mean of value, in hour order.
Read the count column first. Twenty-two of the twenty-four hours have about the same number of readings and two do not. Which two, and what does that tell you about how much weight to put on their means?
Now rank the means. Which hour of the day has the highest average ozone, and which the lowest? What is the ratio between them?
Look at the full twenty-four-hour table from question 16 again, top to bottom. Describe the shape of the curve in a markdown cell. Where does it start rising, where does it turn over, and where does it stop falling?
Ground-level ozone is not emitted by anything. It is manufactured in the air out of other pollutants, by sunlight. In three or four sentences, connect that fact to the shape you just described. Does the timing of the peak match what you would expect, and if it is later than you expected, why might that be?
Do the same grouping for PM2.5 and compare. Does particulate matter follow the same daily pattern as ozone?
In a markdown cell: Which one of these two pollutants has a sharper daily cycle than the other? What does the difference suggest about where each one comes from?
In a single markdown cell of 200 to 300 words, answer:
The Santa Barbara County Air Pollution Control District is deciding whether to keep funding all three of these monitors. They ask you: does the third monitor tell us anything the other two do not? You have one month of data and one afternoon.
Your answer must cite at least four specific numbers you computed here, must name at least one thing that is genuinely different about the CNSI station, and must include at least one thing this month of data cannot tell them. Use complete sentences.
Look back through your notebook. Nearly every code cell was the same pattern, wearing different clothes:
df.groupby('key')['col'].mean() # one number per group
df.groupby('key')['col'].agg([...]) # several numbers per group
df.groupby('key').agg({...}) # several columns, different summaries
df.groupby('key')['col'].mean().sort_values() # and then rank the groupsThe grammar stayed the same from cell to cell, and only the key changed. You grouped by site, by parameter, by both at once, and by hour, and each of those choices asked a different question of the same 4,942 rows.
The last column you grouped by is worth stopping on. site and parameter were both columns in the files. hour was not: you made it in the setup block, out of a string, and grouping by it is what gave you the daily ozone cycle in Part 4. A useful grouping key is very often one you have to build first.
Before you close your notebook, check that:
parameter before every calculation that averaged anythinghour.groupby('site') returns before you attach an aggregation to it