The world’s leading publication for data science, AI, and ML professionals.

Unpivoting a Pandas DataFrame

How to massage a Pandas DataFrame into the shape you need

Photo by Todd Quackenbush on Unsplash
Photo by Todd Quackenbush on Unsplash

Introduction

Last article we shared an embarrassing moment which encouraged us to learn and use Pandas to pivot a DataFrame. Today we are going to look at Pandas built-it .melt() function to reverse our pivoted data. The .melt() function comes in handy when you need to reshape or unpivot a DataFrame.

Getting Started

Before ripping in, if you’re yet to read Pivoting a Pandas DataFrame or haven’t been exposed to Python Pandas previously, we recommend first beginning with Pandas Series & DataFrame Explained or Python Pandas Iterating a DataFrame. Both of these articles will provide you with the installation instructions and background knowledge for today’s article.

Where We Left Off

To get you up to speed we have included a Python snippet below, taken from Pivoting a Pandas DataFrame. This snippet, once executed, is going to create a pivoted DataFrame.

The above screenshot is the result of executing the above Python snippet.
The above screenshot is the result of executing the above Python snippet.

Now that our pivoted DataFrame has been created we will work through the syntax of Pandas .melt() function before applying it to our DataFrame.

Pandas .melt() Syntax

Parameters

  • id_vars: Here you can enter single or multiple columns that Pandas will use as the identifiers for each record.
  • value_vars: You can use this parameter to set the columns that you wish to have unpivoted. If you have not set this parameter Pandas will use all remaining columns except for those specified in id_vars.
  • var_name: This is going to set a labelled column name for the variable column. You can pass in your desired label for the column which contains the group you have unpivoted.
  • value_name: Here you can set the name for the value column. The values column represents the value corresponding to the original pivoted column category.
  • col_level: You can use this to set the level at which the melting occurs if columns are multi indexed.
  • ignore_index: Accepts a boolean, True will retain the original index and False will drop it.

Our Solution

The above screenshot shows the DataFrame after Pandas melt was called.
The above screenshot shows the DataFrame after Pandas melt was called.

To unpivot our original DataFrame, we need to pass staff_no and name to id_vars. Doing this will tell Pandas that we will use staff number and employee name as the identifiers for each grouping. We have then used both var_name and value_name to set the DataFrames labelled columns for our variable and value columns. By not referencing the year columns within our original DataFrame as id_vars, Pandas knows that these are the columns that we intend to unpivot.

If you are having trouble running the above melt snippet, you can find a completed script here.

Summary

Between Pivoting a Pandas DataFrame and this article, we have equipped you with two very powerful methods to reshape a DataFrame. Being able to apply both .pivot() and .melt() will allow you to approach data with a flexible mindset knowing that you can massage a DataFrame to your desired shape.

Thank you for taking the time to read our story – we hope you have found if valuable.


Related Articles