Remove words in each row in a column of dataframe from another list of words in a column of another dataframe

user_14146292 0
Viewed 1482

I want to subtract or remove the words in one dataframe from another dataframe in each row.

This is the main table/columns of a pyspark dataframe.

+----------+--------------------+
|  event_dt|           cust_text|
+----------+--------------------+
|2020-09-02|hi fine i want to go|
|2020-09-02|i need  a line hold |
|2020-09-02|i have the  60 packs|
|2020-09-02|hello want you teach|

Below is another pyspark dataframe. The words in this dataframe needs to be removed from the above main table in column cust_text wherever the words occur in each row. For example, 'want' will be removed from every row wherever it shows up in 1st dataframe.

+-------+
|column1|
+-------+
|   want|
|because|
|   need|
|  hello|
|      a|
|   have|
|     go|
+-------+

This can be done in pyspark or pandas. I have tried googling the solution using Python, Pyspark, pandas, but still not able to remove the words from the main table based on a single column table.

The result should look like this:

+----------+--------------------+
|  event_dt|           cust_text|
+----------+--------------------+
|2020-09-02|hi fine i to        |
|2020-09-02|i line hold         |
|2020-09-02|i the 60 packs      |
|2020-09-02|you teach           |
+----------+--------------------+
pyspark pandas dataframe text python
2 Answers

If you want to remove just the word in the corresponding line of df2, you could do that as follows, but it will probably be slow for large data sets, because it only can partially can use fast C implementations:

# define your helper function to remove the string
def remove_string(ser_row):
    return ser_row['cust_text'].replace(ser_row['remove'], '')

# create a temporary column with the string to remove in the first dataframe
df1['remove']= df2['column1']
df1= df1.apply(remove_string, axis='columns')
# drop the temporary column afterwards
df1.drop(columns=['remove'], inplace=True)

The result looks like:

Out[145]: 
0        hi fine i  to go
1    i need   lines hold 
2    i have the  60 packs
3           can you teach
dtype: object

If however, you want to remove all words in your df2 column from every column, you need to do it differntly. Unfortunately str.replace does not help here with regular strings, unless you want to call it for every line in your second dataframe. So if your second dataframe is not too large, you can create a regular expression to make use of str.replace.

import re
replace=re.compile(r'\b(' + ('|'.join(df2['column1'])) + r')\b')
df1['cust_text'].str.replace(replace, '')

The output is:

Out[184]: 
0      hi fine i  to 
1    i    lines hold 
2    i  the  60 packs
3       can you teach
Name: cust_text, dtype: object

If you don't like the repeated spaces, that remain, you can just perform something like:

df1['cust_text'].str.replace(replace, '').str.replace(re.compile('\s{2,}'), ' ')

Addition: what, if not only the text without the words is relevant, but the words themselves as well. How can we get the words, which were replaced. Here is one attempt, which would work, if one character can be identified, which will not appear in the text. Let's assume this character is a @, then you could do (on the original column value without replacement):

# enclose each keywords in @
ser_matched= df1['cust_text'].replace({replace: r'@\1@'}, regex=True)
# now remove the rest of the line, which is unmatched
# this is the part of the string after the last occurance
# of a @
ser_matched= ser_matched.replace({r'^(.*)@.*$': r'\1', '^@': ''}, regex=True)
# and if you like your keywords to be in a list, rather than a string
# you can split the string at last
ser_matched.str.split(r'@+')
user_69251850

This solution would be specific to pandas. If I understand your challenge correctly, you want to remove all words from column cust_text that occur in column1 of the second DataFrame. Lets give the corresponding DataFrames the names: df1 and df2. This is how you would do this:

for i in range(len(df1)):
    sentence = df1.loc[i, "cust_text"]
    for j in range(len(df2)):
        delete_word = df2.loc[j, "column1"]
        if delete_word in sentence:
            sentence = sentence.replace(delete_word, "")
    df1.loc[i, "cust_text"] = sentence

I have assigned variables to certain data points in these dataframes (sentence and delete_word), but that is just for the sake of understanding. You can easily condense this code to a few lines shorter by not doing that.

user_98883120
Related