Pandas DataFrame conditional formatting function with additional inputs

Question

I developed a Qt GUI that reads data from a database and displays it on the screen in a table. The table includes conditional formatting which colors cell backgrounds based on cell contents. For example, if the cell contains the letter 'G' for good, then the cell background gets colored green.

I'm currently working on a function that will export that table to an HTML document which I can convert to PDF. I have the data itself in dfhealth and the colors defined in dfdefs, both of which come from pd.read_sql statements.

dfhealth
  NAME  STATUS
0 A     G
1 B     N

dfdefs
  STATUS COLOR
0 G      GREEN
1 N      YELLOW

My issue is with the function for defining the conditional formatting of the DataFrame that will later be converted to HTML:

def condFormat(s, df=dfdefs): # <-- Error on this line
    dcolors = {"GREEN": "rgb(146, 208, 80)",
               "YELLOW": "rgb(255, 255, 153)",
               "RED": "rgb(218, 150, 148)",
               None: "rgb(255, 255, 255)"}
    dftemp = df.query("STATUS == @s")
    scolor = dcolors[dftemp["COLOR"].iloc[0]]
    return "background-color: " + scolor

def main():
    dfhealth = pd.read_sql("SELECT name, status FROM health", conn)
    dfdefs = pd.read_sql("SELECT status, color FROM definitions", conn)
    dfhealth.style.applymap(condFormat)
    html = dfhealth.to_html()
    return html

I get the following error on the line shown above: "NameError: name 'dfdefs' is not defined". I can't figure out how to tell the condFormat function that it needs to compare the contents of each cell to the STATUS column of dfdefs to get the color. Thank you in advance for your help.

Answer 1

I don't get your error, but I still get an IndexError with your code when a status is not found.

Anyway, since your dfdefs is static, better provide a simple dictionary to the function (you could use a DataFrame but it would need to be processed for each item):

def condFormat(s, dic=dfdefs.set_index('STATUS')['COLOR'].to_dict()):
    dcolors = {"GREEN": "rgb(146, 208, 80)",
               "YELLOW": "rgb(255, 255, 153)",
               "RED": "rgb(218, 150, 148)",
               None: "rgb(255, 255, 255)"}
    return f'background-color: {dcolors.get(dic.get(s), "")}'

dfhealth.style.map(condFormat)

Output:

Answer 2

Apparently I didn't read enough of the documentation. You can give applymap any kwargs used by the formatting function:

def condFormat(s, dic=None):
    dcolors = {"GREEN": "rgb(146, 208, 80)",
               "YELLOW": "rgb(255, 255, 153)",
               "RED": "rgb(218, 150, 148)",
               None: "rgb(255, 255, 255)"}
    return f'background-color: {dcolors.get(dic.get(s), "")}'

dic = dfdefs.set_index('STATUS')['COLOR'].to_dict()
dfhealth.style.applymap(condFormat, dic=dic)