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

Question

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:

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:

What I want is this:

you problem is not with you code it's when you are using the dataframe pandas, the problem is when you open the excel file you lose the formatting color — DataSciRookie
– DataSciRookie, Commented May 7, 2022 at 17:04

Code Different · Accepted Answer · 2022-05-09 21:17:48Z

4

+25

.style.applymap produces a Styler object, which has a to_excel method to conveniently export it:

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

ddf.style.applymap(highlight_cells).to_excel("data.xlsx", index=False)

# If you want to stylize only the Comments column
ddf.style.applymap(highlight_cells, subset="Comments").to_excel("data.xlsx", index=False)

Result:

answered May 9, 2022 at 21:17

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

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

Comments

Redox · Accepted Answer · 2022-05-09 17:45:06Z

0

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()

edited May 9, 2022 at 17:45

answered May 7, 2022 at 17:49

Redox

10.1k5 gold badges11 silver badges28 bronze badges

2 Comments

Python Bang Over a year ago

My environment does not support openpyxl and I do not have permission to install. Any other alternative ?

Redox Over a year ago

Updated response for using pandas.Excelwriter instead of openpyxl

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related