利用Pandas库实现Excel条件格式自动化
共 8103字,需浏览 17分钟
·
2021-08-19 18:22
今天给大家隆重介绍一下如何利用Pandas实现Excel条件格式的自动化内容。
目录:
1. 概述
2. 突出显示单元格
2.1. 高亮缺失值
2.2. 高亮最大值
2.3. 高亮最小值
2.4. 高亮区间值
2.5. 高亮分位数
3. 色阶(背景及文本渐变色)
3.1. 背景渐变色
3.2. 文本渐变色
4. 数据条
5. 数据格式化
6. 自定义格式函数
7. 其他
1. 概述
咱们先简单介绍一下什么是表格条件格式可视化,以常用的Excel为例说明。
在Excel菜单栏里,默认(选择)开始菜单,在中间部位有个条件格式控件,里面就是关于表格条件格式的方方面面。主要包含突出显示单元格规则
、最前/最后规则
、数据条
、色阶
、图标集
以及规则管理
等。
![](https://filescdn.proginn.com/867733072e26b6fce39d8ef61b54f152/8a00787321f5ccbcbaab3f361cf2ee55.webp)
基于以上,我们其实可以通过函数方式进行多种条件的综合,让Excel表格可视化丰富多彩,比如以下截图展示的就是色阶效果!
![](https://filescdn.proginn.com/0670e60ace097d53b568223317228ba8/0d3b77722da5de158e290545f9fa66cb.webp)
在上图中,我们对每列单独进行条件格式-色阶
设置,绿色->红色 代表数值从小到大,可以很直观的快速感受数值表现。
所谓 表格条件格式可视化,就是对表格的数据按照一定的条件进行可视化的展示(这里的可视化更多是指单元格背景色、字体颜色以及文本格式显示等)。
那么,Pandas作为表格化的数据处理工具,我们可以如何实现 表格条件格式可视化呢?!
大杀器:df.style
2. 突出显示单元格
在Excel条件格式中,突出显示单元格规则提供的是大于、小于、等于以及重复值等内置样式,不过在Pandas中这些需要通过函数方法来实现,我们放在后续介绍。这里介绍Pandas突出显示缺失值
、最大值
、最小值
、区间值
的函数方法以及Excel实现这些操作的自定义操作。
2.1. 高亮缺失值
df.style.highlight_null()
Signature:
df.style.highlight_null(
null_color: 'str' = 'red',
subset: 'Subset | None' = None,
props: 'str | None' = None,
) -> 'Styler'
Docstring:
Highlight missing values with a style.
null_color
用于指定高亮的背景色,默认是红色
subset
用于指定操作的列或行
props
用于突出显示CSS属性(后面案例中会涉及到)
![](https://filescdn.proginn.com/effea4c18e45d3aafc47dff786346206/f9636a451995f9dd48b8581d697deaf2.webp)
比如,我们可以指定高亮的背景色为橙色(颜色可以是英文名称)
![](https://filescdn.proginn.com/9d2734d03c3081123dfcc90fcce351ba/c5f25f73ab31479021f3a1d377095281.webp)
比如,我们可以指定高亮的背景色为紫红色(颜色可以是16进制)
![](https://filescdn.proginn.com/8408fb4250acfa609d0fb7f2260408c6/365c33c8064ba7f23b0b3e36c8564001.webp)
2.2. 高亮最大值
df.style.highlight_max()
Signature:
df.style.highlight_max(
subset: 'Subset | None' = None,
color: 'str' = 'yellow',
axis: 'Axis | None' = 0,
props: 'str | None' = None,
) -> 'Styler'
Docstring:
Highlight the maximum with a style.
subset
用于指定操作的列或行
color
用于指定颜色,默认是黄色
axis
用于指定行最大、列最大或全部,默认是列方向最大
![](https://filescdn.proginn.com/b0678265387efbb70d0482eafaa90273/fead97fcc60256d57a46db1a4f9638d8.webp)
这里我们发现对于中文也有列最大高亮,至于为啥是蒙古其实我也不清楚,为了避免出现这种情况,有两种方法:①将这一列设置为索引(这里不做演示),②采用subset
指定
![](https://filescdn.proginn.com/62059b7954d6e1a61746e79fde751210/fe695cd658728fb6bc4c3e5995118cb6.webp)
指定颜色为灰色
![](https://filescdn.proginn.com/89e76c27c367384fd5e1b5f1c94df089/27798ab4f42f1b0da96e5563d7930447.webp)
显示全部最大值
![](https://filescdn.proginn.com/e22f199737268ba3ff54e77d4a975b75/7dc02199de6c9320e0676953aae8cca9.webp)
那么,Excel如何显示最大值呢?这里我们以显示全部最大值为例展开介绍,逻辑如下:
通过函数MAX获取数据区域的最大值 然后编辑格式满足单元格值等于这个最大值即可
操作为:选中数据区域,进行条件格式设置
->编辑格式规则
具体规则如下图:
![](https://filescdn.proginn.com/de9a5b7617a14ecad2e4be2a1627df12/58c56d32a4132744c3a88c6df13b8502.webp)
我们就可以得到想要的效果:
![](https://filescdn.proginn.com/00c6ff372fa11e9a9b162a55290b0d12/87b2e83eca6ba79138e9194260e5274a.webp)
同样的道理,我们可以根据需求高亮列或行的最大值、最小值等
2.3. 高亮最小值
df.style.highlight_min()
参数基本同高亮最大值,这里不再赘述,看案例
![](https://filescdn.proginn.com/ac58c936ffc9aad4b64c9a0f3b105a68/8ee06a4c68a3a4bc4998b9a998313675.webp)
链式调用 最大最小值高亮
![](https://filescdn.proginn.com/344d58497566093d41699085a93913f7/6f518bc87e172a3d2a076080724b7068.webp)
2.4. 高亮区间值
df.style.highlight_between
Signature:
df.style.highlight_between(
subset: 'Subset | None' = None,
color: 'str' = 'yellow',
axis: 'Axis | None' = 0,
left: 'Scalar | Sequence | None' = None,
right: 'Scalar | Sequence | None' = None,
inclusive: 'str' = 'both',
props: 'str | None' = None,
) -> 'Styler'
Docstring:
Highlight a defined range with a style.
subset
用于指定操作的列或行
color
用于指定颜色,默认是黄色
axis
用于指定行、列或全部,如果left或right作为序列给出,则应用于这些序列的边界
left
用于指定区间最小值
right
用于指定区间最大值
inclusive
用于确定是否左右闭包,可选'both', 'neither', 'left', 'right'
props
用于突出显示CSS属性
高亮数量在[20, 30]
的单元格
![](https://filescdn.proginn.com/5ced788396b948ba9e7632cbeef3e81c/08bade15522e828860bab15a957c4f66.webp)
props
用于突出显示CSS属性,案例中我们将待高亮的部分显示为字体颜色-白色,背景色-紫色
![](https://filescdn.proginn.com/8f49ba638e814f4ad791889606221520/efcab85db0f58b879d8ebf45420d1c8a.webp)
金牌数区间[20, 30]
、银牌数区间[10, 20]
、铜牌数区间[5, 10]
![](https://filescdn.proginn.com/7b865cdecb6ff617147cc3ff3887a287/1fd7973f4c33ab5221ba504b8e8e07e8.webp)
2.5. 高亮分位数
df.style.highlight_quantile()
Signature:
df.style.highlight_quantile(
subset: 'Subset | None' = None,
color: 'str' = 'yellow',
axis: 'Axis | None' = 0,
q_left: 'float' = 0.0,
q_right: 'float' = 1.0,
interpolation: 'str' = 'linear',
inclusive: 'str' = 'both',
props: 'str | None' = None,
) -> 'Styler'
Docstring:
Highlight values defined by a quantile with a style.
subset
用于指定操作的列或行
color
用于指定颜色,默认是黄色
axis
用于指定行、列或全部
q_left
用于指定分位数左边界,默认是0
q_right
用于指定分位数右边界,默认是1
inclusive
用于确定是否左右闭包,可选'both', 'neither', 'left', 'right'
props
用于突出显示CSS属性
比如,高亮各列奖牌数前15%的值
![](https://filescdn.proginn.com/6b5a97c5a953a5dda41b62fe6880e4c2/8a6b717e9e316641c30ef90a2998c105.webp)
3. 色阶(背景及文本渐变色)
色阶部分包含背景渐变色和文本渐变色
3.1. 背景渐变色
在Excel中,直接通过条件格式->色阶
操作即可选择想要的背景渐变色效果
![](https://filescdn.proginn.com/d218804999b536ff1361f81bfecebbb2/ad388020a345d70951284068127d518f.webp)
而在Pandas中,我们可以通过df.style.background_gradient()
进行背景渐变色的设置。
Signature:
df.style.background_gradient(
cmap='PuBu',
low: 'float' = 0,
high: 'float' = 0,
axis: 'Axis | None' = 0,
subset: 'Subset | None' = None,
text_color_threshold: 'float' = 0.408,
vmin: 'float | None' = None,
vmax: 'float | None' = None,
gmap: 'Sequence | None' = None,
) -> 'Styler'
Docstring:
Color the background in a gradient style.
cmap
用于指定matplotlib色条
low
和high
用于指定最小最大值颜色边界,区间[0, 1]
axis
用于指定行、列或全部,默认是列方向
subset
用于指定操作的列或行
text_color_threshold
用于指定文本颜色亮度,区间[0, 1]
vmin
和vmax
用于指定与cmap最小最大值对应的单元格最小最大值
![](https://filescdn.proginn.com/3cc075c791a9b72d295843a47a4934e9/1e0476cf5d80070948b1eaed25ec65fc.webp)
low
和high
用于指定最小最大值颜色边界,区间[0, 1]
![](https://filescdn.proginn.com/bf6f28e83c1137e4f8b7e2b5945d9c4d/7acbddc9d98c98c50ea98db85f1ab100.webp)
cmap
用于指定matplotlib色条,采用seaborn美化样式
![](https://filescdn.proginn.com/c2f559b0a29d7c4721c79636441b9f9c/9eba49ce75cec587130354babdea9186.webp)
text_color_threshold
用于指定文本颜色亮度,区间[0, 1]
![](https://filescdn.proginn.com/8fb7a99095ab05a6e14565e3ac3680b9/b3369c9083ce752c6ae3f522d2ab5640.webp)
vmin
和vmax
用于指定与cmap最小最大值对应的单元格最小最大值(10以下同色,70以上同色)
![](https://filescdn.proginn.com/ae38a6959a644cf6b34dd2a7564847d0/c7be12496a718fe84a0964550ea39faa.webp)
我们可以看到以上对于缺失值来说,其背景色是黑色,我们可以通过链式方法和高亮缺失值对缺失值背景色进行修改
![](https://filescdn.proginn.com/2a38fdc13cab1cdf9e1e14abfaaddc1e/9e5747d08916597c0bb3e93e95b487c5.webp)
3.2. 文本渐变色
文本渐变色顾名思义就是对单元格的文本进行颜色渐变,可以通过df.style.text_gradient()
来操作,其参数和背景渐变色基本一致。
![](https://filescdn.proginn.com/af7002a6b1781fc42f9a7b2fbfee16c4/f3c450d3025bda584b03603143c34d39.webp)
4. 数据条
在Excel中,直接通过条件格式->数据条
操作即可选择想要的数据条效果
![](https://filescdn.proginn.com/2fa3a7655c2194da4c7602bbdf946418/35a1f0967e577b5ddb5b3bdfd9c81723.webp)
而在Pandas中,我们可以通过 df.style.bar()
来进行数据条绘制
Signature:
df.style.bar(
subset: 'Subset | None' = None,
axis: 'Axis | None' = 0,
color='#d65f5f',
width: 'float' = 100,
align: 'str' = 'left',
vmin: 'float | None' = None,
vmax: 'float | None' = None,
) -> 'Styler'
Docstring:
Draw bar chart in the cell backgrounds.
subset
用于指定操作的列或行
axis
用于指定行、列或全部,默认是列方向
color
用于指定数据条颜色
width
用于指定数据条长度,默认是100,区间[0, 100]
vmin
和vmax
用于指定与数据条最小最大值对应的单元格最小最大值
align
数据条与单元格对齐方式,默认是left左对齐,还有zero居中和mid位于(max-min)/2
比如,奖牌数(不算总的)最低0最高40+颜色为橙色+居中展示,金牌差数据条长度为50(也就是单元格一半的长度)、银牌差mid对齐+数据条为单元格一半长度+正负显示不同颜色
![](https://filescdn.proginn.com/2800b5bdb770e928a468e79fc391e214/64b58346dbaab3f7178ee2caf877cd32.webp)
5. 数据格式化
调整数据格式用到df.style.format()
Signature:
df.style.format(
formatter: 'ExtFormatter | None' = None,
subset: 'Subset | None' = None,
na_rep: 'str | None' = None,
precision: 'int | None' = None,
decimal: 'str' = '.',
thousands: 'str | None' = None,
escape: 'str | None' = None,
) -> 'StylerRenderer'
Docstring:
Format the text display value of cells.
formatter
显示格式
subset
用于指定操作的列或行
na_rep
用于指定缺失值的格式
precision
用于指定浮点位数
decimal
用于用作浮点数、复数和整数的十进制分隔符的字符,默认是.
thousands
用作浮点数、复数和整数的千位分隔符的字符
escape
用于特殊格式输出(如html、latex等,这里不做展开,可参考官网)比如,我们给数据加上单位
枚
,缺失值显示为无
![](https://filescdn.proginn.com/7791a14a4ad1bee416f11af2ceac7766/4e31aaa1373fe3a02c8c2b9d42d96457.webp)
设置小数点位数为0
![](https://filescdn.proginn.com/4963969e6b75b6b3b562da4c2cd15c9c/8a3d3e44e926f1762aba64cb5147e1a4.webp)
指定列进行格式化
![](https://filescdn.proginn.com/77d39dccf8112143e5c8d0390976e661/bd66a2547156b25690e1a57b46c9b96d.webp)
分别对指定列进行单独格式化
![](https://filescdn.proginn.com/2ba5531baf1e1bdd277814a493c274a9/370d26bd4b0bdcf81df3cbd5a9b3ffdd.webp)
6. 自定义格式函数
通过传递样式函数来自定义格式:
applymap()
(elementwise):接受一个函数,它接受一个值并返回一个带有 CSS 属性值对的字符串。
apply()
(column-/ row- /table-wise): 接受一个函数,它接受一个 Series 或 DataFrame 并返回一个具有相同形状的 Series、DataFrame 或 numpy 数组,其中每个元素都是一个带有 CSS 属性的字符串-值对。此方法根据axis
关键字参数一次传递一个或整个表的 DataFrame 的每一列或行。对于按列使用axis=0
、按行使用axis=1
,以及一次性使用整个表axis=None
。
比如,我们定义一个函数,如果金牌数<银牌数,则高亮金牌数这一列对应的值
![](https://filescdn.proginn.com/f118d46883c88b7d81641b3e4af09a20/059f41ff620c4b54503637d35b5eb51c.webp)
比如,我们还可以定义函数,如果金牌数<银牌数,则这一行数据都高亮
![](https://filescdn.proginn.com/11d85817e0b8f43c622425f51923fa1d/7ce9a8fa1f16e8c76f605ba9e079669e.webp)
又或者,我们可以根据不同的比值对每行进行不同的高亮
![](https://filescdn.proginn.com/165a0cb235738f503d09d4a8ec591895/55aeb8aa17eb73d46e2f9ac8ccb5b44d.webp)
关于以上函数的写法,我们还可以调用numpy的where
和repeat
方法进行优化,如:
![](https://filescdn.proginn.com/5554985ff3c43cb7ba3e2699c3c305d8/5f754d620318372be35df1cc97d389fd.webp)
7. 其他
还有一些小操作,比如添加标题、隐藏索引、隐藏指定列等等
添加标题
![](https://filescdn.proginn.com/2504d6b58424aed093b9114f23c4c132/80f20d5336c1e41e539d7506cf3b6d4c.webp)
隐藏索引
![](https://filescdn.proginn.com/e308fef4a69a977d900f25a0ae234439/088c54a4c7f215031f60e3972e100ddd.webp)
隐藏指定列
![](https://filescdn.proginn.com/04432fa6602e845f1c03fc8ef7accacc/34b74b226ad06745cf606453262a4784.webp)
设置属性
如果一些单元格属性和单元格值无关,我们可以通过df.style.set_properties()
来进行定制化操作,比如:背景色-黑色
,字体颜色-草绿色
,边框颜色-白色
。(css样式)
![](https://filescdn.proginn.com/06bd10402548ed2e8410b3b1e61cf4ac/ab0c3b914fac7393e06b18ee762f772a.webp)
选中放大
![](https://filescdn.proginn.com/af1c912f6f1bd913d70ea987d6c65fff/71b3512ac0d4306c5b743b28e30e62bb.webp)
鼠标选择单元格会有放大效果
![](https://filescdn.proginn.com/2709ebe06878adf39c4fab7f410adbb0/49cb0f71a17562a4189d956dfc1fe006.webp)
导出Excel
就直接to_excel
就行了,dfs = df.style.xxx
,然后dfs.to_excel()
![](https://filescdn.proginn.com/5a6e154ffa149de53269db2a0b15801b/9bb4b8ce36093b1bb7c536c4d0cec479.webp)
导出html
![](https://filescdn.proginn.com/1b589f3049edfdc0a39d94b290a80daa/a6514743265edb92a3800cbeadbec987.webp)
以上就是本次全部内容,大家感兴趣的话可以自己演示一遍熟悉熟悉,又或者想想日常工作中的一些条件格式需求,然后通过Pandas演示出来效果看看。
![音符](https://filescdn.proginn.com/3d39068caf373ae2ec271985339e16c1/095a2b6efef5c65687074eeb03408dd5.webp)
![](https://filescdn.proginn.com/ee473d00b497cf8aa5e69442997be4ca/9da7c32b6920632e8c7a8b6132e5bc0f.webp)
![](https://filescdn.proginn.com/a86893b6a6b0943fb77b27193e660948/5f3780025e033db5c53f2fd00bafd4e9.webp)
![](https://filescdn.proginn.com/95f66f02ebfbafb8870d4d6a856c6733/5d899f4b6e17ace82a4bdab83d4d648e.webp)
![](https://filescdn.proginn.com/81e9f6ee332813d995e050c93986f7c5/0a6092c661c83684f4e2de456af1cdd4.webp)