Color formatting excel file row in python

Question

I have dataframe where I have 2 Date columns. I have to compare them and if they are different then whole row should be colored. Please check the picture.

Please guide me how can I do that in python. Thanks in advance.

Shijith · Answer 1 · 2019-05-14T09:28:29.953

2

a bit cleaner using a function

def format_df(x):
    if x.Date1 != x.Date2:
        return ['background-color: red'] * len(x)
    return [''] * len(x)

df.style.apply(lambda x: format_df(x), axis=1).to_excel("file.xlsx",index=False)

Edit 1: if you want only the second column to be highlighted,

def format_df(x):
    if x.Date1 != x.Date2:
        return ['']+['background-color: red']
    return [''] * len(x)

df.style.apply(lambda x: format_df(x), axis=1)

edited May 14 '19 at 09:28

answered May 06 '19 at 11:15

Shijith

4,602
2
20
34

is it possible just to color the second cell of second date column. – Abdullah May 14 '19 at 07:14
it raise some value error: "ValueError: arrays must all be same length" – Abdullah May 14 '19 at 11:29
@Abdulla, how many columns do you have , the second code will work only for `two` columns. For multiple columns: for e.g. if your `Date2` is the forth column, then `return ['']*3 + ['background-color: red'] + [''] * (len(x)-4)` – Shijith May 14 '19 at 12:11

score 1 · Accepted Answer · answered May 06 '19 at 10:35

1

Create styles in helper DataFrame and export to excel:

df = pd.DataFrame({'Date1':['19/3/2011','15/5/2015','18/8/2018'],
                   'Date2':['19/3/2011','1/1/2019','18/8/2018']})

print (df)
       Date1      Date2
0  19/3/2011  19/3/2011
1  15/5/2015   1/1/2019
2  18/8/2018  18/8/2018

def highlight_diff(x): 
   c1 = 'background-color: red'
   c2 = '' 
   m = x['Date1'] != x['Date2']

   df1 = pd.DataFrame(c2, index=x.index, columns=x.columns)
   df1.loc[m, :] = c1
   return df1

(df.style
   .apply(highlight_diff,axis=None)
   .to_excel('styled.xlsx', engine='openpyxl', index=False))

answered May 06 '19 at 10:35

jezrael

822,522
95
1,334
1,252

is it possible just to color only the second cell of date 2 column? – Abdullah May 14 '19 at 11:04
@Abdullah - Sure, change `df1.loc[m, :] = c1` to `df1.loc[m, 'Date2'] = c1` – jezrael May 14 '19 at 11:05
@Abdullah - accepting answer working with your new requirement? – jezrael May 14 '19 at 13:11

sentence · Answer 3 · 2019-05-06T11:20:13.700

1

You should use DataFrame.style and DataFrame.to_excel:

import pandas as pd

df = pd.DataFrame({'Date1':['19/3/2011','15/5/2015','18/8/2018'],
                   'Date2':['19/3/2011','1/1/2019','18/8/2018']})

df.style.apply(lambda x: ['background-color: red']*df.shape[1] if x['Date1'] != x['Date2'] else ['']*df.shape[1], axis=1).to_excel("output.xlsx", index=False)

and you get:

edited May 06 '19 at 11:20

answered May 06 '19 at 10:59

sentence

8,213
4
31
40

Color formatting excel file row in python

3 Answers3

Linked