Pandas dataframe with multiindex column - merge levels

There is potentially a better way, more pythonic way to flatten MultiIndex columns.

1. Use map and join with string column headers:

grouped.columns = grouped.columns.map('|'.join).str.strip('|')print(grouped)

Output:

   code  colour   size|sum  size|average  size|size  size|idxmax  \0    one   black       862     53.875000         16           14   1    one   white       554     46.166667         12           18   2  three   black       842     49.529412         17           90   3  three   white       740     56.923077         13           97   4    two   black      1541     61.640000         25           50      scaled_size|sum  scaled_size|average  scaled_size|size  scaled_size|idxmax  0             6980           436.250000                16                  77  1             6101           508.416667                12                  13  2             7889           464.058824                17                  64  3             6329           486.846154                13                  73  4            12809           512.360000                25                  23  

2. Use map with format for column headers that have numeric data types.

grouped.columns = grouped.columns.map('{0[0]}|{0[1]}'.format) 

Output:

   code| colour|  size|sum  size|average  size|size  size|idxmax  \0    one   black       734     52.428571         14           30   1    one   white      1110     65.294118         17           88   2  three   black       930     51.666667         18            3   3  three   white      1140     51.818182         22           20   4    two   black       656     38.588235         17           77   5    two   white       704     58.666667         12           17      scaled_size|sum  scaled_size|average  scaled_size|size  scaled_size|idxmax  0             8229           587.785714                14                  57  1             8781           516.529412                17                  73  2            10743           596.833333                18                  21  3            10240           465.454545                22                  26  4             9982           587.176471                17                  16  5             6537           544.750000                12                  49 

3. Use list comprehension with f-string for Python 3.6+:

grouped.columns = [f'{i}|{j}' if j != '' else f'{i}' for i,j in grouped.columns]

Output:

    code colour  size|sum  size|average  size|size  size|idxmax  \0    one  black      1003     43.608696         23           76   1    one  white      1255     59.761905         21           66   2  three  black       777     45.705882         17           39   3  three  white       630     52.500000         12           23   4    two  black       823     54.866667         15           33   5    two  white       491     40.916667         12           64      scaled_size|sum  scaled_size|average  scaled_size|size  scaled_size|idxmax  0            12532           544.869565                23                  27  1            13223           629.666667                21                  13  2             8615           506.764706                17                  92  3             6101           508.416667                12                  43  4             7661           510.733333                15                  42  5             6143           511.916667                12                  49  

you could always change the columns:

grouped.columns = ['%s%s' % (a, '|%s' % b if b else '') for a, b in grouped.columns]

Based on Scott Boston's answer, little update(it will be work for 2 or more levels column):

temp.columns.map(lambda x: '|'.join([str(i) for i in x]))

Thank you, Boston!