Trying to sort multiindex index using categorical index

user_16891669 0
Viewed 133

I have a MultiIndexed dataframe like below

months = ['January','February','March','April','May','June','July','August','September','October','November','December']
df = pd.DataFrame({'col' : np.arange(1,25,1)},\
                  index = pd.MultiIndex.from_product([months, [1,2]], names = ['idx_1', 'idx_2'])).sort_index()

print(df)

                 col
idx_1     idx_2     
April     1        7
          2        8
August    1       15
          2       16
December  1       23
          2       24
February  1        3
          2        4
January   1        1
          2        2
July      1       13
          2       14
June      1       11
          2       12
March     1        5
          2        6
May       1        9
          2       10
November  1       21
          2       22
October   1       19
          2       20
September 1       17
          2       18

I wanted to sort the index so I created a CategoricalIndex and assigned it to the level_0 of the MultiIndex. However, even after that the sort command doesn't sort the index.

cidx = pd.CategoricalIndex(data = df.index.get_level_values(0).unique(), categories = months, ordered=True)
df.index = df.index.set_levels(cidx, level = 0)
df = df.sort_index(level = 0)
print(df)

It will produce the same output as above. I think it's a bug. Can anyone help me out?

Here's the level_0 of the MultiIndex

print(df.index.get_level_values(0))

CategoricalIndex(['April', 'April', 'August', 'August', 'December', 'December',
                  'February', 'February', 'January', 'January', 'July', 'July',
                  'June', 'June', 'March', 'March', 'May', 'May', 'November',
                  'November', 'October', 'October', 'September', 'September'],
                 categories=['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', ...], ordered=True, dtype='category', name='idx_1')
multi-index pandas dataframe
1 Answers

First, I must say that this problem is a Pandas bug.

In this problem the Dataframe has been sorted after it has been created. Using sort command like sort_index() in earlier steps causes Pandas to have sorting problems. Categorical index sorting fails with lexicographically ordered data. Several tricks can be used to solve this problem in these cases.

  1. Avoid using commands that sort the index lexicographically. lexicographically ordered data cannot be sorted using a categorical index.

  2. You can first reset the desired index level using reset_index(). Then you can make a CategoricalIndex and use it to categorize the column.

    months = ['January', 'February', 'March', 'April', 'May', 'June', 
          'July', 'August', 'September', 'October', 'November', 'December']    
df = pd.DataFrame({'col': np.arange(1, 25, 1)},
          index=pd.MultiIndex.from_product([months, [1, 2]], names=['idx_1', 'idx_2'])).sort_index()


df.reset_index(level=0, inplace=True)

df['idx_1'] = pd.CategoricalIndex(df['idx_1'], months,  ordered=True)

df.set_index('idx_1', append=True, inplace=True)

df.swaplevel(0, 1).sort_index(level=0)

  3. You can first create a CategoricalIndex and assign it to the level_0 of the MultiIndex. Afterward, you need to eliminate the lexicographical sort from the zero-level index. You can use two consecutive sort_index() here as a trick.

    months = ['January', 'February', 'March', 'April', 'May', 'June',
   'July', 'August', 'September', 'October', 'November', 'December']

df = pd.DataFrame({'col': np.arange(1, 25, 1)},
          index=pd.MultiIndex.from_product([months, [1, 2]], names=['idx_1', 'idx_2'])).sort_index()

categories = pd.CategoricalIndex(df.index.levels[0], categories=months, ordered=True)

df.index = df.index.set_levels(categories, level=0)

df.sort_index(level=1).sort_index(level=0)

output:

                 col
idx_1     idx_2     
January   1        1
          2        2
February  1        3
          2        4
March     1        5
          2        6
April     1        7
          2        8
May       1        9
          2       10
June      1       11
          2       12
July      1       13
          2       14
August    1       15
          2       16
September 1       17
          2       18
October   1       19
          2       20
November  1       21
          2       22
December  1       23
          2       24
user_107894390
Related