How to remove the index column after flattening MultiIndex columns and apply styling to the DataFrame?

Viewed 50

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!

1 Answers

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