Handling multi-index columns in pandas

Question

I have a dataframe as follows:

CAR  MAKER  MODEL    0       1
CYL                4  6    4  6

     HONDA  CIVIC  5  0    0   0
     TOYOTA HLAND  0  0    10  15
     HONDA  PILOT  0  0    10   2
     TOYOTA COROLLA 0 5    5    4

The df is an MultiIndex with CAR having 0 and 1 and for each CAR there are 4 and 6 as possibilities for CYL I want to arrange the df such that it results in the following output:

MAKER   MODEL   0_4  0_6  1_4 1_6
HONDA   CIVIC   5     0    0   0
TOYOTA  HLAND   0     0    10  15
HONDA   PILOT   0     0    10   2
TOYOTA  COROLLA 0     5     5   4

I have tried to reset_index(col_level=2) but no success so far. Any ideas on which method I can use to reorganize the df? Its essentially the reverse of the https://pandas.pydata.org/docs/reference/api/pandas.MultiIndex.from_frame.html#pandas.MultiIndex.from_frame df.columns is:

MultiIndex([(  'MAKER', ''),
            ('MODEL', ''),
            (                0,  4),
            (                0,  6),
            (                1,  4),
            (                1,  6)],
           names=['CAR', 'CYL'])

Thanks!

Can you post df.to_dict ? It's not very clear if MAKER MODEL are two columns or index. — akuiper
– akuiper, Commented Feb 24, 2022 at 19:02
@Psidom they are two columns. the actual DF is quite large to do df.to_dict but here is df.columns — FlyingPickle
– FlyingPickle, Commented Feb 24, 2022 at 19:05
No need to post all rows. Posting df.head(6).to_dict() is enough. — akuiper
– akuiper, Commented Feb 24, 2022 at 19:06

Corralien · Accepted Answer · 2022-02-24 19:11:47Z

1

Use:

df.columns = df.columns.map(lambda x: f"{x[0]}_{x[1]}" if x[1] else f"{x[0]}")
print(df)

# Output
    MAKER    MODEL  0_4  0_6  1_4  1_6
0   HONDA    CIVIC    5    0    0    0
1  TOYOTA    HLAND    0    0   10   15
2   HONDA    PILOT    0    0   10    2
3  TOYOTA  COROLLA    0    5    5    4

answered Feb 24, 2022 at 19:11

Corralien

121k8 gold badges44 silver badges69 bronze badges

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

1 Comment

FlyingPickle Over a year ago

OMG :) thats so elegant!!! Thanks!

Collectives™ on Stack Overflow

Handling multi-index columns in pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related