Python pandas dataframe and excel: Add cell background color

Question

Currently I save my dataframe like this

writer = ExcelWriter('test.xlsx')
test_df.to_excel(writer,'Sheet1')
writer.save()

And resulted excel file looks like this

cus  1  abc 2 jbd 3 lkl ...
1   col  v  v  v  v  v ...
2    v   v col v  v  v ... 
3    v   v  v  v col v ...

What I need is that, when cus value == header value, that cell should have a green background. In example above, all cells with value 'col' should be set green background. How can I do this?

Answer 1

There is a new feature in Pandas 0.20.0 - Excel output for styled DataFrames:

styled = (df.style
            .applymap(lambda v: 'background-color: %s' % 'green' if v=='col' else ''))
styled.to_excel('d:/temp/styled.xlsx', engine='openpyxl')

Result:

Answer 2

You can use the StyleFrame library to achieve this.

To install

pip install styleframe

The documentation of this library can be found here.

Try the following code to check whether it works to serve your purpose.

import pandas as pd
from StyleFrame import StyleFrame, Styler

df = pd.DataFrame(" Your Dataframe ")

sf = StyleFrame(df)
style = Styler(bg_color='green') 
for col_name in df.columns:
    sf.apply_style_by_indexes(sf.loc[sf['col_name']== col_name ], cols_to_style=col_name,
                          styler_obj=style)
sf.to_excel('test.xlsx').save()

Cheers!