apply color to the cells based on the value in cell in dataframe

Question

working code

 import pandas as pd
import seaborn as sns
import matplotlib as mpl
import numpy as np
from matplotlib import colors,cm
from matplotlib import pyplot as plt

filename = r'c:\Users\91956\Desktop\time_50.csv'
df = pd.read_csv(filename,index_col=0)
select_col = df.columns[1:]

cmap = mpl.colors.LinearSegmentedColormap.from_list("", ["red","white", "green"])

def background_gradient(s, cmap='PuBu', low=0, high=0):
    s = pd.to_numeric(s, errors='coerce') #<-- here, string will become nan.
    m = s.min() #<---------- here
    M = s.max() #<-----------here
    rng = M - m
    norm = colors.TwoSlopeNorm(vmin=m - (rng * low), vcenter=0., vmax=M + (rng * high))
    normed = norm(s.values)
    c = [colors.rgb2hex(x) for x in plt.cm.get_cmap(cmap)(normed)]
    return ['background-color: %s' % color for color in c]

S = df.style.apply( background_gradient,
                    cmap=cmap,
                    low=0.5,
                    high=0.5,
                    subset= pd.IndexSlice[:, select_col],
                    axis=1
                )

html = S.render()
with open("output.html","w") as fp:
    fp.write(html)

i was getting this error

File "c:\Users\91956\Desktop\asdf.py", line 29, in m=df.min().min(), File "C:\Users\91956\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\generic.py", line 11468, in stat_func return self._reduce( File "C:\Users\91956\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\series.py", line 4248, in _reduce return op(delegate, skipna=skipna, **kwds) File "C:\Users\91956\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\nanops.py", line 129, in f result = alt(values, axis=axis, skipna=skipna, **kwds) File "C:\Users\91956\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\nanops.py", line 873, in reduction result = getattr(values, meth)(axis) File "C:\Users\91956\AppData\Local\Programs\Python\Python39\lib\site-packages\numpy\core_methods.py", line 43, in _amin return umr_minimum(a, axis, None, out, keepdims, initial, where) TypeError: '>=' not supported between instances of 'numpy.ndarray' and 'str'

update 2 did necessary changes. was able to get required output.

The problem is that that the median of your value range is not zero. Try setting `low, high` when calling `background_gradient` [info](https://pandas.pydata.org/pandas-docs/version/0.17.1/generated/pandas.core.style.Styler.background_gradient.html) — cmosig, Dec 17 '20 at 13:58
what should be high and low such that zero will be median, eg values like low = -5 and high = 5? — user4599, Dec 17 '20 at 14:01
i will update you once i get it right. thank you for your help. — user4599, Dec 17 '20 at 15:25

Pygirl · Accepted Answer · 2020-12-17T15:09:15.553

2

This answer will help and also this answer.

To create sample df:

import pandas as pd
import numpy as np

np.random.seed(24)
df = pd.DataFrame({'A': np.linspace(1, 10, 10)})
df = pd.concat([df, pd.DataFrame(np.random.randn(10, 4), columns=list('BCDE'))],
               axis=1)
df.iloc[3, 3] = np.nan
df.iloc[0, 2] = np.nan

from matplotlib import colors

cmap=LinearSegmentedColormap.from_list('rg',["r","w","g"], N=256) 

def background_gradient(s, m, M, cmap='PuBu', low=0, high=0):
    s = pd.to_numeric(s, errors='coerce') #<-- here, string will become nan.
    print(s)
    rng = M - m
    norm = colors.DivergingNorm(vmin=m - (rng * low), vcenter=0., vmax=M + (rng * high))
    normed = norm(s.values)
    c = [colors.rgb2hex(x) for x in plt.cm.get_cmap(cmap)(normed)]
    return ['background-color: %s' % color for color in c]

x = df.apply(pd.to_numeric, errors='coerce') #<--- here string will be converted to `NaN` so that I can find out the max and min value.
df.style.apply(background_gradient,
               cmap=cmap,
               m=x.min().min(),
               M=x.max().max(),
               low=0.5,
               high=0.5, subset=pd.IndexSlice[:, ['B', 'C']]
              )

Edit:

pass subset=pd.IndexSlice[:, ['B', 'C']] to apply <-- I want to apply colour on B and C column.

df.style.apply(background_gradient,
               cmap=cmap,
               m=df.min().min(),
               M=df.max().max(),
               low=0.5,
               high=0.5, subset=pd.IndexSlice[:, ['B', 'C']]
              )

Edit2:

from matplotlib import colors

cmap=LinearSegmentedColormap.from_list('rg',["r","w","g"], N=256) 

def background_gradient(s, cmap='PuBu', low=0, high=0):
    s = pd.to_numeric(s, errors='coerce')
    m = s.min() #<---------- here
    M = s.max() #<-----------here
    rng = M - m
    norm = colors.DivergingNorm(vmin=m - (rng * low), vcenter=0., vmax=M + (rng * high))
    normed = norm(s.values)
    c = [colors.rgb2hex(x) for x in plt.cm.get_cmap(cmap)(normed)]
    return ['background-color: %s' % color for color in c]

# x = df.apply(pd.to_numeric, errors='coerce')
df.style.apply(background_gradient,
               cmap=cmap,
               low=0.5,
               high=0.5, subset=pd.IndexSlice[:, ['B', 'C', 'D']],  axis=1
              )

Use axis=1 in apply (along column wise comparison for each row)

edited Dec 17 '20 at 15:09

answered Dec 17 '20 at 14:12

Pygirl

12,969
5
30
43

what is s in norm(s.values) – user4599 Dec 17 '20 at 14:30
@AniketPatil: s is your dataFrame df's elements – Pygirl Dec 17 '20 at 14:32
pass `subset=pd.IndexSlice[:, ['B', 'C']]` check the edited answer. you can select row also using this or only columns you want to pass you can pass a column name list to the subset. You can modify this code as per your need :) – Pygirl Dec 17 '20 at 14:37
df that i am using is shown in picture, it contains str data as well ticker column, i tried to modify my code, but not able to get desired output, i have updated the question with the code i am using – user4599 Dec 17 '20 at 14:48
can you tell me what are the necessary changes to get desired result? – user4599 Dec 17 '20 at 14:57
Check I have updated and look at arrow one code. – Pygirl Dec 17 '20 at 15:01
m and M values should get calculated for each row – user4599 Dec 17 '20 at 15:01
see Edit2. Can't help further now :) This will be enuf I guess. Now you can modify as per your need. @AniketPatil: What error? – Pygirl Dec 17 '20 at 15:09
see my code in the question i was using axis = 1 before you mentioned, error is TypeError: '>' not supported between instances of 'numpy.ndarray' and 'str' – user4599 Dec 17 '20 at 15:12
you missed this : `s = pd.to_numeric(s, errors='coerce')` . change the row to numeric type and make string as nan and then perform everything. – Pygirl Dec 17 '20 at 15:13
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/226117/discussion-between-aniket-patil-and-pygirl). – user4599 Dec 18 '20 at 05:36
2

Very complete answer! Nice! – Celius Stingher Dec 22 '20 at 18:11
@CeliusStingher: Thanks :') . If you don't mind can I connect to you on linkedin. I will get to know a lot of thing from you guys TmT. – Pygirl Dec 22 '20 at 18:12

apply color to the cells based on the value in cell in dataframe

1 Answers1

Edit2: