Pandas highlighting excel columns with conditions using a function

Question

I have a pandas data-frame (Pre_Final_DataFrame) that I am writing to excel.

I need to highlight a row in Excel if that corresponding row has a "No Match" word on any of the column that starts with 'Result_'.

So, I decided to go for an array to understand which one needed to be highlighted.

But now, I would prefer a way to highlight using a function as it is too slow. Kindly help me with this.

In Simple words, I am writing a dataframe to excel using Pandas and it has million records and I want a row to be highlighted in "Yellow" only when there is a No Match value present in any one of the column that has a name starting with " Result_"

The Expected result appears in excel looks like below,

Input codes to start with a dataframe:-

import pandas as pd

data = {
     'ColA':[1, 1], 
     'ColB':[1, 1],
     'Result_1':['Match', 'Match'], 
     'ColA1':[1, 2], 
     'ColB1':[1, 1],
     'Result_2':['No Match', 'Match'],  
     }

Pre_Final_DataFrame = pd.DataFrame(data)

ResultColumns_df = Pre_Final_DataFrame.filter(like='Result_')
ResultColumns_df_false =ResultColumns_df[ResultColumns_df.values  == "No Match"]
RequiredRows_Highlight = ResultColumns_df_false.index.tolist()

writer = pd.ExcelWriter(OutputName,date_format='%YYYY-%mm-%dd',datetime_format='%YYYY-%mm-%dd')
Pre_Final_DataFrame.to_excel(writer,'Sheet1',index = False)
writer.save()

Output Expected:

Sumanth · Accepted Answer · 2019-01-18 18:10:45Z

6

We can use the StyleFrame package for reading it into an excel sheet.

import pandas as pd
from StyleFrame import StyleFrame, Styler

df = pd.read_excel("Your Excel Sheet")
sf = StyleFrame(df)

style = Styler(bg_color='yellow') 
for col in df:
     sf.apply_style_by_indexes(sf[sf[col]== 'No Match'],styler_obj=style)

sf.to_excel('test.xlsx').save()

This has helped me in getting an output excel sheet with all the rows highlighted that contains at least one column with value No Match.

Hope this helps. Cheers

edited Jan 18, 2019 at 18:10

answered Jan 18, 2019 at 14:31

Sumanth

5075 silver badges13 bronze badges

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

4 Comments

Rojo Over a year ago

It gives me this error - 'Styler' object has no attribute 'to_excel' on this line new_df.to_excel(writer,'Sheet1',index = False)

Sumanth Over a year ago

new_df is a pandas.style.Styler object. Presently pandas do not have a functionality of writing style object to an excel sheet. I think It looks like the openpyxl engine can do the work: [link] (pandas.pydata.org/pandas-docs/stable/style.html)

Rojo Over a year ago

Now, I don't understand how we write this new_df to a new file using openpyxl. This link gave me some Idea, but it is not helping - stackoverflow.com/questions/36657288/…

Sumanth Over a year ago

@Rojo I have updated the answer, I have got the highlighted excel sheet as output. Hope it helps.

Collectives™ on Stack Overflow

Pandas highlighting excel columns with conditions using a function

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related