Siraj S. - 9 months ago 62
Python Question

apply reptitive sequence on multiindex dataframe

i have a multiindex dataframe where the inner most index can be of unequal length, I want to be able to add another column with repetitive values, but since the row counts are unequal, I am not able to so with:

df['marker'] = np.repeat([0,1,2], len(df), axis = 0)
ValueError: Length of values does not match length of index


Here is my dataframe sample:

                               close
date    ticker      expiry_dt
2016-07-27  BHEL    2016-07-28  147
2016-08-25  147
2016-09-29  150
2016-07-28  BHEL    2016-07-28  149
2016-08-25  147
2016-09-29  149
2016-07-29  BHEL    2016-08-25  149
2016-09-29  149


as you can see, the innermost index ('expirty_dt') is of unequal length. My desired output is:

I can maybe achieve this through a loop, but I have a large database and loop will be inefficient in doing so on a daily basis. Thanks in advance

Answer Source

you want

df.groupby(['date', 'ticker']).cumcount()