Resetting multi-index dataframe with categorical index failing with ValueError: The result input mus-CodePudding

I've the following multiindex dataframe:

                              item_quantity  current_stock_qty_9XU7
month name zone  product type                                       
January    East  Product             18111.0                 17799.0    
                 Subtotal            19343.0                 17803.0
           North Combo                2457.0                    34.0
                 Product             16900.0                 31708.0
                 Subtotal            19357.0                 31742.0
           South Combo                3042.0                    24.0                 
                 Product             19453.0                  6630.0
                 Subtotal            22495.0                  6654.0
           West  Combo                2903.0                     6.0
                 Product             19185.0                114959.0
                 Subtotal            22088.0                114965.0
February   East  Combo                 845.0                     0.0
                 Product              6820.0                     0.0
                 Subtotal             7665.0                     0.0
           North Combo                2050.0                     0.0
                 Product             14054.0                     0.0
                 Subtotal            16104.0                     0.0

The index for month name has to be a Categorical Index because I need the month order to be preserved during sort. But when I try to reset_index to a single index, pandas throws a ValueError:

ValueError                                Traceback (most recent call last)
~/github-repos/dolphin/Dolphin_Dashboard/app/compute_engine_wrapper.py in transform_df(df, rows, columns, measure_col_map, measure_order, hide_subtotals, measures_first)
   1120             try:
-> 1121                 pt.reset_index(level=1, inplace=True)
   1122             except ValueError:

~/github-repos/dolphin/dd_env/lib/python3.8/site-packages/pandas/core/frame.py in reset_index(self, level, drop, inplace, col_level, col_fill)
   4707                 # to ndarray and maybe infer different dtype
-> 4708                 level_values = _maybe_casted_values(lev, lab)
   4709                 new_obj.insert(0, name, level_values)

~/github-repos/dolphin/dd_env/lib/python3.8/site-packages/pandas/core/frame.py in _maybe_casted_values(index, labels)
   4658                     if mask.any():
-> 4659                         values, changed = maybe_upcast_putmask(values, mask, np.nan)
   4660 

~/github-repos/dolphin/dd_env/lib/python3.8/site-packages/pandas/core/dtypes/cast.py in maybe_upcast_putmask(result, mask, other)
    231     if not isinstance(result, np.ndarray):
--> 232         raise ValueError("The result input must be a ndarray.")
    233 

ValueError: The result input must be a ndarray.

This is on pandas 0.25.3. Upgrading my pandas version isn't an option due to various dependencies. Is there any way to work around this bug?

PS: Here's the bug report on github. Monkey patching this fix works but I'd rather not.

CodePudding user response：

You can try convert CategoricalIndex to listss:

df.index = df.index.set_levels(df.index.levels[0].tolist(), level=0)
df = df.reset_index()

EDIT: Here is solution for convert each level of MultiIndex to new columns by DataFrame.insert:

for i in df.index.levels[::-1]:
    df.insert(0, i.name, df.index.get_level_values(i.name))

df = df.reset_index(drop=True)