Pandas: How to merge columns containing the same name within a single data frame?

Question

I have a dataframe extracted from an excel file which I have manipulated to be in the following form (there are mutliple rows but this is reduced to make my question as clear as possible):

        |A|B|C|A|B|C|
index 0: 1 2 3 4 5 6

As you can see there are repetitions of the column names. I would like to merge this dataframe to look like the following:

        |A|B|C|
index 0: 1 2 3 
index 1: 4 5 6

I have tried to use the melt function but have not had any success thus far.

what if col A was repeated 3 times but C 0 times? or will every col repeat exactly once as your example? — Shihab Shahriar Khan
– Shihab Shahriar Khan, Commented Aug 16, 2019 at 9:46
In my specific use case each unique data column name will always repeat the same number of times, usually only twice. So I think the answer from @Ferran should always work. — cwm5412
– cwm5412, Commented Aug 16, 2019 at 10:28

Ferran · Accepted Answer · 2019-08-16 09:58:21Z

3

import pandas as pd
df = pd.DataFrame([[1,2,3,4,5,6]], columns = ['A', 'B','C','A', 'B','C'])
df
       A  B  C  A  B  C
    0  1  2  3  4  5  6
pd.concat(x for _, x in df.groupby(df.columns.duplicated(), axis=1))
       A  B  C
    0  1  2  3
    0  4  5  6

answered Aug 16, 2019 at 9:58

Ferran

8409 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

Pandas: How to merge columns containing the same name within a single data frame?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related