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', \ fill_values='') print(pvt_tab)
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']"