如何为多层索引DataFrame中的指定单元格设置样式

问题描述 #

遍历多层索引DataFrame时，需将points_color和stat_color变量中的样式分别应用到指定的points和stat单元格，如何实现？

待补充样式应用逻辑的代码片段 #

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

DataFrame构造代码 #

from collections import defaultdict
import pandas as pd

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

颜色函数定义 #

该函数接收difference和stat两个单元格值，返回points和stat单元格对应的样式：

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

解决方案 #

直接修改DataFrame单元格无法应用样式，需使用Pandas的Styler对象实现，通过Styler.apply方法针对指定单元格设置样式：

实现代码 #

def apply_style(df):
    # 创建与原DataFrame结构一致的空样式DataFrame
    style_df = pd.DataFrame('', index=df.index, columns=df.columns)
    
    idx = pd.IndexSlice
    for metric in df.index:
        for geo in ['US', 'UK']:
            # 获取当前行的difference和stat值
            difference_val = df.loc[metric, idx[geo, '', 'difference']]
            stat_val = df.loc[metric, idx[geo, '', 'stat']]
            
            # 获取对应样式
            points_color, stat_color = color(difference_val, stat_val)
            
            # 为指定单元格赋值样式
            style_df.loc[metric, idx[geo, 'GM', 'points']] = points_color
            style_df.loc[metric, idx[geo, '', 'stat']] = stat_color
    
    return style_df

# 应用样式并展示结果
styled_df = df3.style.apply(apply_style, axis=None)
styled_df

关键说明 #

1. 不能直接修改原DataFrame的单元格内容来设置样式，样式是Styler对象的属性，而非DataFrame的数据本身。
2. apply_style函数创建与原DataFrame结构完全匹配的空样式表，遍历所有目标单元格后，将样式字符串赋值给对应位置。
3. axis=None参数表示将整个DataFrame作为参数传入apply_style，方便全局遍历处理。

内容的提问来源于stack exchange，提问作者M J