Grouping and Multiindexing a pandas dataframe

Question

Suppose I have a dataframe as follows

In [6]: df.head()
Out[6]: 
     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

I have a dictionary as follows:

army = {'Majors' : 'Nighthawks', 'Captains' : 'Dragoons'}

and I want that it and should have a multi-index in the shape of ["army","company"] only.

How will I proceed?

Answer 1

If I understand correctly:

You can use map to find values in a dictionary (using dictionary comprehension to swap key/value pairs since they are backwards):

army = {'Majors': 'Nighthawks', 'Captains': 'Dragoons'}

df.assign(army=df.regiment.map({k:v for v, k in army.items()})).set_index(['army', 'company'], drop=True)
                    regiment      name  preTestScore  postTestScore
army     company                                                   
Majors   1st      Nighthawks    Miller             4             25
         1st      Nighthawks  Jacobson            24             94
         2nd      Nighthawks       Ali            31             57
         2nd      Nighthawks    Milner             2             62
Captains 1st        Dragoons     Cooze             3             70