I'm Melting! From Wide to Long Format and Quarterly Groupby

Recently, a colleague of mine asked me how one might go about taking a dataset that is in wide format and converting it into long format so that you could then perform some groupby operations by quarter.

Here’s a quick example to illustrate one way to go about this using the Pandas melt function.

Getting Started

Let’s import the Pandas package

import pandas as pd

Load Some Data

First, we’ll create a fake dataframe that contains the name of a state and city along with some data for each month in the year 2000. For simplicity, imagine that the data are the number of Canadians spotted eating poutine.

df = pd.DataFrame([['NY', 'New York', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], 
                   ['MI', 'Ann Arbor', 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17],
                   ['OR', 'Portland', 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21],
                  ],
                  columns=['state', 'city', 
                           '2000-01', '2000-02', '2000-03', '2000-04', '2000-05', '2000-06',
                           '2000-07', '2000-08', '2000-09', '2000-10', '2000-11', '2000-12',
                           '2001-01'
                          ])
df

The Goal

The problem was to take this dataset and try to find the average number of Canadians eating poutine in each city and state for each quarter in the year 2000.

First, we need to convert the original dataframe into long format and then make sure that the resulting months are understood to be a datetime column.

long_df = pd.melt(df, id_vars=['state', 'city'], var_name='month', value_name='number of poutine eaters')
long_df['month'] = pd.to_datetime(long_df['month'])
long_df

The First Attempt

Let’s see what happens when we simply groupby the city and state:

long_df.groupby(['state', 'city']).mean()

But this only gives you the annual average by city and state. Instead, we want to be able to split the months out by quarter. Now, you can do some special tricks by mapping each month into a quarter (i.e., 01, 02, and 03 are mapped to Q1 while 10, 11, and 12 are mapped to Q4) and then using that new column to groupby. However, we can make use of the fantastic built in PeriodIndex function to do all of the work for us.

The Final Step

long_df.groupby(['state', 'city', pd.PeriodIndex(long_df.month, freq='Q')]).mean()

That’s it! Pretty Wicked, right? Let me know what you think in the comments below.