Coloring multiindex DataFrame based on another DataFrame-CodePudding

I have 2 multiIndex Dataframes with same index and columns. I would like to publish the first Dataframe df1 to_html() with certain cells colored by condition from the second DataFrame df2. Conditions: if value in df2 >= 1.5: color red if value in in 1 <= df2 <1.5: color green all else uncolored

data1 = {'index1': ['1', '1', '2', '2', '3', '3', '4', '4'], 'index2': ['A', 'B', 'A', 'B', 'A', 'B', 'A', 'B'], 'Val1': ['0.02642262827', '0.8797893206', '0.1707945866', '0.788045912', '0.803239534', '0.3903782267', '0.8182729864', '0.07671425696'], 'Val2': ['0.7940922523', '0.6275405841', '0.6075569809', '0.2292709462', '0.02984363781', '0.9535781396', '0.5034810448', '0.08664072674']}
data2 = {'index1': ['1', '1', '2', '2', '3', '3', '4', '4'], 'index2': ['A', 'B', 'A', 'B', 'A', 'B', 'A', 'B'], 'Val1': ['0.05', '0.34', '1.66', '0.5', '0.57', '1.89', '1.1', '0.003'], 'Val2': ['0.67', '1.3', '0.55', '0.2', '1.56', '0.78', '0.13', '0.43']}

df1=

        Val1            Val2
1   A   0.02642262827   0.7940922523
    B   0.8797893206    0.6275405841
2   A   0.1707945866    0.6075569809
    B   0.788045912 0.2292709462
3   A   0.803239534 0.02984363781
    B   0.3903782267    0.9535781396
4   A   0.8182729864    0.5034810448
    B   0.07671425696   0.08664072674

df2=

        Val1    Val2
1   A   0.05    0.67
    B   0.34    1.3
2   A   1.66    0.55
    B   0.5     0.2
3   A   0.57    1.56
    B   1.89    0.78
4   A   1.1     0.13
    B   0.003   0.43

What is the best way of doing this?

Output should look something like this:

CodePudding user response：

Since the index and columns of df1 and df2 matches, you can create a color mask by applying given conditions on df2, then use this color mask to update the style of df1

mask = np.select(
    [df2.ge(1) & df2.lt(1.5), df2.ge(1.5)], 
    ['color: red', 'color: green'], default=None
)

df1.style.apply(lambda _: mask, axis=None)