Remove all rows containing a piece of a string in multiple columns in pandas

user_14077567 0
Viewed 1972

I have a very large dataframe with many columns. I want to check all the columns and remove any row containing any instance of the string 'MU', and there are some columns that have 'MU#1' or 'MU#2', and they will sometimes switch places (like 'MU#1 would be in column 1 at index 0 and 'MU#2' will be in column 1 at index 1). Initially, I tried removing them with this but it becomes far too cumbersome if I try to do this for both strings above:

df_slice = df[(df.phase_2 != 'MU#1') & (df.phase_3 != 'MU#1') & (df.phase_1 != 'MU#1') & (df.phase_4 != 'MU#1') ]

This may work, but I have to repeat this slice a few times with other dataframes and I imagine there is a much simpler route. I also have more columns than what is shown above, but that is just a snippet. Simply put, all columns need to be checked for 'MU' and the rows with 'MU' need to be removed. Thanks!

pandas dataframe slice python
3 Answers

You could also try .str.contains() and apply to the dataframe. This avoids hardcoding the columns in just in case

df[df.apply(lambda x: (~x.str.contains('MU', case=True, regex=True)))].dropna()

or

df[~df.stack().str.contains('MU').any(level=0)]

How it works

Option 1

when used in df.apply(), x.str.contains, #is a wild card for any column in the datframe that contains

x.str.contains('MU', case=True, regex=True) is a wild card for any column in the datframe that contains 'MU', case sensitive and regular expression implied

~ Reverses, hence you end up with rows that do not have MU

Resulting dataframe returns NaN where the condition is not met. .dropna() hence eliminates the rows with NaN

Option 2

df.stack()# Stacks the dataframe
df.stack().str.contains('MU')#boolean selects rows with the string 'MU'
df.stack().str.contains('MU').any(level=0)# Selects the index
~df.stack().str.contains('MU').any(level=0)# Reverses the selection taking only those without string 'MU'
user_89869750

What we do with all

df = df[df[['phase_1','phase_2','phase_3','phase_4']].ne('MU#1').all(1)]

Update

df = df[(~df[['phase_1','phase_2','phase_3','phase_4']].isin(['MU#1','MU#2'])).all(1)]
user_79645270

This works fine with me.

df[~df.stack().str.contains('Any String').any(level=0)]

Even when searching specific string in the dataframe

df[df.stack().str.contains('Any String').any(level=0)]

Thanks.

user_160443460
Related