python - Pandas Multiindex 选择标准以及列选择标准

Question

我需要根据索引和列的多个条件查询 Pandas Dataframe。请在下面查看我的数据。 'Country' 和 'Surname' 是两个独立的索引，而 'Name'、'Score'、'Type' 是列。

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)

我想选择满足以下条件的数据:

• 国家既不能是印度也不能是美国
• 应该姓夏尔马
• 分数应等于或高于90
• 类型应该是 slim 的

Answer 1

要在列中或在多索引级别中进行选择，可以使用 query通过 and 或 & 链接掩码:

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

或者:

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

替代 boolean indexing , 但链接掩码是严格的 &:

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