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

I have already asked a similar question 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:

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

CodePudding user response：

Use lambda function with extract last value after _ for match another DataFrame by