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:
pvt_tab.reset_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']"
FAQ
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')