0

While comparing two dataframes, I am highlighting and appending results in a separate column(in my example, it’s column C). Using apply, you can see the output, but there is no way to export highlighted results directly. I am using xlsxwriter as a workaround. Is there a direct way to export highlighted results in excel or any other standard format using pandas?

Edit: I have added my code in the answer for reference.

 A    B      C
120  ABC12   Exist
125  BCD45   Doesn’t Exist
456  YKR70   Exist
127  UKB67   Doesn’t Exist
MikeCode
  • 91
  • 11
  • Have you figured it out? from https://stackoverflow.com/questions/60102093/highlight-row-in-pandas-when-condition-match I suppose you did - post your answer here, then, for other people to refer to :) – Tatiana Goretskaya Feb 07 '20 at 14:35
  • I ran into the issue since `apply` is not inplace operator in Pandas. It was working fine in jupyter notebook though. finally, I used the xlsxwriter engine for conditional formatting and export. Added my latest solution here, Thanks to @moys and you too. You both rocks! – MikeCode Feb 07 '20 at 15:31

1 Answers1

0

Here is the working code

import numpy as np
import pandas as pd


df = pd.DataFrame({"ID": [10, 11, 12, 13, 14], 
"Status": ['item1', 'item2', 'item3', 'item4', 'item5']})

a1=[10,12,14]
stat= ['item1', 'item2']

df['C'] =df.apply(lambda x: ''.join([str(a)+' was highlighted' for a in a1 if x.ID==a]) or ''.join([sta +' was highlighted' for sta in stat if x.Status==sta]),axis=1)
number_rows = len(df.index) + 1
writer = pd.ExcelWriter("Report.xlsx",engine='xlsxwriter')

df.to_excel(writer, sheet_name='Sheet1', index=False)

workbook = writer.book
worksheet = writer.sheets['Sheet1']



format1 = workbook.add_format({'bg_color': '#FFC7CE',
                              'font_color': '#9C0006'})

format2 = workbook.add_format({'bg_color': '#FFFF00',
                              'font_color': '#9C0006'})



for a in a1:
    worksheet.conditional_format("$A$1:$A$%d" % (number_rows),
                                     {"type": "cell",
                                      'criteria': '=',
                                      "value": a,
                                      "format": format1
                                     })


for b in stat:
    worksheet.conditional_format("$B$1:$B$%d" % (number_rows),
                                     {"type": "text",
                                      'criteria': 'containing',
                                      "value": b,
                                      "format": format2
                                     })

workbook.close()
MikeCode
  • 91
  • 11