A panda, reshaping swords into ploughshares. MidJourney 5
Friday’s exercise opened with a block of code containing the pd.concat() command, which we asked you to copy rather than write… along with a promise that you would get to write it yourself on Tuesday…
🎉 Today is Tuesday! 🎉
The copied block did two things. It stacked three files into one table, and it pulled an hour out of a timestamp. We will learn more about stacking this morning and we will come back to the 🦹♂️ timestamps 🦹♂️ after lunch.
Stacking three files gives you a long table, which is the layout that is easiest to build and the hardest one to compare across: 4,942 rows, each holding a single number, with the labels that explain that number spread across four other columns. Every comparison you made on Friday needed a .groupby() to reach it. That is what is known as a long table.
The same data can also be arranged wide, with one row per station and one column per pollutant. In that arrangement, most of Friday’s comparisons simplify into reading two numbers that are already sitting next to each other.
By the end of this session you will be able to:
pd.concat([a, b, c]), and say what ignore_index=True fixespivot_table(index=, columns=, values=), and read its result.value_counts() result back into a table you can merge with, using .reset_index()Create the file. In the Explorer, hover over the EDS217 heading and click New File…, then type the name in full, extension included: Session_6B_Reshaping_Data.ipynb
Check 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 6: Session 6B - Long and Wide
[Session Webpage](https://eds-217-essential-python.github.io/course-materials/interactive-sessions/6b_reshaping_data.html)
Date: 09/08/2026Save with Ctrl + S (Cmd + S on macOS), and keep saving as you go.
Read the three station files. Nothing here is new:
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')
print(goleta.shape)
print(santa_barbara.shape)
print(cnsi.shape)(1962, 15)
(2266, 15)
(714, 15)Earlier this morning we joined two tables side by side. The tables had different columns, they shared a key, and the result was wider than either input.
Stacking goes the other direction. The three station files have the same fifteen columns and different rows, so there is no key to line them up on. We want to put one file underneath the other and end up with a taller table.
pd.merge() cannot stack tables. pd.concat() can, and it takes a list of DataFrames:
aq = pd.concat([goleta, santa_barbara, cnsi])
aq.shape(4942, 15)4,942 rows, which is 1,962 plus 2,266 plus 714. Nothing was matched and nothing was dropped, because stacking simply puts the three tables end to end in the order you listed them.
Look at the table you just built and try to answer a simple question: which of those 4,942 rows came from the CNSI monitor?
There is no way to tell. None of the three files has a column naming its station, because each file only ever described one station. Once the three files are stacked, the only record of where a row came from is the order of the rows, and the first sort or filter you run destroys even that.
So, just as we make sure to label columns before we merge dataframes, we must also add dataframe labels first and stack them second…because we can only be certain which station a row belongs to while its rows are still sitting in their own file.
goleta['site'] = 'Goleta'
santa_barbara['site'] = 'Santa Barbara'
cnsi['site'] = 'CNSI'
aq = pd.concat([goleta, santa_barbara, cnsi], ignore_index=True)
aq.shape(4942, 16)The above code uses our derived-column patterns from Thursday, and the stacked table now records where every row came from. The four lines above are the block you copied on Friday, written out in full.
ignore_index=True fixesStack the tables without it and look at the row labels at the join:
messy = pd.concat([goleta, santa_barbara, cnsi])
print(messy.index[:3])
print(messy.index[-3:])Index([0, 1, 2], dtype='int64')
Index([711, 712, 713], dtype='int64')Each input table has its own index, numbered from zero, and stacking keeps all three of those indexes! The stacked table therefore has three rows “numbered” (it’s actually a label!) 0, three numbered 1, and so on, so .loc[0] no longer identifies a single row. ignore_index=True discards the old numbering and renumbers the result from 0 to 4,941.
Use it every time, unless you have a specific reason not to.
Two things, both of them the stacking pattern written from scratch:
Stack the three files again with ignore_index=True, in a different order: CNSI first, then Goleta, then Santa Barbara. Confirm the shape is identical. Then use .head() to show that the row order changed and .value_counts() on site to show that the contents did not.
Stack only two of them, Goleta and CNSI, into a table called two_sites. Predict the row count out loud before you run it, then check.
Look at what one row of this table actually says:
aq[['site', 'parameter', 'value', 'unit', 'datetimeLocal']].head()| site | parameter | value | unit | datetimeLocal | |
|---|---|---|---|---|---|
| 0 | Goleta | o3 | 0.025 | ppm | 2024-07-11T18:00:00-07:00 |
| 1 | Goleta | o3 | 0.028 | ppm | 2024-07-11T19:00:00-07:00 |
| 2 | Goleta | o3 | 0.029 | ppm | 2024-07-11T20:00:00-07:00 |
| 3 | Goleta | o3 | 0.027 | ppm | 2024-07-11T21:00:00-07:00 |
| 4 | Goleta | o3 | 0.026 | ppm | 2024-07-11T22:00:00-07:00 |
One number per row, plus four columns of labels explaining what the number is. As mentioned above, this layout has a name: it is long. Almost every instrument, database and API gives you data in this layout, because a long table can be appended to forever without ever changing its columns.
A long table is also awkward to compare across, because the numbers you want to put side by side are all stacked in the same column. To compare the mean PM2.5 across the three stations on Friday, you had to filter to one parameter and then group. To compare ozone and PM2.5 at Goleta earlier this morning, you had to split the table in two and join it back together.
The other layout is wide: one row per thing, one column per measurement, and the labels promoted out of the cells and up into the column headers. In a wide table you make a comparison by reading two numbers that are already sitting next to each other.
pivot_table() rotates a DataFrame from long to wide. It takes three arguments, and each one is the name of a column in your long table:
means = aq.pivot_table(index='site', columns='parameter', values='value')
means| parameter | o3 | pm10 | pm25 |
|---|---|---|---|
| site | |||
| CNSI | NaN | NaN | 6.083473 |
| Goleta | 0.022470 | 14.972921 | 6.480926 |
| Santa Barbara | 0.019822 | 17.563969 | 6.172324 |
df.pivot_table(index='rows', columns='columns', values='numbers')
# ↑ ↑ ↑
# what labels what labels what goes
# the rows the columns in the cellsindex= is the column whose values become the row labels.columns= is the column whose values become the column headers.values= is the column whose numbers fill the cells.We have three stations, each with three parameters. This creates nine cells… and two of them empty!?
Much of Friday’s Part 1 and Part 2 is in that one small table, including a result that took several questions to reach on Friday: CNSI measures PM2.5 and nothing else.
There are 714 CNSI PM2.5 readings and exactly one cell to put them in, so pivot_table has to reduce them to a single number. By default it takes the mean, which is why the cells above are averages rather than counts or sums.
You can ask for something else with aggfunc=:
aq.pivot_table(index='site', columns='parameter', values='value', aggfunc='count')| parameter | o3 | pm10 | pm25 |
|---|---|---|---|
| site | |||
| CNSI | NaN | NaN | 714.0 |
| Goleta | 711.0 | 517.0 | 734.0 |
| Santa Barbara | 734.0 | 766.0 | 766.0 |
The counts table has the same layout as the means table, and it is worth building first whenever you pivot, because it tells you how many numbers went into each cell. Two cells are empty in the means table and the same two are empty here: CNSI reports no ozone and no PM10 at all, so the gaps are real and not an accident of averaging bad data or some other issue.
🐍 pivot_table with aggfunc= is the split-apply-combine pattern in a different arrangement!
aq.groupby(['site', 'parameter'])['value'].mean() computes exactly the same nine numbers; it just returns them stacked in a single column instead of laid out in a grid. Same calculation, different shape!! (another example of wide versus long, perhaps??)
The result of pivot_table is an ordinary DataFrame whose index is the index= column, so everything you know still works:
means['pm25']site
CNSI 6.083473
Goleta 6.480926
Santa Barbara 6.172324
Name: pm25, dtype: float64means.loc['Goleta', 'pm25']np.float64(6.4809264305177114)Watch out for one change, though: site is now the index rather than a column, so means['site'] raises a KeyError (site is not a column!).
When you want the station names back as a regular column, use .reset_index(), which you met on Friday:
flat = means.reset_index()
flat| parameter | site | o3 | pm10 | pm25 |
|---|---|---|---|---|
| 0 | CNSI | NaN | NaN | 6.083473 |
| 1 | Goleta | 0.022470 | 14.972921 | 6.480926 |
| 2 | Santa Barbara | 0.019822 | 17.563969 | 6.172324 |
Now site is a column again, and the flattened table can be merged, filtered and sorted like any other DataFrame.
Build a wide table with parameter down the rows and site across the columns, which is the transpose of the one above, by swapping the index= and columns= arguments. Then decide which of the two layouts you would put in a report for the Air Pollution Control District, and write a sentence saying why.
One more small move, and it brings this morning’s two sessions together.
.value_counts() has been your counting tool since Day 2, and it returns a Series:
aq['site'].value_counts()site
Santa Barbara 2266
Goleta 1962
CNSI 714
Name: count, dtype: int64A Series is easy enough to read, but you cannot merge one, because pd.merge() needs two DataFrames with columns to join on and a Series has an index where the column would be. So we promote it into a DataFrame:
counts = aq['site'].value_counts().reset_index()
counts| site | count | |
|---|---|---|
| 0 | Santa Barbara | 2266 |
| 1 | Goleta | 1962 |
| 2 | CNSI | 714 |
Two proper columns, site and count. The second name is generic, so rename it to say what it counts:
counts = counts.rename(columns={'count': 'n_readings'})
counts| site | n_readings | |
|---|---|---|
| 0 | Santa Barbara | 2266 |
| 1 | Goleta | 1962 |
| 2 | CNSI | 714 |
Learn the two lines below as a single move, because you will use them constantly:
counts = df['column'].value_counts().reset_index()
counts = counts.rename(columns={'count': 'a_better_name'})And now the two halves of this morning fit together. A wide table and a count table, joined by the station name:
summary = pd.merge(flat, counts, on='site')
summary| site | o3 | pm10 | pm25 | n_readings | |
|---|---|---|---|---|---|
| 0 | CNSI | NaN | NaN | 6.083473 | 714 |
| 1 | Goleta | 0.022470 | 14.972921 | 6.480926 | 1962 |
| 2 | Santa Barbara | 0.019822 | 17.563969 | 6.172324 | 2266 |
One table, three rows, and the means and the counts for all three monitors side by side. On Friday, assembling the same summary took a whole series of separate .groupby() calls.
Build a count table of readings per parameter instead of per site, renaming the count column to n_readings. Then sort it so the best-measured parameter is first.
On Friday you found the daily ozone cycle by grouping every ozone reading by hour, with all of the stations pooled together. Splitting that result out station by station was harder, because a grouped result comes back as one column of numbers and a station-by-station comparison needs one column per station.
A pivot does it in one line. The hour column comes from the second line we supplied on Friday, which cuts characters 11 and 12 out of each timestamp. We come back to it properly after lunch:
aq['hour'] = aq['datetimeLocal'].str[11:13]
pm25 = aq[aq['parameter'] == 'pm25'].copy()
hourly = pm25.pivot_table(index='hour', columns='site', values='value')
hourly.round(2)| site | CNSI | Goleta | Santa Barbara |
|---|---|---|---|
| hour | |||
| 00 | 5.86 | 5.10 | 5.59 |
| 01 | 5.84 | 9.07 | 5.47 |
| 02 | 6.08 | 4.83 | 5.44 |
| 03 | 5.99 | 5.30 | 5.38 |
| 04 | 5.60 | 3.90 | 4.62 |
| 05 | 5.63 | 3.47 | 4.41 |
| 06 | 5.90 | 4.10 | 4.09 |
| 07 | 6.03 | 5.58 | 4.00 |
| 08 | 5.96 | 6.42 | 6.06 |
| 09 | 6.08 | 7.42 | 6.94 |
| 10 | 6.24 | 7.71 | 6.03 |
| 11 | 6.67 | 7.52 | 7.03 |
| 12 | 7.60 | 8.19 | 7.72 |
| 13 | 7.21 | 8.23 | 7.53 |
| 14 | 6.56 | 7.45 | 7.31 |
| 15 | 6.29 | 7.00 | 6.53 |
| 16 | 6.14 | 6.55 | 6.69 |
| 17 | 5.78 | 6.26 | 6.56 |
| 18 | 5.53 | 7.03 | 6.50 |
| 19 | 5.62 | 7.87 | 7.66 |
| 20 | 6.00 | 7.42 | 7.41 |
| 21 | 5.88 | 6.77 | 6.94 |
| 22 | 5.88 | 6.35 | 6.34 |
| 23 | 5.83 | 5.84 | 5.97 |
Twenty-four rows and three columns, laid out so we can ask a question that was awkward to ask on Friday: do the three stations rise and fall together?
Read down the columns. Goleta runs from 3.5 at five in the morning up to 9.1 at one in the morning, a range of 5.6. Santa Barbara does something similar over a narrower range, 4.0 to 7.7. CNSI runs from 5.5 to 7.6, and the only two hours above 7 are noon and one in the afternoon: for the other twenty-two hours it sits between 5.5 and 6.7 and barely moves. Three monitors, fifteen kilometres apart, and the CNSI monitor records a much flatter day than the other two do.
Nothing we just did was something that .groupby() could not have calculated. However, a grouped result would have come back as one long column, and you would have had to hold three separate results in your head to put them alongside each other. The pivot lays the same numbers out in a grid instead, so the comparison is cleaner.
pd.concat([a, b, c]) stacks tables with the same columns, top to bottom. It matches nothing and drops nothing.ignore_index=True so the stacked table gets one clean set of row numbers.df.pivot_table(index=, columns=, values=). It goes long to wide.pivot_table averages by default. Pass aggfunc='count' and look at that version first.index= column. .reset_index() turns it back into a plain table..value_counts().reset_index() promotes a count Series into a two-column table you can merge with. Rename the count column while you are there.