Highlighting rows based on a condition

Question

I have this random dataframe containing two columns with dates, I've been trying to highlight rows where the start date exists inside a list of condition dates. Here goes my failed attempt:

import pandas as pd 
import numpy as np
import datetime

df = pd.DataFrame({"Start": pd.date_range("1-jan-2021", periods=10, freq="1H")}).assign(**{"End": lambda d: d["Start"]+pd.Timedelta(hours=20)})

date_condition = ['2021-01-01 05:00:00','2021-01-01 08:00:00', '2021-01-01 02:00:00']
df = df.style.applymap(lambda x: 'background-color : yellow' if x['Start'] in date_condition)

Since I'm trying to export this dataframe using xlswriter, I'm looking for a method to keep the background color even in the excel file. Thank you !

Margarito

There is no way to apply pandas display options to excel formatting. These are totally separate things, and there's no mechanism for applying formatting, including highlighting, in the pd.DataFrame.to_excel method. Instead, I'd first check out options for applying formatting manually to excel workbooks, since that will be the hard part. – Disforest

That is untrue. The pandas styler has a to excel function pandas.io.formats.style.Styler.to_excel which can export pandas styles to excel. – Preciosa

omg. my bad! thanks @HenryEcker! – Disforest

Answer 1

We can use a combination of np.where and Series.isin:

date_condition = ['2021-01-01 05:00:00', '2021-01-01 08:00:00',
                  '2021-01-01 02:00:00']

highlighted_rows = np.where(df['Start'].isin(date_condition),
                            'background-color: yellow',
                            '')
# Apply calculated styles to each column:
styler = df.style.apply(lambda _: highlighted_rows)
styler.to_excel('output.xlsx')  # use styler to export to excel
# styler  # to display in NoteBook

Or with isin + Series.map:

date_condition = ['2021-01-01 05:00:00', '2021-01-01 08:00:00',
                  '2021-01-01 02:00:00']

highlighted_rows = df['Start'].isin(date_condition).map({
    True: 'background-color: yellow',
    False: ''
})
# Apply calculated styles to each column:
styler = df.style.apply(lambda _: highlighted_rows)
styler.to_excel('output.xlsx')  # use styler to export to excel
# styler  # to display in NoteBook

---

In Excel:

In Jupyter Notebook

Preciosa