Interactive Session

🧩 Long and Wide

The same data, arranged two ways.

Last night’s exercise opened with a block of code I asked you to copy rather than write, and a promise that you would write it yourself on Tuesday.

It is Tuesday.

That block did two things. It stacked three files into one table, and it pulled an hour out of a timestamp. This session is the first of those; the second one is after lunch.

Then it goes one step further, because stacking three files gives you a table in the shape that is easiest to build and hardest to read. Four thousand nine hundred and forty-two rows, each one a single number, with the labels that explain it spread across three other columns. Every comparison you made last night needed a .groupby() to get at it.

There is a second shape the same data can take, and in that shape most of last night’s questions are answered by simply looking.

By the end of this session you will be able to:

Getting Started

  1. Create a new notebook from the Command Palette (Create: New Jupyter Notebook), and confirm its kernel reads eds217_2026.

  2. Save your notebook (Ctrl + S, or Cmd + S on macOS) as: Session_6B_Reshaping_Data.ipynb

  3. Add a title cell (Markdown), updating the date to today:

# 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/2026
  1. Read the three station files. Nothing here is new:
Code 
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)

Stacking is not joining

This morning you joined two tables side by side. The tables had different columns, they shared a key, and the result was wider than either input.

Stacking is the other direction. The three station files have the same fifteen columns and different rows. You do not want to line them up by a key; you want to put one underneath the other and end up with a taller table.

pd.merge() cannot do that. pd.concat() can, and it takes a list of DataFrames:

Code 
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, nothing was dropped; the three tables were laid end to end in the order you listed them.

Label your pieces before you stack them

Look at what you just built and try to answer a simple question: which of those 4,942 rows came from the CNSI monitor?

You cannot. The three files never had a column saying which station they were, because each file only ever described one. The moment you stack them, that information exists only in the order of the rows, and the first operation that sorts or filters the table destroys it.

So label first, stack second. Always.

Code 
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)

Three derived-column sentences from Thursday, and now the stacked table knows where every row came from. That is the block you copied last night, written out.

What ignore_index=True fixes

Stack the tables without it and look at the row labels at the join:

Code 
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 brought its own index along, numbered from zero. The stacked table therefore has three rows numbered 0, three numbered 1, and so on, and .loc[0] no longer identifies a single row. ignore_index=True throws the old numbering away and renumbers the result from 0 to 4,941.

Use it every time, unless you have a specific reason not to.

✏️ Test your knowledge

Two things, both of them the stacking sentence written from scratch:

  1. 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.

  2. 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.

Long and wide

Look at what one row of this table actually says:

Code 
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. That shape has a name: the table is long. It is what almost every instrument, database and API hands you, because it is the shape you can keep appending to forever without changing the columns.

It is also the shape in which nothing is directly comparable. To compare the mean PM2.5 across the three stations last night, you had to filter to one parameter and then group. To compare ozone and PM2.5 at Goleta this morning, you had to split the table in two and join it back together.

The other shape is wide: one row per thing, one column per measurement, and the labels promoted out of the cells and into the column headers. In a wide table, a comparison is a glance.

The pivot sentence

pivot_table() goes from long to wide. It takes three arguments, and each one is the name of a column in your long table:

Code 
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 cells
  • index= 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.

Three stations, three parameters, nine cells, and two of them empty. The whole of last night’s Part 1 and Part 2 is in that one small table, including the finding it took you four questions to reach: CNSI measures PM2.5 and nothing else.

What happens when a cell has more than one number

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=:

Code 
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

That is the same shape, filled with the counts instead, and it is the table you should look at first every time. Two empty cells in the means table and two empty cells here: CNSI has no ozone and no PM10 at all, so the emptiness is real rather than an averaging accident.

Note

🐍 pivot_table with aggfunc= is the split-apply-combine sentence 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.

Reading a wide table

The result of pivot_table is an ordinary DataFrame whose index is the index= column, so everything you know still works:

Code 
means['pm25']
site
CNSI             6.083473
Goleta           6.480926
Santa Barbara    6.172324
Name: pm25, dtype: float64
Code 
means.loc['Goleta', 'pm25']
np.float64(6.4809264305177114)

The one thing that surprises people is that site is now the index, not a column, so means['site'] raises a KeyError. When you want it back as a regular column, use .reset_index(), which you met on Friday:

Code 
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 this table can be merged, filtered and sorted like any other.

✏️ Test your knowledge

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 say which of the two layouts you would put in a report for the Air Pollution Control District, and why.

From a Series back to a table

One more small move, and it is the one that connects this morning’s two sessions.

.value_counts() has been your counting tool since Day 2, and it hands back a Series:

Code 
aq['site'].value_counts()
site
Santa Barbara    2266
Goleta           1962
CNSI              714
Name: count, dtype: int64

That is fine to read and impossible to merge. pd.merge() needs two DataFrames with columns to join on, and a Series has an index instead. So you promote it:

Code 
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:

Code 
counts = counts.rename(columns={'count': 'n_readings'})

counts
site n_readings
0 Santa Barbara 2266
1 Goleta 1962
2 CNSI 714

Learn those three lines as one move, because you will use it 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:

Code 
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, everything you know about the three monitors. Last night that was an evening of .groupby() calls.

✏️ Test your knowledge

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.

The shape that answers last night’s best question

Last night you found the daily ozone cycle by grouping one station’s readings by hour. You could not easily do it for all three stations at once, because a grouped result is one column of numbers and you wanted three.

Here it is, in one sentence, now that you can pivot. The hour column is last night’s supplied line, which gets its proper explanation after lunch:

Code 
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, three columns, and the answer to a question you could not previously ask: do the three stations rise and fall together?

Read down the columns. Goleta swings from about 3.5 in the small hours to about 9 in the evening. Santa Barbara does something similar with a smaller range. CNSI sits between 5.6 and 6.6 all day and barely moves at all. Three monitors, fifteen kilometres apart, and one of them is not recording the same phenomenon as the other two.

That is a wide table earning its keep. Nothing was calculated here that .groupby() could not have calculated. What changed is that the comparison is now something you can see.

Key points

  • pd.concat([a, b, c]) stacks tables with the same columns, top to bottom. It matches nothing and drops nothing.
  • Label the pieces before you stack them. Once the rows are combined, β€œwhich file did this come from” is not recoverable.
  • Use ignore_index=True so the stacked table gets one clean set of row numbers.
  • A long table has one measurement per row and its labels in columns. It is easy to append to and hard to compare across.
  • A wide table has one row per thing and one column per measurement. It is easy to compare and awkward to extend.
  • The pivot sentence is 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.
  • The result is indexed by the 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.

Resources