Pivot Tables

Pivot tables are very much similar to what we experienced in spreadsheets. The difference between pivot tables and GroupBy function: “Pivot table is essentially a multi-dimensional version of GroupBy aggregation." — that is, you split-apply-combine, but both the split and the combine happen across not a one-dimensional index, but across a two-dimensional grid.
import numpy as np
import pandas as pd
import seaborn as sns
🛳 Titanic dataset for demonstration
# importing dataset for demonstration
titanic = pd.read_csv('data/titanic.csv')
titanic.head()
Text
survived
pclass
sex
age
fare
embarked
who
embark_town
alive
alone
0
0
3
male
22.0
7.2500
S
man
Southampton
no
False
1
1
1
female
38.0
71.2833
C
woman
Cherbourg
yes
False
2
1
3
female
26.0
7.9250
S
woman
Southampton
yes
True
3
1
1
female
35.0
53.1000
S
woman
Southampton
yes
False
4
0
3
male
35.0
8.0500
S
man
Southampton
no
True

1. WHAT IF WE USE GROUPBY

1.1. Finding survival rate by Gender

Essentially:
  • group(split) by sex,
  • select survived, and,
  • apply mean
titanic.groupby('sex')['survived'].mean()
sex
female 0.742038
male 0.188908
Name: survived, dtype: float64

1.2. Finding survival rate by Gender and Class

Essentially;
  • group(split) by sex & pclass,
  • select survived column, and,
  • apply mean aggregate
titanic.groupby(['sex','pclass'])['survived'].mean()
sex pclass
female 1 0.968085
2 0.921053
3 0.500000
male 1 0.368852
2 0.157407
3 0.135447
Name: survived, dtype: float64
# unstack the result for better presentation
titanic.groupby(['sex','pclass'])['survived'].mean().unstack()
pclass 1 2 3
sex
female 0.968085 0.921053 0.500000
male 0.368852 0.157407 0.135447
**Conclusion: ** Though we can apply two-dimensional Groupby but the code will start to look long-to-read and understand. Pandas have better tool, pivot_table, to deal with this.

2. USING PIVOT TABLE

The above two-dimensional GroupBy result can be easily derived from following pivot_table code. We will use .pivot_table() constructor, whose default aggfunc is np.mean
titanic.pivot_table('survived', index='sex', columns='pclass')
pclass 1 2 3
sex
female 0.968085 0.921053 0.500000
male 0.368852 0.157407 0.135447
We can also get same result without mentioning the index and column kwargs
titanic.pivot_table('survived', 'sex', 'pclass')
pclass 1 2 3
sex
female 0.968085 0.921053 0.500000
male 0.368852 0.157407 0.135447

2.1. Multilevel Pivot Table

Let suppose, we want to group by age, sex and get the survived mean value by each pclass. But instead of a using each age value as separate group, we will make age_groups. To do this, we will first use pd.cut function to make the segment for age column. To make age segments, first let see min and max age in our dataset:
print(f"Min Age: {titanic['age'].min()}")
print(f"Max Age: {titanic['age'].max()}")
Min Age: 0.42
Max Age: 80.0
Lets make two age group: 0-18 and 18-80
age_group = pd.cut(titanic['age'], [0,18,80])
age_group.head()
0 (18, 80]
1 (18, 80]
2 (18, 80]
3 (18, 80]
4 (18, 80]
Name: age, dtype: category
Categories (2, interval[int64]): [(0, 18] < (18, 80]]
Now, we will apply pivot_table on sex and age (through newly created age_group) Other variables will stay the same — finding survived mean value for each pclass
titanic.pivot_table('survived', index=['sex',age_group], columns='pclass')
pclass 1 2 3
sex age
female (0, 18] 0.909091 1.000000 0.511628
(18, 80] 0.972973 0.900000 0.423729
male (0, 18] 0.800000 0.600000 0.215686
(18, 80] 0.375000 0.071429 0.133663

2.2. Additional Pivot Table Options

a. Parameters of pivot_table

Paramter
Default
values=
None
index=
None
aggfunc=
‘mean’
margins=
False
dropna=
True
margins_name=
‘all’

b. aggfunc

Let suppose, we want to know the sum of survived and mean of fare columns, in each pclass
titanic.pivot_table(index='sex',columns='pclass', aggfunc={'survived': sum, 'fare': 'mean'})
# omitted the values keyword;
# when you’re specifying a mapping for aggfunc, this is determined automatically.
fare survived
pclass 1 2 3 1 2 3
sex
female 106.125798 21.970121 16.118810 91 70 72
male 67.226127 19.741782 12.661633 45 17 47

c. margins =True

This simple property margins=True computes sum along each column and row
titanic.pivot_table('survived', index='sex', columns='pclass', margins=True)
pclass 1 2 3 All
sex
female 0.968085 0.921053 0.500000 0.742038
male 0.368852 0.157407 0.135447 0.188908
All 0.629630 0.472826 0.242363 0.383838
Overall, approx. 38% people on board survived

3. CONCEPTS IN PRACTICE: BIRTHRATE DATA

  • First, load the dataset using Pandas read_csv function
  • Then we view the head of the dataset, .head() to get initial sense of dataset
  • To find total rows and columns in the dataset, we will use .shape method
births = pd.read_csv('data/births.csv')
print(births.head())
print(births.shape)
year month day gender births
0 1969 1 1.0 F 4046
1 1969 1 1.0 M 4440
2 1969 1 2.0 F 4454
3 1969 1 2.0 M 4548
4 1969 1 3.0 F 4548
(15547, 5)
1️⃣ Finding sum of births in each month, across each gender
births.pivot_table('births', index='month', columns='gender', aggfunc='sum', margins=True)
gender F M All
month
1 6035447 6328750 12364197
2 5634064 5907114 11541178
3 6181613 6497231 12678844
4 5889345 6196546 12085891
5 6145186 6479786 12624972
6 6093026 6428044 12521070
7 6512299 6855257 13367556
8 6600723 6927284 13528007
9 6473029 6779802 13252831
10 6330549 6624401 12954950
11 5956388 6241579 12197967
12 6184154 6472761 12656915
All 74035823 77738555 151774378
Plotting the results
# using matplotlib to draw figure of
# sum of births in each month, across each gender
# magic function (%matplotlib) to make the plot appear and store in notebook
%matplotlib inline
import matplotlib.pyplot as plt
sns.set() # set seaborn styles
births.pivot_table('births', index='month', columns='gender', aggfunc='sum').plot()
plt.ylabel('total births in each month');
2️⃣ Finding sum of births in each decade, across each gender
# adding a decade column
births['decade'] = 10 * (births['year'] // 10 ) # //10 will remove the last digit in year
# creating pivot table for total births, in each decade, along each gender type
print(births.pivot_table('births', index='decade', columns='gender', aggfunc='sum', margins=True))
gender F M All
decade
1960 1753634 1846572 3600206
1970 16263075 17121550 33384625
1980 18310351 19243452 37553803
1990 19479454 20420553 39900007
2000 18229309 19106428 37335737
All 74035823 77738555 151774378
Let’s put this table into figure
# using matplotlib to draw figure of
# sum of births in each decade, across each gender
# magic function (%matplotlib) to make the plot appear and store in notebook
%matplotlib inline
sns.set() # set seaborn styles
births.pivot_table('births', index='year', columns='gender', aggfunc='sum').plot()
plt.ylabel('total births per year');