GroupBy, Merge, and Pivot Tables

Data rarely comes in the shape you need. You split data into groups, combine multiple tables, and reshape structures to answer your questions. This lesson covers the essential reshaping and combination operations in pandas.

Split-Apply-Combine Flow

Split-Apply-Combine

GroupBy implements the split-apply-combine pattern:

1. Split: Divide data into groups based on one or more keys.
2. Apply: Apply a function to each group independently.
3. Combine: Merge the results back together.

Aggregation Methods

Named Aggregation

Transform

transform returns a result the same size as the input – useful for adding group-level calculations as new columns.

Filter

Merging and Joining

Merge combines two DataFrames based on common columns or indices.

Types of Joins

Merge on Different Column Names

Merge on Index

Cross Join

Pivot Tables

Pivot tables aggregate data and reshape it from long to wide format.

Pivot vs Pivot Table

Melt (Unpivot)

Melt does the opposite of pivot – it converts wide format to long format.

Stack and Unstack

Stack moves columns to rows (wide to long). Unstack moves rows to columns (long to wide).

Cross-Tabulation

Aggregation Formulas

The GroupBy operation can be expressed mathematically:

Common aggregation functions:

Named aggregation:

Practical Pipeline

Key Takeaways

• GroupBy splits data by keys and applies functions independently to each group.
• Use agg() for multiple aggregations and transform() to add group stats as columns.
• Merge types: inner (matching), left (all left), right (all right), outer (all).
• Pivot tables aggregate and reshape simultaneously – they are your most powerful reshaping tool.
• Melt is the inverse of pivot – use it to convert wide to long format.
• Stack/unstack operate on multi-index levels for hierarchical data.
• Always inspect your data shape before and after reshaping operations.