How to map color names with their HEX to make a conditionnal formating

Question

I have the dataframe below:

import pandas as pd

df = pd.DataFrame({'ID': ['ID001', 'ID002', 'ID003', 'ID004', 'ID005', 'ID006'],
                   'Color': ['Red', 'Green', 'Blue', 'Green', 'Yellow', 'Purple']})

      ID   Color
0  ID001     Red
1  ID002   Green
2  ID003    Blue
3  ID004   Green
4  ID005  Yellow
5  ID006  Purple

And I'm trying to get this kind of Excel spreadsheet output :

By using xlsxwriter, the code below that I made has no effect :

with pd.ExcelWriter('TestColor_SpreadSheet.xlsx') as writer:
    df.to_excel(writer, index=False, sheet_name='TestWorksheet')

    workbook = writer.book
    worksheet = writer.sheets['TestWorksheet']

    format_red = workbook.add_format({'bg_color':'#FF0000'})

    worksheet.conditional_format('B1:B7', {'type': 'cell',
                                        'criteria': '=',
                                        'value': "Red",
                                        'format': format_red})

Do you have any propositions please on how to do this conditionnal formating in xlsxwriter or maybe even with pandas ?

jprebys · Accepted Answer · 2022-11-01T13:43:03.133

2

Matplotlib can convert color names to RGB and hex values for you

>>> from matplotlib import colors
>>> 
>>> colors.to_rgb('blue')
(0.0, 0.0, 1.0)
>>> 
>>> colors.to_hex('blue')
'#0000ff'

edited Nov 01 '22 at 13:43

answered Nov 01 '22 at 13:37

jprebys

2,469
1
11
16

1

Thank you so much! I posted an answer with the full code inspired by your proposition ;). – Timeless Nov 01 '22 at 14:16

Timeless · Answer 2 · 2022-11-01T14:21:51.600

Thanks to @jprebys's answer above and @Sergey's here, I ended up with this code:

import pandas as pd
from matplotlib import colors

df = pd.DataFrame({'ID': ['ID001', 'ID002', 'ID003', 'ID004', 'ID005', 'ID006'],
                   'Color': ['Red', 'Green', 'Blue', 'Green', 'Yellow', 'Purple']})

# ---- Retrieving HEXes with matplotlib

def to_hex(df):
    return colors.to_hex(df["Color"])

df["HEX"] = df.apply(to_hex, axis=1)

# ---- Creating a dictionnary of color names and their HEX

df_colors  = df.loc[:, ["Color", "HEX"]].drop_duplicates()
dico_colors = dict(zip(df_colors["Color"], df_colors["HEX"]))

df.drop(columns="HEX", inplace=True)

# --- Formatting the Excel Spreadsheet

with pd.ExcelWriter('TestColor_SpreadSheet.xlsx') as writer:
    df.to_excel(writer, index=False, sheet_name='TestWorksheet')
    
    workbook = writer.book
    worksheet = writer.sheets['TestWorksheet']
    
    dico_format = {}
    for idx, row in df.iterrows():
        key = row['Color']
        dico_format['bg_color'] = dico_colors[key]
        cell_format = workbook.add_format(dico_format)
        worksheet.write(idx+1, 1, key, cell_format)

Note : 'bg_color' stands for Background color as per the official documentation.

How to map color names with their HEX to make a conditionnal formating

2 Answers2

# Outupt (in Excel):