how to highlight pandas data frame on selected rows

Question

I have the data like this:

df:


    A-A      A-B    A-C      A-D     A-E
Tg  0.37    10.24   5.02    0.63    20.30
USL 0.39    10.26   5.04    0.65    20.32
LSL 0.35    10.22   5.00    0.63    20.28
1   0.35    10.23   5.05    0.65    20.45
2   0.36    10.19   5.07    0.67    20.25
3   0.34    10.25   5.03    0.66    20.33
4   0.35    10.20   5.08    0.69    20.22
5   0.33    10.17   5.05    0.62    20.40
Max 0.36    10.25   5.08    0.69    20.45
Min 0.33    10.17   5.03    0.62    20.22

I would like to color-highlight the data (index 1-5 in this df) by comparing Max and Min of the data (last two rows) to USL and LSL respectively. if Max > USL or Min < LSL, I would like to highlight the corresponding data points as red. if Max == USL or Min == LSL, corresponding data point as yellow and otherwise everything green.

I tried this :

highlight = np.where(df.loc['Max']>df.loc['USL'], 'background-color: red', '')
df.style.apply(lambda _: highlight)

but i get the error:

ValueError: Function <function <lambda> at 0x7fb681b601f0> created invalid index labels.
Usually, this is the result of the function returning a Series which contains invalid labels, or returning an incorrectly shaped, list-like object which cannot be mapped to labels, possibly due to applying the function along the wrong axis.
Result index has shape: (5,)
Expected index shape:   (10,)

Out[58]:
<pandas.io.formats.style.Styler at 0x7fb681b52e20>

jezrael · Accepted Answer · 2022-08-16 05:21:07Z

3

Use custom function for create DataFrame of styles by conditions:

#changed data for test
print (df)
      A-A    A-B   A-C    A-D
Tg   0.37  10.24  5.02   0.63
USL  0.39  10.26  5.04   0.65
LSL  0.33   0.22  5.00  10.63
1    0.35  10.23  5.05   0.65
2    0.36  10.19  5.07   0.67
3    0.34  10.25  5.03   0.66
4    0.35  10.20  5.08   0.69
5    0.33  10.17  5.05   0.62
Max  0.36  10.25  5.08   0.69
Min  0.33  10.17  5.03   0.62

def hightlight(x):
    c1 = 'background-color:red'
    c2 = 'background-color:yellow'
    c3 = 'background-color:green'
    #if values of index are strings
    r = list('12345')
    #if values of index are integers
    r = [1,2,3,4,5]

    m1 = (x.loc['Max']>x.loc['USL']) | (x.loc['Min']<x.loc['LSL'])
    print (m1)
    m2 = (x.loc['Max']==x.loc['USL']) | (x.loc['Min']==x.loc['LSL'])
    print (m2)
    #DataFrame with same index and columns names as original filled empty strings
    df1 = pd.DataFrame('', index=x.index, columns=x.columns)
    #modify values of df1 columns by boolean mask
    df1.loc[r, :] = np.select([m1, m2], [c1, c2], default=c3)
    return df1

df.style.apply(hightlight, axis=None)

EDIT: For compare 1-5 and Min/Max use:

def hightlight(x):
    c1 = 'background-color:red'
    c2 = 'background-color:yellow'
    c3 = 'background-color:green'
    #if values of index are strings
    r = list('12345')
    #if values of index are integers
    # r = [1,2,3,4,5]
    
    r += ['Max','Min'] 

    m1 = (x.loc[r]>x.loc['USL']) | (x.loc[r]<x.loc['LSL'])
    m2 = (x.loc[r]==x.loc['USL']) | (x.loc[r]==x.loc['LSL'])

    #DataFrame with same index and columns names as original filled empty strings
    df1 = pd.DataFrame('', index=x.index, columns=x.columns)
    #modify values of df1 columns by boolean mask
    df1.loc[r, :] = np.select([m1, m2], [c1, c2], default=c3)
    return df1

df.style.apply(hightlight, axis=None)

edited Aug 16, 2022 at 5:21

answered Aug 15, 2022 at 6:25

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

10 Comments

A.E Over a year ago

thanks for the answer but it does not do what is intended; here is the output ibb.co/K5qQsyk

jezrael Over a year ago

@A.E - Because always match first condition then all values are red. If match condition need same color per columns, right?

jezrael Over a year ago

@A.E - added changed data for testing, then got also green and yellow columns

jezrael Over a year ago

@A.E - sure, answer was edited.

jezrael Over a year ago

@A.E - Answer was edited.

|

Collectives™ on Stack Overflow

how to highlight pandas data frame on selected rows

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related