1. Excel
学生成绩,按照不同的成绩,标识不同的颜色
选中数据区域/条件格式/小于,设置60分
可以在上图右半部分,设置为自定义,字体颜色为鲜红色,填充为无填充
第二个条件/设置每次考试的第一名,给以奖励,这样,这是选中每一列。
设置显示前1,设定格式,使得字体显示颜色为绿色,背景为i淡绿色
同样的方式,做每一行。、
这样,图标就很有冲击力了,最佳/不佳的都可以显示,一目了然
2. Python/jupyter nobebook实现
实现将不及格的标为红色
import pandas as pd
def low_score_red(s):
color = 'red' if s<60 else 'green'
return f'color:{color}'
students = pd.read_excel('C:/Tools/Python/Pandas/025,026/Students.xlsx')
students.style.applymap(low_score_red, subset=['Test_1', 'Test_2', 'Test_3']) #实现无差别的应用到读入的表格中
实现,只把第一名标为绿色
import pandas as pd
def low_score_red(s):
color = 'red' if s<60 else 'black'
return f'color:{color}'
def highest_score_green(col):
return ['background-color:lime' if s==col.max() else 'background-color:white' for s in col]
students = pd.read_excel('C:/Tools/Python/Pandas/025,026/Students.xlsx')
students.style.applymap(low_score_red, subset=['Test_1', 'Test_2', 'Test_3']) \
.apply(highest_score_green, subset=['Test_1', 'Test_2', 'Test_3'])
将结果保存到excel
import pandas as pd
def low_score_red(s):
color = 'red' if s<60 else 'black'
return f'color:{color}'
def highest_score_green(col):
return ['background-color:lime' if s==col.max() else 'background-color:white' for s in col]
students = pd.read_excel('C:/Tools/Python/Pandas/025,026/Students.xlsx')
students.style.applymap(low_score_red, subset=['Test_1', 'Test_2', 'Test_3']) \
.apply(highest_score_green, subset=['Test_1', 'Test_2', 'Test_3']).to_excel('C:/Tools/Python/Pandas/025,026/test1.xlsx')
存储格式有待后续继续优化
用背景颜色的深浅,来标识数值的高低
Excel
选中数据区,清空现有规则
使用数据条的长短来标识值的大小
2. Pandas
import pandas as pd
import seaborn as sns
color_map = sns.light_palette('green', as_cmap=True)
students = pd.read_excel('C:/Tools/Python/Pandas/025,026/Students.xlsx')
students.style.background_gradient(cmap=color_map, subset=['Test_1','Test_2','Test_3'])
import pandas as pd
students = pd.read_excel('C:/Tools/Python/Pandas/025,026/Students.xlsx')
students.style.bar(color='orange', subset=['Test_1','Test_2','Test_3'])