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?

Solution 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:

Solution 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!

Comments

[Jason Li]

This works well in notebook. If I run the same code in spyder. It complains that 'Styler' object has no attribute 'to_excel'. Any idea?

[MaxU - stop genocide of UA]

@JasonLi, please check that you have the same environment (versions, settings) when using spyder ...

[mp252]

what happens if you have multiple sheets

[Mahaveer Jangir]

Is it possible to apply multiple styles to a single dataframe ? How can I color the headers with different color . I want to know more about styling . Not able to find much on internet . Help please.

[MaxU - stop genocide of UA]

@MahaveerJangir, please check the official documentation: pandas.pydata.org/pandas-docs/stable/user_guide/style.html