Pandas applymap to Style cell based on another column

Question

DB	Policy	Snapshot Time
A-DB	PROD_BACKUP	10/17/2022 12:00:00
B-DB	PROD_BACKUP	10/16/2022 10:00:00
C-DB	NONPROD_BACKUP	10/15/2022 16:00:00

If the above data is in my dataframe, I pass snapshot time through applymap and currently it will highlight the snapshot time cell if the snapshot time was >24 hours ago. But I only want that for prod backups. For non-prod backups, I want >72 hours. Here is what I have right now:

import pandas as pd
from datetime import datetime,timedelta

report_start_time = datetime.now()


def highlight_cells(val, check_time, limit):
    time_diff = check_time - val

    total_hours = (time_diff.days*24) + (time_diff.seconds / (60*60))

    if (total_hours >= limit):
        format_code = '''background-color: #B00202;
           font-weight: bold'''
    else:
        format_code = ''

    return format_code
def highlight_snap(val, check_time):
    style = highlight_cells(val, check_time, 24)
    return style

d = {'DB': ['A-DB', 'B-DB', 'C-DB'], 'Policy': ['PROD_BACKUP','PROD_BACKUP','NONPROD_BACKUP'], 'Snapshot Time': ['10/17/2022 12:00:00','10/16/2022 10:00:00','10/15/2022 16:00:00']}
df = pd.DataFrame(data=d)
df['Snapshot Time'] = pd.to_datetime(df['Snapshot Time'])

table_styler = [
    {   "selector" : "table",
        "props":[   ("border", "3px solid black"),
                    ("border-collapse","separate"),
                    ("width", "100%")
                ]
     },
     {   "selector" : "th",
         "props":[   ("color","black"),
                     ("background-color", "#F5F3F3"),
                     ("border", "1px solid gray"),
                     ("padding", "3px 3px"),
                     ("border-collapse","separate"),
                     ("font-size", "16px")
                 ]
     },
     {   "selector" : "td",
         "props":[   ("color","black"),
                     ("border", "1px solid gray"),
                     ("padding", "1px 3px"),
                     ("font-size", "14px")
                 ]
     }
]

df_styler = df.style.set_table_styles(table_styler)

df_styler = df_styler.applymap(highlight_snap, subset=['Snapshot Time'], check_time=report_start_time)

print (df_styler.to_html())

So how do I pass the current row's policy into the applymap function so I can add an if statement that says "if prod, do 24 hours, if non-prod do 72 hours"?

score 0 · Accepted Answer · answered Oct 18 '22 at 17:33

I solved my issue by following this other question.

Couldn't find that before I asked my own question. Anyway, here is my test code with the fix:

#!/appsw/oracle/local/bin/python3.9
import pandas as pd
from datetime import datetime,timedelta

report_start_time = datetime.now()


def highlight_cells(val, check_time, limit):
    time_diff = check_time - val

    total_hours = (time_diff.days*24) + (time_diff.seconds / (60*60))

    if (total_hours >= limit):
        format_code = '''background-color: #B00202;
           font-weight: bold'''
    else:
        format_code = ''

    return format_code

def highlight_snap(row, check_time):
    # subset=['SLA Domain Name', 'Last Snapshot']
    if row['Policy'] == 'NONPROD_BACKUP':
        style = highlight_cells(row['Snapshot Time'], check_time, 120)
    else:
        style = highlight_cells(row['Snapshot Time'], check_time, 24)

    return pd.Series({'Snapshot Time':style})

d = {'DB': ['A-DB', 'B-DB', 'C-DB'], 'Policy': ['PROD_BACKUP','PROD_BACKUP','NONPROD_BACKUP'], 'Snapshot Time': ['10/18/2022 12:00:00','10/16/2022 10:00:00','10/15/2022 16:00:00']}
df = pd.DataFrame(data=d)
df['Snapshot Time'] = pd.to_datetime(df['Snapshot Time'])

table_styler = [
    {   "selector" : "table",
        "props":[   ("border", "3px solid black"),
                    ("border-collapse","separate"),
                    ("width", "100%")
                ]
     },
     {   "selector" : "th",
         "props":[   ("color","black"),
                     ("background-color", "#F5F3F3"),
                     ("border", "1px solid gray"),
                     ("padding", "3px 3px"),
                     ("border-collapse","separate"),
                     ("font-size", "16px")
                 ]
     },
     {   "selector" : "td",
         "props":[   ("color","black"),
                     ("border", "1px solid gray"),
                     ("padding", "1px 3px"),
                     ("font-size", "14px")
                 ]
     }
]

df_styler = df.style.set_table_styles(table_styler)

df_styler = df_styler.apply(highlight_snap, subset=['Policy','Snapshot Time'], axis=1, check_time=report_start_time)

print (df_styler.to_html())

I had a feeling it was going to be something with apply and not applymap but couldn't make sense of the documentation. The key for me, which the answer I linked to helped me understand, is what I was expected to return back to apply. Hope this helps someone out.

Pandas applymap to Style cell based on another column

1 Answers1