How to delete words from a column, contained in another column?

Question

  Audience              Ad
  Audience1     Audience4.Ad1.image
  Audience2     Audience1.Ad4.image
  Audience3     Audience7.Ad1.image
  Audience4     Audience2.Ad3.image
  Audience5     Audience9.Ad1.image
  Audience6     Audience4.Ad2.image
  Audience7     Audience5.Ad1.image
  Audience8     Audience7.Ad3.image
  Audience9     Audience8.Ad1.image
  Audience10    Audience9.Ad1.image

This is some sample data. What I am trying to do is to look in the Ad column and if it contains anything from the Audience column replace it with nothing. The most difficult part for me here is that on the left might say Audience1 and on the right Audience2 so that they are not the same. If they were I know how to implement this but unfortunately, they are not!

So the expected result will look like this

  Audience      Ad
  Audience1     Ad1.image
  Audience2     Ad4.image
  Audience3     Ad1.image  
  Audience4     Ad3.image
  Audience5     Ad1.image
  Audience6     Ad2.image
  Audience7     Ad1.image
  Audience8     Ad3.image
  Audience9     Ad1.image
  Audience10    Ad1.image

The way I thought of doing this is by going through the Audience column with a for loop and then if I find that any of the elements of the Audience column is contained in the Ad column delete it.

This is how I tried to solve it but I am stuck on what to put in the return statement (if the rest of the logic of course is correct):

def replace(text):
    for i in df['Audience']:
        if i in text:
            return ???
df['Ad'] = df['Ad'].apply(replace)

Any help would be appreciated!

Trenton McKinney · Accepted Answer · 2020-06-14T22:08:22.603

Convert Audience to a set to make sure there are no repeated values.
str.split the Ad column
Remove terms from Ad that are in the aud list, with a list comprehension, and then str.join the terms.
- [y for y in x if y not in aud] is a list comprehension
  - Each row was converted to a list with .split. This iterates through each value and checks if it's in the aud list. It it is, then it's not included in the new list.
  - '.'.join() creates a string from the elements of the list
Given a sample dataset of 10e6 rows (df = pd.concat([pd.DataFrame(data)]*1000000)):
- This answer: Wall time: 16.9 s
- The answer from Shubham Sharma: Wall time: 27.7 s
- The answer from Ch3steR: Wall time: 15.7 s
  - This time is variable on the number of unique words in df[Audience], because the words are being joined into a string.

import pandas as pd

# data and dataframe
data = {'Audience': ['Audience1', 'Audience2', 'Audience3', 'Audience4', 'Audience5', 'Audience6', 'Audience7', 'Audience8', 'Audience9', 'Audience10'],
        'Ad': ['Audience4.Ad1.image', 'Audience1.Ad4.image', 'Audience7.Ad1.image', 'Audience2.Ad3.image', 'Audience9.Ad1.image', 'Audience4.Ad2.image', 'Audience5.Ad1.image', 'Audience7.Ad3.image', 'Audience8.Ad1.image', 'Audience9.Ad1.image']}

df = pd.DataFrame(data)

# create list of unique words from Audience
aud = set(df.Audience.str.lower())

# remove Audience words from Ad column
df.Ad = df.Ad.str.split('.').apply(lambda x: '.'.join([y for y in x if y.lower() not in aud]))

|    | Audience   | Ad        |
|---:|:-----------|:----------|
|  0 | Audience1  | Ad1.image |
|  1 | Audience2  | Ad4.image |
|  2 | Audience3  | Ad1.image |
|  3 | Audience4  | Ad3.image |
|  4 | Audience5  | Ad1.image |
|  5 | Audience6  | Ad2.image |
|  6 | Audience7  | Ad1.image |
|  7 | Audience8  | Ad3.image |
|  8 | Audience9  | Ad1.image |
|  9 | Audience10 | Ad1.image |

Option 2:

Updated from comment with new data

data = {'Audience': ['Football.And.Basketball.Interests', 'Baseball.Interests', 'Cricket.Interests', 'Website.Visitors'],
        'Ad': ['Baseball.Interests.Ad1.image', 'Football.And.Basketball.Interests.Ad4.image', 'Cricket.Interests.Ad1.image', 'Website.Visitors.Ad3.image']}

df = pd.DataFrame(data)

                          Audience                                           Ad
 Football.And.Basketball.Interests                 Baseball.Interests.Ad1.image
                Baseball.Interests  Football.And.Basketball.Interests.Ad4.image
                 Cricket.Interests                  Cricket.Interests.Ad1.image
                  Website.Visitors                   Website.Visitors.Ad3.image

# if Audience contains multiple values
aud = set(df.Audience.str.split('.').explode().str.lower())

# remove Audience words from Ad column
df.Ad = df.Ad.str.split('.').apply(lambda x: '.'.join([y for y in x if y.lower() not in aud]))

                          Audience         Ad
 Football.And.Basketball.Interests  Ad1.image
                Baseball.Interests  Ad4.image
                 Cricket.Interests  Ad1.image
                  Website.Visitors  Ad3.image

Thank so much again @TrentonMcKinney! I tried running this but it gives me back the errror "'Series' object has no attribute 'explode'" in the line where we define "aud". Tried to search of how to resolve this but didn't manage to do it — truelis, Jun 14 '20 at 22:53
@truelis You need to update pandas. `explode` is in version 0.25 and greater. If you're using anaconda, then at the anaconda prompt `conda update pandas` — Trenton McKinney, Jun 14 '20 at 23:00
@TrentonMcKinney thanks I was trying this in Azure Notebooks. I tried it in my local Jupyter and worked fine. I also tried it without the `str.lower` and again it worked. I am still in the process of understanding what exactly is happening with the lambda and the list comprehension as I have never really used them but I will hopefully get there! Thanks again — truelis, Jun 14 '20 at 23:39
@truelis `.lower` isn't strictly required, but it's customary to force the case, to make sure all the words match without having multiple versions, which is wasteful. — Trenton McKinney, Jun 14 '20 at 23:50

Ch3steR · Answer 2 · 2020-06-14T19:02:01.310

2

You can use pd.Series.str.replace with pd.Series.contains

mask = df['Ad'].str.contains('\.|'.join(set(df['Audience'])))
df.loc[mask,'Ad'] = df.loc[mask,'Ad'].str.replace(r'(Audience\d+.)','')
df
     Audience         Ad
0   Audience1  Ad1.image
1   Audience2  Ad4.image
2   Audience3  Ad1.image
3   Audience4  Ad3.image
4   Audience5  Ad1.image
5   Audience6  Ad2.image
6   Audience7  Ad1.image
7   Audience8  Ad3.image
8   Audience9  Ad1.image
9  Audience10  Ad1.image

Example with non-matches:

df
      Audience                     Ad
0    Audience1    Audience4.Ad1.image
1    Audience2    Audience1.Ad4.image
2    Audience3    Audience7.Ad1.image
3    Audience4    Audience2.Ad3.image
4    Audience5    Audience9.Ad1.image
5    Audience6    Audience4.Ad2.image
6    Audience7    Audience5.Ad1.image
7    Audience8    Audience7.Ad3.image
8    Audience9    Audience8.Ad1.image
9   Audience10    Audience9.Ad1.image
10  Audience12  Audience11.Ad11.image

mask = df['Ad'].str.contains('\.|'.join(set(df['Audience'])))
df.loc[mask,'Ad'] = df.loc[mask,'Ad'].str.replace(r'(Audience\d+.)','')
df

      Audience                     Ad
0    Audience1              Ad1.image
1    Audience2              Ad4.image
2    Audience3              Ad1.image
3    Audience4              Ad3.image
4    Audience5              Ad1.image
5    Audience6              Ad2.image
6    Audience7              Ad1.image
7    Audience8              Ad3.image
8    Audience9              Ad1.image
9   Audience10              Ad1.image
10  Audience12  Audience11.Ad11.image #---> Audience11 not deleted as 'Audience11' is not in `df['Audience']`

edited Jun 14 '20 at 19:02

answered Jun 14 '20 at 18:20

Ch3steR

20,090
4
28
58

More than a minute. it's still going. – Trenton McKinney Jun 14 '20 at 18:56
`Wall time: 4min 58s` – Trenton McKinney Jun 14 '20 at 18:58
1

@TrentonMcKinney Thank you. It was really helpful. It's really slow. This may be `'\.|'.join(df['Audience'])` major reason. – Ch3steR Jun 14 '20 at 18:59
Add `set()` around it. – Trenton McKinney Jun 14 '20 at 19:01
@TrentonMcKinney Yes, that's the best optimization. Joining a million row is definitely slow in previous approach. Thank you for the `set` hint. – Ch3steR Jun 14 '20 at 19:02
That reduced the time to 15.7 seconds. But the more unique works in df[Audience], the longer it will take – Trenton McKinney Jun 14 '20 at 19:02
1

@TrentonMcKinney More unique words in `df['Audience']` will make it slow agreed. – Ch3steR Jun 14 '20 at 19:05
Thanks a lot @Ch3steR! Works fine as well. As I replied above I am not that familiar yet with Pandas and also in this answer I need to do a bit of digging to understand exactly what is happening. One quick question: replace(r'(Audience\d+.)',''), what does the "r" right after the parenthesis of "replace" does? – truelis Jun 14 '20 at 21:15
1

@truelis They are called raw strings they are useful when working with regex. You can [read more about `"r string"` here.](https://stackoverflow.com/questions/33729045/what-does-an-r-represent-before-a-string-in-python?lq=1#:~:text=r%20means%20the%20string%20will,and%20a%20lowercase%20'n'.) – Ch3steR Jun 15 '20 at 03:45

score 1 · Answer 3 · answered Jun 14 '20 at 18:20

Use, Series.str methods along with Series.isin, Series.where:

s = df['Ad'].str.split('.')
m = s.str[0].isin(df['Audience'])
df['Ad'] = s.where(~m, s.str[1:]).str.join('.')

# print(df)

     Audience         Ad
0   Audience1  Ad1.image
1   Audience2  Ad4.image
2   Audience3  Ad1.image
3   Audience4  Ad3.image
4   Audience5  Ad1.image
5   Audience6  Ad2.image
6   Audience7  Ad1.image
7   Audience8  Ad3.image
8   Audience9  Ad1.image
9  Audience10  Ad1.image

How to delete words from a column, contained in another column?

3 Answers3

Option 2: