pivot_table: 교차 집계

A pivot table is a cross-tabulation that summarises data by two categorical dimensions — one forms the rows, another forms the columns — with an aggregated value in each cell. If you have ever built a pivot table in Excel, you will recognise the concept immediately. Pandas provides pd.pivot_table() (and the DataFrame method version) to create these summaries entirely in Python.

The four key parameters of pd.pivot_table() are: values (the column to aggregate), index (which column becomes the rows), columns (which column becomes the columns), and aggfunc (the aggregation function, defaulting to 'mean'). The result is a DataFrame where each cell contains the aggregated value for that row-column combination.

The aggfunc parameter accepts any string name of a NumPy/Pandas aggregation function, a callable, or a list of callables. Common choices: 'sum' for totals, 'mean' for averages, 'count' for frequencies, 'min'/'max' for extremes. When you pass a list, each aggregation gets its own level in the column MultiIndex.

When a combination of row and column labels has no data, the cell contains NaN by default. Pass fill_value to replace those missing cells with a meaningful default, such as 0 for counts or revenue. This makes the table easier to read and prevents downstream arithmetic operations from silently producing NaN results.

Pass margins=True to add row and column totals to the pivot table. Pandas appends an 'All' row at the bottom and an 'All' column on the right, containing the aggregated values across all categories. You can rename the margin label with margins_name. This mirrors the 'Grand Total' row in Excel pivot tables.

The values parameter can be a list of column names to aggregate several metrics at once. The result has a column MultiIndex where the outer level is the value column and the inner level is the category. This lets you create a comprehensive summary table in a single call instead of building and merging multiple separate pivot tables.

When you pass a list to index or columns, the pivot table has a MultiIndex on the corresponding axis. This lets you create more granular summaries — for example, breaking down revenue by region and quarter on the rows, and by product on the columns. Access sub-levels with .loc and index tuples as usual.

After creating a pivot table, the index is the grouping column and the column MultiIndex (when using multiple values) can be unwieldy. A common clean-up pattern is to call reset_index() to flatten the row index, then collapse the column MultiIndex into a single level by joining the levels with an underscore using a list comprehension.

Both pivot_table and groupby().agg() produce summary statistics. The difference is in the output shape: groupby().agg() returns a long format DataFrame with one row per group combination, while pivot_table returns a wide format table where one dimension becomes columns. For dashboards and reports, the wide format of pivot tables is often more readable; for further machine learning or statistical analysis, long format is preferred.

A pivot table is a regular DataFrame, so you can sort it, compute derived columns, and format it exactly like any other Pandas DataFrame. For example, sort by a specific product column descending to rank regions by that product's performance, or add a percentage column by dividing each cell by the row total.

Pivot tables are a core tool for building management reports and dashboards. A typical workflow: load raw transaction data, create a pivot table with months as columns and product categories as rows, add margin totals, then export to Excel. The entire workflow from raw data to boardroom-ready table takes just a handful of Pandas calls.

Test your understanding of pd.pivot_table from this lesson.

In this lesson you learned: pd.pivot_table() creates cross-tabulation summaries with one dimension as rows and another as columns; fill_value handles missing cells and margins=True adds totals; you can aggregate multiple values at once; and pivot tables produce wide-format output ideal for reports. Next up we explore the reverse operation: melt() to convert wide data back to long format.