Applying aggregate function on columns of Pandas pivot table -

i generated following pivot table via taking maximum of values in z column:

   val x   x1     x2 y   y1  y2 y1  y2 id    9   1  5  11 b    8  10  7   6 

after taking max on z values, need report mean(y1,y2). desired table is:

    val x    x1          x2 y    mean(y1,y2) mean(y1,y2) id    5           8 b    9           6.5 

how can achieve using pandas?

my mwe:

#!/usr/bin/python pandas import dataframe import pandas pd import numpy np  data=pd.read_table('data.txt') pv=data.pivot_table(index=['id'], columns=['x','y'], values=['val'], aggfunc=np.max ) print pv 

data.txt:

id  x   y   z   val   x1  y2  z1  1 b   x1  y1  z2  2   x2  y2  z2  3   x1  y1  z4  4   x2  y1  z1  5 b   x2  y2  z3  6 b   x2  y1  z2  7 b   x1  y1  z3  8   x1  y1  z3  9 b   x1  y2  z3  10   x2  y2  z2  11 

you can pass level param , axis calc mean desired axis level:

in [142]: pv.mean(level='x', axis=1)  out[142]: x   x1   x2 id             5  8.0 b    9  6.5