Handling multi-index columns in pandas-CodePudding

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!

CodePudding user response：

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