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, Mar 20 '17 at 11:49

DeepSpace · Accepted Answer · 2017-03-20T12:04:20.733

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 '17 at 12:04

answered Mar 20 '17 at 11:57

DeepSpace

78,697
11
109
154

But I have 201 rows and 2105 columns. @DeepSpace – jovicbg Mar 20 '17 at 12:02
@jovicbg See my updated answer for a generalized solution. The number of rows doesn't matter. – DeepSpace Mar 20 '17 at 12:04
Just deleted my row names, there is no that first column in excel – jovicbg Mar 21 '17 at 13:15

score 2 · Answer 2 · answered Mar 20 '17 at 11:42

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 '17 at 11:42

MaxU - stand with Ukraine

205,989
36
386
419

This actually work, but I can't save it to excel. It's disapeared after running in new jupyter cell – jovicbg Mar 20 '17 at 12:04

Colour cells in pandas dataframe

2 Answers2

Linked