Aran Freel Aran Freel - 4 months ago 19
Python Question

Apply multiple string containment filters to pandas dataframe using dictionary

I need to set a filter on multiple columns based on string containment which will be specified in the dict

column_filters
while ignoring text case using
toupper()
or something along those lines ... for example

column_filters = {'COLUMN_1': ['drum', 'gui'], 'COLUMN_2': ['sta', 'kic']}

df = pd.DataFrame({'COLUMN_1': ['DrumSet', 'GUITAR', 'String', 'Bass', 'Violin'],
'COLUMN_2': ['STAND', 'DO', 'KICKSET', 'CAT', 'CELLO'],
'COLUMN_3': ['LOSER', 'LOVE', 'LICKING', 'STICK', 'BOLOGNA'])


DATAFRAME TO FILTER BASED ON
COLUMN_FILTERS
DICT



COLUMN_1 COLUMN_2 COLUMN_3
0 DrumSet STAND LOSER
1 GUITAR DO LOVE
2 String KICKSET LICKING
3 Bass CAT STICK
4 Violin CELLO BOLOGNA


RESULT



COLUMN_1 COLUMN_2 COLUMN_3
0 DrumSet STAND LOSER
1 GUITAR DO LOVE
2 String KICKSET LICKING

Answer Source

I'd convert the dict values into a regex pattern by joining all strings with '|', you can then use str.contains to filter the df:

In [50]:
for k in column_filters.keys():
    column_filters[k] = '|'.join(column_filters[k])
column_filters

Out[50]:
{'COLUMN_1': 'drum|gui', 'COLUMN_2': 'sta|kic'}

now filter using using str.contains with param case=False:

In [51]:
df.loc[(df['COLUMN_1'].str.contains(column_filters['COLUMN_1'], case=False)) | (df['COLUMN_2'].str.contains(column_filters['COLUMN_2'], case=False))]

Out[51]:
  COLUMN_1 COLUMN_2
0  DrumSet    STAND
1   GUITAR       DO
2   String  KICKSET

Update

OK there is a dynamic method:

In [68]:
df[df.apply(lambda x: x.str.contains('|'.join(column_filters[x.name]), case=False)).any(axis=1)]

Out[68]:
  COLUMN_1 COLUMN_2
0  DrumSet    STAND
1   GUITAR       DO
2   String  KICKSET

We can see without the boolean masking that it correctly matches:

In [69]:
df.apply(lambda x: x.str.contains('|'.join(column_filters[x.name]), case=False))

Out[69]:
  COLUMN_1 COLUMN_2
0     True     True
1     True    False
2    False     True
3    False    False
4    False    False

Update 2

To answer you modified question again:

In [75]:
df[df[list(column_filters.keys())].apply(lambda x: x.str.contains('|'.join(column_filters[x.name]), case=False)).any(axis=1)]

Out[75]:
  COLUMN_1 COLUMN_2 COLUMN_3
0  DrumSet    STAND    LOSER
1   GUITAR       DO     LOVE
2   String  KICKSET  LICKING