Excel Pivot Tables Actually Work — Until They Don't
I've watched analysts fight with 500MB Excel files that took 10 minutes to refresh a pivot table. The formulas break when you add columns. Dragging cells around creates mysterious reference errors. And then someone asks "can you automate this?" — which is when you realize Excel pivot tables are a UI for data aggregation, not code.
Pandas groupby() is the programmatic equivalent, but the mental model is completely different. Excel users think in drag-and-drop; pandas users think in method chains. This gap causes real friction when migrating reports from Excel to Python. You can't just translate row/column/value fields directly — you need to rethink the entire aggregation pipeline.
This post walks through that migration with a realistic dataset: sales transactions with missing values, duplicate entries, and hierarchical categories. I'll show the Excel pivot workflow, then rebuild it in pandas with the intermediate DataFrame shapes visible at each step.
Continue reading the full article on TildAlice
Top comments (0)