Conditional formatting pandas

Question

How do I colour in cells below 1 as green and cells between 0 and 0.5 yellow for Column C.

Current output:

   A    B    C
0 TT    WW   -5
1 AA    WW   0
2 DD    WW   5

Desired:

The following code for me just writes to Excel with no colour change.

import pandas as pd
import numpy as np


df = pd.read_excel("C:\\13\\13\\13.xlsx")
def highlight_vals(val, min=-1111, max=1, color='green'):
    if min < val < max:
        return 'background-color: %s' % color
    else:
        return ''
df.style.applymap(highlight_vals, subset=['C'])

def highlight_vals(val, min=0, max=0.5, color='yellow'):
    if min < val < max:
        return 'background-color: %s' % color
    else:
        return ''
df.style.applymap(highlight_vals, subset=['C'])


writer = pd.ExcelWriter("C:\\13\\13\\zz.xlsx")
df.to_excel(writer, startrow=0, startcol=0, index = False)

writer.save()

Georgy · Accepted Answer · 2017-12-08 14:28:31Z

1

This should work:

import pandas as pd
import numpy as np


df = pd.read_excel("C:\\13\\13\\13.xlsx")
def highlight_vals(val, min=-1111, max=1, color='green'):
    if min <= val <= max:
        return 'background-color: %s' % color
    else:
        return ''
style_1 = df.style.applymap(highlight_vals, subset=['C'])

def highlight_vals(val, min=0, max=0.5, color='yellow'):
    if min <= val <= max:
        return 'background-color: %s' % color
    else:
        return ''
style_2 = df.style.applymap(highlight_vals, subset=['C'])

style_1.use(style_2.export())

writer = pd.ExcelWriter("C:\\13\\13\\zz.xlsx")
style_1.to_excel(writer, startrow=0, startcol=0, index = False)

writer.save()

I kept both Styler objects, applied one on another and saved the resulting one to Excel instead of a dataframe. Also note that I fixed your conditions. Otherwise, you won't get yellow color because of min < val

answered Dec 8, 2017 at 14:28

Georgy

14k7 gold badges69 silver badges80 bronze badges

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

11 Comments

user9036931 Over a year ago

My Excel does not change using this

user9036931 Over a year ago

Positive.I am using windows 10. Copied that code exactly. Maybe its wrong approach. The output appears but no colour change

Georgy Over a year ago

How do you run the code? If you run from Jupyter, you can check results after every time you apply new style like display(style_1). Each time you should see coloured tables

user9036931 Over a year ago

I am using pycharm. Perhaps there is colour change if I had Jupyter. Can this be written to excel though or in pandas you can only colour dataframes but change is not noticed in excel. Is xlsxwriter required?

Georgy Over a year ago

Everything works fine for me from PyCharm as well. I don't know about xlsxwriter, but I had to install openpyxl

|

Collectives™ on Stack Overflow

Conditional formatting pandas

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related