Pandas 数据帧 - 选择一列的值包含字符串而另一列的值以特定字符串开头的行

Question

我想选择state包含单词Traded且trading _book不以字母'E'、'L'、'N'开头的行

Test_Data = [('originating_system_id', ['RBCL', 'RBCL', 'RBCL','RBCL']),
('rbc_security_type1', ['CORP', 'CORP','CORP','CORP']),
('state', ['Traded', 'Traded Away','Traded','Traded Away']),
('trading_book', ['LCAAAAA','NUBBBBB','EDFGSFG','PDFEFGR'])
]
dfTest_Data = pd.DataFrame.from_items(Test_Data)
display(dfTest_Data)
originating_system_id   rbc_security_type1     state        trading_book
RBCL                   CORP            Traded          LCAAAAA
RBCL                   CORP            Traded Away     NUBBBBB
RBCL                   CORP            Traded          EDFGSFG
RBCL                   CORP            Traded Away     PDFEFGR

期望输出：

originating_system_id   rbc_security_type1     state        trading_book
RBCL                   CORP            Traded Away     PDFEFGR

我以为这会奏效：

prefixes = ['E','L','N']
df_Traded_Away_User = dfTest_Data[
dfTest_Data[~dfTest_Data['trading_book'].str.startswith(tuple(prefixes))]  &
(dfTest_Data['state'].str.contains('Traded')) 
][['originating_system_id','rbc_security_type1','state','trading_book']]
display(df_Traded_Away_User)

但我得到了：

ValueError: Must pass DataFrame with boolean values only

Answer 1

我建议单独创建每个布尔掩码，以便更好地读取代码，然后通过&:将它们链接起来

prefixes = ['E','L','N']
m1 = ~dfTest_Data['trading_book'].str.startswith(tuple(prefixes))
m2 = dfTest_Data['state'].str.contains('Traded')
cols = ['originating_system_id','rbc_security_type1','state','trading_book']
df_Traded_Away_User = dfTest_Data.loc[m1 & m2, cols]
print (df_Traded_Away_User)
originating_system_id rbc_security_type1        state trading_book
3                  RBCL               CORP  Traded Away      PDFEFGR