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

Question

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

jezrael · Accepted Answer · 2022-09-19 11:03:27Z

1

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)

edited Sep 19, 2022 at 11:03

answered Sep 19, 2022 at 10:39

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

s.cerioli Over a year ago

Many many thanks, really. Coming from R, pandas is a bit confusing sometimes, but thanks to your answers I could start understanding what was going on!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related