How to replace values in columns in DataFrame with staing coma if exists in Python Pandas?-CodePudding

I have Pandas DataFrame like below:

df = pd.DataFrame()
df["COL1"] = [111,222,333]
df["COL2"] = ["CV_COUNT_ABC_XM_BF, CV_COUNT_DEF_XM_BF", "CV_COUNT_DEF_XM_ BF", "LACK"]
df["COL3"] = ["LACK", "CV_COUNT_ABC_XM_BF, CV_COUNT_DEF_XM_BF", "CV_COUNT_DEF_XM_ BF xx"]

df:

COL1   | COL2                                     | COL3
-------|------------------------------------------|---------
111    | CV_COUNT_ABC_XM_BF, CV_COUNT_DEF_XM_BF   | LACK
222    | CV_COUNT_DEF_XM_ BF                      | CV_COUNT_ABC_XM_BF, CV_COUNT_DEF_XM_BF
333    | LACK                                     | CV_COUNT_DEF_XM_ BF xx
...    | ...                                      | ...

And I need to:

if there is only "LACK" in COL2 or COL3 stay it
if COL2 or COL3 contains "ABC" or "DEF" change values to stay only "ABC" or "DEF", but if values containing "ABC" or "DEF" are mentioned after coma, replaced values have to be also mentioned after coma
delete any other values in columns (if exists like for ID=333 in COL2 "xx") except values "ABC" or "DEF" or coma or "LACK"

So, as a result I need something like below:

COL1   | COL2                                     | COL3
-------|------------------------------------------|---------
111    | ABC, DEF                                 | LACK
222    | DEF                                      | ABC, DEF
333    | LACK                                     | DEF
...    | ...                                      | ...

How can I do taht in Python Pandas ?

CodePudding user response：

Use Series.str.findall for get ABC or DEF or LACK with ^ for start of string and $ for end of string and then join values by Series.str.join:

cols = ['COL2','COL3']

df[cols] = df[cols].apply(lambda x: x.str.findall('(ABC|DEF|^LACK$)').str.join(', '))
print (df)
   COL1      COL2      COL3
0   111  ABC, DEF      LACK
1   222       DEF  ABC, DEF
2   333      LACK       DEF

Another ida is also get comma with space:

cols = ['COL2','COL3']

df[cols] = df[cols].apply(lambda x: x.str.findall('(ABC|DEF|,\s |^LACK$)').str.join(''))
print (df)
   COL1      COL2      COL3
0   111  ABC, DEF      LACK
1   222       DEF  ABC, DEF
2   333      LACK       DEF