Search code examples
pythonpandasdataframeconcatenation

Combine two columns, ignore empty cells and add a separator


I need to combine column 1 and 2 in 3 with separator , but ignore empty cells. So I have this dataframe:

         1     2
0  A, B, B     D
1           C, D
2  B, B, C  D, A

And need to create column 3 (desired output):

         1     2              3
0  A, B, B     D     A, B, B, D
1           C, D           C, D
2  B, B, C  D, A  B, B, C, D, A

So as you see here, empty sell was ignored and , separate elements between in df["3"] (B, B, C, D, A).

I try to do this with simple concatenation, but didn't succeed.

If I simple concatenate df["1"] + df["2"] I will get that the last element of first column combine with first element of last column (BD, CD):

         1     2            3
0  A, B, B     D     A, B, BD
1           C, D         C, D
2  B, B, C  D, A  B, B, CD, A

If add ", " (df["1"] + ", " + df["2"]):

         1     2              3
0  A, B, B     D     A, B, B, D
1           C, D         , C, D
2  B, B, C  D, A  B, B, C, D, A

You will see that each empty cell replace with ", " and added to df["3"] (example = , C, D, but I need C, D).

Code for reproduce:

import pandas as pd
df = pd.DataFrame({"1":["A, B, B","","B, B, C"], "2":["D","C, D","D, A"]})
print(df)

Solution

  • Use str.strip for possible removing , from both sides:

    (df["1"] + ", " + df["2"]).str.strip(', ')