Filter for rows if any value in a list of substrings is contained in any column in a dataframe

user_14365042 0
Viewed 508

Suppose I have a dataframe df as:

df = pd.DataFrame({'Index': [1, 2, 3, 4, 5],
                   'Name': ['A', 'B', 100, 'C', 'D'],
                   'col1': [np.nan, 'bbby', 'cccy', 'dddy', 'EEEEE'],
                   'col2': ['water', np.nan, 'WATER', 'soil', 'cold air'],
                   'col3': ['watermelone', 'hot AIR', 'air conditioner', 'drink', 50000],
                  'Results': [1000, 2000, 3000, 4000, 5000]})


Out

Index  Name  col1     col2         col3           Results
    1  A     NaN    water       watermelone        1000
    2  B     bbbY    NaN         hot AIR           2000
    3  100   cccY    water       air conditioner   3000
    4  C     dddf    soil        drink             4000
    5  D     EEEEE   cold air    50000             5000

I have a list: matches = ['wat','air']

How can I choose all rows with col1 or col2 or col3 containing i in matches.

Expected output:

Index  Name  col1     col2         col3           Results
    1  A     NaN     water       watermelone       1000
    2  B     bbbY    NaN         hot AIR           2000
    3  100   cccY    water       air conditioner   3000

    5  D     EEEEE   cold air    50000              5000
pandas dataframe substring python
2 Answers

You can use .T to transpose the dataframe and str.contains to check the values column-wise and then transpose back (also str.contains can have multiple values passed to if separated with |, which is why I change the list to a string with matches = '|'.join(matches)).

The benefit of transposing the dataframe is that you can use column-wise pandas method instead of looping through rows or a long lambda x: list comprehension. This technique should have good performance compared to a lambda x with axis=1 answer:

# df = df.set_index('Index')
matches = ['wat','air']
matches = '|'.join(matches)
df = df.reset_index(drop=True).T.fillna('')
df = df.T[[df[col].str.lower().str.contains(matches).values.any() for col in df.columns]]
df
Out[1]: 
  Name   col1      col2             col3
0    A            water      watermelone
1    B   bbbY                    hot AIR
2    B   cccY     water  air conditioner
4    D  EEEEE  cold air              eat
user_63667700

Try this as well:

df = df[df['col1'].str.contains('|'.join(matches))|df['col2'].str.contains('|'.join(matches))|df['col3'].str.contains('|'.join(matches))]

Prints:

  Name   col1      col2             col3
1    A   aadY     water      watermelone
2    B   bbbY       air          hot AIR
3    B   cccY     water  air conditioner
5    D  EEEEE  cold air              eat
user_136288830
Related