Pandas Styler conditional formatting based on comparison of each row with last row

Question

I have a large dataframe that comes from calculation with varying number of columns and rows:

   a  b  c  d  e
0  5  1  5  1  4
1  0  3  1  1  5
2  2  5  2  5  1
3  2  3  2  3  4

Each column has last row that decides coloring of each cell in that column. Each cell of the column needs to be compared with the last cell of that particular column and then the condition to be applied is: if s>s[-1]: background-color: green, if s<s[-1]: background-color: red, if s==s[-1]: background-color: yellow.

To get output like this:

Therefore I need to highlight all rows except last using the above conditions.

I tried with two conditions but could not get the third condition:




data = [[5,1,5,1,4], [0,3,1,1,5], [2,5,2,5,1],[2,3,2,3,4]]
  
# Create the pandas DataFrame
df = pd.DataFrame(data, columns=['a','b','c','d','e'])
       
def highlight_greater(s):
    is_max = s > s[-1]
    return ['background-color: green' if i else 'background-color: yellow'  for i in is_max ]   
    
df.style.apply(highlight_greater)

I don't know how to use three condition using this function. Can some one help?

Please provide a reproducible input

mozway
– mozway

2022-08-27 03:39:14 +00:00
Commented Aug 27, 2022 at 3:39 — mozway
– mozway, Commented Aug 27, 2022 at 3:39

mozway · Accepted Answer · 2022-08-27 04:18:18Z

2

You can use:

colors = {1: 'green', 0: 'yellow', -1: 'red'}

# mask to leave the last row without style
mask = np.tile(df.index==df.index[-1], (df.shape[1],1)).T

# compute the sign of difference to last row
# and map colors + style
style = (np.sign(df.sub(df.iloc[-1])
                   .mask(mask)
                 )
           .replace(colors)
           .radd('background-color: ')
         )

# apply style
df.style.apply(lambda x: style, axis=None)

Output:

Used input:

df = pd.DataFrame({'a': [5, 0, 2, 2],
                   'b': [1, 3, 5, 3],
                   'c': [5, 1, 2, 2],
                   'd': [1, 1, 5, 3],
                   'e': [4, 5, 1, 4]})

edited Aug 27, 2022 at 4:18

answered Aug 27, 2022 at 4:10

mozway

267k13 gold badges56 silver badges106 bronze badges

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

6 Comments

Shiva Over a year ago

when I ran this i got: AttributeError: 'float' object has no attribute 'rstrip'

mozway Over a year ago

Are you sure you ran my exact code? I don't use rstrip. Check the full error message. And make sure to run my exact code on the provided example (see Reddit for a reproducible input).

Shiva Over a year ago

Yes, this the full code i used:

data = [[5,1,5,1,4], [0,3,1,1,5], [2,5,2,5,1],[2,3,2,3,4]]    # Create the pandas DataFrame df = pd.DataFrame(data, columns=['a','b','c','d','e'])         colors = {1: 'green', 0: 'yellow', -1: 'red'}  mask = np.tile(df.index==df.index[-1], (df.shape[1],1)).T  style = (np.sign(df.sub(df.iloc[-1])                    .mask(mask)                  )            .replace(colors)            .radd('background-color: ')          )  df.style.apply(lambda x: style, axis=None)

mozway Over a year ago

I checked again and it works without issue. Please ensure you have an up to date pandas (tested on 1.4.3).

mozway Over a year ago

@Shiva have you tested again? What is your pandas version? I'm wondering whether the error comes from the masked values. Try without the .mask(mask), if this works without error (this will also color the last list in yellow), then you can mask with a white color.

|

Collectives™ on Stack Overflow

Pandas Styler conditional formatting based on comparison of each row with last row

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related