CODEWITHSUNDEEP
pythonexcelpandaspandas-styles
user16413195
user16413195

Reputation: 11

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

screenshot of desired Excel formatting

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'])

Upvotes: 1

Views: 145

Answers (2)

Ibrahim Berber
Ibrahim Berber

Reputation: 860

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:

enter image description here

Upvotes: 0

jezrael
jezrael

Reputation: 862611

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)

Upvotes: 1

Related Questions