2

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

Hamza
  • 5,373
  • 3
  • 28
  • 43

3 Answers3

1

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

Scott Boston
  • 147,308
  • 15
  • 139
  • 187
  • This helps. Exactly what I had in mind. Turned out setting max value was the answer. Thanks @Scott Boston – Hamza Apr 03 '21 at 23:15
0

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

Vishnudev Krishnadas
  • 10,679
  • 2
  • 23
  • 55
  • Hello! @Vishnudev This still doesnot encompass for the heatmap. The functionality you just posted is the same as what my function achieves (A lot cleaner tho. I admit) – Hamza Mar 10 '21 at 18:18
  • Also not to be very specific but condition should be `>= 100` – Hamza Mar 10 '21 at 18:29
  • This still does not answer my question as I want: 1. same color across each row. 2. Exclude already achieved goal rows from heatmap and color them differently – Hamza Mar 10 '21 at 23:03
0

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

Quang Hoang
  • 146,074
  • 10
  • 56
  • 74
  • Hey @Quang Hoang! This does not my question completely. This goes to applying heatmap to whole datafame based on single column value but it does not incorporate exclusion of already achieved goals. – Hamza Mar 10 '21 at 23:02