How to set a style for a particular cell in a multiindex dataframe-CodePudding

I'm iterating over a multi-index dataframe, and I trying to set the color for particular cells to the style in the two variables points_color and stat_color.

How to apply the style to the cells?

for metric, new_df in df3.groupby(level=0):
    idx = pd.IndexSlice
    row = new_df.loc[(metric),:]
    for geo in ['US', 'UK']:
        points_color, stat_color = color(new_df.loc[metric,idx[:,:,['difference']]][geo]['']['difference'],
                                   new_df.loc[metric,idx[:,:,['stat']]][geo]['']['stat'])
        

#####  SEE HERE  #######
        df3.loc[metric,idx[:,:,['points']]][geo]['GM']['points'] = # apply points_color style to this value df3.loc[metric,idx[:,:,['points']]][geo]['GM']['points']
        df3.loc[metric,idx[:,:,['stat']]][geo]['']['stat'] = # apply stat_color style to this value df3.loc[metric,idx[:,:,['stat']]][geo]['']['stat']
###########

df3

Setup for the dataframe:

dic = {'US':{'Quality':{'points':"-2 n", 'difference':'equal', 'stat': 'same'}, 'Prices':{'points':"-7 n", 'difference':'negative', 'stat': 'below'}, 'Satisfaction':{'points':"3 n", 'difference':'positive', 'stat': 'below'}},
      'UK': {'Quality':{'points':"3 n", 'difference':'equal', 'stat': 'above'}, 'Prices':{'points':"-13 n", 'difference':'negative', 'stat': 'below'}, 'Satisfaction':{'points':"2 n", 'difference':'negative', 'stat': 'same'}}}
d1 = defaultdict(dict)
for k, v in dic.items():
    for k1, v1 in v.items():
        for k2, v2 in v1.items():
            d1[(k, k2)].update({k1: v2})

df = pd.DataFrame(d1)

df.columns = df.columns.rename("Skateboard", level=0)
df.columns = df.columns.rename("Metric", level=1)

df3 = pd.concat([df], keys=[''], names=['Q3'], axis=1).swaplevel(0, 1, axis=1)
df3.columns = df3.columns.map(lambda x: (x[0], 'GM', x[2]) if x[2] == 'points' else x)
df3.insert(loc=0, column=('','', 'Mode'), value="Website")

df3

Setup for the color function: It takes two cell values difference and stat and determines if the style for cells points and stats is in the dataframe.

def color(difference, stat):
    points_color, stat_color = '', ''
    
    if stat in ('below', 'above'):
        stat_color = 'background-color: #f2dcdb; color: red'
    
    if difference == "negative":
        points_color = 'color: red'
    elif difference == "positive":
        points_color = 'color: green' 
    
    return points_color, stat_color

CodePudding user response：

You can select geo columns by list, compare stat and difference and set values in slices:

def color(x):
    
    idx = pd.IndexSlice
    geo = ['US', 'UK']
    
    m1 = x.loc[:, idx[geo, :, 'stat']].isin(('below', 'above'))
    diff = x.loc[:, idx[geo, :, 'difference']]
    
    df1 = pd.DataFrame('', index=x.index, columns=x.columns)
    
    diff = (diff.rename(columns={'difference':'points'}, level=2)
                 .rename(columns={'':'GM'}, level=1))
    df1.loc[:, idx[geo, 'GM', 'points']] = np.select([diff.eq('negative'), 
                                                      diff.eq('positive')], 
                                                     ['color: red','color: green'], '')
    df1.loc[:, idx[geo, :, 'stat']] = np.where(m1, 
                                           'background-color: #f2dcdb; color: red', '')
                     
    return df1

df.style.apply(color, axis=None)