How to skip coloring inf values?

Question

I'm styling some data in excel after processing in pandas. But I have some bad data that gives me lot of inf values. Is there a way to skip coloring inf values?

        cr = sns.light_palette("green", as_cmap=True)
        styler = data.style
        zeleno = styler.background_gradient(
            cmap=cr,
            subset=data.columns.get_loc_level('value1', level=1)[0]
        )
        styler.to_excel(writer, sheet_name=name)

I got data like this.

I need to be like this, (skip inf values, they can be white or some other colors)

Answer 1

Currently, it is still a better approach to use apply and implement this manually as, even with the addition of gmap in 1.3.0, Styler.background_gradient cannot support separate Data manipulation and display as well as subsetting.

---

To make this work we need to accomplish 2 things:

1. set_bad to establish how to handle the "bad" values
2. Make sure that inf are considered to be "bad" (which they are not)

The easiest way to ensure that inf are considered bad is to replace inf with NaN.

# Subset The DataFrame Before-Hand
cr = sns.light_palette("green", as_cmap=True)
# Set Bad to something like White
cr.set_bad('white')


def my_gradient(s, cmap):
    return [f'background-color: {mcolors.rgb2hex(x)}'
            for x in cmap(s.replace(np.inf, np.nan))]


# Use Subset Styler
styler = data.style
zeleno = styler.apply(
    my_gradient,
    cmap=cr,  # Passed to function
    subset=data.columns.get_loc_level('value1', level=1)[0],
    axis=0  # (Default)
)
zeleno

*Styling only applied to subset (value1 on level=1)

---

Setup and Imports Used:

import numpy as np
import pandas as pd  # version 1.3.3
import seaborn as sns
from matplotlib import colors as mcolors

np.random.seed(25)
a = np.random.random((3, 4))
# Replace Randomly With inf
a[np.random.randint(0, 2, size=a.shape) == 0] = np.inf

data = pd.DataFrame(a, columns=pd.MultiIndex.from_product([
    list('AB'),
    ['value1', 'value2']
]))
data