Python - Using pandas to format excel cell

user_3843433 0
Viewed 32530

I have a pandas dataframe, which is something like shown below.

dataframee

I would like to format the column "Pass/Fail" as if Fail --> red background, else green background, like:

format

I have tried to use Pandas to do the formatting, but it fails to add color to the excel. Following is the code: 

writer = pandas.ExcelWriter(destination,engine = 'xlsxwriter')
color = Answer.style.applymap(lambda x: 'color: red' if x == "Fail" else 'color: green',subset= pandas.IndexSlice[:,['Pass/Fail']])
color.to_excel(writer,'sheet1')

I tried StyleFrame which failed to install. Seems that StyleFrame does not comply with my python version 3.6.

How can I format the excel as I want?

pandas dataframe format excel python
3 Answers

If have one or more columns and more than two values to format, and want to apply multiple format rules at once then you can do the following:

def fmt(data, fmt_dict):
    return data.replace(fmt_dict)

styled = df.style.apply(fmt, fmt_dict=fmt_dict, subset=['Test_1', 'Test_2' ])
styled.to_excel('styled.xlsx', engine='openpyxl')

more than two values, multiple columns, multiple format

Above, fm_dict is a dictionary with the values mapped to the corresponding format:

fmt_dict = {
    'Pass': 'background-color: green',
    'Fail': 'background-color: red',
    'Pending': 'background-color: yellow; border-style: solid; border-color: blue'; color: red,
}

Notice that for the 'Pending' value, you can also specify multiple format rules (e.g. border, background color, foreground color)

(Requires: openpyxl and jinja2)

Here is a full running example:

import pandas as pd

df = pd.DataFrame({'Test_1':['Pass','Fail', 'Pending', 'Fail'],
                   'expect':['d','f','g', 'h'],
                   'Test_2':['Pass','Pending', 'Pass', 'Fail'],
                  })

fmt_dict = {
    'Pass': 'background-color: green',
    'Fail': 'background-color: red',
    'Pending': 'background-color: yellow; border-style: solid; border-color: blue; color:red',
}

def fmt(data, fmt_dict):
    return data.replace(fmt_dict)

styled = df.style.apply(fmt, fmt_dict=fmt_dict, subset=['Test_1', 'Test_2' ])
styled.to_excel('styled.xlsx', engine='openpyxl')
user_10603490
Related