Pandas DataFrame pivot_table() Method

In this tutorial, we will discuss and learn the Python pandas DataFrame.pivot_table() method. This method can be used to aggregate and summarize the data of the DataFrame. When this method is applied to the DataFrame, it returns a spreadsheet-style pivot table as a DataFrame.

The below is the syntax of the DataFrame.pivot_table() method.

Syntax

DataFrame.pivot_table(values=None, index=None, columns=None, aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All', observed=False)

Parameters

values: It represents the column to aggregate, which is optional.

index: It represents the column, Grouper, array, or list of the previous.

columns: It represents the column, Grouper, array, or list of the previous.

aggfunc: It represents the function, list of functions, dict, and the default is numpy.mean

Example: Aggregate the DataFrame using the DataFrame.pivot_table() Method

By default, the DataFrame.pivot_table() method aggregate the data of the DataFrame using the function np.mean. Here, in this example, we reshaped the DataFrame by the 'Date' as index axis, 'state' as column axis and aggregate the data according to this using the DataFrame.pivot_table() method. See the below example.

#importing pandas as pd
import pandas as pd
#creating the DataFrame
df=pd.DataFrame({'Date':['1/1/2021','1/1/2021','2/1/2021','2/1/2021','1/1/2021','1/1/2021','2/1/2021','2/1/2021'],
                 'state':['karnataka','karnataka','karnataka','karnataka','Tamilnadu','Tamilnadu','Tamilnadu','Tamilnadu'],
                 'Tempreture':[25,29,28,31,26,27,22,32],
                 'Humidity':[46,50,52,59,42,45,46,43]})
df.pivot_table(index='Date',columns='state')

Example: Apply function using the DataFrame.pivot_table() Method

We can pass different functions to the DataFrame.pivot_table() method using the aggfunc parameter. See the below example.

The DataFrame.pivot_table() method returns the DataFrame according to the specified function.

#importing pandas as pd
import pandas as pd
import numpy as np
#creating the DataFrame
df=pd.DataFrame({'Date':['1/1/2021','1/1/2021','2/1/2021','2/1/2021','1/1/2021','1/1/2021','2/1/2021','2/1/2021'],
                 'state':['karnataka','karnataka','karnataka','karnataka','Tamilnadu','Tamilnadu','Tamilnadu','Tamilnadu'],
                 'Tempreture':[25,29,28,31,26,27,22,32],
                 'Humidity':[46,50,52,59,42,45,46,43]})
df.pivot_table(index='Date',columns='state',aggfunc='max')

Example: Pass list of Functions to the pivot_table() Method

We can pass the list of functions to the DataFrame.pivot_table() method using the aggfunc parameter. The DataFrame.pivot_table() method returns the DataFrame consists of hierarchical columns where function names are at the top level. See the below example.

#importing pandas as pd
import pandas as pd
import numpy as np
#creating the DataFrame
df=pd.DataFrame({'Date':['1/1/2021','1/1/2021','2/1/2021','2/1/2021','1/1/2021','1/1/2021','2/1/2021','2/1/2021'],
                 'state':['karnataka','karnataka','karnataka','karnataka','Tamilnadu','Tamilnadu','Tamilnadu','Tamilnadu'],
                 'Tempreture':[25,29,28,31,26,27,22,32],
                 'Humidity':[46,50,52,59,42,45,46,43]})
df.pivot_table(index='Date',columns='state',aggfunc=['sum','count'])

Example: Set margins=True to the DataFrame.pivot_table() Method

If the parameter margins=True in the DataFrame.pivot_table() method, it adds the 'All' row and column in the resulted DataFrame that consists of the aggregate of the values. See the below example.

#importing pandas as pd
import pandas as pd
#creating the DataFrame
df=pd.DataFrame({'Date':['1/1/2021','1/1/2021','2/1/2021','2/1/2021','1/1/2021','1/1/2021','2/1/2021','2/1/2021'],
                 'state':['karnataka','karnataka','karnataka','karnataka','Tamilnadu','Tamilnadu','Tamilnadu','Tamilnadu'],
                 'Tempreture':[25,29,28,31,26,27,22,32],
                 'Humidity':[46,50,52,59,42,45,46,43]})
df.pivot_table(index='Date',columns='state',margins=True)

Conclusion

In this tutorial, we learned the Python pandas DataFrame.pivot_table() method. We learned syntax, parameters, and solved examples by applying this method on the DataFrame.