# Issue

This Content is from Stack Overflow. Question asked by s.cerioli

I have already asked a similar question here: Color code a column based on values in another column in Excel using pandas but then I realised it was too much simplified for my case.

I want to create an Excel file with a table having the cells color-coded based on certain conditions. The condition is that the value in a cell is between a lower an an upper limit, these limits given for two different types of data – aa and bb. Therefore, I have a table summarising the limits:

In another table, I have the values that I need to compare to the respective limit to understand if they are within or not. I don’t know in advance how many values I will have, but they can be of type aa or type bb, and this is given in their name:

How to have a final table to be then written in Excel, where I have my values color-coded based on if they are within limits or not?

Here the code to reproduce the example:

``````import pandas as pd

df_limits_1 = pd.DataFrame({"Measure": ["A", "B", "C"],
"lower limit": [0.1, 1, 10],
"upper limit": [1.2, 3.4, 100]})
df_limits_1 = df_limits_1.set_index("Measure")

df_limits_2 = pd.DataFrame({"Measure": ["A", "B", "C"],
"lower limit": [0.3, 2, 15],
"upper limit": [1.1, 5, 28]})
df_limits_2 = df_limits_2.set_index("Measure")

df_limits_1.columns = pd.MultiIndex.from_product([['aa'], df_limits_1.columns])
df_limits_2.columns = pd.MultiIndex.from_product([['bb'], df_limits_2.columns])
df_limits = pd.concat([df_limits_1, df_limits_2], axis = 1)

df_values = pd.DataFrame({"Measure": ["A", "B", "C"],
"value1_aa": [1, 5, 34],
"value1_bb": [0.2, 3, 21],
"value2_aa": [0.3, 2, 23],
"value2_bb": [1, 0.9, 12]})
df_values = df_values.set_index("Measure")
``````

# Solution

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:

``` This Question was asked in  StackOverflow by  s.cerioli and Answered by mozway It is licensed under the terms of
CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.```