Colour cells in pandas dataframe

Question

I have a dataframe with different values, but most of tham are NaN (None). I want to colour cells where value is not a None, where is actually value. For example: dataxml=

    1      2     3      4     5 
a  NaN    23    NaN   NaN    65
b  NaN    NaN    54   NaN   NaN
c   33    NaN   NaN   NaN   NaN
d  NaN    NaN    24   NaN   NaN

This code color whole dataframe:

def highlight_cells(value):
    if value != None:
        return 'background-color: yellow'

dataxml.style.applymap(highlight_cells)

I have parsed XML file, find the difference between attribute value, then transform into pandas dataframe and now I need highlight that cells with difference to make it easier to find it in excel, if there is no difference, do nothing. — jovicbg
– jovicbg, Commented Mar 20, 2017 at 11:49

DeepSpace · Accepted Answer · 2017-03-20 12:04:20Z

3

As far as I know, df.style is only used when exporting the dataframe to HTML.

For Excel, If I may suggest using a library that I wrote, see this example code (Note that this is using strings for the column headers since there is a small issue with using integers as headers):

import pandas as pd
from StyleFrame import StyleFrame, Styler

df = pd.DataFrame({'1': [None, None, 33, None],
                   '2': [23, None, None, None],
                   '3': [None, 54, None, 24]})

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

This generates the following Excel:

edited Mar 20, 2017 at 12:04

answered Mar 20, 2017 at 11:57

DeepSpace

82.1k12 gold badges119 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jovicbg Over a year ago

But I have 201 rows and 2105 columns. @DeepSpace

DeepSpace Over a year ago

@jovicbg See my updated answer for a generalized solution. The number of rows doesn't matter.

jovicbg Over a year ago

Just deleted my row names, there is no that first column in excel

MaxU - stand with Ukraine · Accepted Answer · 2017-03-20 11:42:19Z

2

you can do it this way:

notnull_color = 'yellow'
df.style.applymap(lambda x: 'background-color: %s' % notnull_color if pd.notnull(x) else '')

answered Mar 20, 2017 at 11:42

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

1 Comment

jovicbg Over a year ago

This actually work, but I can't save it to excel. It's disapeared after running in new jupyter cell

Collectives™ on Stack Overflow

Colour cells in pandas dataframe

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related