Siraj S. Siraj S. - 11 months ago 73
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:

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:

enter image description here

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()