在特定表格的特定列中找到共同值并显示交叉输出的方法

Question

表

Roll  Class  Country  Rights  CountryAcc
1     x      IND      23      US
1     x1     IND      32      Ind
2     s      US       12      US
3     q      IRL      33      CA
4     a      PAK      12      PAK
4     e      PAK      12      IND
5     f      US       21      CA
5     g      US       31      PAK
6     h      US       21      BAN

我只想显示那些Rolls，它们的CountryAcc不在US或CA中。例如：如果Roll1在US中有一个CountryAcc，那么我不希望它的另一行有CountryAccInd，同样的情况也适用于Roll5，因为它有一行有CountryAcc作为CA。所以我的最终结果是：

Roll  Class  Country  Rights  CountryAcc
4     a      PAK      12      PAK
4     e      PAK      12      IND
6     h      US       21      BAN

我试着通过以下方式获得输出：

Home_Country = ['US', 'CA']

#First I saved two countries in a variable
Account_Other_Count = df.loc[~df.CountryAcc.isin(Home_Country)]
Account_Other_Count_Var = df.loc[~df.CountryAcc.isin(Home_Country)][['Roll']].values.ravel()

# Then I made two variables one with CountryAcc in US or CA and other variable with remaining and I got their Roll
Account_Home_Count = df.loc[df.CountryAcc.isin(Home_Country)]
Account_Home_Count_Var = df.loc[df.CountryAcc.isin(Home_Country)][['Roll']].values.ravel()

#Here I got the common Rolls
Common_ROLL = list(set(Account_Home_Count_Var).intersection(list(Account_Other_Count_Var)))
Final_Output = Account_Other_Count.loc[~Account_Other_Count.Roll.isin(Common_ROLL)]

有没有更好的，更多的熊猫或Python的方式来做这件事

Answer 1

一种解决办法是

In [37]: df.ix[~df['Roll'].isin(df.ix[df['CountryAcc'].isin(['US', 'CA']), 'Roll'])]
Out[37]:
   Roll Class Country  Rights CountryAcc
4     4     a     PAK      12        PAK
5     4     e     PAK      12        IND
8     6     h      US      21        BAN

Answer 2

这是一种方法：

sortdata = df[~df['CountryAcc'].isin(['US', 'CA'])].sort(axis=0)