pandas的Excel文件读写（二）——将数据写入文件

四、将数据写入文件4.1 将列表字典转化为DataFrame

dsy0221

13514人浏览 · 2021-09-10 01:27:45

dsy0221 · 2021-09-10 01:27:45 发布

四、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个工作表。