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

user_9691078 0
Viewed 984

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

conditional-formatting pandas fonts python
2 Answers

.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:

Stylized Excel

user_25389390

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