I have a pandas data frame that I am then writing into an Excel sheet:

I would like to color code the column "value" based on the condition that "value" is contained between "lower limit" and "upper limit".

What I have done so far is to create an extra column called "within limits" to check that the condition is true or false, but for this case I can only find solutions in pandas that are color coding the column "within limits" itself, and not the "value" column.

Is there a way to color code based on another column value?

CodePudding user response：

You can use a custom function:

def color(df):
    out = pd.DataFrame(None, index=df.index, columns=df.columns)
    out['value'] = (df['value']
                    .between(df['lower limit'], df['upper limit']) 
                    .map({True: 'background-color: yellow'})
                    )
    return out

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

With parameters:

def color(df, value, low, high, color='red'):
    out = pd.DataFrame(None, index=df.index, columns=df.columns)
    out[value] = (df[value]
                    .between(df[low], df[high]) 
                    .map({True: f'background-color: {color}'})
                    )
    return out

df.style.apply(color, value='value', low='lower limit', high='upper limit',
               color='yellow', axis=None)

output: