How to get rid of multilevel index after using pivot table pandas?

Question

I had following data frame (the real data frame is much more larger than this one ) :

sale_user_id    sale_product_id count 1                 1              1 1                 8              1 1                 52             1 1                 312            5 1                 315            1 

Then reshaped it to move the values in sale_product_id as column headers using the following code:

reshaped_df=id_product_count.pivot(index='sale_user_id',columns='sale_product_id',values='count') 

and the resulting data frame is:

sale_product_id -1057   1   2   3   4   5   6   8   9   10  ... 98  980 981 982 983 984 985 986 987 99 sale_user_id                                                                                     1                NaN    1.0 NaN NaN NaN NaN NaN 1.0 NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 3                NaN    1.0 NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 4                NaN    NaN 1.0 NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 

as you can see we have a multililevel index , what i need is to have sale_user_is in the first column without multilevel indexing:

i take the following approach :

reshaped_df.reset_index() 

the the result would be like this i still have the sale_product_id column , but i do not need it anymore:

sale_product_id sale_user_id    -1057   1   2   3   4   5   6   8   9   ... 98  980 981 982 983 984 985 986 987 99 0                          1    NaN 1.0 NaN NaN NaN NaN NaN 1.0 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 1                          3    NaN 1.0 NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 2                          4    NaN NaN 1.0 NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN  

i can subset this data frame to get rid of sale_product_id but i don't think it would be efficient.I am looking for an efficient way to get rid of multilevel indexing while reshaping the original data frame

Answer 1

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  315 sale_user_id                             1                  1    1    1    5    1  print (reshaped_df.index.name) sale_user_id  print (reshaped_df.rename_axis(None)) sale_product_id  1    8    52   312  315 1                  1    1    1    5    1 

Another solution working in pandas below 0.18.0:

reshaped_df.index.name = None print (reshaped_df)  sale_product_id  1    8    52   312  315 1                  1    1    1    5    1 

---

If need remove columns name also:

print (reshaped_df.columns.name) sale_product_id  print (reshaped_df.rename_axis(None).rename_axis(None, axis=1))    1    8    52   312  315 1    1    1    1    5    1 

Another solution:

reshaped_df.columns.name = None reshaped_df.index.name = None print (reshaped_df)    1    8    52   312  315 1    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  315 0                  1    1    1    5    1  #if need reset index nad remove column name reshaped_df = reshaped_df.reset_index(drop=True).rename_axis(None, axis=1) print (reshaped_df)    1    8    52   312  315 0    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  315 sale_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  315 0             1  1  1   1    5    1