Pandas Dynamic Conditional Formatting

Question

Suppose I have a dataframe created from the following list:

import pandas as pd 
from openpyxl import Workbook 
from pandas import ExcelWriter import openpyxl

#Using openpyxl engine
wb = Workbook() 
sheet = wb.active 
data = [
    [0, 0, 1],
    [3, 0, 4],
    ['=EVALUATE(Something ends up being 1)', 0, 4],
    [4, 0, 1]]
df = pd.DataFrame(data, columns=["value", "min", "max"])

Now I want to format(color the cell) the first(value) column based on the min and max column; green if within range, and red if not.

In this example it should be [Green,Green,Green,Red].

I've tried a few examples at: https://pandas.pydata.org/pandas-docs/stable/style.html, however, the formatting is static.

Problems:

If I were to change the value in the excel sheet to fit/unfit the condition, the style remains the same.
If 1. cannot be addressed, excel formulas are interpreted as non numbers, so how do I get them to evaluate within the code?

what do you mean by "If I were to change the value in the excel sheet to fit/unfit the condition". As in you manually open the Excel File and change the value of the cell - you'd expect the color to change? — MattR, Sep 26 '18 at 20:20
@MattR yep, same with changes in the value from the underlying formula — Henry, Sep 26 '18 at 20:21
pandas does not connect 'live' with the excel sheet -- it reads its content into memory. If the excel sheet changes, you have to reload it. Also, in your example you don't seem to do anything with `wb`, but you create the `DataFrame` with manuallly defined data. — Andy, Sep 26 '18 at 20:24
Well I think it is possible. but may require some coding. Try Calling/Writing VBA using Python like [here](https://stackoverflow.com/questions/19616205/running-an-excel-macro-via-python) or [here](https://stackoverflow.com/questions/19505676/use-python-to-write-vba-script). Ultimately, this sounds like a job straight for VBA though. Instead of creating the Excel Sheet with Pandas each time, Use Excel's built-in Data Connector and refresh it from the original data source. Then use VBA to do your formatting. — MattR, Sep 26 '18 at 20:25
@Andy yes, but is there is a way for pandas to configure a conditional styling(as a formula) within excel that re-evaluates everytime a value in a cell is changed? — Henry, Sep 26 '18 at 20:32
@Henry do you want to use python to write an excel file with conditional formating, or do you want to have a pandas dataframe with cell formatting? If it's the latter, maybe this can help you: https://openpyxl.readthedocs.io/en/stable/formatting.html (although I haven't tried it myself) — Andy, Sep 26 '18 at 20:43

Pandas Dynamic Conditional Formatting

0 Answers0