5 Best Ways to Create a Pivot Table as a DataFrame in Python Pandas

by

💡 Problem Formulation: When working with data in Python, analysts often need to restructure or summarize large datasets to make them more understandable and accessible. Doing so can involve creating pivot tables, which rearrange and aggregate data across multiple dimensions. This article shows different methods to create a pivot table as a DataFrame using Python’s Pandas library, turning a sample dataset with sales information by date and product into a summarized table showing total sales by product.

Method 1: Using the pivot_table() Function

The pivot_table() function in Pandas is a highly versatile tool that allows you to create pivot tables, specifying index/column values and an aggregation function. It can be customized to contain all the necessary groupings and aggregations needed for a particular analysis.

Here’s an example:

import pandas as pd

# Sample dataset
data = {'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02'],
        'Product': ['A', 'B', 'A', 'B'],
        'Sales': [100, 200, 150, 250]}

df = pd.DataFrame(data)

# Creating pivot table
pivot_df = df.pivot_table(values='Sales', index='Product', aggfunc='sum')

print(pivot_df)

Output:

          Sales
Product       
A           250
B           450

This example code snippet imports the pandas library and creates a DataFrame from a simple dataset. It then creates a pivot table that summarizes the total sales by product using the pivot_table() function. The result is a new DataFrame where each row corresponds to a unique product, and the aggregated total sales are displayed in the ‘Sales’ column.

Method 2: Using the groupby() Function with unstack()

Combining the groupby() method with unstack() is a two-step process that first groups the data according to specified keys and then reshapes the result into a pivot table format.

Here’s an example:

import pandas as pd

# Sample dataset
data = {'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02'],
        'Product': ['A', 'B', 'A', 'B'],
        'Sales': [100, 200, 150, 250]}

df = pd.DataFrame(data)

# Using groupby and unstack to create pivot table
grouped = df.groupby(['Product', 'Date'])['Sales'].sum()
pivot_df = grouped.unstack()

print(pivot_df)

Output:

Date      2023-01-01  2023-01-02
Product                   
A             100         150
B             200         250

In this code snippet, we use the groupby() function to group data by ‘Product’ and ‘Date’, and then aggregate the ‘Sales’ using the sum() function. Next, the unstack() method is called on the grouped object to pivot the ‘Date’ levels so that the dates become column headers, and we get a pivot table showing the sales for each product on each date.

Method 3: Cross-tabulation with pd.crosstab()

The pd.crosstab() function is a specialized tool that computes a simple cross-tabulation of two (or more) factors. It is useful when we want to compute a frequency table.

Here’s an example:

import pandas as pd

# Sample dataset
data = {'Product': ['A', 'B', 'A', 'B'],
        'Sales': [100, 200, 150, 250]}

df = pd.DataFrame(data)

# Cross-tabulation
pivot_df = pd.crosstab(index=df['Product'], columns='Total Sales', values=df['Sales'], aggfunc='sum')

print(pivot_df)

Output:

Total Sales    A    B
Product              
A             250  0
B               0  450

This example uses the pd.crosstab() function to create a pivot table that shows the total sales by product. By setting the ‘Product’ as the index and fixing the column label as ‘Total Sales’, we use the ‘Sales’ data and the sum() function as the aggregation method to summarize the data.

Method 4: Using pivot() Method

The pivot() method is straightforward and is typically used for purely reshaping data, without any aggregation.

Here’s an example:

import pandas as pd

# Sample dataset
data = {'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02'],
        'Product': ['A', 'B', 'A', 'B'],
        'Sales': [100, 200, 150, 250]}

df = pd.DataFrame(data)

# Creating pivot table
pivot_df = df.pivot(index='Date', columns='Product', values='Sales')

print(pivot_df)

Output:

Product         A    B
Date                  
2023-01-01  100  200
2023-01-02  150  250

The pivot() method takes three arguments: index, columns, and values, allowing you to create a pivot table that shows sales by product for each date. This method is useful when you don’t need any aggregation and simply want to reorient the dataset.

Bonus One-Liner Method 5: Using set_index() with unstack()

Sometimes you can create a pivot table using a one-liner by setting an index and then unstacking the DataFrame.

Here’s an example:

import pandas as pd

# Sample dataset
data = {'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02'],
        'Product': ['A', 'B', 'A', 'B'],
        'Sales': [100, 200, 150, 250]}

df = pd.DataFrame(data)

# One-liner pivot table
pivot_df = df.set_index(['Product', 'Date'])['Sales'].unstack()

print(pivot_df)

Output:

Date      2023-01-01  2023-01-02
Product                   
A             100         150
B             200         250

This concise method chains the set_index() method to specify multi-level indexing by ‘Product’ and ‘Date’, and then immediately unstacks the ‘Date’ level which becomes the columns of our pivot table, similar to what we achieved in Method 2.

Summary/Discussion

  • Method 1: pivot_table(). Highly customizable for different aggregations. Can be slightly more complex for beginners.
  • Method 2: groupby() with unstack(). Good for multi-step data processing. Unstacking can become complicated with multiple levels.
  • Method 3: pd.crosstab(). Best for frequency tables. Offers less flexibility compared to pivot_table().
  • Method 4: pivot(). Straightforward reshaping, without aggregation. Not suitable for when aggregation is needed.
  • Method 5: One-liner with set_index() and unstack(). Quick and compact. May not be as clear for understanding complex data manipulations.