Search code examples
pythonpandasfontsconditional-formatting

Pandas change font color based on condition in Excel and save to the same excel python

New to Pandas as of now. My Problem statement is I am trying to open an existing excel sheet, Traverse through the values present in that, add an if condition and change the font colour of text if the condition is true.

This is the sample excel where I am trying to change the color:

enter image description here

Below is my code which I have tried:

def highlight_cells(val):
    color = 'red' if val =='DATA' else '#C6E2E9'
    return 'color: %s' % color

ddf = pd.read_excel(PathToTheExcelFile)
ddf.style.applymap(highlight_cells)
ddf.to_excel(PathToTheExcelFile,index=False)

What I am currently getting is this:

enter image description here

What I want is this:

enter image description here

Solution

  • The style.applymap is for showing output of dataframes in HTML, not updating excel sheets. You can change the code thus to update the font in excel. I am reading the excel input.xlsx, updating the contents using openpyxl and writing it to output.xlsx. You can change other things like size, bold, fontname, etc. as well. Note: Color used is HEX color, but without the # symbol in front

    import openpyxl

wb = openpyxl.load_workbook(filename="input.xlsx")
ws = wb.active

for row in range(2,ws.max_row+1): #Skipping first row as I assume it is header
    if ws.cell(row=row, column=3).value == 'DATA':
        ws.cell(row=row, column=3).font = openpyxl.styles.Font(color='FF0000') #, size=16, bold=True, name='Calibri')
    else:
        ws.cell(row=row, column=3).font = openpyxl.styles.Font(color='C6E2E9')
wb.save("output.xlsx")

    USING pandas.ExcelWriter instead of openpyxl You can use the below code pandas.ExcelWriter to change the font to RED for DATA and GREEN for others. Note: you can edit the colors to anything you want using # followed by the 6 char hexcode in case you want to change the font color

    import pandas as pd
import numpy as np
df = pd.read_excel('output.xlsx')
df.fillna('NA', inplace = True)

writer = pd.ExcelWriter('output1.xlsx')
df.to_excel(writer, sheet_name= 'sheet1', index=False)
worksheet = writer.sheets['sheet1']
workbook = writer.book
cell_format_red = workbook.add_format({'font_color': 'red'})
cell_format_green = workbook.add_format({'font_color': 'green'})

start_row = 1
start_col = 2
end_row = len(df)
end_col = start_col

worksheet.conditional_format(start_row, start_col, end_row, end_col, {'type': 'cell', 'criteria': '==', 'value': '"DATA"', 'format': cell_format_red})
worksheet.conditional_format(start_row, start_col, end_row, end_col, {'type': 'cell', 'criteria': '!=', 'value': '"DATA"', 'format': cell_format_green})

writer.save()