CODEWITHSUNDEEP
pandasdataframe
Samto
Samto

Reputation: 101

How to check if values in one dataframe column are contained in another entire column?

In my project I need to check if some value exists in entire dataframe column. Example dataframe:

df=pd.DataFrame([['abc', 'a'], ['def', 'x'], ['aef', 'f']])
df.columns=['a', 'b']
>>>df
     a  b
0  abc  a
1  def  x
2  aef  f

This static code works well:

df['a'].str.contains('f').any()
True

What I need is to iterate it over rows and check if each value in "b" column is contained in entire "a" column. I did not find a way how to do it. This is what I expected should work, but it returns an error:

df['c']=df.apply(lambda row:df['a'].str.contains(row['b']).any())

...
    return self._engine.get_value(s, k, tz=getattr(series.dtype, "tz", None))
  File "pandas\_libs\index.pyx", line 80, in pandas._libs.index.IndexEngine.get_value
  File "pandas\_libs\index.pyx", line 88, in pandas._libs.index.IndexEngine.get_value
  File "pandas\_libs\index.pyx", line 128, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\_libs\index_class_helper.pxi", line 91, in pandas._libs.index.Int64Engine._check_type
KeyError: ('b', 'occurred at index a')

Any idea?

Update: As I see, my df is not a good example. Here is better one (including expected result):

     a  b      c
0  abc  a   True
1  def  b   True
2  aef  x  False

Upvotes: 1

Views: 165

Answers (1)

jezrael
jezrael

Reputation: 862406

Use Series.str.extractall with Series.isin for test:

df=pd.DataFrame([['#123 - some text', '', False], 
                 ['#124 - some text', '123', True], 
                 ['#125 - some text', '', False], 
                 ['#126 - some text', '126', True], 
                 ['#127 - some text', '123', True], 
                 ['#128 - some text', '129', False]],columns=['Text', 'ID', 'Expected result'])


s = df['Text'].str.extractall("(" + '|'.join(set(df['ID'])) + ")")[0].dropna()
df['new'] = df['ID'].isin(s)
print (df)
               Text   ID  Expected result    new
0  #123 - some text                 False  False
1  #124 - some text  123             True   True
2  #125 - some text                 False  False
3  #126 - some text  126             True   True
4  #127 - some text  123             True   True
5  #128 - some text  129            False  False

Details:

First create pattern of all unique values by sets with | for regex OR:

print ("(" + '|'.join(set(df['ID'])) + ")")
(|123|129|126)

Then extract all matched values from Text, remove missing values by Series.dropna and last test membership by isin:

print (df['Text'].str.extractall("(" + '|'.join(set(df['ID'])) + ")")[0].dropna())
   match
0  2        123
3  2        126
Name: 0, dtype: object

Upvotes: 1

Related Questions