4 Reshaping and Tidying

The previous chapters taught you to load, summarize, and combine data. This chapter addresses a subtler but equally important skill: reshaping data between wide and long formats. The same underlying data can be arranged in multiple ways, and the shape you need depends on what you want to do with it. Groupby-aggregation and pivot tables often produce wide tables that are easy to read, but visualization libraries like seaborn expect long tables with one observation per row. Being able to move fluently between these formats is essential.

We will use monthly passenger traffic data from Denver International Airport (DIA) to illustrate these operations. The COVID-19 pandemic caused dramatic disruptions to air travel, making this dataset both analytically interesting and motivating.

4.1 Setup

import numpy as np
import pandas as pd

idx = pd.IndexSlice

pd.options.display.max_columns = 100

4.2 The Data

4.2.1 Formulate your question

How did the COVID-19 shutdowns impact passenger and flight traffic at Denver International Airport?

4.2.2 Read in data

passenger_df = pd.read_csv('passengers.csv')
passenger_df.head()

passenger_df.tail()

4.2.3 Check the packaging

The data should span from 1995 (when DIA opened) through late 2021. A simple but fragile way to check this is to sort the “Month” column — but string sorting on dates gives wrong results (e.g., “April” comes before “January” alphabetically). Instead, convert to proper Timestamp objects using pd.to_datetime.

passenger_df['Month'] = pd.to_datetime(passenger_df['Month'])

passenger_df['Month'].min(), passenger_df['Month'].max()

4.2.4 Clean up the data

Make the “Month” column the index and drop the “Cargo” column, which is not relevant to our passenger analysis.

passenger_df.set_index('Month', inplace=True)

passenger_df.drop(columns=['Cargo'], inplace=True)

passenger_df.head()

4.2.5 Validate against an external source

These statistics are collected by the DIA authority. The U.S. Department of Transportation’s Bureau of Transportation Statistics also publishes historical passenger statistics by airport. Let’s compare.

transstats_df = pd.read_excel(
    'transtats_passengers.xlsx',
    sheet_name=0,
    header=1
)

# Filter out annual total rows and null rows
transstats_df = transstats_df[transstats_df['Month'] != "TOTAL"]
transstats_df.dropna(subset=['Month'], inplace=True)

transstats_df.head()

Combine the Year and Month columns into a proper datetime. This is a common data-cleaning task when dates are split across columns.

string_dates = transstats_df[['Year', 'Month']].apply(
    lambda x: '{Year}-{Month:02d}'.format(**x), axis=1
)

transstats_df['Date'] = pd.to_datetime(string_dates)
transstats_df.set_index('Date', inplace=True)

transstats_df.head()

Join them together for comparison (practice from Chapter 3).

dia_bts_merge_df = pd.merge(
    left=passenger_df,
    right=transstats_df,
    left_index=True,
    right_on='Date',
    how='right'
)

dia_bts_merge_df.tail()

Calculate the correlation between the monthly totals from each dataset. Not perfect, but more than high enough to trust.

dia_bts_merge_df[['Grand', 'TOTAL']].corr()

4.2.6 Make a plot

Visualize the monthly total passenger data. We will go into visualization in much more detail in Chapter 6 and beyond.

%matplotlib inline
import matplotlib.pyplot as plt

ax = passenger_df['Grand'].plot()

ax.axvline(pd.Timestamp('2001-09-01'), color='r', linestyle='--', linewidth=1, zorder=-1)
ax.axvline(pd.Timestamp('2020-03-01'), color='r', linestyle='--', linewidth=1, zorder=-1)

4.2.7 Try an easy solution

How much did passenger traffic decrease in March 2020 compared to March 2019?

total_march_2019 = passenger_df.loc['2019-03-01', 'Grand']
total_march_2020 = passenger_df.loc['2020-03-01', 'Grand']

"{0:.1%}".format((total_march_2020 - total_march_2019) / total_march_2019)

4.3 Stacking: Wide to Long

If you have a DataFrame and want to convert its columns into index levels, use pandas’s .stack() (docs, user guide).

Our passenger_df is currently in wide format: each airline is a column, each row is a month, and the values are passenger counts.

passenger_df.head()

Stacking converts this into a long format — a MultiIndexed Series with Month at the first level and airline at the second level.

passenger_stack_df = passenger_df.stack()

passenger_stack_df.head(10)

Turn this MultiIndexed Series into a flat DataFrame with .reset_index(), then rename the columns for clarity.

passenger_stack_df = passenger_stack_df.reset_index()

passenger_stack_df.head(2)

passenger_stack_df.rename(
    columns={
        'level_1': 'Airline',
        0: 'Passengers'
    },
    inplace=True
)

passenger_stack_df.head()

This long format — where each row is a single observation (one airline, one month, one passenger count) — is what Hadley Wickham calls tidy data. Each variable forms a column, each observation forms a row, and each type of observational unit forms a table.

Tidy data and visualization

Seaborn and many other visualization libraries expect tidy (long) data. If a plot function asks for x, y, and hue parameters that all reference column names, you need your data in long format.

4.4 Unstacking: Long to Wide

Now we want to reverse the process — take our long DataFrame and return it to wide format. Set the columns you want as index levels using .set_index(), then call .unstack() to pivot one level into columns.

passenger_unstack_df = passenger_stack_df.set_index(['Month', 'Airline'])

passenger_unstack_df

passenger_unstack_df = passenger_unstack_df.unstack('Airline')

passenger_unstack_df.head()

This output is a little annoying because it created a MultiIndex on the columns — see the “Passengers” above the airline names? Accessing United requires passing passenger_unstack_df[('Passengers', 'United')]. You can remove this extra level with .droplevel().

passenger_unstack_df.droplevel(0, axis='columns')

The cleaner alternative is to unstack a Series rather than a DataFrame. Access the single value column as a Series first, then unstack.

passenger_stack_s = passenger_stack_df.set_index(['Month', 'Airline']).loc[:, 'Passengers']

passenger_stack_s

passenger_stack_s.unstack(1)

This produces a clean, simple wide DataFrame without the MultiIndex on columns.

4.5 Melting: Another Path from Wide to Long

The pd.melt function offers a more explicit interface for going from wide to long. You specify which columns remain as identifiers (id_vars) and which get “melted” into value rows.

First, reset the index to get “Month” as a column.

passenger_df_reset = passenger_df.reset_index()
print(passenger_df_reset.shape)
passenger_df_reset.head()

Note the shape: 321 rows × 9 columns. After melting, 8 of those 9 columns (the airlines) should become rows. So we should get approximately 321 × 8 = 2,568 rows.

321 * 8

passengers_melt_df = pd.melt(
    frame=passenger_df_reset,
    id_vars=['Month'],
    var_name='Airline',
    value_name='Count'
)

print(passengers_melt_df.shape)
passengers_melt_df.head()

You can also pass explicit value_vars to melt only specific columns.

passengers_melt_df = pd.melt(
    frame=passenger_df_reset,
    id_vars=['Month'],
    value_vars=['American', 'Delta', 'Frontier', 'Other',
                'Southwest', 'United', 'United Express', 'Grand'],
    var_name='Airline',
    value_name='Count'
)

print(passengers_melt_df.shape)
passengers_melt_df.head()

Choose your tool

.stack() and .melt() both go from wide to long, but .melt() gives you more control over which columns to include and how to name the result. Use .melt() when you want to reshape a subset of columns; use .stack() when you want to convert all value columns.

4.6 Unmelting with Pivot

A melted DataFrame can be “unmelted” with the .pivot() method. Pass the column names you want for the index, columns, and values.

passengers_melt_df.pivot(index='Month', columns='Airline', values='Count').head()

You can also pivot the other way — airlines as rows, months as columns.

passengers_melt_df.pivot(index='Airline', columns='Month', values='Count')

4.7 Summary of Reshaping Operations

Here is a quick reference for how the reshaping operations relate to each other:

Wide → Long: .stack() or pd.melt()
Long → Wide: .unstack() or .pivot() or pd.pivot_table()
Wide → Long → Wide should return you to where you started (a good sanity check)

4.8 Exercises

Exercise 1: Filter the melted data (review)

Filter the passengers_melt_df data down to only “United.” Identify the months with the most and fewest passengers.

Solution:

united = passengers_melt_df[passengers_melt_df['Airline'] == 'United']

print("Most passengers:")
print(united.sort_values('Count', ascending=False).head(3))

print("\nFewest passengers:")
print(united.sort_values('Count', ascending=True).head(3))

Exercise 2: Total passengers by airline (review)

Do a groupby-aggregation or pivot table on passengers_melt_df to compute total passengers by airline since 1994. Which airline has carried the most passengers through DIA?

Solution:

passengers_melt_df[passengers_melt_df['Airline'] != 'Grand'].groupby('Airline').agg(
    {'Count': 'sum'}
).sort_values('Count', ascending=False)

Exercise 3: Convert back to wide data

Turn the tidy data in passengers_melt_df back into wide format using either a .pivot() or an .unstack() approach. Try making the months into columns too.

Solution:

# Using pivot
wide = passengers_melt_df.pivot(index='Month', columns='Airline', values='Count')
wide.head()

Exercise 4: Tidy the CDC data

Load the CDC deaths data from Chapter 2. Identify which columns should be id_vars (identifiers like State, Year, Week) and which should be value_vars (the disease columns). Use pd.melt or .stack() to reshape it into tidy format. How many rows does the result have? Does that match your prediction?

Solution:

deaths_df = pd.read_csv('CDC_deaths.csv', parse_dates=['Week date'])

id_cols = ['State', 'Year', 'Week', 'Week date']
value_cols = [c for c in deaths_df.columns if c not in id_cols]

deaths_tidy = pd.melt(
    deaths_df,
    id_vars=id_cols,
    value_vars=value_cols,
    var_name='Cause',
    value_name='Deaths'
)

print(f"Original shape: {deaths_df.shape}")
print(f"Tidy shape: {deaths_tidy.shape}")
print(f"Expected rows: {deaths_df.shape[0]} × {len(value_cols)} = {deaths_df.shape[0] * len(value_cols)}")

Exercise 5: Untidy and re-tidy

Take the tidy CDC data from Exercise 4 and pivot it back into wide format. Verify that the result has the same shape as the original DataFrame.

Solution:

deaths_wide = deaths_tidy.pivot_table(
    index=id_cols,
    columns='Cause',
    values='Deaths',
    aggfunc='sum'
).reset_index()

print(f"Re-widened shape: {deaths_wide.shape}")

4.9 Looking Ahead

You now have a complete toolkit for shaping data: loading, aggregating, combining, and reshaping. In Chapter 5, we step back from code to think about what makes a data visualization clear, honest, and effective — principles that will guide every chart you build in the rest of this book.

# Reshaping and Tidying {#sec-reshaping} The previous chapters taught you to load, summarize, and combine data. This chapter addresses a subtler but equally important skill: reshaping data between **wide** and **long** formats. The same underlying data can be arranged in multiple ways, and the shape you need depends on what you want to do with it. Groupby-aggregation and pivot tables often produce wide tables that are easy to read, but visualization libraries like seaborn expect long tables with one observation per row. Being able to move fluently between these formats is essential. We will use monthly passenger traffic data from Denver International Airport (DIA) to illustrate these operations. The COVID-19 pandemic caused dramatic disruptions to air travel, making this dataset both analytically interesting and motivating. ## Setup ```{python} import numpy as np import pandas as pd idx = pd.IndexSlice pd.options.display.max_columns = 100 ``` ## The Data ### Formulate your question How did the COVID-19 shutdowns impact passenger and flight traffic at Denver International Airport? ### Read in data ```{python} passenger_df = pd.read_csv('passengers.csv') passenger_df.head() ``` ```{python} passenger_df.tail() ``` ### Check the packaging The data should span from 1995 (when DIA opened) through late 2021. A simple but fragile way to check this is to sort the "Month" column — but string sorting on dates gives wrong results (e.g., "April" comes before "January" alphabetically). Instead, convert to proper `Timestamp` objects using `pd.to_datetime`. ```{python} passenger_df['Month'] = pd.to_datetime(passenger_df['Month']) passenger_df['Month'].min(), passenger_df['Month'].max() ``` ### Clean up the data Make the "Month" column the index and drop the "Cargo" column, which is not relevant to our passenger analysis. ```{python} passenger_df.set_index('Month', inplace=True) ``` ```{python} passenger_df.drop(columns=['Cargo'], inplace=True) ``` ```{python} passenger_df.head() ``` ### Validate against an external source These statistics are collected by the DIA authority. The U.S. Department of Transportation's [Bureau of Transportation Statistics](https://www.bts.gov/) also publishes [historical passenger statistics by airport](https://www.transtats.bts.gov/Data_Elements.aspx?Data=1). Let's compare. ```{python} transstats_df = pd.read_excel( 'transtats_passengers.xlsx', sheet_name=0, header=1 ) # Filter out annual total rows and null rows transstats_df = transstats_df[transstats_df['Month'] != "TOTAL"] transstats_df.dropna(subset=['Month'], inplace=True) transstats_df.head() ``` Combine the Year and Month columns into a proper datetime. This is a common data-cleaning task when dates are split across columns. ```{python} string_dates = transstats_df[['Year', 'Month']].apply( lambda x: '{Year}-{Month:02d}'.format(**x), axis=1 ) transstats_df['Date'] = pd.to_datetime(string_dates) transstats_df.set_index('Date', inplace=True) transstats_df.head() ``` Join them together for comparison (practice from @sec-combining). ```{python} dia_bts_merge_df = pd.merge( left=passenger_df, right=transstats_df, left_index=True, right_on='Date', how='right' ) dia_bts_merge_df.tail() ``` Calculate the correlation between the monthly totals from each dataset. Not perfect, but more than high enough to trust. ```{python} dia_bts_merge_df[['Grand', 'TOTAL']].corr() ``` ### Make a plot Visualize the monthly total passenger data. We will go into visualization in much more detail in @sec-histograms and beyond. ```{python} %matplotlib inline import matplotlib.pyplot as plt ax = passenger_df['Grand'].plot() ax.axvline(pd.Timestamp('2001-09-01'), color='r', linestyle='--', linewidth=1, zorder=-1) ax.axvline(pd.Timestamp('2020-03-01'), color='r', linestyle='--', linewidth=1, zorder=-1) ``` ### Try an easy solution How much did passenger traffic decrease in March 2020 compared to March 2019? ```{python} total_march_2019 = passenger_df.loc['2019-03-01', 'Grand'] total_march_2020 = passenger_df.loc['2020-03-01', 'Grand'] "{0:.1%}".format((total_march_2020 - total_march_2019) / total_march_2019) ``` ## Stacking: Wide to Long If you have a DataFrame and want to convert its columns into index levels, use pandas's `.stack()` ([docs](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.stack.html), [user guide](https://pandas.pydata.org/pandas-docs/stable/user_guide/reshaping.html#reshaping-by-stacking-and-unstacking)). Our `passenger_df` is currently in **wide** format: each airline is a column, each row is a month, and the values are passenger counts. ```{python} passenger_df.head() ``` Stacking converts this into a **long** format — a MultiIndexed Series with Month at the first level and airline at the second level. ```{python} passenger_stack_df = passenger_df.stack() passenger_stack_df.head(10) ``` Turn this MultiIndexed Series into a flat DataFrame with `.reset_index()`, then rename the columns for clarity. ```{python} passenger_stack_df = passenger_stack_df.reset_index() passenger_stack_df.head(2) ``` ```{python} passenger_stack_df.rename( columns={ 'level_1': 'Airline', 0: 'Passengers' }, inplace=True ) passenger_stack_df.head() ``` This long format — where each row is a single observation (one airline, one month, one passenger count) — is what Hadley Wickham calls **tidy data**. Each variable forms a column, each observation forms a row, and each type of observational unit forms a table. ::: {.callout-tip} ## Tidy data and visualization Seaborn and many other visualization libraries expect tidy (long) data. If a plot function asks for `x`, `y`, and `hue` parameters that all reference column names, you need your data in long format. ::: ## Unstacking: Long to Wide Now we want to reverse the process — take our long DataFrame and return it to wide format. Set the columns you want as index levels using `.set_index()`, then call `.unstack()` to pivot one level into columns. ```{python} passenger_unstack_df = passenger_stack_df.set_index(['Month', 'Airline']) passenger_unstack_df ``` ```{python} passenger_unstack_df = passenger_unstack_df.unstack('Airline') passenger_unstack_df.head() ``` This output is a little annoying because it created a MultiIndex on the columns — see the "Passengers" above the airline names? Accessing United requires passing `passenger_unstack_df[('Passengers', 'United')]`. You can remove this extra level with `.droplevel()`. ```{python} passenger_unstack_df.droplevel(0, axis='columns') ``` The cleaner alternative is to unstack a *Series* rather than a DataFrame. Access the single value column as a Series first, then unstack. ```{python} passenger_stack_s = passenger_stack_df.set_index(['Month', 'Airline']).loc[:, 'Passengers'] passenger_stack_s ``` ```{python} passenger_stack_s.unstack(1) ``` This produces a clean, simple wide DataFrame without the MultiIndex on columns. ## Melting: Another Path from Wide to Long The `pd.melt` function offers a more explicit interface for going from wide to long. You specify which columns remain as identifiers (`id_vars`) and which get "melted" into value rows. First, reset the index to get "Month" as a column. ```{python} passenger_df_reset = passenger_df.reset_index() print(passenger_df_reset.shape) passenger_df_reset.head() ``` Note the shape: 321 rows × 9 columns. After melting, 8 of those 9 columns (the airlines) should become rows. So we should get approximately 321 × 8 = 2,568 rows. ```{python} 321 * 8 ``` ```{python} passengers_melt_df = pd.melt( frame=passenger_df_reset, id_vars=['Month'], var_name='Airline', value_name='Count' ) print(passengers_melt_df.shape) passengers_melt_df.head() ``` You can also pass explicit `value_vars` to melt only specific columns. ```{python} passengers_melt_df = pd.melt( frame=passenger_df_reset, id_vars=['Month'], value_vars=['American', 'Delta', 'Frontier', 'Other', 'Southwest', 'United', 'United Express', 'Grand'], var_name='Airline', value_name='Count' ) print(passengers_melt_df.shape) passengers_melt_df.head() ``` ::: {.callout-warning} ## Choose your tool `.stack()` and `.melt()` both go from wide to long, but `.melt()` gives you more control over which columns to include and how to name the result. Use `.melt()` when you want to reshape a subset of columns; use `.stack()` when you want to convert all value columns. ::: ## Unmelting with Pivot A melted DataFrame can be "unmelted" with the `.pivot()` method. Pass the column names you want for the index, columns, and values. ```{python} passengers_melt_df.pivot(index='Month', columns='Airline', values='Count').head() ``` You can also pivot the other way — airlines as rows, months as columns. ```{python} passengers_melt_df.pivot(index='Airline', columns='Month', values='Count') ``` ## Summary of Reshaping Operations Here is a quick reference for how the reshaping operations relate to each other: - **Wide → Long:** `.stack()` or `pd.melt()` - **Long → Wide:** `.unstack()` or `.pivot()` or `pd.pivot_table()` - **Wide → Long → Wide** should return you to where you started (a good sanity check) ## Exercises ::: {.callout-note collapse="true"} ## Exercise 1: Filter the melted data (review) Filter the `passengers_melt_df` data down to only "United." Identify the months with the most and fewest passengers. **Solution:** ```{python} united = passengers_melt_df[passengers_melt_df['Airline'] == 'United'] print("Most passengers:") print(united.sort_values('Count', ascending=False).head(3)) print("\nFewest passengers:") print(united.sort_values('Count', ascending=True).head(3)) ``` ::: ::: {.callout-note collapse="true"} ## Exercise 2: Total passengers by airline (review) Do a groupby-aggregation or pivot table on `passengers_melt_df` to compute total passengers by airline since 1994. Which airline has carried the most passengers through DIA? **Solution:** ```{python} passengers_melt_df[passengers_melt_df['Airline'] != 'Grand'].groupby('Airline').agg( {'Count': 'sum'} ).sort_values('Count', ascending=False) ``` ::: ::: {.callout-note collapse="true"} ## Exercise 3: Convert back to wide data Turn the tidy data in `passengers_melt_df` back into wide format using either a `.pivot()` or an `.unstack()` approach. Try making the months into columns too. **Solution:** ```{python} # Using pivot wide = passengers_melt_df.pivot(index='Month', columns='Airline', values='Count') wide.head() ``` ::: ::: {.callout-note collapse="true"} ## Exercise 4: Tidy the CDC data Load the CDC deaths data from @sec-aggregating. Identify which columns should be `id_vars` (identifiers like State, Year, Week) and which should be `value_vars` (the disease columns). Use `pd.melt` or `.stack()` to reshape it into tidy format. How many rows does the result have? Does that match your prediction? **Solution:** ```{python} deaths_df = pd.read_csv('CDC_deaths.csv', parse_dates=['Week date']) id_cols = ['State', 'Year', 'Week', 'Week date'] value_cols = [c for c in deaths_df.columns if c not in id_cols] deaths_tidy = pd.melt( deaths_df, id_vars=id_cols, value_vars=value_cols, var_name='Cause', value_name='Deaths' ) print(f"Original shape: {deaths_df.shape}") print(f"Tidy shape: {deaths_tidy.shape}") print(f"Expected rows: {deaths_df.shape[0]} × {len(value_cols)} = {deaths_df.shape[0] * len(value_cols)}") ``` ::: ::: {.callout-note collapse="true"} ## Exercise 5: Untidy and re-tidy Take the tidy CDC data from Exercise 4 and pivot it back into wide format. Verify that the result has the same shape as the original DataFrame. **Solution:** ```{python} deaths_wide = deaths_tidy.pivot_table( index=id_cols, columns='Cause', values='Deaths', aggfunc='sum' ).reset_index() print(f"Re-widened shape: {deaths_wide.shape}") ``` ::: ## Looking Ahead You now have a complete toolkit for shaping data: loading, aggregating, combining, and reshaping. In @sec-clarity, we step back from code to think about what makes a data visualization clear, honest, and effective — principles that will guide every chart you build in the rest of this book.