How to turn a Pandas pivot table to a dataframe (with Example)?

In this tutorial we’ll explore a simple recipe that you can use to reshape the structure of a Pandas pivot table into a simple tabular looking DataFrame. Remember, the pandas pivot table is already a DataFrame – it is just arranged differently so there is no need to convert it, just to reshape its structure as needed using some simple Python code.

Creating the example dataset

We’ll get started by creating a random dataset that you can use to follow along this example. First off, we’ll import the Pandas library and then initialize our DataFrame.

import pandas as pd

language = ['R', 'C#', 'Python', 'R', 'Python', 'Kotlin', 'R', 'R']
office = ['BAR', 'LON', 'PAR', 'LON', 'LON', 'BAR', 'LON', 'BAR']
salary = [111.0, 120.0, 125.0, 120.0, 89.0, 126.0, 89.0, 118.0]
interviews = dict(office=office,language=language, salary = salary)

# construct the DataFrame
df = pd.DataFrame(data=interviews)

# pivot the data
pvt_tab = df.pivot_table(values='salary', index = 'office', \
                     columns= 'language',  \
                     aggfunc= 'sum',  \ 


Here’s our Pivot table:

Pivot table to tabular DataFrame structure

We’ll start by re-setting the Pandas pivot table index:


This will result in the following structure – note the index column at the left hand side.

And then use the melt DataFrame method to convert the pivot from its originally wide form into a longer form DataFrame:

pvt_tab.reset_index().melt(id_vars = ['office'])

Note that we use the id_vars parameter to specify the office structure as the identifier variable, to obtain the following result:

Important note: Remember to reset the index of the pivot table before converting it to long form with melt. Failing to do that will result in the following Jey error:

KeyError: "The following 'id_vars' are not present in the DataFrame: ['your_filed']"


How to rename the columns of the resulting DataFrame?

Once you rest the pivot table index, you can use the rename() pandas method to modify your DataFrame columns names. Optionally, use a dictionary to define your column names.

col_map = {"col_1": "language", "col_2": "office", "col_3":"salary"}
my_df = my_df.rename(columns = col_map)

How to handle missing values when converting pivot tables to DataFrame?

Use the fillna() method on the resulting DataFrame to replace missing value with zeros, text and so forth.

Can i use the melt function to unpivot in pandas?

Yes, here’s a simple example:

melted_dataframe = pd.melt(df, id_vars=['language'], var_name='office', value_name='salary')

Additional Learning

How to add up values in Pandas Pivot tables?