Pandas pivot table with multiple aggregations

Select a timestamp to watch that moment on YouTube.

1. 00:00

   A pandas pivot table answers a grouped cross-tab question: one dimension becomes rows, another becomes columns, and values are aggregated into each cell. Unlike DataFrame.pivot, pivot_table can handle repeated region-product pairs because it aggregates them. That distinction matters with transaction data, where multiple purchases of the same product in the same region are normal. Today we will test the claim with a tiny example, inspect a misleading first attempt, and finish with a verification case. Build a region-by-product pivot table with both sum and mean of an amount column, then read each measure reliably.

2. 00:40

   Use pd.pivot_table(frame, index='region', columns='product', values='amount', aggfunc=['sum', 'mean']) to request two measures. The result has a MultiIndex across columns: the first level identifies sum or mean and the second identifies the product. A cell can be selected with table.loc['West', ('sum', 'Pen')]. This is clearer than guessing which printed column belongs to which measure. The first run is intentionally incomplete or incorrect. ValueError: duplicate entries cannot be reshaped. Read the actual output before editing the code; a familiar-looking command can still

3. 01:20

   answer the wrong question. A pivot table is useful when a grouped result should be laid out as a matrix. Region labels run down the rows, product labels run across the columns, and each cell summarizes the amount values that belong to one pair. Before writing the call, decide what one cell means. Here it means sales for exactly one region and one product. A plain pivot requires each pair to appear at most once. Transaction records commonly repeat the same pair; West/Pen appears twice in the sample. Try pivot to see the duplicate-pair error, then use pivot_table with aggfunc='sum'.

4. 02:01

   The sum is 12 because both amounts, 8 and 4, belong in that cell. We use a second run to make the cause visible. The printed values are small enough to check by hand. If they disagree with your prediction, pause and identify the assumption that changed. The long-tail question pandas pivot table with multiple aggregations calls for more than one summary of each group. Pass a list of functions to aggfunc. This lesson asks for sum, which reports the total amount, and mean, which reports the average amount per source row. In the two-record example those values are 12 and 6. The result has two column levels.

5. 02:41

   The first identifies the aggregation and the second the product. Read a value with a tuple such as ('sum', 'Pen') rather than relying on a printed table whose spacing may change. If you later export a CSV, decide whether to flatten and rename these hierarchical columns; do that deliberately so the measure and product remain distinguishable. Now compare the corrected run with the earlier one. The important change is the rule encoded in the operation, not merely the final display. Run the code and inspect the returned values as well as their interpretation. A region-product combination with no source row is missing, not a measured

6. 03:22

   zero. In a report where zero means no sales, fill_value=0 may be appropriate for sum, but a zero mean can be misleading because no observations were averaged. This lesson leaves empty combinations missing. An existing row with amount zero is an observation and should not be confused with an absent combination. A cross-tab can include a row-column pair that never appears in source data. Pandas represents that cell as missing. Do not automatically fill it with zero just to make the table look complete. A sum of zero may be a valid report convention, but a mean of zero for no

7. 04:03

   observations suggests a calculation that never happened. The final example contains an actual East/Pen sale amount of zero and no East/Pad record. Inspect both cells: one prints 0.0 and the other passes a missing-value test. The distinction is essential when interpreting the report. The task preserves missing cells and checks a second repeated group, so it verifies both aggregation and the meaning of empty combinations. The final run checks a different condition so a special-case answer cannot pass unnoticed. Try the linked browser task and use the guide when you need a smaller step.

8. 04:43

   The source links on the lesson page give the complete API behavior. The browser exercise uses a tiny in-memory DataFrame. In local Python install pandas with python -m pip install pandas. The task builds the pivot using both aggregations and proves duplicate West/Pen rows produce a sum of 12 and mean of 6. The indexed cell values and column levels, rather than the printed spacing of a table, are the contract. For a report sent to another team, label each measure explicitly. A total and an average share the same source amount column but answer different questions.

9. 05:23

   If the input contains duplicated transactions accidentally, pivot_table will still aggregate them, so verify source identity separately before trusting the numbers. Compare the sum of reported totals to the raw amount sum when missing grouping keys are present. Investigate a discrepancy rather than filling every gap. This lesson chooses a small table so the number twelve and the average six can be audited directly from the two West Pen rows.