Concat strings in multiple columns without duplicates

Question

I have a data like below:

# df
Col1   Col2   Col3   Col4
AAA
AAA    AAA    BBB
AAA    CCC

I want to concat them without duplicates.

Col1   Col2   Col3   Col4   COMBINE
AAA                         AAA
AAA    AAA    BBB           AAA/BBB
AAA    CCC                  AAA/CCC

The code I tried:

df["COMBINE"] = df[df.filter(regex='^Col',axis=1).columns].apply(lambda x: '/'.join(pd.unique(x)),axis=1)

But I got:

Col1   Col2   Col3   Col4   COMBINE
AAA                         AAA/
AAA    AAA    BBB           AAA/BBB/
AAA    CCC                  AAA/CCC/

Chain your solution with .str.strip() would work for this data. — Quang Hoang
– Quang Hoang, Commented Oct 21, 2021 at 14:35

Mahdi F. · Accepted Answer · 2021-10-21 14:32:55Z

1

This happen because you have empty string in your columns, you can change code like below and get waht you want:

>>> df.replace('', np.nan, inplace=True)
>>> df["COMBINE"] = df[df.filter(regex='^Col',axis=1).columns].apply(lambda x: '/'.join(x.dropna().unique()),axis=1)

>>> df

Col1   Col2   Col3   Col4   COMBINE
AAA                         AAA
AAA    AAA    BBB           AAA/BBB
AAA    CCC                  AAA/CCC

answered Oct 21, 2021 at 14:32

Mahdi F.

24.1k5 gold badges25 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Quang Hoang · Accepted Answer · 2021-10-21 14:31:53Z

1

Try:

df['Combine'] = df.apply(lambda x: '/'.join(x.dropna().unique()), axis=1)

Note: if you have empty string instead of NaN, then do: df.replace('', np.nan).apply(...).

Output:

  Col1  Col2  Col3  Col4  Combine
0  AAA  None  None   NaN      AAA
1  AAA   AAA   BBB   NaN  AAA/BBB
2  AAA   CCC  None   NaN  AAA/CCC

answered Oct 21, 2021 at 14:31

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

1 Comment

Mahdi F. Over a year ago

You edited very good , lol ;))

mozway · Accepted Answer · 2021-10-21 14:32:49Z

1

You could cleanup after:

df['Combine'] = df.fillna('').apply('/'.join, axis=1).str.replace('/+(?=/|$)', '', regex=True)

How it works: If one or several / are followed by another / or the end of line -> remove

[AAA, None, None, NaN] -> [AAA, '', '', ''] -> AAA/// -> AAA

output:

  Col1  Col2  Col3  Col4      Combine
0  AAA  None  None   NaN          AAA
1  AAA   AAA   BBB   NaN  AAA/AAA/BBB
2  AAA   CCC  None   NaN      AAA/CCC

answered Oct 21, 2021 at 14:32

mozway

267k13 gold badges56 silver badges106 bronze badges

Collectives™ on Stack Overflow

Concat strings in multiple columns without duplicates

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related