Title: Pivot Tables In Pandas
Slug: pandas_pivot_tables
Summary: Pivot Tables In Pandas
Date: 2016-05-01 12:00
Category: Python
Tags: Data Wrangling
Authors: Chris Albon

import modules


In [15]:
import pandas as pd

Create dataframe


In [16]:
raw_data = {'regiment': ['Nighthawks', 'Nighthawks', 'Nighthawks', 'Nighthawks', 'Dragoons', 'Dragoons', 'Dragoons', 'Dragoons', 'Scouts', 'Scouts', 'Scouts', 'Scouts'], 
        'company': ['1st', '1st', '2nd', '2nd', '1st', '1st', '2nd', '2nd','1st', '1st', '2nd', '2nd'], 
        'TestScore': [4, 24, 31, 2, 3, 4, 24, 31, 2, 3, 2, 3]}
df = pd.DataFrame(raw_data, columns = ['regiment', 'company', 'TestScore'])
df


Out[16]:
regiment company TestScore
0 Nighthawks 1st 4
1 Nighthawks 1st 24
2 Nighthawks 2nd 31
3 Nighthawks 2nd 2
4 Dragoons 1st 3
5 Dragoons 1st 4
6 Dragoons 2nd 24
7 Dragoons 2nd 31
8 Scouts 1st 2
9 Scouts 1st 3
10 Scouts 2nd 2
11 Scouts 2nd 3

Create a pivot table of group means, by company and regiment


In [17]:
pd.pivot_table(df, index=['regiment','company'], aggfunc='mean')


Out[17]:
TestScore
regiment company
Dragoons 1st 3.5
2nd 27.5
Nighthawks 1st 14.0
2nd 16.5
Scouts 1st 2.5
2nd 2.5

Create a pivot table of group score counts, by company and regimensts


In [18]:
df.pivot_table(index=['regiment','company'], aggfunc='count')


Out[18]:
TestScore
regiment company
Dragoons 1st 2
2nd 2
Nighthawks 1st 2
2nd 2
Scouts 1st 2
2nd 2