Home > Enterprise >  How to remove the index column after flattening MultiIndex columns and apply styling to the DataFram
How to remove the index column after flattening MultiIndex columns and apply styling to the DataFram

Time:07-02

I have a sample pandas dataframe with 3 columns.

Here's the sample dataset -

In [3]: df = pd.DataFrame({
   ...:                     'mode': ['car'] * 4   ['train'] * 3   ['truck'] * 5,
   ...:                     'qty1': [2,4,9,7,6,3,4,1,2,5,5,7],
   ...:                     'qty2': [5,2,3,6,9,7,4,5,1,6,3,9]
   ...:                   })
   ...: df
Out[3]:
     mode  qty1  qty2
0     car     2     5
1     car     4     2
2     car     9     3
3     car     7     6
4   train     6     9
5   train     3     7
6   train     4     4
7   truck     1     5
8   truck     2     1
9   truck     5     6
10  truck     5     3
11  truck     7     9

Now I have used pivot_table to get the following output -

In [4]: pivot1 = pd.pivot_table(data=df,
   ...:                         index=['mode'],
   ...:                         values=['qty1','qty2'],
   ...:                         aggfunc='sum')

In [5]: pivot1
Out[5]:
       qty1  qty2
mode
car      22    16
train    13    20
truck    20    24

Now when I apply the following code,

In [7]: pivot1 = pivot1.style.set_table_styles([^M
   ...:                           {^M
   ...:                                 "selector":"th",^M
   ...:                                 "props": [("border", "1px solid black"),^M
   ...:                                           ("font-style", "italic")]^M
   ...:                             },^M
   ...:                             {^M
   ...:                                 "selector":"th.row_heading",^M
   ...:                                 "props": [("border", "1px solid black"),^M
   ...:                                           ("font-style", "italic")]^M
   ...:                             }                           ^M
   ...:                         ]).set_properties(border="1px solid black").format('${:.2f}')
   ...: pivot1

I get the following output - Styled Pivot Table

Now I actually want all the column headers - 'mode', 'qty1' and 'qty2' on one level. I tried resetting the index using reset_index() but I don't want the index column after resetting the index. I want the final table to look like this -

mode qty1 qty2
car $22.00 $16.00
train $13.00 $20.00
truck $20.00 $24.00

My end goal is to save this table as png/jpg and upload it to a pptx presentation using python.

Any help will be appreciated! Thanks!

CodePudding user response:

mode is the index name, you can assign the index name to column header name

pivot1.columns.name, pivot1.index.name = pivot1.index.name, None
print(pivot1)

mode   qty1  qty2
car      22    16
train    13    20
truck    20    24
  • Related