Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pandas reset inner level of MultiIndex

Tags:

python

pandas

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.

like image 614
KOB Avatar asked Aug 13 '18 18:08

KOB


People also ask

How do I reset MultiIndex pandas?

A multi-index dataframe has multi-level, or hierarchical indexing. We can easily convert the multi-level index into the column by the reset_index() method. DataFrame. reset_index() is used to reset the index to default and make the index a column of the dataframe.

How do I reset my index to default?

Use DataFrame.reset_index() function We can use DataFrame. reset_index() to reset the index of the updated DataFrame. By default, it adds the current row index as a new column called 'index' in DataFrame, and it will create a new row index as a range of numbers starting at 0.

How do I remove a level from a data frame?

DataFrame - droplevel() function The droplevel() function is used to remove index / column level(s) from a given DataFrame. If a string is given, must be the name of a level If list-like, elements must be names or positional indexes of levels.


1 Answers

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...

like image 100
cs95 Avatar answered Sep 23 '22 23:09

cs95