Pandas reset inner level of MultiIndex

  • A+
Category:Languages

I have a DF in the following format:

                   col1    col2 ID          Date  1    1993-12-31      4       6       1994-12-31      8       5       1995-12-31      4       7       1996-12-31      3       3  2    2000-12-31      7       8       2001-12-31      5       9       2002-12-31      8       4 

And I want to reset the 'Date' index giving the following:

             col1    col2 ID    Date  1       0      4       6          1      8       5          2      4       7          3      3       3  2       0      7       8          1      5       9          2      8       4 

I thought simply df.reset_index(level='Date', inplace=True, drop=True) would do it, but it does not.

 


Using pd.MultiIndex.from_arrays and groupby + cumcount.

df.index = pd.MultiIndex.from_arrays(     [df.index.get_level_values(0), df.groupby(level=0).cumcount()],     names=['ID', 'Date']) 

df          col1  col2 ID Date             1  0        4     6    1        8     5    2        4     7    3        3     3 2  0        7     8    1        5     9    2        8     4 

This won't generalise to N levels, but there should be a df.index.set_levels equivalent I'm forgetting...

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: