Apply multiple string containment filters to pandas dataframe using dictionary

debugcn Published at Dev

Aran Freel

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

EdChum

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

Collected from the Internet

Please contact [email protected] to delete if infringement.