How to color a dataframe to a conditional heatmap with same color across whole row based on a single column

user_7212929 0
Viewed 534

So I have a dataframe which looks like:

Target, Achieved, Goal, Remaining
10, 5, 50, 5
4, 5, 125, 0
3, 3, 100, 0
8, 2, 25, 6

I want to display this dataframe with visible info based on colors, Under this criteria:

  1. If goal is achieved I just wanted row to be green regardless of how surpassed goal actually is. So the whole 2nd and 3rd rows would be the same green color
  2. If goal is not achieved, I want to color them based on heatmap. So here I want 4th row to be a darker shade (of lets say red) than 1st row since I am missing more on that row's goal.

For single color, following function works perfectly: Thanks to the answer here

def highlight_col(x):
    #copy df to new - original data are not changed
    df = x.copy()
    #set by condition
    mask = df['Goal Completion (%)'] >= 100
    df.loc[mask, :] = 'background-color: lightgreen'
    df.loc[~mask,:] = 'background-color: pink'
    return df

For a simple heatmap without excluding Goal completion condition, its possible via:

df.style.background_gradient(cmap='Reds')

But it:

  1. Includes whole dataframe
  2. Color by each column separately
  3. Cannot exclude rows with goals achieved
  4. Could not be used inside my above function (tried using: df.loc[~mask,:] = 'background_gradient: Reds' in last line but didn't work either.

P.S. My Dataframe isn't very large so I prefer table coloring itself from where I can select rows instead of having a whole new viz. Any suggestions bettering the situation are highly welcomed!

Sample output:

enter image description here

pandas heatmap dataframe python
3 Answers

Perhaps you are looking for something like this (using @QuangHoang methods):

import pandas as pd
import numpy as np
import matplotlib as mpl

df = pd.read_clipboard(sep=',\s+')

cmap = mpl.cm.get_cmap('RdYlGn')
norm = mpl.colors.Normalize(df['Goal'].min(), 100.)

def colorRow(s):
    return [f'background-color: {mpl.colors.to_hex(cmap(norm(s["Goal"])))}' for _ in s]


df.style.apply(colorRow, axis=1)

Output:

enter image description here

user_63615310

You can apply cmap to a certain subset of a dataframe

df.style.background_gradient('Reds', subset=pd.IndexSlice[df.Goal < 100, ])

Output

enter image description here

user_51200490

You can try extracting the color by the value in Goal with cmap:

def hightlight_col(d, refcol=None, cmap='Reds'):
    min_goal, max_goal = d[refcol].agg(['min','max'])
    goals = d[refcol].sub(min_goal)/(max_goal-min_goal)
    cmap=mpl.cm.get_cmap(cmap)
    
    return pd.DataFrame([[f'background-color: {mpl.colors.to_hex(cmap(goal))}']*d.shape[1]
                   for goal in goals
                 ], index=d.index, columns=d.columns)
    
df.style.apply(hightlight_col, refcol='Goal', 
               cmap='coolwarm', axis=None)

Output:

enter image description here

user_42384080
Related