Color code a column based on values in another column in Excel using pandas

Question

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

Measure	value	lower limit	upper limit
A	1	0.1	1.2
B	2	0.5	1.5
C	10	1	100

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?

mozway · Accepted Answer · 2022-09-12 17:26:28Z

2

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:

answered Sep 12, 2022 at 17:26

mozway

267k13 gold badges56 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

Color code a column based on values in another column in Excel using pandas

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related