Styling pandas pivot_table with multiple columns

Question

I have a DataFrame from which I created a pivot table and formatted the results.

index1 = ["New Category"]
columns1 = []
values1 = ["Net Sales"]

pivot = pd.pivot_table(df, index=index1, values=values1, columns=columns1,aggfunc=np.sum, fill_value="", margins=True)
pivot.style.format({ ('Net Sales'):'${0:,.2f}'})

<style type="text/css"></style><table id="T_c34e9_"> <thead> <tr> <th class="blank level0" >&nbsp;</th> <th class="col_heading level0 col0" >Net Sales</th> </tr> <tr> <th class="index_name level0" >New Category</th> <th class="blank col0" >&nbsp;</th> </tr> </thead> <tbody> <tr> <th id="T_c34e9_level0_row0" class="row_heading level0 row0" >Beans</th> <td id="T_c34e9_row0_col0" class="data row0 col0" >$99,741.99</td> </tr> <tr> <th id="T_c34e9_level0_row1" class="row_heading level0 row1" >Coffee</th> <td id="T_c34e9_row1_col0" class="data row1 col0" >$167,041.77</td> </tr> <tr> <th id="T_c34e9_level0_row2" class="row_heading level0 row2" >Gift Card</th> <td id="T_c34e9_row2_col0" class="data row2 col0" >$100.00</td> </tr> <tr> <th id="T_c34e9_level0_row3" class="row_heading level0 row3" >Hot Choc</th> <td id="T_c34e9_row3_col0" class="data row3 col0" >$18,213.55</td> </tr> <tr> <th id="T_c34e9_level0_row4" class="row_heading level0 row4" >Merchandise</th> <td id="T_c34e9_row4_col0" class="data row4 col0" >$122,122.79</td> </tr> <tr> <th id="T_c34e9_level0_row5" class="row_heading level0 row5" >Package Food</th> <td id="T_c34e9_row5_col0" class="data row5 col0" >$13,923.21</td> </tr> <tr> <th id="T_c34e9_level0_row6" class="row_heading level0 row6" >Prepared Food</th> <td id="T_c34e9_row6_col0" class="data row6 col0" >$38,770.69</td> </tr> <tr> <th id="T_c34e9_level0_row7" class="row_heading level0 row7" >Shipping</th> <td id="T_c34e9_row7_col0" class="data row7 col0" >$1,088.49</td> </tr> <tr> <th id="T_c34e9_level0_row8" class="row_heading level0 row8" >Tea</th> <td id="T_c34e9_row8_col0" class="data row8 col0" >$2,927.17</td> </tr> <tr> <th id="T_c34e9_level0_row9" class="row_heading level0 row9" >All</th> <td id="T_c34e9_row9_col0" class="data row9 col0" >$463,929.66</td> </tr> </tbody></table>

Format works. When I try to add a column the format stops working:

index1 = ["New Category"]
columns1 = ["year"]
values1 = ["Net Sales"]

pivot = pd.pivot_table(df, index=index1, values=values1, columns=columns1, aggfunc=np.sum, fill_value="", margins=True)
pivot.style.format({ ('Net Sales'):'${0:,.2f}'})

<style type="text/css"></style><table id="T_52828_"> <thead> <tr> <th class="blank level0" >&nbsp;</th> <th class="col_heading level0 col0" colspan="5">Net Sales</th> </tr> <tr> <th class="index_name level1" >year</th> <th class="col_heading level1 col0" >2018</th> <th class="col_heading level1 col1" >2019</th> <th class="col_heading level1 col2" >2020</th> <th class="col_heading level1 col3" >2021</th> <th class="col_heading level1 col4" >All</th> </tr> <tr> <th class="index_name level0" >New Category</th> <th class="blank col0" >&nbsp;</th> <th class="blank col1" >&nbsp;</th> <th class="blank col2" >&nbsp;</th> <th class="blank col3" >&nbsp;</th> <th class="blank col4" >&nbsp;</th> </tr> </thead> <tbody> <tr> <th id="T_52828_level0_row0" class="row_heading level0 row0" >Beans</th> <td id="T_52828_row0_col0" class="data row0 col0" >3344.250000</td> <td id="T_52828_row0_col1" class="data row0 col1" >24658.720000</td> <td id="T_52828_row0_col2" class="data row0 col2" >39929.330000</td> <td id="T_52828_row0_col3" class="data row0 col3" >31809.690000</td> <td id="T_52828_row0_col4" class="data row0 col4" >99741.990000</td> </tr> <tr> <th id="T_52828_level0_row1" class="row_heading level0 row1" >Coffee</th> <td id="T_52828_row1_col0" class="data row1 col0" >7039.850000</td> <td id="T_52828_row1_col1" class="data row1 col1" >50371.570000</td> <td id="T_52828_row1_col2" class="data row1 col2" >58534.170000</td> <td id="T_52828_row1_col3" class="data row1 col3" >51096.180000</td> <td id="T_52828_row1_col4" class="data row1 col4" >167041.770000</td> </tr> <tr> <th id="T_52828_level0_row2" class="row_heading level0 row2" >Gift Card</th> <td id="T_52828_row2_col0" class="data row2 col0" ></td> <td id="T_52828_row2_col1" class="data row2 col1" ></td> <td id="T_52828_row2_col2" class="data row2 col2" >75.000000</td> <td id="T_52828_row2_col3" class="data row2 col3" >25.000000</td> <td id="T_52828_row2_col4" class="data row2 col4" >100.000000</td> </tr> <tr> <th id="T_52828_level0_row3" class="row_heading level0 row3" >Hot Choc</th> <td id="T_52828_row3_col0" class="data row3 col0" >2405.920000</td> <td id="T_52828_row3_col1" class="data row3 col1" >7868.880000</td> <td id="T_52828_row3_col2" class="data row3 col2" >5536.500000</td> <td id="T_52828_row3_col3" class="data row3 col3" >2402.250000</td> <td id="T_52828_row3_col4" class="data row3 col4" >18213.550000</td> </tr> <tr> <th id="T_52828_level0_row4" class="row_heading level0 row4" >Merchandise</th> <td id="T_52828_row4_col0" class="data row4 col0" >2919.410000</td> <td id="T_52828_row4_col1" class="data row4 col1" >32250.860000</td> <td id="T_52828_row4_col2" class="data row4 col2" >51714.060000</td> <td id="T_52828_row4_col3" class="data row4 col3" >35238.460000</td> <td id="T_52828_row4_col4" class="data row4 col4" >122122.790000</td> </tr> <tr> <th id="T_52828_level0_row5" class="row_heading level0 row5" >Package Food</th> <td id="T_52828_row5_col0" class="data row5 col0" >190.400000</td> <td id="T_52828_row5_col1" class="data row5 col1" >2116.750000</td> <td id="T_52828_row5_col2" class="data row5 col2" >6943.500000</td> <td id="T_52828_row5_col3" class="data row5 col3" >4672.560000</td> <td id="T_52828_row5_col4" class="data row5 col4" >13923.210000</td> </tr> <tr> <th id="T_52828_level0_row6" class="row_heading level0 row6" >Prepared Food</th> <td id="T_52828_row6_col0" class="data row6 col0" >489.730000</td> <td id="T_52828_row6_col1" class="data row6 col1" >7905.560000</td> <td id="T_52828_row6_col2" class="data row6 col2" >15263.650000</td> <td id="T_52828_row6_col3" class="data row6 col3" >15111.750000</td> <td id="T_52828_row6_col4" class="data row6 col4" >38770.690000</td> </tr> <tr> <th id="T_52828_level0_row7" class="row_heading level0 row7" >Shipping</th> <td id="T_52828_row7_col0" class="data row7 col0" ></td> <td id="T_52828_row7_col1" class="data row7 col1" >51.880000</td> <td id="T_52828_row7_col2" class="data row7 col2" >840.610000</td> <td id="T_52828_row7_col3" class="data row7 col3" >196.000000</td> <td id="T_52828_row7_col4" class="data row7 col4" >1088.490000</td> </tr> <tr> <th id="T_52828_level0_row8" class="row_heading level0 row8" >Tea</th> <td id="T_52828_row8_col0" class="data row8 col0" >85.050000</td> <td id="T_52828_row8_col1" class="data row8 col1" >742.120000</td> <td id="T_52828_row8_col2" class="data row8 col2" >1078.000000</td> <td id="T_52828_row8_col3" class="data row8 col3" >1022.000000</td> <td id="T_52828_row8_col4" class="data row8 col4" >2927.170000</td> </tr> <tr> <th id="T_52828_level0_row9" class="row_heading level0 row9" >All</th> <td id="T_52828_row9_col0" class="data row9 col0" >16474.610000</td> <td id="T_52828_row9_col1" class="data row9 col1" >125966.340000</td> <td id="T_52828_row9_col2" class="data row9 col2" >179914.820000</td> <td id="T_52828_row9_col3" class="data row9 col3" >141573.890000</td> <td id="T_52828_row9_col4" class="data row9 col4" >463929.660000</td> </tr> </tbody></table>

The formatting doesn't pass on to the sub columns.

I tried these too, with no luck

pivot.style.format({ ('Net Sales','year'):'${0:,.2f}'})
pivot.style.format({ ('Net Sales','2018'):'${0:,.2f}'})

How do I style the sub columns?

William Holtam · Accepted Answer · 2021-08-05 14:15:34Z

1

As per the documentation here (see the table in the example for reference): https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html#Formatting-Values

df.style.format(precision=0, na_rep='MISSING', thousands=" ",
            formatter={('Decision Tree', 'Tumour'): "{:.2f}",
                       ('Regression', 'Non-Tumour'): lambda x: "$ {:,.1f}".format(x*-1e6)
                      })

You can see that 'year' isn't the column that you need to apply formatting to; it's the individual years "2019", "2020" etc.

If you need to apply it to all numbers you could just use:

pivot.style.format('{:.2f}')

as per the examples here: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.io.formats.style.Styler.format.html#pandas-io-formats-style-styler-format

OR you could list out each column combination (better to create these tuples before passing them into the function):

pivot.style.format({ ('Net Sales','2018'): "{:.2f}", ('Net Sales','2019'):'${0:,.2f}', ('Net Sales','2020'):'${0:,.2f}', ('Net Sales','2021'):'${0:,.2f}', ('Net Sales','All'):'${0:,.2f}'})

answered Aug 5, 2021 at 14:15

William Holtam

6198 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pstouffer Over a year ago

I have tried the pivot.style.format({ ('Net Sales','2018'):'${0:,.2f}'}) approach and it doesn't seem to work. I think its because that is the format that is used when applied to MultiIndex - It doesn't seem to work on pivot tables.

pstouffer · Accepted Answer · 2021-08-06 11:53:13Z

0

Turns out that the years are not strings, they're numbers. So pivot.style.format({ ('Net Sales',2018):'${0:,.2f}'}) works. The reason this didn't work for me in prior attempts is that the values in the columns were not all floats; some were strings. This was because I had used fill_value="" to fill in missing values. Changing this to fill_value=0 solved the problem.

answered Aug 6, 2021 at 11:53

pstouffer

11 silver badge5 bronze badges

Collectives™ on Stack Overflow

Styling pandas pivot_table with multiple columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related