如何基于另一组DataFrame列表实现单元格着色高亮

我需要用test2列表中的DataFrame，为test列表里对应位置的DataFrame单元格添加背景色高亮，尝试了以下代码但未达到预期效果，求正确的实现方式：

import pandas as pd
#Creating a set of dataframes
data = {'product_name': ['laptop', 'printer', 'tablet', 'desk', 'chair'],'item_name': ['hp', 'logitech', 'samsung', 'lg', 'lenovo'],
        'price': [1200, 150, 300, 450, 200]}
df1 = pd.DataFrame(data)

data2 = {'product_name': ['laptop', 'printer', 'tablet', 'desk', 'chair'],'item_name': ['hp', 'mac', 'fujitsu', 'lg', 'asus'],
        'price': [2200, 200, 300, 450, 200]}
df2 = pd.DataFrame(data2)

data3 = {'product_name': ['laptop', 'printer', 'tablet', 'desk', 'chair'],'item_name': ['microsoft', 'logitech', 'Average', 'lg', 'asus'],
        'price': [1500, 100, 200, 350, 400]}
df3 = pd.DataFrame(data3)

#Creating another set of dataframes
data = {'product_name': ['Low', 'Low', 'Low', 'Excellent', 'Excellent'],'item_name': ['hp', 'Excellent', 'Excellent', 'Average', 'Excellent'],
        'price': [10, 20, 30, 40, 50]}
dv1 = pd.DataFrame(data)

data2 = {'product_name': ['Average', 'Average', 'Average', 'Average', 'Average'],'item_name': ['Excellent', 'mac', 'Excellent', 'lg', 'Average'],
        'price': [10, 20, 30, 50, 50]}
dv2 = pd.DataFrame(data2)

data3 = {'product_name': ['Excellent', 'Average', 'Excellent', 'Better than Average', 'Better than Average'],'item_name': ['Excellent', 'Average', 'Average', 'lg', 'asus'],
        'price': [1, 2, 3, 4, 5]}
dv3 = pd.DataFrame(data3)

#creating a list for dataframes
test=[df1,df2,df3]
test2=[dv1,dv2,dv3]

#combining two lists
zipped = zip(test, test2)
zipped_list = list(zipped)

#the final output should go here
final=[]
for x in zipped_list:
    def apply_color(val):
        colors = {'Excellent': 'green',
              'Better than Average': 'olive',
              'Average': '#fdee73', 
              'Worse than Average': 'pink', 'Low':'pink',
              'Very low':'red'}
        return x[1].applymap(lambda val: 'background-color: {}'.format(colors.get(val,'')))
        z=x[0].style.apply(apply_color, axis=None)
        final.append(z)

问题分析 #

原代码存在两处核心错误：

1. apply_color函数中return语句写在样式应用代码之前，导致后续逻辑永远无法执行
2. 着色函数的参数未被合理使用，逻辑上未正确关联test和test2的对应单元格

修正后的实现代码 #

import pandas as pd

# 创建第一组DataFrame
data = {'product_name': ['laptop', 'printer', 'tablet', 'desk', 'chair'],
        'item_name': ['hp', 'logitech', 'samsung', 'lg', 'lenovo'],
        'price': [1200, 150, 300, 450, 200]}
df1 = pd.DataFrame(data)

data2 = {'product_name': ['laptop', 'printer', 'tablet', 'desk', 'chair'],
         'item_name': ['hp', 'mac', 'fujitsu', 'lg', 'asus'],
         'price': [2200, 200, 300, 450, 200]}
df2 = pd.DataFrame(data2)

data3 = {'product_name': ['laptop', 'printer', 'tablet', 'desk', 'chair'],
         'item_name': ['microsoft', 'logitech', 'Average', 'lg', 'asus'],
         'price': [1500, 100, 200, 350, 400]}
df3 = pd.DataFrame(data3)

# 创建第二组用于着色映射的DataFrame
data = {'product_name': ['Low', 'Low', 'Low', 'Excellent', 'Excellent'],
        'item_name': ['hp', 'Excellent', 'Excellent', 'Average', 'Excellent'],
        'price': [10, 20, 30, 40, 50]}
dv1 = pd.DataFrame(data)

data2 = {'product_name': ['Average', 'Average', 'Average', 'Average', 'Average'],
         'item_name': ['Excellent', 'mac', 'Excellent', 'lg', 'Average'],
         'price': [10, 20, 30, 50, 50]}
dv2 = pd.DataFrame(data2)

data3 = {'product_name': ['Excellent', 'Average', 'Excellent', 'Better than Average', 'Better than Average'],
         'item_name': ['Excellent', 'Average', 'Average', 'lg', 'asus'],
         'price': [1, 2, 3, 4, 5]}
dv3 = pd.DataFrame(data3)

# 生成DataFrame列表
test = [df1, df2, df3]
test2 = [dv1, dv2, dv3]

# 颜色映射字典（提前定义，避免重复创建）
color_map = {
    'Excellent': 'green',
    'Better than Average': 'olive',
    'Average': '#fdee73',
    'Worse than Average': 'pink',
    'Low': 'pink',
    'Very low': 'red'
}

final = []
# 遍历配对的DataFrame
for df, dv in zip(test, test2):
    def highlight_cells(_):
        # 基于dv的单元格值生成背景色样式
        return dv.applymap(lambda val: f'background-color: {color_map.get(val, "")}')
    
    # 应用样式并添加到结果列表
    styled_df = df.style.apply(highlight_cells, axis=None)
    final.append(styled_df)

# 查看效果示例：print(final[0].to_html()) 或在notebook中直接显示final[0]

关键优化点 #

• 将颜色映射字典提前定义，避免循环中重复创建，提升执行效率
• 修正着色函数的逻辑，通过dv（来自test2）的单元格值匹配颜色，生成对应样式
• 使用style.apply(..., axis=None)确保函数接收整个DataFrame，实现单元格级别的精准着色
• 调整代码执行顺序，确保样式应用逻辑能正常运行

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