Crosstabs In Pandas

Want to learn more? I recommend these Python books: Python for Data Analysis, Python Data Science Handbook, and Introduction to Machine Learning with Python.

Import pandas

import pandas as pd
raw_data = {'regiment': ['Nighthawks', 'Nighthawks', 'Nighthawks', 'Nighthawks', 'Dragoons', 'Dragoons', 'Dragoons', 'Dragoons', 'Scouts', 'Scouts', 'Scouts', 'Scouts'],
        'company': ['infantry', 'infantry', 'cavalry', 'cavalry', 'infantry', 'infantry', 'cavalry', 'cavalry','infantry', 'infantry', 'cavalry', 'cavalry'],
        'experience': ['veteran', 'rookie', 'veteran', 'rookie', 'veteran', 'rookie', 'veteran', 'rookie','veteran', 'rookie', 'veteran', 'rookie'],
        'name': ['Miller', 'Jacobson', 'Ali', 'Milner', 'Cooze', 'Jacon', 'Ryaner', 'Sone', 'Sloan', 'Piger', 'Riani', 'Ali'],
        'preTestScore': [4, 24, 31, 2, 3, 4, 24, 31, 2, 3, 2, 3],
        'postTestScore': [25, 94, 57, 62, 70, 25, 94, 57, 62, 70, 62, 70]}
df = pd.DataFrame(raw_data, columns = ['regiment', 'company', 'experience', 'name', 'preTestScore', 'postTestScore'])
df
regiment company experience name preTestScore postTestScore
0 Nighthawks infantry veteran Miller 4 25
1 Nighthawks infantry rookie Jacobson 24 94
2 Nighthawks cavalry veteran Ali 31 57
3 Nighthawks cavalry rookie Milner 2 62
4 Dragoons infantry veteran Cooze 3 70
5 Dragoons infantry rookie Jacon 4 25
6 Dragoons cavalry veteran Ryaner 24 94
7 Dragoons cavalry rookie Sone 31 57
8 Scouts infantry veteran Sloan 2 62
9 Scouts infantry rookie Piger 3 70
10 Scouts cavalry veteran Riani 2 62
11 Scouts cavalry rookie Ali 3 70

12 rows × 6 columns

Create a crosstab table by company and regiment

Counting the number of observations by regiment and category

pd.crosstab(df.regiment, df.company, margins=True)
company cavalry infantry All
regiment
Dragoons 2 2 4
Nighthawks 2 2 4
Scouts 2 2 4
All 6 6 12

4 rows × 3 columns

Create a crosstab of the number of rookie and veteran cavalry and infantry soldiers per regiment

pd.crosstab([df.company, df.experience], df.regiment,  margins=True)
regiment Dragoons Nighthawks Scouts All
company experience
cavalry rookie 1 1 1 3
veteran 1 1 1 3
infantry rookie 1 1 1 3
veteran 1 1 1 3
All 4 4 4 12

5 rows × 4 columns