Pivot_table vs groupby pandas: Key Differences and Usage

pivot_table and groupby in pandas both aggregate data but serve different purposes: pivot_table reshapes data into a spreadsheet-like format with multi-dimensional grouping, while groupby groups data for flexible aggregation and transformation without reshaping. Use pivot_table for summary tables and groupby for custom group operations.

Quick Comparison

Here is a quick side-by-side comparison of pivot_table and groupby in pandas.

Feature	pivot_table	groupby
Purpose	Create spreadsheet-style pivot tables with multi-level indexes	Group data for aggregation, transformation, or filtering
Output Shape	Reshaped DataFrame with rows and columns based on grouping keys	Grouped object or aggregated DataFrame, usually not reshaped
Aggregation	Supports multiple aggregation functions with easy syntax	Supports flexible aggregation and custom functions
Handling Missing Data	Can fill missing values with fill_value	No built-in fill; missing data handled manually
Use Case	Summarizing data in cross-tab format	Performing complex group-wise calculations or transformations
Syntax Complexity	Simpler for pivot tables	More flexible but requires more code for reshaping

Key Differences

pivot_table is designed to create pivot tables similar to Excel, where you specify rows, columns, and values to aggregate. It automatically reshapes the data into a two-dimensional table with hierarchical indexes if needed. This makes it ideal for quick summaries and cross-tabulations.

In contrast, groupby is a more general tool that groups data by one or more keys and allows you to apply aggregation, transformation, or filtering functions on each group. It returns a grouped object that you can further manipulate. Unlike pivot_table, it does not reshape data by default.

Another difference is that pivot_table can handle missing data by filling values with fill_value, while groupby requires manual handling of missing data. Also, pivot_table syntax is simpler for creating summary tables, whereas groupby offers more flexibility for custom operations.

Code Comparison

Here is how to use pivot_table to summarize average sales by region and product category.

python

import pandas as pd

data = {
    'Region': ['East', 'East', 'West', 'West', 'East', 'West'],
    'Category': ['A', 'B', 'A', 'B', 'A', 'B'],
    'Sales': [100, 150, 200, 250, 300, 350]
}
df = pd.DataFrame(data)

pivot = df.pivot_table(index='Region', columns='Category', values='Sales', aggfunc='mean')
print(pivot)

Output

Category      A      B
Region                
East       200.0  150.0
West       200.0  300.0

groupby Equivalent

Here is how to achieve the same summary using groupby with unstack to reshape the output.

python

grouped = df.groupby(['Region', 'Category'])['Sales'].mean().unstack()
print(grouped)

Output

Category      A      B
Region                
East       200.0  150.0
West       200.0  300.0

When to Use Which

Choose pivot_table when you want a quick, easy-to-read summary table that reshapes data into rows and columns, especially for reporting or exploratory analysis. It is best for creating cross-tabulations and handling missing values automatically.

Choose groupby when you need more control over group-wise operations, such as applying custom aggregation, filtering, or transformations. It is better for complex data processing pipelines where reshaping is not the main goal.

Key Takeaways

pivot_table reshapes data into summary tables with rows and columns.

groupby groups data flexibly for aggregation or transformation without reshaping.

Use pivot_table for quick cross-tab summaries and groupby for custom group operations.

pivot_table handles missing data with fill_value, groupby does not.

groupby combined with unstack can mimic pivot_table output.