reset_index() doesn't work with CategoricalIndex columns

[Stanpol]

Code Sample, a copy-pastable example if possible

df=pd.DataFrame({'Year': np.random.randint(2000,2017,10000), 'Month': np.random.randint(1,12,10000), 'Data': np.random.randint(0,100,10000)})
grouped=df.groupby(['Year','Month', pd.cut(df.Data, range(0,100,10))]).size().unstack()

# This doesn't work:
grouped.reset_index() #returns TypeError: unorderable types: int() < str()

# This works:
grouped.columns=grouped.columns.astype('str')
grouped.reset_index()

Problem description

reset_index() should work with dataframes that have any types of columns.

Expected Output

Two extra columns with multiindex content, in the example above - Year and Month.
Column's type changes to string? Or documentation should specify that reset_index() doesn't work with specific types of columns.

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None

pandas: 0.22.0
pytest: None
pip: 9.0.1
setuptools: 36.4.0
Cython: 0.26
numpy: 1.13.3
scipy: 1.0.0
pyarrow: None
xarray: None
IPython: 6.1.0
sphinx: None
patsy: 0.4.1
dateutil: 2.6.1
pytz: 2017.3
blosc: None
bottleneck: None
tables: 3.4.2
numexpr: 2.6.4
feather: None
matplotlib: 2.1.1
openpyxl: None
xlrd: 1.1.0
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 0.9999999
sqlalchemy: 1.2.0
pymysql: None
psycopg2: None
jinja2: 2.9.6
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[TomAugspurger]

Or documentation should specify that reset_index() doesn't work with specific types of columns.

I think that's my preference, but I don't feel strongly about it. I'd just rather not lose all the categorical information.

We could give an example like

grouped.rename(columns=str).reset_index().head()

[maykulkarni]

Hi, I'd like to take this up

[gfyoung]

@maykulkarni : Go for it! No need to ask for permission if no one has said they are working on it.

[Stanpol]

Actually, CategoricalIndex columns break other things as well, like assigning a new column.

df=pd.DataFrame({'Year': np.random.randint(2000,2017,10000), 'Month': np.random.randint(1,12,10000), 'Data': np.random.randint(0,100,10000)})
grouped=df.groupby(['Year','Month', pd.cut(df.Data, range(0,100,10))]).size().unstack()

grouped.assign(A=1)

# TypeError: cannot determine next label for type <class 'str'>

Maybe Error text should be more meaningful?

[Gijs-Koot]

Hi, I ran into a similar problem, but I get a different error message. This seems to happens with a MultiIndex of categoricals.

    test = pd.DataFrame({
        "a": pd.Series(["a_1", "a_2"], dtype = "category"),
        "b": pd.Series(["b_1", "b_2"], dtype = "category")
    })

    problem = test.groupby(["a", "b"]).size().unstack(1)
    problem

This looks good, but

    problem.reset_index()

thows the following error

    TypeError: cannot insert an item into a CategoricalIndex that is not already an existing category

[akielbowicz]

@Gijs-Koot
I had the same problem, and found a work around after I read this post
Category Type Error

def reset_index(df):
  '''Returns DataFrame with index as columns'''
  index_df = df.index.to_frame(index=False)
  df = df.reset_index(drop=True)
  #  In merge is important the order in which you pass the dataframes
  # if the index contains a Categorical. 
  # pd.merge(df, index_df, left_index=True, right_index=True) does not work
  return pd.merge(index_df, df, left_index=True, right_index=True)

It works and keeps the categorical type:

problem

b	b_1	b_2
a
a_1	1	NaN
a_2	NaN	1

solved = reset_index(problem)
solved

	a	b_1	b_2
0	a_1	1	NaN
1	a_2	NaN	1

solved.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2 entries, 0 to 1
Data columns (total 3 columns):
a 2 non-null category
b_1 1 non-null float64
b_2 1 non-null float64
dtypes: category(1), float64(2)
memory usage: 210.0 bytes