How to filter rows containing specific string values with an AND operator

Question

My question is kind of an extension of the question answered quite well in this link:

How to filter rows containing a string pattern from a Pandas dataframe

I've posted the answer here below where the strings are filtered out when they contain the word "ball":

In [3]: df[df['ids'].str.contains("ball")]
Out[3]:
     ids     vals
0  aball     1
1  bball     2
3  fball     4

Now my question is: what if I have long sentences in my data, and I want to identify strings with the words "ball" AND "field"? So that it throws away data that contains the word "ball" or "field" when only one of them occur, but keeps the ones where the string has both words in it.

BTW, if searching for fixed strings (i.e. not regex), you can often use df['ids'].str.contains("ball", regex=False) for a bit of a speed boost. — Alex Riley
– Alex Riley, Commented Nov 5, 2017 at 18:58

s3bw · Accepted Answer · 2017-11-05 18:30:27Z

5

df[df['ids'].str.contains("ball")]

Would become:

df[df['ids'].str.contains("ball") & df['ids'].str.contains("field")]

If you are into neater code:

contains_balls = df['ids'].str.contains("ball")
contains_fields = df['ids'].str.contains("field")

filtered_df = df[contains_balls & contains_fields]

answered Nov 5, 2017 at 18:30

s3bw

3,0872 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

s3bw · Accepted Answer · 2019-01-23 15:42:55Z

2

If you have more than 2 , You can using this ..(Notice the speed is not as good as foxyblue's method )

l = ['ball', 'field']
df.ids.apply(lambda x: all(y in x for y in l))

edited Jan 23, 2019 at 15:42

s3bw

3,0872 gold badges23 silver badges31 bronze badges

answered Nov 5, 2017 at 18:35

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

Zero · Accepted Answer · 2017-11-05 18:45:35Z

0

You could use np.logical_and.reduce and str.contains takes care of multiple words.

df[np.logical_and.reduce([df['ids'].str.contains(w) for w in ['ball', 'field']])]

In [96]: df
Out[96]:
             ids
0  ball is field
1     ball is wa
2  doll is field

In [97]: df[np.logical_and.reduce([df['ids'].str.contains(w) for w in ['ball', 'field']])]
Out[97]:
             ids
0  ball is field

answered Nov 5, 2017 at 18:45

Zero

77.4k22 gold badges153 silver badges153 bronze badges

Comments

MaxU - stand with Ukraine · Accepted Answer · 2017-11-05 23:52:09Z

0

Yet another RegEx approach:

In [409]: df
Out[409]:
               ids
0   ball and field
1  ball, just ball
2      field alone
3  field and ball

In [410]: pat = r'(?:ball.*field|field.*ball)'

In [411]: df[df['ids'].str.contains(pat)]
Out[411]:
               ids
0   ball and field
3  field and ball

answered Nov 5, 2017 at 23:52

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Collectives™ on Stack Overflow

How to filter rows containing specific string values with an AND operator

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related