Create a new pandas DataFrame from an existing one based on whether it's multiIndex appears in a list

user_13666843 0
Viewed 39

I want to be able to take a large DataFrame which has a multiIndex and create two new ones based on whether or not the index appears in a list.

As an example, lets say I have a DataFrame which has a MultiIndex of ['Name', 'Surname'] and columns of different test results ['Score1', 'Score2']. Given a list of names I want to be able to create two new DataFrames, one containing entries whose names are in the list and one whose names are not. 

In [1]:
data = {'Name':['Jake', 'Jack', 'June', 'Jane'], 
        'Surname': ['Scott', 'Smith', 'Saint', 'Smith'],
        'Score1':[85, 78, 95, 90],
        'Score2': [79, 91, 83, 68]} 
df = pd.DataFrame(data)
df = df.set_index(['Name', 'Surname'])
Males = [['Jake', 'Scott'], ['Jack', 'Smith']]
df

Out [1]:
            Score1  Score2
Name    Surname     
Jake    Scott   85  79
Jack    Smith   78  91
June    Saint   95  83
Jane    Smith   90  68

Then if a row index is in the Males it will be put into MalesDF else it will be put into FemalesDF

In [2]:    MalesDF

Out [2]: 
             Score1 Score2
Name    Surname     
Jake    Scott   85  79
Jack    Smith   78  91

Obviously in this small example it might be simple enough to do with a for loop but in my actual case I have thousands of names and a list about a quarter of the size. So a for loop would be too inefficient

multi-index pandas dataframe python
1 Answers

Use, index.isin to create a boolean mask, and filter the df to create the male and female df based on this boolean mask:

m = df.index.isin(Males)
df_males = df[m]
df_females = df[~m]

Result:

# df_males
              Score1  Score2
Name Surname                
Jake Scott        85      79
Jack Smith        78      91

# df_females
              Score1  Score2
Name Surname                
June Saint        95      83
Jane Smith        90      68
user_128331660
Related