1

I have a dataframe df

first        bar                 baz           
second       one       two       one       two 
A       0.487880 -0.487661 -1.030176  0.100813 
B       0.267913  1.918923  0.132791  0.178503
C       1.550526 -0.312235 -1.177689 -0.081596

I'd like to add a average columns and then move the average to the front

df['Average'] = df.mean(level='second', axis='columns')  #ERROR HERE
cols = df.columns.tolist()
df = df[[cols[-1]] + cols[:-1]]

I get the error:

ValueError: Wrong number of items passed 2, placement implies 1

Maybe, I could add each column df['Average', 'One'] = ... in the mean one at a time but that seems silly especially as the real life index is more complicated.

Edit: (Frame Generation) 

arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
    ['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two']]

tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
df = DataFrame(np.random.randn(3, 8), index=['A', 'B', 'C'], columns=index)
rhaskett
  • 1,864
  • 3
  • 29
  • 48

2 Answers2

2

I'm not sure on your target output. Something like this?

df2 = df.mean(level='second', axis='columns')
df2.columns = pd.MultiIndex.from_tuples([('mean', col) for col in df2])
>>> df2
       mean          
        one       two
A -0.271148 -0.193424
B  0.200352  1.048713
C  0.186419 -0.196915

>>> pd.concat([df2, df], axis=1)
       mean                 bar                 baz          
        one       two       one       two       one       two
A -0.271148 -0.193424  0.487880 -0.487661 -1.030176  0.100813
B  0.200352  1.048713  0.267913  1.918923  0.132791  0.178503
C  0.186419 -0.196915  1.550526 -0.312235 -1.177689 -0.081596

You are getting the error because your mean operation results in a dataframe (with two columns in this case). You are then trying to assign this result into one column in the original dataframe.

Alexander
  • 105,104
  • 32
  • 201
  • 196
  • Works. I'm not sure I 100% understand the error. The other `level=first` have two columns each. I assumed I was inserting on that level. – rhaskett Sep 05 '18 at 21:50
  • You are not inserting. `level='first'` means you are grouping the items in that level (e.g. 'bar' and 'baz') and taking the mean in this case. The result is simply a dataframe as I have tried to demonstrate above which uses `level='second'` to avoid confusion with your example in the comment. – Alexander Sep 05 '18 at 21:54
1

pandas.concat

df.join(pd.concat([df.mean(level='second', axis='columns')], axis=1, keys=['Average']))

first        bar                 baz             Average          
second       one       two       one       two       one       two
A       0.255301  0.286846  1.027024 -0.060594  0.641162  0.113126
B      -0.608509 -2.291201  0.675753 -0.416156  0.033622 -1.353679
C       2.714254 -1.330621 -0.099545  0.616833  1.307354 -0.356894

stack/unstack

Not necessarily efficient, but neat

df.stack().assign(Average=df.mean(level='second', axis='columns').stack()).unstack()

first        bar                 baz             Average          
second       one       two       one       two       one       two
A       0.255301  0.286846  1.027024 -0.060594  0.641162  0.113126
B      -0.608509 -2.291201  0.675753 -0.416156  0.033622 -1.353679
C       2.714254 -1.330621 -0.099545  0.616833  1.307354 -0.356894
piRSquared
  • 285,575
  • 57
  • 475
  • 624