python - Highlight cell on condition

1
votes

I'm trying to highlight all cells that are before the current date. However I am highlighting all cells instead of just the old dates. 

import pandas as pd
from datetime import datetime 

#get file 
df = pd.read_excel(r'C:\Users\cc-621.xlsx')
df

# sort the data by date 
df['Date'] = pd.to_datetime(df['Expiration Date'])
df = df.sort_values(by='Expiration Date')
df = df.reset_index()
df

# sort by todays date 
today = datetime.now(tz=None)
today

def expired(self):
    for index, rows in df.iterrows():
        color = 'red' if rows['Expiration Date'] < today else 'green'
        return 'background-color: %s' %color

new = df.style.applymap(expired)
new
2
pythonpandasjupyter
also tried with df.style.apply(expired,axis=None) ? also change df.iterrows() to self.iterrows() (though you should look for a vectorized way and not using iterrows) in the function but to test you have to provide a data - anky
No still not working - c-mac
Why don't you try to provide a mcve? - rpanai

2 Answers

2
votes

Idea is create new DataFrame filled by styles by condition with Styler.apply, for set rows by conditions is used numpy.where:

def expired(x):
    c1 = 'background-color: red'
    c2 = 'background-color: green'

    df1 = pd.DataFrame('', index=x.index, columns=x.columns)
    m = x['Expiration Date'] < today
    df1['Expiration Date'] = np.where(m, c1, c2)
    return df1

df.style.apply(expired, axis=None)

If coloring all rows by condition use DataFrame.mask:

def expired(x):
    c1 = 'background-color: red'
    c2 = 'background-color: green'

    df1 = pd.DataFrame(c1, index=x.index, columns=x.columns)
    m = x['Expiration Date'] < today
    df1 = df1.mask(m, c2)
    return df1
2
votes

applymap is executed on each cell. You don't need to loop over each row if using this. However, it seems you are trying to highlight the entire row, so you likely want apply by row. Using this method, you have to return an array with the same size as each row.

def expired(val):
    return ['background-color: green' if val['Expiration Date'] else ''] * len(val)

new = df.style.apply(expired, axis=1)