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 ?
>Solution :
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