Pandas Multiindex selection criteria alongwith column selection criteria

user_10434791 0
Viewed 164

I need to query a Pandas Dataframe based on multiple criteria on the Index and the Column. Please have a look at my data below. 'Country' and 'Surname' are two separate indices, while 'Name', 'Score', 'Type' are columns.

y = pd.DataFrame({'Name':['Nikhil', 'Ankit', 'Keval', 'Darpan', 'Rajesh', 'John', 'Lynda'],
                  'Score':[89,92,96,82,95,98,97], 
                  'Type':['Fat','Slim','Fat','Slim','Fat','Slim','Slim'],
                  'Country':['India','USA','Denmark','Australia','Italy','China','Israel'],
                  'Surname':['Sharma','Sharma','Patel','Shah','Sharma','Sharma','Sharma']}                 
                ).set_index('Country').set_index('Surname', append=True)

enter image description here

I want to select data which meets following criteria:

  • Country should neither be India nor be USA
  • Surname should be Sharma
  • Score should be equal to or above 90
  • Type should be Slim
multi-index pandas python
2 Answers

For select in columns or in MultiIndex levels is possible use query with chained masks by and or &:

q='Country not in ["India","USA"] and Surname == "Sharma" and Score >= 90 and Type == "Slim"'

Or:

q = 'Country not in ["India","USA"] & Surname == "Sharma" & Score >= 90 & Type == "Slim"'

print (y.query(q))
                  Name  Score  Type
Country Surname                    
China   Sharma    John     98  Slim
Israel  Sharma   Lynda     97  Slim

Alternative with boolean indexing, but chaining masks is strictly &:

m1 = ~y.index.get_level_values('Country').isin(["India","USA"])
m2 = y.index.get_level_values('Surname') == 'Sharma'
m3 = y['Score'].ge(90)
m4 = y['Type'].eq('Slim')
print (y[m1 & m2 & m3 & m4])
                  Name  Score  Type
Country Surname                    
China   Sharma    John     98  Slim
Israel  Sharma   Lynda     97  Slim
user_29010020

You can use this code:

df.loc[(~df.index.isin(['India', 'USA'], level='Country'))& (df.index.get_level_values('Surname') == 'Sharma') & (df['Score'] >= 90) & (df['Type'] == 'Slim')]
user_100080080
Related