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


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:

enter image description here

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:

enter image description here

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")


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)


enter image description here

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.

people found this article helpful. What about you?