Concatenate columns if same column name in a dataframe

Question

I've tried re-searching on stack over but couldn't get a solution for my problem

I want to concatenate the columns if they have the same column name:

Example:

input = { 'A' : [0,1,0,1,0], 'B' : [0,1,1,1,1], 'C':[1,1,1,1,0],
          'D' : [1,1,0,0,0], 'E' : [1,0,1,0,1]}

df = pd.DataFrame(input)
df.columns = ['A','B','C','C','B']

   A  B  C  C  B
0  0  0  1  1  1
1  1  1  1  1  0
2  0  1  1  0  1
3  1  1  1  0  0
4  0  1  0  0  1

Desired output:

   A    B    C
0  0  0;1  1;1
1  1  1;0  1;1
2  0  1;1  1;0
3  1  1;0  1;0
4  0  1;1  0;0

Any pointers are highly appreciated.

jezrael · Accepted Answer · 2021-01-21 08:36:45Z

5

You can grouping by columns names and for duplicates get DataFrame, so is used apply with join for join per rows:

DF = DF.astype(str).groupby(DF.columns, axis=1).agg(lambda x: x.apply(';'.join, 1))

Or:

DF = DF.astype(str).groupby(DF.columns, axis=1).agg(lambda x: [';'.join(y) for y in x.to_numpy()])

print (DF)
   A    B    C
0  0  0;1  1;1
1  1  1;0  1;1
2  0  1;1  1;0
3  1  1;0  1;0
4  0  1;1  0;0

answered Jan 21, 2021 at 8:36

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Comments

Robot Jung · Accepted Answer · 2021-01-21 08:45:52Z

2

you can try this code:

def function(x):
    return x.apply(';'.join, 1)


DF = DF.astype(str).groupby(DF.columns, axis=1).agg(function)

answered Jan 21, 2021 at 8:45

Robot Jung

3971 gold badge4 silver badges14 bronze badges

Comments

Mykola Zotko · Accepted Answer · 2021-01-21 09:46:03Z

1

You can transpose, groupby, join strings and transpose back:

df.T.astype('str').groupby(level=0).agg(';'.join).T

Output:

   A    B    C
0  0  0;1  1;1
1  1  1;0  1;1
2  0  1;1  1;0
3  1  1;0  1;0
4  0  1;1  0;0

edited Jan 21, 2021 at 9:46

answered Jan 21, 2021 at 9:28

Mykola Zotko

18.2k6 gold badges88 silver badges90 bronze badges

Collectives™ on Stack Overflow

Concatenate columns if same column name in a dataframe

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related