How to split and concate text separated by commas with the corresponding text in the pposing column in pandas

Question

So I'm trying to join text separated by commas in one column, and join it with the text in corresponding position in the opposing column. English isn't my first language but I think that the photo below with the Desired output column, will clarify what I need to do.

enter image description here

CNTRY       CUSTOMERID          ACC_TYPE                TheDesiredResult
JO,AE,QA    HJM23,JKU78,OI9PUJ  AH,SH,AS                JO,HJM23,AH - AE,JKU78,SH - QA,OI9PUJ,AS

I'm trying to do this in pandas, as in excel text-to-column can't achieve the desired result because each row will have different amount of strings separated by commas. I tried youtube and couldn't piece a code to solve this.

Thanks in Advance!

I tried spiting the data separated by commas into different columns and then joining them back, but it was a huge fail. since the amount of columns needed for each attribute would be insane by this approach.

Answer 1

Assuming you have the same number of (,) in each column (per row), you can try :

# optional if you have only 3 columns
cols = ["CNTRY", "CUSTOMERID", "ACC_TYPE"]

splits = df[cols].apply(lambda s: s.str.split(",\s*"))

df["TheDesiredResult"] = " - ".join(
    ",".join(lst) for vals in splits.to_numpy() for lst in zip(*vals))

Output :

CNTRY	CUSTOMERID	ACC_TYPE	TheDesiredResult
JO,AE,QA	HJM23,JKU78,OI9PUJ	AH,SH,AS	JO,HJM23,AH - AE,JKU78,SH - QA,OI9PUJ,AS