Pandas Dataframe fill colour individual cells by hex colour input into cell before exporting to Excel

Question

I've created a dataset using a Pandas Dataframe where I have two columns, one of which has various hex colour codes that have been automatically generated from another dataset. I want to check that the hex colour input is what is expectedso it would be helpful to be able to fill colour each of the cells with the hex code provided. I have several codes to check through, so would be helpful to have this automatically done in Python prior to exporting to excel.

Cells filled by hex colour in cell

Any help would be greatly appreciated.

I've looked at this solution, but it seems too generic for what I'm after Coloring Cells in Pandas

And this solution is closest to what I'm after but applymap seems to have depreciated so I can't get it to work Python - color DF cell by hex value in cell

Answer 1

There doesn't seem to be an API for direct conversion from Pandas styling to Excel styling. As a result, I would suggest applying the color formatting AFTER the dataframe is converted to an Excel object. This could be done in-memory with a Pythonic Excel API - for which I chose openpyxl in the following sample code, but it could also be xlsxwriter or anything capable.

Edit: It is also possible to convert DataFrame rows to Worksheet rows via dataframe_to_rows() in openpyxl. The code might be cleaner, but a slow row-iteration loop is still present.

Solution

import io
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

# *** Part 1: Pandas DataFrame -> Openpyxl Workbook (in-memory) ***

# source df
df = pd.read_csv(io.StringIO("""
      hex1     hex2
0  #dcddde  #ffba00
1  #954eff  #b889ff
"""), sep=r"\s{2,}", engine="python")

# columns to be colored - please assign manually if irrelevant columns exist
hex_cols = df.columns.to_list()
#hex_cols = [f"hex{i}" for i in range(1, 1 + 2)]

# DataFrame -> I/O buffer
buffer = io.BytesIO()
with pd.ExcelWriter(buffer) as writer:
    df.to_excel(writer)

# I/O buffer -> Workbook
wb = load_workbook(buffer)
#del buffer

# *** Part 2: Apply background color ***

ws = wb["Sheet1"]

for hex_col in hex_cols:
    
    # locate the associated column index for the hex-color column
    for col_idx, cell in enumerate(ws["1:1"]):
        if cell.value == hex_col:
            col_idx += 1  # convert to 1-based index
            break
    else:
        raise ValueError("hex column name not found")

    # apply background color cell-by-cell
    for row_idx in range(2, 1 + ws.max_row):
        cell = ws.cell(row_idx, col_idx)
        color = cell.value[1:]  # remove leading "#" character
        # print(color)
        cell.fill = PatternFill(start_color=color, end_color=color, fill_type="solid")

# optional additional styling
for cell in ws['A'] + ws[1]:
    cell.style = 'Pandas'

# final output
wb.save("test.xlsx")

Result

test.xlsx opened with LibreOffice

Test environment

• Python (from conda): 3.12.2
• Pandas: 2.1.4
• Openpyxl: 3.0.10
• OS: Debian 12 (x86-64)