How to use Pandas styler to implement Excel-like conditional formatting?

Question

I have 2 columns that I want to compare (SO Status & Past_Week_SO).

I want to show "Past_Week_SO" with red color when the data is different from the "SO Status".

This is my current attempt but it doesn't work:

df_style = df_master.style.applymap(
    lambda x: 'background-color: %s' % 'yellow' if x != df_master['SO Status'] else
              'background-color: %s' % 'green', subset=df_master['Past_Week_SO'])

You need to give a bit more context, what libraries/modules are you using to model your excel file in? — Thomas Kimber
– Thomas Kimber, Commented Jul 9, 2021 at 8:59
Ahh, very helpful thankyou. I'd not come across the styling/styler module until today. Apparently it's been around since v 0.17 or so waay back in 2015. — Thomas Kimber
– Thomas Kimber, Commented Jul 9, 2021 at 13:31

jezrael · Accepted Answer · 2021-07-09 09:35:09Z

1

Use Styler.apply for set DataFrame of styles by conditions:

def highlight(x):
    c1 = 'background-color: red'
    c2 = ''  
    m = x['Past_Week_SO'] !=  x['SO Status'] 
    df1 = pd.DataFrame(c2, index=x.index, columns=x.columns)
    df1.loc[m, 'Past_Week_SO'] = c1
    return df1

df_master.style.apply(highlight, axis=None)

answered Jul 9, 2021 at 9:35

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user16413195 Over a year ago

this method works fine :) thanks jezrael!

Ibrahim Berber · Accepted Answer · 2021-07-09 09:35:22Z

A solution might be like this.

import pandas as pd
import numpy as np

data = pd.DataFrame(np.random.randint(low=1, high=3, size=(7, 2)),
                    columns=['col_' + str(e) for e in range(2)])
print(data)
   col_0  col_1
0      2      2
1      2      2
2      1      1
3      2      1
4      2      2
5      1      2
6      2      1

def change_color(workbook_param, color_hex_code):
    """Returns color format for excelsheet."""
    formatter = workbook_param.add_format({
        'bold': True,
        'text_wrap': True,
        'valign': 'top',
        'fg_color': color_hex_code,
        'border': 1})

    return formatter


import xlsxwriter

with xlsxwriter.Workbook('hello.xlsx') as workbook:
    worksheet = workbook.add_worksheet()

    worksheet.write(0, 0, 'col_0')
    worksheet.write(0, 1, 'col_1')
    for index, row in data.iterrows():
        if row['col_0'] != row['col_1']:
            worksheet.write(index + 1, 0, row['col_0'], change_color(workbook, '#ffb3b3'))
            worksheet.write(index + 1, 1, row['col_1'], change_color(workbook, '#ffb3b3'))

        else:
            worksheet.write(index + 1, 0, row['col_0'], change_color(workbook, '#e3fc03'))
            worksheet.write(index + 1, 1, row['col_1'], change_color(workbook, '#e3fc03'))

Here is result:

Collectives™ on Stack Overflow

How to use Pandas styler to implement Excel-like conditional formatting?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related