Highlight rows in excels using Python

Question

1. I have two different excel for compare...

   enter image description here

2. I want to highlight duplicated 'Name' and 'Reg Date' record in excels after merge.

3. Am I use wrong function of 'pandas' or my logic incorrect?

   import pandas as pd 
   dt1 = pd.read_excel('Data1.xlsx')
   dt2 = pd.read_excel('Data2.xlsx')
   meg_rlt = pd.merge(dt1.iloc[:, [0, 1]], dt2.iloc[:, [0, 2]], on=['Name', 'Reg Date'], how='outer', indicator=True)
   for a in meg_rlt.iloc[:, [2]].values:
       if a == 'both':
          return meg_rlt.style.apply[background: green]
   

Answer 1

After some research I found this post. This shows how to use apply to color rows and columns.
The solution below used the depicted approach (see function bg_color) and should fit your needs.
The resulting Excel-file highlights duplicate entries.

import pandas as pd

dt1 = pd.read_excel('Data1.xlsx')
dt2 = pd.read_excel('Data2.xlsx')

# Rename column to match data2.xlsx
dt1 = dt1.rename({"Student Name": "Name"}, axis='columns')

df_merge = pd.merge(dt1.iloc[:, [0, 1]], dt2.iloc[:, [0, 2]], on=[
    'Name', 'Reg Date'], how='outer', indicator=True)

i = 0
rows_to_color = []

for a in df_merge.iloc[:, [2]].values:
    if a == 'both':
        # remember the rows you want to color
        rows_to_color.append(i)
    i += 1


def bg_color(col):
    color = '#ffff00'
    # color every cell, where rows_to_color contains the index
    return ['background-color: %s' % color
            if i in rows_to_color
            else ''
            for i, x in col.iteritems()]  # i --> row; x --> value


styled = df_merge.style.apply(bg_color)

styled.to_excel("merged_file_styled.xlsx")