How to Create a Pivot Table in Python Using Pandas?

Pandas is a popular data manipulation library in Python that provides a powerful and flexible way to work with data. One of the best features of Pandas is the capability to create/make pivot tables. A pivot table is a valuable tool for analyzing/diagnosing large datasets and summarizing them.

In this blog post, we’ll explore creating a pivot table in Python utilizing Pandas. 

Let’s get started with the following content:

  • Using pandas.pivot_table() Function
    • Create a Simple Pivot Table in Python
    • Creating a Pivot Table For Case “Total Spent by Per Person”
    • Creating a Pivot Table With Multiple Indexes

Using pandas.pivot_table() Function

The “pandas.pivot_table()” function is utilized to create/make a spreadsheet-style pivot table as a Pandas DataFrame. A pivot table summarizes a data table by aggregating the values based on one or more columns. The “pandas.pivot_table()” syntax is shown below:

pandas.pivot_table(data, values=None, index=None, columns=None, aggfunc='max/mean', fill_value=None, margins=False/True, dropna=True/False, margins_name='All')

The following parameter used in the above syntax:

  • The “data” parameter indicates the DataFrame containing the data to be pivoted.
  • The “values” parameter states the column(s) of the DataFrame to aggregate.
  • The “index” parameter indicates the column(s) to use as row labels in the resulting pivot table
  • The “columns” parameter indicates the column(s) to use as column labels in the resulting pivot table
  • The “aggfunc” parameter states the function to use for aggregation. The default is ‘mean,’ but other functions such as ‘sum’ and ‘count’ can also be used.
  • The other parameters are also used for specific purposes.

Example 1: Create a Simple Pivot Table in Python

The following example is used to create a pivot table in Python:

Code: 

import pandas
import numpy
data = {'Name': ['Joseph', 'Mary', 'Anna', 'Lily', 'John'],
        'Gender': ['M', 'F', 'M', 'M', 'F'],
        'Sector': ['E1', 'B2', 'C3', 'B2', 'E1'],
        'Spent': [1000, 2000, 3000, 4000, 5000]}
df = pandas.DataFrame(data)
# Pivot table using index and values
table = pandas.pivot_table(df, index=['Sector'], values=['Spent'], aggfunc=numpy.sum)
print(table)
  • The module named “pandas” and “numpy” is imported.
  • The dictionary is passed inside the “pd.DataFrame()” function to create the DataFrame.
  • The “pd.pivot_table()” function is used to create a pivot table by using the ‘Sector’ column as the index and the ‘Spent’ column as the values. 
  • The function named “numpy.sum()” is utilized inside the “pd.pivot_table()” to calculate the sum of all values in each sector.

Output:

The above pivot table shows how much money was spent in each sector.

Example 2: Creating a Pivot Table For Case “Total Spent by Per Person”

The given below example is used to create a pivot table for the specific scenario “total spent by per person”:

Code:

import pandas
import numpy
data = {'Name': ['Joseph', 'Mary', 'Anna', 'Lily', 'John'],
        'Gender': ['M', 'F', 'M', 'M', 'F'],
        'Sector': ['E1', 'B2', 'C3', 'B2', 'E1'],
        'Spent': [1000, 2000, 3000, 4000, 5000]}
df = pandas.DataFrame(data)
# Pivot table using index and values
table = pandas.pivot_table(df, index=['Name'], values=['Spent'], aggfunc=numpy.sum)
print(table)
  • The “pandas.pivot_table()” function takes the “index” parameter and “values” parameter along with the “aggfunc” to create a pivot table. 
  • The “numpy.sum” is used to calculate the spent value for each person.

Output:

The total spent by the specific person has been calculated and displayed using the pivot table.

Example 3: Creating a Pivot Table With Multiple Indexes

The below example is used to create a pivot table with multiple indexes:

Code: 

import pandas
import numpy
data = {'Name': ['Joseph', 'Mary', 'Anna', 'Lily', 'John'],
        'Gender': ['M', 'F', 'M', 'M', 'F'],
        'Sector': ['E1', 'B2', 'C3', 'B2', 'E1'],
        'Spent': [1000, 2000, 3000, 4000, 5000]}
df = pandas.DataFrame(data)
table = pandas.pivot_table(df, values='Spent', index=['Name', 'Sector'], aggfunc=numpy.sum)
print(table)

The “pandas.pivot_table()” function takes the multiple index value in the “index” parameter along with the “values” and “aggfunc” parameters to create a pivot table.

Output:

The total value spent based on the “person” and “sector” has been displayed.

Conclusion

The “pandas.pivot_table()” function is used to create/make a pivot table in Python. Pivot tables are also created based on index values and column values. The simple pivot table in Python is created by taking the dataframe, index, columns, and aggfunc as an argument. The pivot table can be created for each different individual index and multiple indexes. This article presented the basics of creating a pivot table in Pandas using numerous examples.