Pivot Tables in Python

A pivot table reshapes a 'long' or 'tidy' DataFrame — one row per observation — into a 'wide' cross-tabulation, the same mental model Excel users already know from dragging fields into rows, columns, and values. pd.pivot_table(df, values="Sales", index="Date", columns="City") takes the unique values of Date and turns them into row labels, the unique values of City and turns them into column headers, and fills each cell with an aggregation of Sales for that specific Date/City combination.

The key detail beginners miss is that pivot_table always aggregates, even when there's only one matching row per intersection. By default it computes the mean, so if two rows share the same Date and City, you silently get an average instead of an error — which is exactly why the aggfunc parameter matters. Passing aggfunc="sum" (or "count", "max", or a custom function) changes what happens at each intersection without changing the shape of the pivot.

Missing combinations are just as important to handle correctly. If a City had no Sales recorded for a given Date, that cell has no source rows to aggregate, so Pandas fills it with NaN rather than 0. Passing fill_value=0 tells pivot_table to replace those missing intersections with zero, which is usually what you want before summing totals or plotting the result — leaving them as NaN can silently break downstream .sum() calls or charts.

Pivot tables in Pandas work exactly like Pivot Tables in Microsoft Excel. They summarize data and reshape it into a multi-dimensional cross-tabulation.

What business software tool is the Pandas pivot_table explicitly designed to emulate?

• →Adobe Photoshop.
• →Microsoft Excel Pivot Tables.
• →Linux Bash Scripts.

To create a pivot table, use pd.pivot_table(). You must define the index (the rows), the columns (the new headers), and the values (the data to calculate).

In pd.pivot_table(df, values="Sales", index="Date", columns="City"), what will the new column headers of the resulting DataFrame be?

• →The unique dates (e.g., 'Mon', 'Tue').
• →The unique cities (e.g., 'NY', 'LA').
• →The Sales numbers themselves.

By default, if there are multiple entries for the exact same row/column intersection, pivot_table calculates the mean (average). You can change this using aggfunc.

What parameter do you change in pivot_table if you want it to calculate the total sum instead of the average?

• →math_type
• →aggfunc
• →calculation

Now, prepare yourself. We are about to enter the ADA Defense Protocol. Ensure you know how to handle missing intersections.

ADA DEFENSE: If you pivot a table, but there were no sales in LA on Tuesday, the intersection will be filled with NaN. How do you force Pandas to replace those NaNs with zeros automatically?

• →Use the fill_value=0 argument inside the pivot_table function.
• →Manually edit the CSV file before loading.
• →Pivot tables automatically drop rows with NaN values.

Threat neutralized. Pivot logic secured. You can now dynamically cross-tabulate any dataset.

Threat neutralized. Concept validated. Proceed to the next section.

Build a Real Pivot Table. Finish pivot_sales(): reshape rows into a City x Date cross-tab, summing Sales at each intersection.