Search code examples
pythonpandasdataframestyles

Color-code values in a table with condition given in another table

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

  • Use lambda function with extract last value after _ for match another DataFrame by DataFrame.xs and then use original solution:

    def color(x):
    g = x.name.split('_')[-1]
    df1 = df_limits.xs(g, axis=1, level=0)
    return x.between(df1['lower limit'], df1['upper limit'])
             .map({True: 'background-color: yellow', False:''})

df_values.style.apply(color, axis=0)

    pic