Aggregation, grouping and pivoting

The essentials

Real data often needs to be summarised by category, for example total sales per region or average marks per class. Pandas provides groupby() for this, sort_values() and sort_index() for ordering, pivot() and pivot_table() for reshaping, and reindex() for changing the order of labels.

Key points

• Aggregation: applying a summary function such as sum, mean, max, min or count. df.agg(['sum', 'mean']) applies several at once.
• groupby: df.groupby('Dept')['Sal'].sum() splits rows by Dept, sums Sal in each group and returns one value per group. The number of results equals the number of distinct Dept values.
• Sorting: df.sort_values(by='Sal', ascending=False) sorts rows by values; df.sort_index() sorts by the index labels.
• Pivoting: df.pivot(index='Year', columns='Item', values='Qty') turns the distinct values of Item into columns, giving a table with one row per Year. pivot() fails if an index–column pair appears more than once.
• pivot_table(): like pivot but combines repeated entries using aggfunc (mean by default), for example pivot_table(index='Year', columns='Item', values='Qty', aggfunc='sum').
• Reindexing: s.reindex(['c', 'a', 'z']) rearranges the Series to the new label order; a new label such as 'z' gets NaN. reset_index() turns the index back into a column.

Exam tips

• For groupby output questions, first list the distinct groups, then compute the function for each.
• Use pivot_table when the same combination of index and column can occur more than once.

Common mistakes

• Thinking sort_values() sorts in descending order by default; the default is ascending=True.
• Expecting reindex to drop NaN for new labels.