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

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?

Answer

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]})