coloring cells in excel with pandas

Question

I need some help here. So i have something like this

import pandas as pd
path = '/Users/arronteb/Desktop/excel/ejemplo.xlsx'
xlsx = pd.ExcelFile(path)
df = pd.read_excel(xlsx,'Sheet1')
df['is_duplicated'] = df.duplicated('#CSR')
df_nodup = df.loc[df['is_duplicated'] == False]
df_nodup.to_excel('ejemplo.xlsx', encoding='utf-8')

So basically this program load the ejemplo.xlsx (ejemplo is example in Spanish, just the name of the file) into df (a DataFrame), then checks for duplicate values in a specific column​​. It deletes the duplicates and saves the file again. That part works correctly. The problem is that instead of removing duplicates, I need highlight the cells containing them with a different color, like yellow.

Answer 1

You can create a function to do the highlighting...

def highlight_cells():
    # provide your criteria for highlighting the cells here
    return ['background-color: yellow']

And then apply your highlighting function to your dataframe...

df.style.apply(highlight_cells)

Answer 2

I just had this same problem and I just solved it this week. My problem was not getting the includes to work properly to get the online code that I found working properly.

I am going to assume you mean change the background color not change the font color. If I am wrong clarify your request.

My solution is tied to a particular library. openpyxl

#### This import section is where my mistake was at
#### This works for me
import openpyxl    ### Excel files 
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
from openpyxl.styles import Fill, Color
from openpyxl.styles import Style
from openpyxl.styles.colors import RED
from openpyxl.styles.colors import GREEN


str_xls_PathFileCurrent = str_xls_FileName
### Opens Excel Document
var_xls_FileOpen    = openpyxl.load_workbook(str_xls_PathFileCurrent) 
### Opens up the Excel worksheet 
var_xls_TabName     = var_xls_FileOpen.worksheets[0]                  
### Put the spreadsheet tab names into an array 
ary_xls_SheetNames  = var_xls_FileOpen.get_sheet_names()              
### Open the sheet in the file you working on 
var_xls_TabSheet    = var_xls_FileOpen.get_sheet_by_name(ary_xls_SheetNames[0])
xls_cell = var_xls_TabSheet['d10']

#### Changes the cell background color 
xls_cell.style = Style(fill=PatternFill(patternType='solid'
    , fgColor=Color('C4C4C4')))  ### Changes background color 

#### Changes the fonts (does not use style) 
xls_cell.font = xls_cell.font.copy(color  = 'FFFF0000') ### Works (Changes to red font text) 
xls_cell.font = xls_cell.font.copy(bold  = True) ### Works (Changes to bold font) 
xls_cell.font = xls_cell.font.copy(italic= True) ### Works (Changes to Italic Text) 
xls_cell.font = xls_cell.font.copy(size  =   34) ### Works (Changes Size) 

Answer 3

step by step coloring cells in excel with pandas and xlsxwriter

    import pandas as pd
    import xlsxwriter

    writer = pd.ExcelWriter( 'filename.xlsx', engine = 'xlsxwriter' )

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

    # create your own style 
    my_style = {    'bg_color': '#FFC7CE',
                    'font_color': '#9C0006'    }

    # make your style as a known format to the workbook
    workbook  = writer.book
    known_format = workbook.add_format(my_style)

    # define your boundaries: 
    start_row = 1
    end_row = len(df)
    start_col = 8
    end_col = start_col

    # define your condition when to apply the formatting: 
    my_condition =  {   'type'    :    'cell',
                        'criteria':    '>',
                        'value'   :    17, 
                        'format'  :   known_format
                    }

    # Apply everything into your chosen worksheet 
    worksheet = writer.sheets["Sheet1"]
    worksheet.conditional_format(   start_row, start_col,
                                    end_row, end_col,
                                    my_condition
                                )
    # Close the writer 
    writer.close()

    # open your file to see the Excel workbook now has some styles