import pandas as pd
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'],
'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', 'name', 'preTestScore', 'postTestScore'])
df
regiment | company | name | preTestScore | postTestScore | |
---|---|---|---|---|---|
0 | Nighthawks | 1st | Miller | 4 | 25 |
1 | Nighthawks | 1st | Jacobson | 24 | 94 |
2 | Nighthawks | 2nd | Ali | 31 | 57 |
3 | Nighthawks | 2nd | Milner | 2 | 62 |
4 | Dragoons | 1st | Cooze | 3 | 70 |
5 | Dragoons | 1st | Jacon | 4 | 25 |
6 | Dragoons | 2nd | Ryaner | 24 | 94 |
7 | Dragoons | 2nd | Sone | 31 | 57 |
8 | Scouts | 1st | Sloan | 2 | 62 |
9 | Scouts | 1st | Piger | 3 | 70 |
10 | Scouts | 2nd | Riani | 2 | 62 |
11 | Scouts | 2nd | Ali | 3 | 70 |
12 rows × 5 columns
df.set_index(['regiment', 'company'], drop=False)
df
regiment | company | name | preTestScore | postTestScore | |
---|---|---|---|---|---|
0 | Nighthawks | 1st | Miller | 4 | 25 |
1 | Nighthawks | 1st | Jacobson | 24 | 94 |
2 | Nighthawks | 2nd | Ali | 31 | 57 |
3 | Nighthawks | 2nd | Milner | 2 | 62 |
4 | Dragoons | 1st | Cooze | 3 | 70 |
5 | Dragoons | 1st | Jacon | 4 | 25 |
6 | Dragoons | 2nd | Ryaner | 24 | 94 |
7 | Dragoons | 2nd | Sone | 31 | 57 |
8 | Scouts | 1st | Sloan | 2 | 62 |
9 | Scouts | 1st | Piger | 3 | 70 |
10 | Scouts | 2nd | Riani | 2 | 62 |
11 | Scouts | 2nd | Ali | 3 | 70 |
12 rows × 5 columns
df = df.set_index(['regiment', 'company'])
df
name | preTestScore | postTestScore | ||
---|---|---|---|---|
regiment | company | |||
Nighthawks | 1st | Miller | 4 | 25 |
1st | Jacobson | 24 | 94 | |
2nd | Ali | 31 | 57 | |
2nd | Milner | 2 | 62 | |
Dragoons | 1st | Cooze | 3 | 70 |
1st | Jacon | 4 | 25 | |
2nd | Ryaner | 24 | 94 | |
2nd | Sone | 31 | 57 | |
Scouts | 1st | Sloan | 2 | 62 |
1st | Piger | 3 | 70 | |
2nd | Riani | 2 | 62 | |
2nd | Ali | 3 | 70 |
12 rows × 3 columns
df.index
MultiIndex(levels=[['Dragoons', 'Nighthawks', 'Scouts'], ['1st', '2nd']], labels=[[1, 1, 1, 1, 0, 0, 0, 0, 2, 2, 2, 2], [0, 0, 1, 1, 0, 0, 1, 1, 0, 0, 1, 1]], names=['regiment', 'company'])
df.swaplevel('regiment', 'company')
name | preTestScore | postTestScore | ||
---|---|---|---|---|
company | regiment | |||
1st | Nighthawks | Miller | 4 | 25 |
Nighthawks | Jacobson | 24 | 94 | |
2nd | Nighthawks | Ali | 31 | 57 |
Nighthawks | Milner | 2 | 62 | |
1st | Dragoons | Cooze | 3 | 70 |
Dragoons | Jacon | 4 | 25 | |
2nd | Dragoons | Ryaner | 24 | 94 |
Dragoons | Sone | 31 | 57 | |
1st | Scouts | Sloan | 2 | 62 |
Scouts | Piger | 3 | 70 | |
2nd | Scouts | Riani | 2 | 62 |
Scouts | Ali | 3 | 70 |
12 rows × 3 columns
df.sum(level='regiment')
preTestScore | postTestScore | |
---|---|---|
regiment | ||
Dragoons | 62 | 246 |
Nighthawks | 61 | 238 |
Scouts | 10 | 264 |
3 rows × 2 columns