Convert Pivot Table to Regular Data Frame in Pandas

John D K -

In this post, we will see how to convert a Pandas pivot table to a regular DataFrame.

To convert pivot table to DataFrame we can use:

(1) the reset_index() method

df_p.set_axis(df_p.columns.tolist(), axis=1).reset_index()

(2) to_records() + pd.DataFrame()

pd.DataFrame(df_p.to_records())

Let's cover both ways in detail in the next sections.

Setup

First, let's create the DataFrame with 4 columns:

import pandas as pd
import numpy as np

data = {'date': np.random.choice([202303, 202304], size=100),
    	'code': np.random.choice([*'ABC'], size=100),
    	'type': np.random.choice([*'RB'], size=100),
    	'val': np.arange(100)}
df = pd.DataFrame(data)
df

First 5 rows of this DataFrame are:

date code type val
0 202304 B B 0
1 202304 A B 1
2 202304 B B 2
3 202303 C B 3
4 202304 A B 4

We can create pivot table from above data by:

df_p = df.pivot_table(index=['date','code'], columns='type', values='val', aggfunc="count")
df_p

result:

type B R
date code
202303 A 9 5
B 9 10
C 11 12
202304 A 11 6
B 10 4
C 6 7

Let's see how to convert the pivot table back to normal DataFrame. Essentially this means to remove the MultiIndex or flatten the DataFrame.

reset_index()

To convert pivot table to a normal DataFrame in Pandas, we can combine:

  • reset_index() method
  • set_axis()

We can flatten the pivot table by removing the MultiIndex:

df_p.set_axis(df_p.columns.tolist(), axis=1).reset_index()

The result is:

date code B R
0 202303 A 9 5
1 202303 B 9 10
2 202303 C 11 12
3 202304 A 11 6
4 202304 B 10 4
5 202304 C 6 7

to_records()

Another way to convert pivot tables in Pandas is by:

  • extracting data with to_records()
  • create new DataFrame
pd.DataFrame(df_p.to_records())

We get the same result:

date code B R
0 202303 A 9 5
1 202303 B 9 10
2 202303 C 11 12
3 202304 A 11 6
4 202304 B 10 4
5 202304 C 6 7

Summary

We saw how to convert pivot tables to normal DataFrame in Pandas. This is useful when we need to work with pivot tables as regular DataFrame with MultiIndex.

If you like to unpivot tables you can check the resources below.

Resources