How to get rid of multilevel index after using pivot table pandas?
You need remove only index name
, use rename_axis
(new in pandas
0.18.0
):
print (reshaped_df)sale_product_id 1 8 52 312 315sale_user_id 1 1 1 1 5 1print (reshaped_df.index.name)sale_user_idprint (reshaped_df.rename_axis(None))sale_product_id 1 8 52 312 3151 1 1 1 5 1
Another solution working in pandas below 0.18.0
:
reshaped_df.index.name = Noneprint (reshaped_df)sale_product_id 1 8 52 312 3151 1 1 1 5 1
If need remove columns name
also:
print (reshaped_df.columns.name)sale_product_idprint (reshaped_df.rename_axis(None).rename_axis(None, axis=1)) 1 8 52 312 3151 1 1 1 5 1
Another solution:
reshaped_df.columns.name = Nonereshaped_df.index.name = Noneprint (reshaped_df) 1 8 52 312 3151 1 1 1 5 1
EDIT by comment:
You need reset_index
with parameter drop=True
:
reshaped_df = reshaped_df.reset_index(drop=True)print (reshaped_df)sale_product_id 1 8 52 312 3150 1 1 1 5 1#if need reset index nad remove column namereshaped_df = reshaped_df.reset_index(drop=True).rename_axis(None, axis=1)print (reshaped_df) 1 8 52 312 3150 1 1 1 5 1
Of if need remove only column name:
reshaped_df = reshaped_df.rename_axis(None, axis=1)print (reshaped_df) 1 8 52 312 315sale_user_id 1 1 1 1 5 1
Edit1:
So if need create new column from index
and remove columns names
:
reshaped_df = reshaped_df.rename_axis(None, axis=1).reset_index() print (reshaped_df) sale_user_id 1 8 52 312 3150 1 1 1 1 5 1
Make a DataFrame
import randomd = {'Country': ['Afghanistan','Albania','Algeria','Andorra','Angola']*2, 'Year': [2005]*5 + [2006]*5, 'Value': random.sample(range(1,20),10)}df = pd.DataFrame(data=d)
df:
Country Year Value 1 Afghanistan 2005 62 Albania 2005 133 Algeria 2005 104 Andorra 2005 115 Angola 2005 56 Afghanistan 2006 37 Albania 2006 28 Algeria 2006 79 Andorra 2006 310 Angola 2006 6
Pivot
table = df.pivot(index='Country',columns='Year',values='Value')
Table:
Year Country 2005 20060 Afghanistan 16 91 Albania 17 192 Algeria 11 73 Andorra 5 124 Angola 6 18
I want 'Year' to be 'index':
clean_tbl = table.rename_axis(None, axis=1).reset_index(drop=True)
clean_tbl:
Country 2005 20060 Afghanistan 16 91 Albania 17 192 Algeria 11 73 Andorra 5 124 Angola 6 18
Done!
The way it works for me is
df_cross=pd.DataFrame(pd.crosstab(df[c1], df[c2]).to_dict()).reset_index()