python - Pandas: use of aggregate with a MultiIndex -

python - Pandas: use of aggregate with a MultiIndex -

i have question right utilize of agg in pandas. specific problem working on in field of finance and, more specifically, calculate liquidity measure total limit order book.

my info contain inquire side of order book (which represents how many shares people want sell @ particular moment , @ price) , want calculate hypothetical cost buying 50 shares @ specific moment in time. assume illustration order book stock x @ 9am looks this:

example_data=pd.dataframe({'price':[100.023,100.031,100.039,100.109,100.219 ],'avail_shares': [40,1,20,23,15],'midpoint':[99.996 ,99.996 ,99.996 ,99.996,99.996 ]})

where cost price @ shares sold, avail_shares number of shares available @ each cost , midpoint average of best inquire , bid cost in order book. liquidity measure takes business relationship big order can nail multiple cost levels @ 1 time (i.e. ‘walk book’) define next cost-to-trade (ctt) function:

def ctt_ask(dfrm,level=50): dfrm['cumshares']=dfrm['avail_shares'].cumsum() dfrm['indicator']=0 dfrm['indicator'].ix[dfrm.cumshares<level,]=dfrm.cumshares dfrm['indicator'].ix[(dfrm.cumshares>level) & (dfrm.cumshares.shift(1)<level),]=(level- dfrm.cumshares.shift(1)) liquidity_measure=((dfrm.price-dfrm.midpoint)*dfrm.indicator).sum() homecoming liquidity_measure

this works fine (i.e. ctt_ask(example_data) yields 2.90) above illustration real dataset has several stocks , many date times (it has multiindex). when utilize groupby , agg apply function every stock-date time combination ( full_book_ask.groupby(level=[0,1]).agg(ctt_ask)) error: keyerror: 'avail_shares'. unusual because have column named avail_shares in actual dataset. have tried same apply functionality raises error message exception: cannot handle non-unique multi-index! . can't seem figure out i'm doing wrong here. input much appreciated!

python pandas aggregate finance