How to use the Pandas melt function to reshape wide format data

When you gain access to a new dataset, chances are, it’s probably not in the format you require for analysis or modeling. The most common problem you’ll encounter is datasets that are in “wide format” instead of “long format”.

To understand the differences and how to deal with these two types of data, let’s load up an impractical dataset that’s in wide format and convert it to long format using the Pandas melt function.

Load up some wide format data

One great, and pertinent example of this is the COVID-19 time series dataset from the Center for Systems Science and Engineering at John Hopkins University. Import Pandas, then use the read_csv() function to load the current CSV dataset on confirmed global COVID-19 cases into a DataFrame.

import pandas as pd
URL =  'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv'
df = pd.read_csv(URL)
df.sample(3)

As you can see from the DataFrame above (truncated for readability) we have one row per country, with the count of confirmed cases appended as a new column bearing the date on a daily basis. Although it’s convenient and easy to scan, this is not very convenient for modeling or analysis.

Use Pandas melt to reshape the data

Instead of having each country on one row, with the count of confirmed cases in appended and awkwardly names columns, we want a dataset in long format. By putting each observation into a separate row, the data are much easier to analyse, visualise and model. We’ll be able to see, for each given date, how many cases each country had.

To reshape our data from wide format to long format we can use melt and pass in four parameters. Firstly, we pass in the original Pandas dataframe in wide format, then we define the id_vars for the columns we want to keep (Province/State, Country/Region, Lat and Long), and finally we define the var_name as Date and the value_name as Confirmed.

This reshapes the dataframe to put each observation on one row. The dates are converted from column heading names to column values and the number of cases are added to the Confirmed column.

df_melted = pd.melt(df, 
                id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'],
                var_name='Date',
                value_name='Confirmed')

df_melted.tail(10)

Plotting the data

Now that the data are in long format, we can convert the Date field to datetime format and plot the time series. The below chart will plot the number of confirmed cases for the US and Brazil over time.

import matplotlib.pyplot as plt

df_melted['Date'] = pd.to_datetime(df_melted['Date'])
us = df_melted.loc[df_melted['Country/Region'] == 'US']
br = df_melted.loc[df_melted['Country/Region'] == 'Brazil']
plt.plot( 'Date', 'Confirmed', data=us, marker='', color='blue', linewidth=2)
plt.plot( 'Date', 'Confirmed', data=br, marker='', color='red', linewidth=2)

Matt Clarke, Tuesday, March 02, 2021