Apply conditional formatting in Excel using pandas does not work-CodePudding

I have spent hours trying all possible pandas style methods for conditional formatting for Excel sheet but none of them worked... I have a dataframe like this below.

         Category      Month   Sales    Margin  Margin_Rate
0              TV       July   50000  10354.64     0.207093
1          Steros     August   45000  39379.38     0.875097
2          Phones  September   22000   8130.23     0.369556
3     Cell Phones    October   90000   8089.23     0.089880
4  Video Consoles   November   12000   8564.69     0.713724
5     Video Games   December    7000   3187.74     0.455391
6       Wearables    January   28000   8470.51     0.302518
7         Tablets   February   75000   8649.36     0.115325
8          Movies      March   25000  20372.73     0.814909
9         Laptops      April  100000  62309.34     0.623093

(1). I am trying to do the number formatting using style.format() from pandas but it never worked. (2). Trying to apply the cell background color based on conditions using style.apply or style.applymap and also it never worked.

Here is my code:

# First of all I tried style.format(precision=1)
df.style.format(precision=1)  <--- This causes TypeError: format() got an unexpected keyword argument 'precision'. Any idea why? 

# And then I tried the dictionary approach. No error from this but it does not format numbers
df.style.format({'Margin': '{:.1f}', 'Margin_Rate': '{:.2f}'})

# And then I tried apply background-color and none of them worked..
def highlight_cell(val):
    color = 'yellow' if val == 'Movies' else ''
    return f"bacground-color: {color}"

df.style.applymap(highlight_cell)

# No number formatting nor background color change in the excel sheet...
df.to_excel('retails.xlsx', engine='openpyxl')

None of them worked... Any idea please? Or did I miss anything?

Thank you.

[ Adding df.to_dict() as Kraigolas recommends ]:

{'Category': {0: 'TV', 1: 'Steros', 2: 'Phones', 3: 'Cell Phones', 4: 'Video Consoles', 5: 'Video Games', 6: 'Wearables', 7: 'Tablets', 8: 'Movies', 9: 'Laptops'}, 'Month': {0: 'July', 1: 'August', 2: 'September', 3: 'October', 4: 'November', 5: 'December', 6: 'January', 7: 'February', 8: 'March', 9: 'April'}, 'Sales': {0: 50000, 1: 45000, 2: 22000, 3: 90000, 4: 12000, 5: 7000, 6: 28000, 7: 75000, 8: 25000, 9: 100000}, 'Margin': {0: 10354.64, 1: 39379.38, 2: 8130.23, 3: 8089.23, 4: 8564.69, 5: 3187.74, 6: 8470.51, 7: 8649.36, 8: 20372.73, 9: 62309.34}, 'Margin_Rate': {0: 0.2070928, 1: 0.8750973333333333, 2: 0.3695559090909091, 3: 0.08988033333333333, 4: 0.7137241666666667, 5: 0.45539142857142856, 6: 0.3025182142857143, 7: 0.1153248, 8: 0.8149092, 9: 0.6230934}}

CodePudding user response：

There are a few issues in your code:

typo in "background"
your function should return '' or None if no style is applied, not "background-color:"
you should export the return of df.style.applymap, not df

To set up the decimals, use float_format="%0.1f" in to_excel:

# And then I tried apply background-color and none of them worked..
def highlight_cell(val):
    color = 'yellow' 
    return f"background-color: {color}" if val == 'Movies' else ''

(df.style.applymap(highlight_cell)
   .to_excel('retails.xlsx', engine='openpyxl', float_format="%0.1f")
)

output:

Formatting without excel export:

(df.style.applymap(highlight_cell)
   .format({'Margin': '{:.1f}', 'Margin_Rate': '{:.2f}'})
   #.to_excel('retails.xlsx', engine='openpyxl')
)

output: