Pivot Tables In pandas

import modules

import pandas as pd

Create dataframe

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
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

pd.pivot_table(df, index=['regiment','company'], aggfunc='mean')
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 regiments

df.pivot_table(index=['regiment','company'], aggfunc='count')
TestScore
regiment company
Dragoons 1st 2
2nd 2
Nighthawks 1st 2
2nd 2
Scouts 1st 2
2nd 2