Python:Pandas Pivot Tables

Pivot tables in Pandas are a data summarization tool that perform operations such as aggregation, grouping, and reshaping of tabular data based on specific criteria, making it easier to derive meaningful insights.

Syntax

The general syntax for creating a pivot table in Pandas is:

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

• data: The input DataFrame.
• values: The column(s) to aggregate.
• index: Column(s) to group data by (rows).
• columns: Column(s) to group data by (columns).
• aggfunc: The aggregation function(s) (default is mean).
• fill_value: Value to replace NaNs.
• margins: Add subtotals (row/column totals).
• margins_name: Name for row/column totals.
• dropna: Whether to drop columns with NaN values.

Example

Consider the following DataFrame:

import pandas as pd

data = {
    'Region': ['North', 'South', 'East', 'West', 'North', 'South'],
    'Category': ['A', 'A', 'B', 'B', 'A', 'B'],
    'Sales': [100, 150, 200, 130, 120, 180]
}

df = pd.DataFrame(data)
print(df)

Copy to clipboard
Copy to clipboard

The output for the above code will be as follows:

Pivoting Data

To summarize total sales by Region and Category:

pivot = pd.pivot_table(df, values='Sales', index='Region', columns='Category', aggfunc='sum', fill_value=0)
print(pivot)

Copy to clipboard
Copy to clipboard

The output of the above code will be as follows:

Adding Margins for Totals

To include row and column totals in the pivot table, set margins=True:

pivot = pd.pivot_table(df, values='Sales', index='Region', columns='Category', aggfunc='sum', fill_value=0, margins=True)
print(pivot)

Copy to clipboard
Copy to clipboard

The output of the above code will be as follows:

Aggregating with Multiple Functions

Multiple aggregation functions can be applied using a list:

pivot = pd.pivot_table(df, values='Sales', index='Region', columns='Category', aggfunc=['sum', 'mean'], fill_value=0)
print(pivot)

Copy to clipboard
Copy to clipboard

The output of the above code will be as follows:

Codebyte Example

Run the following example to see how pivot tables can be used to aggregate Quantity by Category and Region:

Visit us
Visit us
Hide code
Code
Output
Hide output
Loading...
Copy to your clipboard
Copy Codebyte
Run