四、to_excel()函数
4.1 to_excel()函数声明与调用
4.1.1 函数声明
def to_excel(self,
excel_writer: Any,
sheet_name: str = "Sheet1",
na_rep: str = "",
float_format: str | None = None,
columns: Any = None,
header: Any = True,
index: Any = True,
index_label: Any = None,
startrow: Any = 0,
startcol: Any = 0,
engine: Any = None,
merge_cells: Any = True,
encoding: Any = None,
inf_rep: Any = "inf",
verbose: Any = True,
freeze_panes: Any = None,
storage_options: Dict[str, Any] | None = None) -> None
4.1.2 函数调用
对指定的DataFrame对象进行操作,以成员函数形式调用,形式如下:
dataframe1.to_excel('output.xls',...)
# dataframe1为指定的DataFrame对象,output.xls为要写入的Excel文件,“...”表示选项
DataFrame对象可由两种方法生成:
(1)从已有的Excel文件中通过pd.read_excel()来读取;
(2)将已有的以字符串-列表字典形式存储的表格转化成DataFrame对象(详见5.1节)。
4.2 参数说明
这里只列出一些比较常用的参数的说明。
4.2.1 excel_writer
用来指定写入文件的路径和文件名。路径支持相对路径和绝对路径。
路径必须是系统中存在的路径,否则会出现错误提示:
FileNotFoundError: [Errno 2] No such file or directory: 'OutputDir/output.xls'
如果要在指定路径不存在时新建路径再写入文件,需要在调用to_excel()之前用os.path.exists()检查路径是否存在,如果不存在,则用os.makedirs新建路径(可以一次新建多级目录)。代码如下:
# 注意要在文件头部导入os库
import os
outputdir = 'OutputDir' # 存放输出文件的目录
isExists = os.path.exists(outputdir) # 检查目录是否存在
if not isExists:
os.makedirs(outputdir) # 不存在,新建目录
data.to_excel(outputdir+'/output.xls') # 生成输出文件
4.2.2 sheet_name
指定输出表格存放的工作表名称,默认为'Sheet1'。
有关将多个DataFrame分别存入一个文件的不同工作表中的方法,详见5.3节。
4.2.3 na_rep
设置缺失数据的表示方式,默认为空字符串""。
4.2.4 columns
设置要写入文件的列。默认为None,表示写入全部列。
对于没有表头(即使用默认表头0,1,2,……)的DataFrame,需要通过列序号列表(列序号从0开始计算)来设置要写入的列。
对于有表头的DataFrame,只能通过列名字符串列表来设置要写入的列。
例如有两个DataFrame对象df1和df2,内容如下(注意与print函数打印的结果格式有所不同):
df1(无表头)
50101 | 95 | 98 | 98 |
50102 | 93 | 100 | 96 |
50103 | 90 | 96 | 95 |
…… | …… | …… | …… |
df2(有表头)
学号 | 语文 | 数学 | 英语 |
50101 | 95 | 98 | 98 |
50102 | 93 | 100 | 96 |
50103 | 90 | 96 | 95 |
…… | …… | …… | …… |
如果将它们分别写入result1.xls和result2.xls文件中,且都只保留第一列和第三列,应该分别使用以下语句:(其他必要参数从略)
df1.to_excel('result1.xls',columns=[0,2])
df2.to_excel('result2.xls',columns=['学号','数学'])
4.2.5 header
是否写入表头,也可重新设置表头。分三种:
(1)True(默认):写入表头,保持原有表头内容不变。对于以header=None选项从文件读入的DataFrame,以0,1,2,……作为表头输出。
(2)False:不写入表头,从输出文件的第一行开始就写入数据。
(3)字符串列表:写入表头,但将表头中的各列名称重新命名。列表元素个数必须与拟写入表格的列数相等,否则会出现错误提示“ValueError: Writing m cols but got n aliases”。
例如将4.2.4节的df2分别按照下列语句写入相应文件:
df2.to_excel('result1.xls',header=True,index=False) # 输出与df2表格相同
df2.to_excel('result2.xls',header=False,index=False) # 输出与df1表格相同
df2.to_excel('result3.xls',header=['ID','Chinese','Math','English'],index=False)
#输出表格标题行替换为'ID','Chinese','Math','English'
4.2.6 index与index_label
index:是否写入索引列作为输出表格的第一列,默认为True。索引列是在读入时以index_col选项指定的,如果没有指定,则默认为从0开始编号的行号。
index_label:仅当index=True时有效,设置(或重设)索引列的名称放在表头。默认为None,即保持原有列名(在读入时以index_col选项指定的索引列名,未指定则为空白)。
4.2.7 startrow与startcol
startrow与startcol分别用于设定写入Excel文件的起始行数和列数(均从0开始编号),其左侧和上方均为空白。默认均为0,即从第一行、第一列开始。
五、将数据写入Excel文件
5.1 将列名-列表字典转化为DataFrame对象(按列存储)
to_excel()函数是对DataFrame对象进行的操作。如果需要将一个自行以列表形式编写的数据表格写入Excel文件,需要先将其转化为DataFrame对象才能使用该函数。
如果表格是按列存储的,可用列名-列表字典表示数据表格,以这个字典作为DataFrame构造函数的参数。具体步骤:
(1)将数据按列分别存储于各个列表中(不包括表头);
(2)建立一个字典,按照从左到右的顺序,分别以表头中的各个列名字符串作为键,以相应的各列列表作为键值;
(3)以字典为参数,用pandas.DataFrame()将上面的列名-列表字典转化为DataFrame对象;
(4)对该对象调用to_excel()成员函数,生成Excel文件。
例如要通过pandas生成如下表格到文件result_501.xls(粗体字行为表头),
学号 | 姓名 | 语文 | 数学 | 英语 |
50101 | 张三 | 96 | 100 | 98 |
50102 | 李四 | 99 | 99 | 98 |
50103 | 王五 | 93 | 99 | 95 |
50104 | 孙甲 | 89 | 92 | 90 |
50105 | 杨乙 | 90 | 95 | 91 |
代码如下:
# -*- coding: utf-8 -*-
import pandas as pd
stuID = [50101, 50102, 50103, 50104, 50105]
name = ['张三','李四','王五','孙甲','杨乙']
Chinese = [96, 99, 93, 89, 90]
maths = [100, 99, 99, 92, 95]
English = [98, 98, 95, 90, 91]
data1 = {'学号': stuID, '姓名': name, '语文': Chinese, '数学': maths, '英语': English}
pd.DataFrame(data1).to_excel('result_501.xls', sheet_name='Sheet1', index=False)
5.2 将二维列表转化为DataFrame对象(按行存储)
如果表格是按行存储的,可将各行按从上到下的顺序建立成一个二维列表,以这个二维列表作为DataFrame构造函数的参数。具体步骤:
(1)将数据按行分别存储于各个列表中(不包括索引列);
(2)将除表头(如果有)外各行按从上到下的顺序,依次放进一个列表中,形成一个二维列表;
(3)以这个二维列表为参数,表头为参数columns的值,索引列为参数index的值,用pandas.DataFrame()将二维列表转化为DataFrame对象;
(4)对该对象调用to_excel()成员函数,生成Excel文件。
对5.1节的例子,用这种方法的代码如下:
# -*- coding: utf-8 -*-
import pandas as pd
header = ["学号", "姓名", "语文", "数学", "英语"]
form = [["50101", "张三", "96", "100", "98"],
["50102", "李四", "99", "99", "98"],
["50103", "王五", "93", "99", "95"],
["50104", "孙甲", "89", "92", "90"],
["50105", "杨乙", "90", "95", "91"]]
df = pd.DataFrame(form, columns=header)
df.to_excel("result_501.xls", sheet_name='Sheet1', index=False)
5.3 将多个DataFrame分别存入一个文件的不同工作表
参考:https://www.cnblogs.com/xiao02fang/p/12885476.html
已知有一个DataFrame列表df包含n个DataFrame。现准备将这些DataFrame一起存入一个名为data.xls的文件中,且将各个DataFrame依次存入工作表Sheet1、Sheet2、……、Sheetn。
如果运行下面的程序段:
i = 1
for dataframe in df:
dataframe.to_excel('data.xls', sheet_name='Sheet'+str(i), index=False)
i += 1
虽然能正常运行,但生成的文件data.xls里面的工作表只有一个Sheetn。这是因为每次调用to_excel()函数时,都是在完成写入操作后就关闭了文件,再次调用时就会把原有的data.xls删除,利用新的数据生成新的data.xls文件。
这里,需要在with … as …语句中通过pd.ExcelWriter()构造ExcelWriter对象,作为excel_writer的参数,而后执行如上面程序段所示的操作。
这是因为,作为with所求值的对象ExcelWriter,有一个__enter__()方法和一个__exit__()方法。这段with … as …语句体的运行过程大致如下:
(1)在进入语句构造对象时,__enter__()方法被调用,它用来生成并打开目标Excel文件。
(2)语句体运行过程中,会持续在这个文件中进行操作,而不会关闭文件。
(3)语句体运行结束时,__exit__()方法被调用,它用来关闭目标Excel文件。
(有关with … as …语句的具体说明参见:https://www.cnblogs.com/DswCnblog/p/6126588.html)
按照上述思路,正确的代码应该是:
with pd.ExcelWriter('data.xls') as writer:
i = 1
for dataframe in df:
dataframe.to_excel(writer, sheet_name='Sheet'+str(i), index=False)
i += 1
此时生成的文件就包括了Sheet1、Sheet2、……、Sheetn共n个工作表。