Replace the content of all cells matching a condition

user_7112012 0
Viewed 1093

How can I loop through an entire dataframe to remove data in cells that contain a particular string when the column names are unknown?

Here’s what I have so far:

for (i in colnames(df)){
   df2 = df[~df[i].str.contains('found')]

My data:

  Getting links from: https://www.bar.com/ Getting links from: https://www.boo.com/ Getting links from: https://www.foo.com/
0           ├───OK─── http://www.this.com/           ├───OK─── http://www.this.com/           ├───OK─── http://www.this.com/
1             ├───OK─── http://www.is.com/             ├───OK─── http://www.is.com/             ├───OK─── http://www.is.com/
2         ├─BROKEN─ http://www.broken.com/     2 links found. 0 excluded. 0 broken.         ├─BROKEN─ http://www.broken.com/
3                                      NaN                                      NaN            ├───OK─── http://www.set.com/
4                                      NaN                                      NaN            ├───OK─── http://www.one.com/

How can I delete the entire contents of a cell if it contains a string, for instance, 'found'? I want to delete everything in the cell (including before and after the string.)

pandas dataframe python
3 Answers

You can use applymap here:

import re    

# just make sure all values are string type
df = df.astype(str)    

# remove the text which has word found
df.applymap(lambda x: re.sub('.*found.*','',x ))

                                          0
0  Getting links from: https://www.boo.com/
1            ├───OK─── http://www.this.com/
2              ├───OK─── http://www.is.com/
3                                          
4                                       nan
5                                       nan
user_92992590

As you are looking for a string/value to check and take the action against the whole DataFrame hence DataFrame.replace method fits the caveats here ..

Example DataFrame:

>>> df
      a
0  foo1
1  foo2
2   bar
3   bar
4   bar

Replacing bar with blank or you can replace it with NaN Value if desired:

>>> df.replace("bar", "", regex=True)
      a
0  foo1
1  foo2
2
3
4

Or replace bar with NaN

>>> df.replace("bar", np.nan, regex=True)
 # df.replace("bar", np.nan, regex=True, inplace=True)
      a
0  foo1
1  foo2
2   NaN
3   NaN
4   NaN

You can choose to have inplace=True if you like to replace to take place into the actual dataframe:

Simulated the example as given:

>>> df
                                                                                                                         col1
0  Getting links from: https://www.bar.com/ Getting links from: https://www.boo.com/ Getting links from: https://www.foo.com/
1            ├───OK─── http://www.this.com/           ├───OK─── http://www.this.com/           ├───OK─── http://www.this.com/
2              ├───OK─── http://www.is.com/             ├───OK─── http://www.is.com/             ├───OK─── http://www.is.com/
3          ├─BROKEN─ http://www.broken.com/     2 links found. 0 excluded. 0 broken.         ├─BROKEN─ http://www.broken.com/
4                                       NaN                                      NaN            ├───OK─── http://www.set.com/
5                                       NaN                                      NaN            ├───OK─── http://www.one.com/

Result with str.contains:

>>> df[~df["col1"].str.contains("found")]
                                                                                                                         col1
0  Getting links from: https://www.bar.com/ Getting links from: https://www.boo.com/ Getting links from: https://www.foo.com/
1            ├───OK─── http://www.this.com/           ├───OK─── http://www.this.com/           ├───OK─── http://www.this.com/
2              ├───OK─── http://www.is.com/             ├───OK─── http://www.is.com/             ├───OK─── http://www.is.com/
4                                       NaN                                      NaN            ├───OK─── http://www.set.com/
5                                       NaN                                      NaN            ├───OK─── http://www.one.com/

OR as Stated make sure values are string type to apply the operation

>>> df[~df["col1"].astype(str).str.contains("found")]
user_57048630

You can replace with nan with df.replace({'test':np.nan}) and that should replace all instances

user_105437710
Related