Pandas style.background_gradient ignore NaN

Question

I have the following code to dump the dataframe results into a table in HTML, such that the columns in TIME_FRAMES are colored according to a colormap from seaborn.

import seaborn as sns

TIME_FRAMES = ["24h", "7d", "30d", "1y"]

# Set CSS properties for th elements in dataframe
th_props = [
    ('font-size', '11px'),
    ('text-align', 'center'),
    ('font-weight', 'bold'),
    ('color', '#6d6d6d'),
    ('background-color', '#f7f7f9')
]

# Set CSS properties for td elements in dataframe
td_props = [
    ('font-size', '11px')
]


cm = sns.light_palette("green", as_cmap=True)
s = (results.style.background_gradient(cmap=cm, subset=TIME_FRAMES)
                  .set_table_styles(styles))
a = s.render()
with open("test.html", "w") as f:
    f.write(a)

From this, I get the warning:

/python3.7/site-packages/matplotlib/colors.py:512: RuntimeWarning: invalid value encountered in less xa[xa < 0] = -1

And, as you can see in the picture below, the columns 30d and 1y don't get rendered correctly, as they have NaN's. How can I just make it so that the NaN's are ignored and the colors are rendered only using the valid values? Setting the NaN's to 0 is not a valid option, as NaN's here have a meaning by themselves.

Burnell

how would you like the NaNs to be rendered? just a white background? – Baccalaureate

@Baccalaureate Either just plain white or blue – Burnell

This is due to how numpy handles the NaN value github.com/pandas-dev/pandas/issues/…, not sure what a workaround would be and the corresponding pandas issue has been closed. You could possibly fill with some unique small value that you know represents the NaN. – Habilitate

Answer 1

this works fine for me

df.style.applymap(lambda x: 'color: transparent' if pd.isnull(x) else '')

Reparable

This also combines the setting the background color, which makes @cheevahagadog's answer unneeded, too: .applymap(lambda x: 'color: transparent; background-color: transparent' if pd.isnull(x) else '') – Strafe

Answer 2

A bit late, but for future reference.

I had the same problem, and here is how I solved it:

import pandas as pd
import numpy as np
dt = pd.DataFrame({'col1': [1,2,3,4,5], 'col2': [4,5,6,7,np.nan], 'col3': [8,2,6,np.nan,np.nan]})

First fill in the nas with a big value

dt.fillna(dt.max().max()+1, inplace=True)

Function to color the font of this max value white

def color_max_white(val, max_val):
    color = 'white' if val == max_val else 'black'
    return 'color: %s' % color

Function to color the background of the maximum value white

def highlight_max(data, color='white'):
    attr = 'background-color: {}'.format(color)
    if data.ndim == 1:  # Series from .apply(axis=0) or axis=1
        is_max = data == data.max()
        return [attr if v else '' for v in is_max]
    else:  # from .apply(axis=None)
        is_max = data == data.max().max()
        return pd.DataFrame(np.where(is_max, attr, ''),
                            index=data.index, columns=data.columns)

Putting everything together

max_val = dt.max().max()

dt.style.format("{:.2f}").background_gradient(cmap='Blues', axis=None).applymap(lambda x: color_max_white(x, max_val)).apply(highlight_max, axis=None)

This link helped me for the answer

Louis

You could also change the colormap instead. cmap = copy.copy(plt.cm.get_cmap("Blues")) and then set cmap.set_under("white"). For some reason pandas interprets nan values as "under" instead of "bad", but anyhow this works with minimal effort. – Eradis

Answer 3

@quant 's answer almost worked for me but my background gradient would still use the max value to calculate the color gradient. I implemented @night-train 's suggestion to set the color map, then used two functions:

import copy
cmap = copy.copy(plt.cm.get_cmap("Blues"))
cmap.set_under("white")

def color_nan_white(val):
    """Color the nan text white"""
    if np.isnan(val):
        return 'color: white'

def color_nan_white_background(val):
     """Color the nan cell background white"""
    if np.isnan(val):
        return 'background-color: white'

And then applied them to my dataframe again borrowing from @quant with a slight modification for ease:

(df.style
    .background_gradient(axis='index')
    .applymap(lambda x: color_nan_white(x))
    .applymap(lambda x: color_nan_white_background(x))
)

Then it worked perfectly.

Axe

Answer 4

I did apply some of the answers above, before discovering there is a built-in option : Styler.highlight_null.html. Makes the code slightly shorter and easier to read.

To be applied in a simple fashion:

(df.style
    .background_gradient(cmap=my_gradient)
    .highlight_null(color="transparent")
)

Instead

No idea why this was downvoted. This is the way to do this in modern Pandas. – Evanescent