Merge MultiIndex columns together into 1 level

Question

Here's some data from another question:

date       type       value
1/1/2016   a          1
1/1/2016   b          2
1/1/2016   a          1
1/1/2016   b          4
1/2/2016   a          1
1/2/2016   b          1

Run this line of code:

x = df.groupby(['date', 'type']).value.agg(['sum', 'max']).unstack()

x should look like this:

         sum    max   
type       a  b   a  b
date                  
1/1/2016   2  6   1  4
1/2/2016   1  1   1  1

I want to combine the columns on the upper and lower level to get this:

           sum_a  sum_b   max_a  max_b
date                  
1/1/2016   2       6        1       4
1/2/2016   1       1        1       1

Is there an easy way to do this?

score 15 · Accepted Answer · edited Aug 25 '17 at 09:46

15

There's discussion of this here:

Python Pandas - How to flatten a hierarchical index in columns

And the consensus seems to be:

x.columns = ['_'.join(col) for col in x.columns.values]
print(x)
          sum_a  sum_b  max_a  max_b
date                                
1/1/2016      2      6      1      4
1/2/2016      1      1      1      1

Would be nice if there was an inbuilt method for this, but there doesn't seem to be.

edited Aug 25 '17 at 09:46

cs95

379,657
97
704
746

answered Aug 25 '17 at 09:35

greg_data

2,247
13
20

1

Thanks. This seems like the least headache version. – cs95 Aug 25 '17 at 09:40
I kinda feel that this question is a dupe now of the linked question, should we mark as dupe? – EdChum Aug 25 '17 at 09:41
@EdChum Yeah, I already came across that, but it was so verbose that my eyes glazed over it. I think this would be good in that it's a much simpler formulation of the same problem. I leave it to you, resident pandas gold badge :) – cs95 Aug 25 '17 at 09:43
1

I'll think I'll dupe hammer this then, note that in your case the `strip` is superfluous – EdChum Aug 25 '17 at 09:45

score 2 · Answer 2 · answered Aug 25 '17 at 09:35

Very similar solution to the above using zip:

x.columns = [x + '_' + i for x, i in zip(x.columns.get_level_values(0), x.columns.get_level_values(1))]
x
          sum_a  sum_b  max_a  max_b
date                                
1/1/2016      2      6      1      4
1/2/2016      1      1      1      1

Merge MultiIndex columns together into 1 level

2 Answers2

Linked