pandas 花式保存 Excel 数据的 N 种姿势！

相关资料

总结

准备数据

import

from

import

'Date and time'

2015

'Dates only'

2015

'Numbers'

1010

2020

3030

2020

1515

'Percentage'

.33

.25

'final'

{i}

for

结果：

Date and time

Dates only

Numbers

Percentage

final

2015/1/1 11:30

2015/2/1

1010

0.1

=C2*D2

2015/1/2 1:20

2015/2/2

2020

0.2

=C3*D3

2015/1/3 11:10

2015/2/3

3030

0.33

=C4*D4

2015/1/4 16:45

2015/2/4

2020

0.25

=C5*D5

2015/1/5 12:10

2015/2/5

1515

0.5

=C6*D6

Pandas直接保存数据

对于这个pandas对象，如果我们需要将其保存为excel，有那些操作方式呢？首先，最简单的，直接保存：

"demo1.xlsx"

'Sheet1'

False

效果如下：

但如果我们想要给这个excel在保存时，同时指定一些特殊的自定义格式又该怎么做呢？这时就可以使用ExcelWriter进行操作，查看API文档发现两个重要参数：

date_format : str, default None

Format string for dates written into Excel files (e.g. 'YYYY-MM-DD').

datetime_format : str, default None

Format string for datetime objects written into Excel files. (e.g. 'YYYY-MM-DD HH:MM:SS').

这说明对于日期类型数据，都可以通过这两个参数指定特定的显示格式，那么我们采用以下方式才创建ExcelWriter，并保存结果：

"demo1.xlsx"

'mmm d yyyy hh:mm:ss'

'mmmm dd yyyy'

'Sheet1'

False

可以看到excel保存的结果中，格式已经确实的发生了改变：

Pandas的Styler对表格着色输出

如果我们想对指定的列的数据设置文字颜色或背景色，可以直接pandas.io.formats.style工具，该工具可以直接对指定列用指定的规则着色：

lambda

'color:red'

"Date and time"

lambda

'color:green'

"Dates only"

lambda

'background-color:#ADD8E6'

"Numbers"

"PuBu"

0.5

"Percentage"

显示效果：

"demo_style.xlsx"

'mmm d yyyy hh:mm:ss'

'mmmm dd yyyy'

'Sheet1'

False

保存效果：

虽然Pandas的Styler样式还包括设置显示格式、条形图等功能，但写入到excel却无效，所以我们只能借助Pandas的Styler实现作色的功能，而且只能对数据着色，不能对表头作色。

Pandas使用xlsxwriter引擎保存数据

进一步的，我们需要将数值等其他类型的数据也修改一下显示格式，这时就需要从ExcelWriter拿出其中的workbook进行操作：

"demo1.xlsx"

结果：

从返回的结果可以看到这是一个xlsxwriter对象，说明pandas默认的excel写出引擎是xlsxwriter，即上面的ExcelWriter创建代码其实等价于：

"demo1.xlsx"

'xlsxwriter'

关于xlsxwriter可以参考官方文档：

https://xlsxwriter.readthedocs.org/

下面的代码即可给数值列设置特定的格式：

"demo1.xlsx"

'xlsxwriter'

'mmm d yyyy hh:mm:ss'

'mmmm dd yyyy'

'Sheet1'

False

'Sheet1'

'A:A'

'B:B'

'num_format'

'#,##0.00'

'num_format'

'0%'

'C:C'

'D:D'

'E:E'

效果：

关于set_column方法：

worksheet.set_column('C:C', 8, format1) 表示将C列的列宽设置为8个字符，并采用format1的样式，当然 format1 = workbook.add_format({'num_format': '#,##0.00'}) 就是表示生成一个指定的格式对象。

xlsxwriter按照指定样式写出Pandas对象的数据

假如，我现在希望能够定制excel表头的样式，并给数据添加边框。我翻遍了xlsxwriter的API文档发现，并没有一个可以修改指定范围样式的API，要修改样式只能通过set_column修改列，或者通过set_row修改行，这种形式的修改都是针对整行和整列，对于显示格式还能满足条件，但对于背景色和边框之类的样式就不行了，这点上确实不如openpyxl方便，但xlsxwriter还有个优势，就是写出数据时可以直接指定样式。

下面看看如何直接通过xlsxwriter保存指定样式的数据吧：

import

'demo2.xlsx'

'sheet1'

# 创建列名的样式

'bold'

True

'text_wrap'

True

'valign'

'top'

'fg_color'

'#D7E4BC'

'border'

# 从A1单元格开始写出一行数据，指定样式为header_format

# 创建一批样式对象

'border'

'num_format'

'mmm d yyyy hh:mm:ss'

'border'

'num_format'

'mmmm dd yyyy'

'border'

'num_format'

'#,##0.00'

'border'

'num_format'

'0%'

# 从第2行（角标从0开始）开始，分别写出每列的数据，并指定特定的样式

# 设置对应列的列宽，单位是字符长度

'A:A'

'B:B'

'C:C'

'D:D'

'E:E'

上面的代码应该都比较好理解，header_format和formatN是创建的样式对象，write_row用于按行写出数据，write_column用于按列写出数据，set_column则是用于设置整列的列宽和样式（没传入的不设置）。

运行结果如下：

Pandas使用openpyxl引擎保存数据

pandas的默认写出引擎是xlsxwriter，那么是不是可以修改为其他引擎呢？答案是可以，下面我们使用 openpyxl 实现同样的效果。

关于openpyxl可参考：

https://openpyxl.readthedocs.org/

也可以直接参考黄同学编写的文档（中文，相对也比较全面）：

https://blog.csdn.net/weixin_41261833/article/details/106028038

"demo3.xlsx"

'openpyxl'

'mmm d yyyy hh:mm:ss'

'mmmm dd yyyy'

'Sheet1'

False

'Sheet1'

结果：

从上述打印结果可以看到，从engine指定为'openpyxl'后，workbook已经是 openpyxl 对象了。

与 xlsxwriter 不同的是 openpyxl 只能对逐个单元格设置样式，而xlsxwriter只能指定行或指定列或写入数据指定样式。

下面首先修改表头的样式：

import

from

import

"微软雅黑"

True

"top"

True

"solid"

"D7E4BC"

"thin"

for

"A1:E1"

上述代码引入的了itertools.chain方便迭代出每个单元格，而不用写多重for循环。

下面再修改数值列的格式：

for

"A2:E6"

for

"C2:C6"

"E2:E6"

'#,##0.00'

for

"D2:D6"

'0%'

最后给各列设置一下列宽：

"A"

"B"

"C"

"D"

"E"

最后保存即可：

writer.save()

整体完整代码：

from

import

"demo3.xlsx"

'openpyxl'

'mmm d yyyy hh:mm:ss'

'mmmm dd yyyy'

'Sheet1'

False

'Sheet1'

"微软雅黑"

True

"top"

True

"solid"

"D7E4BC"

"thin"

for

"A1:E1"

for

"A2:E6"

for

"C2:C6"

"E2:E6"

'#,##0.00'

for

"D2:D6"

'0%'

"A"

"B"

"C"

"D"

"E"

最终效果：

从上述代码可以看到，openpyxl 修改样式时相对xlsxwriter来说麻烦了太多，特别是修改一个表头就需要创建5个对象。

openpyxl加载数据模板写出Pandas对象的数据

虽然 openpyxl 直接写出数据指定样式相对xlsxwriter麻烦，但 openpyxl 还有个巨大的优势就是可以读取已有的excel文件，在其基础上修改。

那我们就完全可以先将模板数据写入到一个excel，然后加载这个模板文件进行修改，所以上面那个固定不变的表头，我们就可以事先创建好：

然后加载模板，再写入：

（经测试ExcelWriter无法对已经存在的工作表进行操作，会创建新的工作表，所以这里直接使用openpyxl自己的API）

from

import

'template.xlsx'

"Sheet1"

# 添加数据列，i表示当前的行号，用于后续格式设置

for

# 批量修改给写入的数据的单元格范围加边框

"thin"

for

{i}

# 批量给各列设置指定的自定义格式

for

{i}

'mmm d yyyy hh:mm:ss'

for

{i}

'mmmm dd yyyy'

for

{i}

'#,##0.00'

for

{i}

'0%'

"demo4.xlsx"

最终效果：

可以明显的看到openpyxl在加载模板后，可以省掉表头设置和列宽设置的代码。

Pandas自适应列宽保存数据

大多数时候我们并不需要设置自定义样式，也不需要写出公式字符串，而是直接写出最终的结果文本，这时我们就可以使用pandas计算一下各列的列宽再保存excel数据。

例如我们有如下数据：

'Region'

'East'

'South'

'North'

'West'

'South'

'North'

'West'

'South'

'West'

'South'

'Item'

'Apple'

'Orange'

'Apple'

'Pear'

'Orange'

'Grape'

'Pear'

'Grape'

'Orange'

'Volume'

9000

5000

9000

2000

9000

7000

9000

1000

10000

6000

3000

'Month'

'July'

'September'

'November'

'October'

'August'

'December'

'November'

'April'

'January'

'May'

结果：

Region

Item

Volume

Month

East

Apple

9000

July

East

Apple

5000

July

South

Orange

9000

September

North

Apple

2000

November

West

Apple

9000

November

South

Pear

7000

October

North

Pear

9000

August

West

Orange

1000

December

West

Grape

1000

November

South

Pear

10000

April

West

Grape

6000

January

South

Orange

3000

May

使用pandas来进行计算各列列宽，思路是计算出每列的字符串gbk编码（Windows下的Excel软件默认使用gbk编码）后的最大字节长度：

# 计算表头的字符宽度

lambda

'gbk'

# 计算每列的最大字符宽度

lambda

'gbk'

# 计算整体最大宽度

结果：

下面将改造一下前面的代码。

首先，使用xlsxwriter引擎自适应列宽保存数据：

"auto_column_width1.xlsx"

'xlsxwriter'

'Sheet1'

False

'Sheet1'

for

然后，使用openpyxl引擎自适应列宽保存数据(openpyxl引擎设置字符宽度时会缩水0.5左右个字符，所以干脆+1)：

from

import

"auto_column_width2.xlsx"

'openpyxl'

'Sheet1'

False

'Sheet1'

for

结果：

可以看到列宽设置的都比较准确。

相关资料

1. python读写Excel文件的那些库

来源：

http://www.python-excel.org/

openpyxl

读取和写入EXCEL2010文件的包(即.xlsx)

文档：

https://openpyxl.readthedocs.org/

xlsxwriter

拥有丰富的特性，支持图片/表格/图表/筛选/格式/公式等，功能与openpyxl相似，优点是相比 openpyxl 还支持 VBA 文件导入，迷你图等功能，缺点是不能打开/修改已有文件，意味着使用 xlsxwriter 需要从零开始。支持EXCEL2010文件(即.xlsx) 文档：

https://xlsxwriter.readthedocs.org/GitHub：https://github.com/jmcnamara/XlsxWriter

pyxlsb

专门用于读取

xlsb

格式的excel文件 GitHub：https://github.com/willtrnr/pyxlsb

pylightxl

用于读取

xlsx

和

xlsm

格式的excel文件，或写入

xlsx

格式的excel文件文档：

https://pylightxl.readthedocs.io/en/latest/GitHub：https://github.com/PydPiper/pylightxl

xlrd

用于读取

xls

格式的excel文件的库文档：

http://xlrd.readthedocs.io/en/latest/GitHub：https://github.com/python-excel/xlrd

xlwt

用于写入

xls

格式的excel文件的库文档：

http://xlwt.readthedocs.io/en/latest/Examples：https://github.com/python-excel/xlwt/tree/master/examplesGitHub：https://github.com/python-excel/xlwt

xlutils

用于配合

xlrd

和

xlwt

的工具库，包括样式的复制文档：

http://xlutils.readthedocs.io/en/latest/GitHub：https://github.com/python-excel/xlutils

必须安装 Microsoft Excel应用程序后才能使用的库：

xlwings

xlwings是开源，用Python替代VBA自动化操作Excel，同时支持Windows和MacOS。在Windows平台下，通过xlwings在Python中编写UDF可以实现在Excel中调用Python。xlwings PRO是一个具有附加功能的商业插件。

主页：

https://www.xlwings.org/

文档：https://docs.xlwings.org/en/stable/GitHub：https://github.com/xlwings/xlwings

2. Excel单元格自定义格式参数含义

一、代码结构组

代码结构组成分为四个部分，中间用";"号分隔，具体如下：正数格式；负数格式；零格式；文本格式

二、各个参数的含义

1、"G/通用格式"：以常规的数字显示，相当于"分类"列表中的"常规"选项。

G/通用格式

10显示为10；10.1显示为10.1。

2、"0"：数字占位符。如果单元格的内容大于占位符，则显示实际数字，如果小于点位符的数量，则用0补足。

00000

1234567显示为1234567；123显示为00123

00.000

100.14显示为100.140；1.1显示为01.100

0000-00-00

20050512显示为2005-05-12

3、"#"：数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于"#"的数量，则按"#"的位数四舍五入。

###.##

12.1显示为12.10；12.1263显示为12.13

4、"？"：数字占位符。在小数点两边为无意义的零添加空格，对齐结果为以小数点对齐，另外还用于对不等到长数字的分数。

??.??

结果自动以小数点对齐：

#??/??

原数据：

展示效果：

5、"."：小数点。如果外加双引号则为字符。

11.23显示为11.2

6、"%"：百分比。

0.1显示为10%

7、","：千位分隔符。数字使用千位分隔符。如在代码中","后空，则把原来的数字缩小1000倍。

#,###

"10000"显示为"10,000"

"10000"显示为"10"

#,,

"1000000"显示为"1"

8、"@"：文本占位符。如果只使用单个@，作用是引用原始文本，要在输入数字数据之前自动添加文本，使用自定义格式为："文本内容"@；要在输入数字数据之后自动添加文本，使用自定义格式为：@"文本内容"。@符号的位置决定了Excel输入的数字数据相对于添加文本的位置。如果使用多个@，则可以重复文本。

"集团"

"部"

财务显示为：集团财务部

@@@

财务显示为：财务财务财务

9、

：重复下一次字符，直到充满列宽。

@*-

"ABC"显示为"ABC-------------------"(仅在office中生效，wps中无效果)

10、[颜色]：用指定的颜色显示字符。可有八种颜色可选：红色、黑色、黄色，绿色、白色、兰色、青色和洋红。

[青色];[红色];[黄色];[兰色]

显示结果为正数为青色，负数显示红色，零显示黄色，文本则显示为兰色

11、[颜色N]：是调用调色板中颜色，N是0~56之间的整数。

[颜色3]

单元格显示的颜色为调色板上第3种颜色。

12、[条件]：可以单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另个是"所有的其他"。条件要放到方括号中。必须进行简单的比较。

"正数"

"零"

"负数"

13、"!"：显示"""。由于引号是代码常用的符号。在单元格中是无法用"""来显示出来"""。要想显示出来，须在前加入"!"

#!"

"10"显示"10""

#!"!"

"10"显示"10"""

14、时间和日期代码

"YYYY"或"YY"：按四位（1900~9999）或两位（00~99）显示年；

"MM"或"M"：以两位（01~12）或一位（1~12）表示月；

"DD"或"D"：以两位（01~31）或一位（1-31）来表示天；

"YYYY-MM-DD"。2005年1月10日显示为："2005-01-10"；

"YY-M-D"。2005年10月10日显示为："05-1-10"；

"AAAA"：日期显示为星期；

"H"或"HH"：以一位（0~23）或两位（01~23）显示小时；

"M"或"MM"：以一位（0~59）或两位（01~59）显示分钟；

"S"或"SS"：以一位（0~59）或两位（01~59）显示秒；

"HH:MM:SS"："23:1:15"显示为"23:01:15"；

总结

openpyxl

xlsxwriter

推荐关注，好内容不断

原文链接

作者： ANALYTICS VIDHYA 翻译：吴振东校对：和中华 4000字 10 分钟本文中Kaggle大佬Dan Becker谈论了自己从经济学向数据科学的转变，从零开始成为数据科学家的旅程，并给予初学者们一些建议。 “如果我下定决心要进入前1%，有可能在达成目标前我就放弃了。这是一个过于困难的目标，如果我心里总想着这件事，那我永远无法去完成它。” ——Dan Becker 我坚信对于很多已经抛弃Kaggle之旅的人来说都有这样的经历，无法进入榜单前1%，并且认为练习也毫无意义。 Dan Becke Becker Becker 这篇采访包括了以下几大主题： Becker Becker Becker 让我们来读一下这篇采访，看看从中会有怎样的感悟吧！贝克尔的学业和工作 Analytics Vidhya (AV)： “看您的教育背景是经济学博士。您能告诉我们自己是怎样从经济学转战到数据科学，从中面对过怎样的挑战吗？” Dan Becker (DB