How to write dataframe to excel with conditional formatting in Python?

Question

How to export pandas dataframe to excel with conditional formatting?

Sample Data

from random import randint

x = [randint(0, 1) for p in range(0, 10)]

sample_dict = {"Col1": [randint(0, 1) for p in range(0, 10)],
               "Col2": [randint(0, 1) for p in range(0, 10)],
               "Col3": [randint(0, 1) for p in range(0, 10)],
               "Col4": [randint(0, 1) for p in range(0, 10)],
               "Col5": [randint(0, 1) for p in range(0, 10)],
               "Col6": [randint(0, 1) for p in range(0, 10)]}

sample = pd.DataFrame(sample_dict)

  Col1  Col2    Col3    Col4    Col5    Col6
0   1   1   0   1   0   0
1   0   1   1   0   1   1
2   1   0   1   0   0   1
3   1   1   0   1   0   0
4   1   0   0   1   1   1
5   0   0   1   1   0   0
6   1   1   0   0   0   0
7   0   0   1   0   1   1
8   0   1   1   1   0   0
9   0   1   1   0   0   1

Required Conditional Formatting in pandas styler

sample.style.apply(lambda x: ["background: orange" if v != x.iloc[0] else "" for v in x], axis = 1)

Answer 1

I changed the background: orange to background-color: orange, if you use background-color: none or simple "" in your else statement doesn't effect the output. See:

from random import randint
import pandas as pd

x = [randint(0, 1) for p in range(0, 10)]

sample_dict = {"Col1": [randint(0, 1) for p in range(0, 10)],
               "Col2": [randint(0, 1) for p in range(0, 10)],
               "Col3": [randint(0, 1) for p in range(0, 10)],
               "Col4": [randint(0, 1) for p in range(0, 10)],
               "Col5": [randint(0, 1) for p in range(0, 10)],
               "Col6": [randint(0, 1) for p in range(0, 10)]}

sample = pd.DataFrame(sample_dict)

sample = sample.style.apply(lambda x: ["background-color: orange" if v != x.iloc[0] else "background_color: none" for v in x], axis=1)
sample.to_excel('sample.xlsx', engine='openpyxl')

This will give you: