How can remove a column name/label from a pivot table and remaining column names drop to index name level?

Question

I have a pivot table using CategoricalDtype so I can get the month names in order. How can I can drop the column name/label "Month" and then move the month abbreviation names to the same level as "Year"?

... .pivot_table(index='Year',columns='Month',values='UpClose',aggfunc=np.sum))

Current output:

Month   Jan   Feb   Mar  Apr  May   Jun   Jul   Aug   Sep   Oct   Nov  Dec  Total
Year    
1997    12.0  8.0   8.0  12.0  11.0 12.0  14.0  10.0  10.0  10.0  10.0 9.0  126.0
1998    10.0  12.0  14.0 12.0  9.0  11.0  10.0  8.0   11.0  10.0  10.0 12.0 129.0

Desired output:

Year    Jan   Feb   Mar  Apr  May   Jun   Jul   Aug   Sep   Oct   Nov  Dec  Total
1997    12.0  8.0   8.0  12.0  11.0 12.0  14.0  10.0  10.0  10.0  10.0 9.0  126.0
1998    10.0  12.0  14.0 12.0  9.0  11.0  10.0  8.0   11.0  10.0  10.0 12.0 129.0

If I use, data.columns.name = None it will remove the "Month" label, but it doesn't drop the month abbreviations to the same level as "Year.

You can't reset the index because it's a CategoricalIndex, I get errors when trying this. — sslack88, Aug 26 '19 at 18:58
@MarkWang please post that as the answer so it can be +1 and accepted — davetapley, Jun 14 '23 at 22:44

score -3 · Answer 1 · answered Aug 26 '19 at 16:27

You need to replace the column name by doing something like this Renaming columns in dataframe w.r.t another specific column

# replace the Month with year 
df = df.rename(columns={"Month":"Year"})

# drop first column
df = df.iloc[1:].reset_index(drop=True)

How can remove a column name/label from a pivot table and remaining column names drop to index name level?

1 Answers1