How to drop duplicate multiindex column in Pandas

Question

Been searching the net, but I cannot find any resource on deleting duplicate multiindex column name.

Given a multiindex as below

      level1                    
      level2                    
           A   B   C   B   A   C
ONE       11  12  13  11  12  13
TWO       21  22  23  21  22  23
THREE     31  32  33  31  32  33

Drop duplicated B and C

Expected output

      level1                    
      level2                    
           A   B   C   A
ONE       11  12  13  11
TWO       21  22  23  21
THREE     31  32  33  31

Code

import pandas as pd
df = pd.DataFrame({'A': [11, 21, 31],
               'B': [12, 22, 32],
               'C': [13, 23, 33]},
              index=['ONE', 'TWO', 'THREE'])

df2 = pd.DataFrame({'B': [11, 21, 31],
               'A': [12, 22, 32],
               'C': [13, 23, 33]},
              index=['ONE', 'TWO', 'THREE'])

df.columns = pd.MultiIndex.from_product([['level1'],['level2'],df.columns ])
df2.columns = pd.MultiIndex.from_product([['level1'],['level2'],df2.columns ])
df=pd.concat([df,df2],axis=1)

-Drop by index not working

the drop index function cant tell which to drop, since they have the same names. df.iloc[:, :-2] fixes it; I would however, caution against using same names for columns — sammywemmy
– sammywemmy, Commented Aug 7, 2021 at 6:21
Thanks for the advice. This happen due to the fact several df been concatenated — rpb
– rpb, Commented Aug 7, 2021 at 6:23
But, why do you want the duplicated column names for A? I'm afraid, normal dropping is not going to work for your use case, you need to have some custom dropping mechanism. — ThePyGuy
– ThePyGuy, Commented Aug 7, 2021 at 6:25
This is a simple example. Please ignore the need of maintaining duplicated A @ThePyGuy — rpb
– rpb, Commented Aug 7, 2021 at 6:26

Anurag Dabas · Accepted Answer · 2021-08-07 07:59:21Z

2

You can try:

mask=(df.T.duplicated() | (df.columns.get_level_values(2).isin(['A','D'])))

Finally:

df=df.loc[:, mask]
#OR
#df=df.T.loc[mask].T

edited Aug 7, 2021 at 7:59

answered Aug 7, 2021 at 7:19

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

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

4 Comments

rpb Over a year ago

It will be interesting to generalize this suggestion for larger set of column at the second level.Say for example,at the second level we have A,B,C ,D, and we want to remove both B and C and maintain both A and D.

Anurag Dabas Over a year ago

@balandongiv In that case use isin() updated answer...kindly have a look :)

mozway Over a year ago

Can't you directly do df.loc[:, mask]? +1

Anurag Dabas Over a year ago

@balandongiv kindly check the updated answer :)

rpb · Accepted Answer · 2021-08-07 08:18:18Z

0

Adaptation to df.T.drop_duplicates().T by Anurag Dabas.

Select only unique column and its value

drop_col=['B','C']
drop_single=[df.loc [:, (slice ( None ), slice ( None ), DCOL)].T.drop_duplicates().T for DCOL in drop_col]

Drop the columns from the df

df=df.drop ( drop_col, axis=1, level=2 )

Combine everything to get the intended output

df=pd.concat([df,*drop_single],axis=1)

Complete solution

import pandas as pd
df = pd.DataFrame({'A': [11, 21, 31],
               'B': [12, 22, 32],
               'C': [13, 23, 33]},
              index=['ONE', 'TWO', 'THREE'])

df2 = pd.DataFrame({'B': [11, 21, 31],
               'A': [12, 22, 32],
               'C': [13, 23, 33]},
              index=['ONE', 'TWO', 'THREE'])

df.columns = pd.MultiIndex.from_product([['level1'],['level2'],df.columns ])
df2.columns = pd.MultiIndex.from_product([['level1'],['level2'],df2.columns ])
df=pd.concat([df,df2],axis=1)
drop_col=['B','C']
drop_single=[df.loc [:, (slice ( None ), slice ( None ), DCOL)].iloc [:, 1] for DCOL in drop_col]

df=df.drop ( drop_col, axis=1, level=2 )
df_unique=pd.concat(drop_single,axis=1)
df=pd.concat([df,df_unique],axis=1)
print(df)

edited Aug 7, 2021 at 8:18

answered Aug 7, 2021 at 7:29

rpb

3,3073 gold badges32 silver badges72 bronze badges

1 Comment

rpb Over a year ago

But still, the suggestion by Anurag is much more compact stackoverflow.com/a/68690202/6446053

Mohammed Hamdan · Accepted Answer · 2021-08-07 06:30:44Z

-1

You can try this:

# Drop last 2 columns of dataframe
df.drop(columns=df.columns[-2:], 
        axis=1, 
        inplace=True)

answered Aug 7, 2021 at 6:30

Mohammed Hamdan

991 silver badge8 bronze badges

2 Comments

rpb Over a year ago

Thanks for the effort @Mohammad, but this suggestion is not robust for not symmetry column orientation

rpb Over a year ago

Im down voting this as this does not solve the issue

Collectives™ on Stack Overflow

How to drop duplicate multiindex column in Pandas

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related