Identifying and removing duplicates present in column 2 when checked with column 1

This is the table, I want to identify and remove the duplicate values present in bc_keywords*,* if those are already present in the column: ab_keywords

For eg: "ryzen" is present in both the columns for ID: ABL345, so i want to identify it and remove it from the bc_keywords

so my expected table would look something like this:

Is there any way that I can do it ?
column "duplis" is also not much needed, my main objective is to remove the duplis and add the new list of keywords in a new column.

I have tried using df.duplicated(), but definitely seems like I am doing something wrong, and did not get the answer that I was looking for

df.duplicated() just gave me a boolean series column

I have also tried the following method:

dof['new'] = list(set(dof['bc']) - set(dof['ab']))
dof['new']
dof.head()

but the output seems weird:

Try:

1. Split the bc_keywords column by ","
2. explode the column to get one row per keyword
3. Identify duplicates
4. groupby and agg-regate as needed

df["bc_keywords"] = df["bc_keywords"].str.split(",")
df = df.explode("bc_keywords")
duplicates = df.apply(lambda row: row["bc_keywords"] in row["ab_keywords"].split(","), axis=1)
df["bc_new"] = df["bc_keywords"].where(~duplicates)
df["duplis"] = df["bc_keywords"].where(duplicates)
output = df.groupby("ID").agg({"ab_keywords": "first", 
                               "bc_keywords": ",".join, 
                               "duplis": "first",
                               "bc_new": lambda x: ",".join(x.dropna())})

>>> output

You should apply this logic to each row in your dataframe.

I've created a mock dataset based on your example:

data = pd.DataFrame({
    "ab_keywords": ["aaa,bbb,ccc", "bbb,ccc,dd,eee"],
    "bc_keywords": ["bbb,ccc,rrr", "ccc,eee,fff,ggg"]
})

Then prepare a function to apply to each row:

def remove_duplicates(row):
    return list(set(row['bc_keywords'].split(",")) - set(row['ab_keywords'].split(",")))

data["bc_new"] = data.apply(remove_duplicates, axis=1)
data

Output:

    ab_keywords	    bc_keywords	    bc_new
0	aaa,bbb,ccc	    bbb,ccc,rrr	    [rrr]
1	bbb,ccc,dd,eee	ccc,eee,fff,ggg	[fff, ggg]

If your values are stored as strings rather then lists, you should also split them into lists before applying the function or inside the function.

Edit: I've updated the code to deal with values as strings - added splitting them into lists first.

A possible solution would be to use a helper func to dedup the keywords formed with split :

from functools import partial

def sjoin(kws, sep=","):
    return sep.join(filter(None, kws))

def dedup(lst_ab, lst_bc):
    dups, new_bc = zip(
        *[(bc, None) if bc in lst_ab else (None, bc) for bc in lst_bc]
    )
    return sjoin(dups), sjoin(new_bc) # <-- add the sep(s) if needed

keywords = df[["ab_keywords", "bc_keywords"]].apply(lambda x: x.str.split(","), axis=1)

df["duplis"], df["new_bc"] = zip(
    *[dedup(lst_ab, lst_bc) for lst_ab, lst_bc in keywords.to_numpy()]
)

Output :