Applying style to multindex and multilevel pandas dataframe

Question

I have a pandas dataframe which when dumped to excel appears as following:

I am trying to achieve the following during excel dump however:

So, I am using the following code:

import pandas as pd, numpy as np, sys, os

df = pd.DataFrame()
df['ATC'] =np.random.rand(1, 7).round(2).flatten()
df['P25'] =np.random.rand(1, 7).round(2).flatten()
df['Type1'] = ['A', 'B', 'B', 'A', 'B', 'B', 'A']
df['Type11'] = ['A', 'Aa', 'Bb', 'A', 'Bb', 'B', 'Bb']
df['Type2'] = ['X', 'X', 'X', 'Y', 'Y', 'Y', 'Y']
df = df.pivot_table(index=['Type1', 'Type11'], columns='Type2', aggfunc=[np.mean])['mean']

def color(x):
   c1 = 'background-color: red'
   c2 = 'background-color: green'
   c3 = 'background-color: yellow'
   c = ''

   cols = [('ATC', 'X'), ('P25', 'X')]
   m1 = x[cols].lt(x[('ATC', 'Y')], axis=0)
   m2 = x[cols].gt(x[('P25', 'Y')], axis=0)
   arr = np.select([m1, m2], [c1, c2], default=c3)

   df1 = pd.DataFrame(arr, index=x.index, columns=cols)
   return df1.reindex(columns=x.columns, fill_value=c)

df1 = df.reset_index().style.apply(color,axis=None)
fn = r'C:\Users\Desktop\format_file.xlsx'
ut.removeFile(fn)
df1.to_excel(fn, index=True, engine='openpyxl')

But I have not been able to get the desired output format. I don't care about the color in the code as this moment as long as I get the format.

i.e. requirements are:

(1) for each ATC, if X < Y I make it green else I make it blue.

Answer 1

Here's what I would do:

def color(col):
    colors = ('background-color: blue', 'background-color: green',
              'background-color: yellow')
    c = ''

    l0, l1 = col.name

    # for other columns
    if not (l1 in ['X','Y']): return [''] * len(col)

    l2 = 'X' if l1=='Y' else 'Y'
    others = df[(l0,l2)]
    conds = (others.gt(col), others.lt(col), others.eq(col))

    return np.select(conds, colors, c)


df.style.apply(color)

Output: