Pivoting a table and calculating row-wise or column-wise percentages is a common task in data analysis — often used to understand how values in a row contribute to the row total. Here's how to make a pivot table with it with percentage in Pandas:
(1) Calculate row-wise percentage
(2) Calculate column-wise percentage
(3) Using crosstab and normalize
Data
Original data looks like:
category | type | value | |
---|---|---|---|
0 | A | X | 75 |
1 | A | Y | 25 |
2 | B | X | 20 |
3 | B | Y | 50 |
4 | B | Z | 30 |
1. Pivot Table with Row Percentages
Output:
type | X | Y | Z |
---|---|---|---|
category | |||
A | 78.947368 | 33.333333 | 0.0 |
B | 21.052632 | 66.666667 | 100.0 |
Explanation
.pivot()
groups data like a spreadsheet pivot: it aggregates values based on row and column labels..div(..., axis=0)
divides each row by its sum (row-wise operation).* 100
converts proportions to percentages.
Optional: Format as Percent Strings
2. Pivot Table with Column Percentages
We can normalize pivot table column-wise by:
result:
type | X | Y | Z |
---|---|---|---|
category | |||
A | 78.9 | 33.3 | 0.0 |
B | 21.1 | 66.7 | 100.0 |
3. Crosstab and normalize
Final way to pivot multiple columns and get the normalized values instead of counts will be by using the crosstab
method:
result:
type | category | X_ | Y_ | Z_ |
---|---|---|---|---|
0 | A | 0.75 | 0.25 | 0.0 |
1 | B | 0.20 | 0.50 | 0.3 |
Resources
- Pandas
.pivot_table()
Docs - pandas.pivot
- Pandas
.div()
Docs - pandas.crosstab
- How can I pivot a table and get the percentage of each row in Python?
This method is great for creating readable summary tables for reports or dashboards.