Cell color excel dataframe Pandas

Question

I need your help with my dataframe. I want to color a certain cell of my excel dataframe. [![Image][1]][1]

As you can see in the image, i want to color in red each day of week where the value is 0 and in green if the value is 1. ("LU", "MA", "ME"......)

You can see a part of my code here :

But it doesn't work and I have no idea why. I tried many possibilites.

Can you help me ?

thanks a lot

Answer 1

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 💖🤗
    

Answer 2

You need to use the styler object and not the dataframe to call to_excel(). Something like this:

import pandas as pd


def highlight(val):
    if isinstance(val, (int, float)):
        color = 'red' if val < 0 else 'green'
        return 'color: %s' % color

df_final = pd.DataFrame({'Data': [1, "foo", -1, -5, 5, 6, -5]})

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

styler = df_final.style.applymap(highlight)
styler.to_excel(writer)

writer.save()

Output:

Updated the highlight() function to have it only apply to numbers. You may need to strengthen/extend it a bit more for other data types.

Alternatively, you could use an Excel conditional format, like this:

import pandas as pd



# Create a Pandas dataframe from some data.
df_final = pd.DataFrame({'Data': [1, "Foo", -1, -5, 5, 6, -5]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_conditional.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df_final.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a format. Light red fill with dark red text.
red_format = workbook.add_format({'bg_color': '#FFC7CE',
                                  'font_color': '#9C0006'})

# Add a format. Green fill with dark green text.
green_format = workbook.add_format({'bg_color': '#C6EFCE',
                                    'font_color': '#006100'})

# Calculate the range to which the conditional format is applied.
(max_row, max_col) = df_final.shape
min_row = 1  # Skip header.
min_col = 1  # Skip index.
max_row = min_row + max_row -1
max_col = min_col + max_col -1

# Apply a conditional format to the cell range.
worksheet.conditional_format(min_row, min_col, max_row, max_col,
                             {'type':     'cell',
                              'criteria': '<',
                              'value':    0,
                              'format':   red_format})

worksheet.conditional_format(min_row, min_col, max_row, max_col,
                             {'type':     'cell',
                              'criteria': '>=',
                              'value':    0,
                              'format':   green_format})

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output2: