The to_excel功能允许您导出PandasDataFrame 到 Excel 文件。这对每个数据科学家都非常有益。
本教程将让您全面了解如何to_excel函数的工作原理，以及如何自定义其行为以满足您的需求。
基本语法和参数
的基本语法为to_excelPandas 中的函数如下：
DataFrame.to_excel(excel_writer, sheet_name='Sheet1', na_rep='',
float_format=None, columns=None, header=True, index=True,
index_label=None, startrow=0, startcol=0, engine=None,
merge_cells=True, encoding=None, inf_rep='inf',
verbose=True, freeze_panes=None)
我们简单讨论一下最常用的参数：
-
excel_writer：这是目标文件名或ExcelWriter对象。
-
sheet_name：Excel 文件中工作表的名称。默认为“Sheet1”。
-
na_rep：这是要使用的 NaN 的字符串表示形式。
-
float_format：用于浮点数的格式。
-
columns：要写入 Excel 文件的列。
-
header：写出列名。如果给出了字符串列表，则假定它是列名的别名。
-
index：写入行名称（索引）。
-
startrow & startcol：用于转储 DataFrame 的左上单元格行和列。
-
engine：编写要使用的引擎，‘openpyxl’或‘xlsxwriter’。
The to_excel函数提供了强大的功能，用于自定义数据导出到 Excel。接下来，我们将深入探讨如何有效使用它的示例。
将 Pandas DataFrame 导出到 Excel 文件
首先，让我们导入 Pandas 并创建一个数据框作为起点：
import pandas as pd
# import numpy for sample data
import numpy as np
df = pd.DataFrame({
'A': 1.,
'B': pd.Timestamp('20130620'),
'C': pd.Series(1, index=list(range(4)), dtype='float32'),
'D': np.array([3] * 4, dtype='int32'),
'E': pd.Categorical(["test", "train", "test", "train"]),
'F': 'foo'
})
print(df)
Output:
A B C D E F
0 1.0 2013-06-20 1.0 3 test foo
1 1.0 2013-06-20 1.0 3 train foo
2 1.0 2013-06-20 1.0 3 test foo
3 1.0 2013-06-20 1.0 3 train foo
在这里，我们创建了一个混合了数字类型、字符串和分类数据的 DataFrame。
我们可以使用to_excel函数将 DataFrame 写入 Excel 工作表，如下所示：
df.to_excel("example.xlsx")
这将写入 DataFramedf到一个名为example.xlsx。默认情况下，导出的 Excel 文件中包含标题和索引。生成的 Excel 文件如下所示：
| A B C D E F
--|--------------------------------------
0 | 1.0 2013-06-20 1.0 3 test foo
1 | 1.0 2013-06-20 1.0 3 train foo
2 | 1.0 2013-06-20 1.0 3 test foo
3 | 1.0 2013-06-20 1.0 3 train foo
需要注意的是，DataFrame 的索引已被写入 Excel 文件中的第一列。
Excel 文件中的其余列 (A to F）对应于 DataFrame 的列。
在这种情况下，to_excel函数创建一个新文件。如果文件名已经存在，该函数将覆盖现有文件的内容。
选择写入引擎
Pandas to_excel功能允许您选择用于写入 Excel 文件的引擎。您可以使用engine参数来指定您要使用的引擎。
这是引擎：
- openpyxl
- XLSX作家
让我们使用以下代码编写 DataFramexlsxwriter engine:
df.to_excel("example_xlsxwriter.xlsx", engine='xlsxwriter')
这将创建一个名为的 Excel 文件example_xlsxwriter.xlsx使用xlsxwriter engine.
引擎的选择可能会影响可用于写入 Excel 文件的功能，例如添加图表、条件格式设置等。
请记住，所选引擎需要安装在您的 Python 环境中，因此如果尚未安装，请确保使用 pip 安装它。
pip install xlsxwriter
将多个 Pandas DataFrame 写入多个工作表
要将多个 pandas DataFrame 写入 Excel 文件的多个工作表，您可以使用ExcelWriter对象，它允许您指定每个 DataFrame 的工作表名称。
让我们创建两个不同的 DataFrame 并将它们写入同一 Excel 工作簿中的两个不同工作表：
df_sheet1 = pd.DataFrame({
'A': ['foo', 'bar', 'baz', 'qux'],
'B': ['alpha', 'beta', 'gamma', 'delta'],
'C': np.random.rand(4),
'D': np.random.randint(10, size=4)
})
df_sheet2 = pd.DataFrame({
'E': ['apple', 'banana', 'cherry', 'date'],
'F': ['elephant', 'fox', 'giraffe', 'hippo'],
'G': np.random.rand(4),
'H': np.random.randint(10, size=4)
})
# create an ExcelWriter object
with pd.ExcelWriter("example_multiple_dataframes.xlsx") as writer:
df_sheet1.to_excel(writer, sheet_name='FirstSheet')
df_sheet2.to_excel(writer, sheet_name='SecondSheet')
在生成的 Excel 文件中，您将找到两个名为“FirstSheet”和“SecondSheet”的工作表，每个工作表都包含来自各自 DataFrame 的数据：
第一张：
| A | B | C | D
-- |---------------------------------------
0 | foo | alpha | 0.618012313202 | 6
1 | bar | beta | 0.912971647978 | 8
2 | baz | gamma | 0.344552180436 | 0
3 | qux | delta | 0.982718734163 | 9
第二张：
| E | F | G | H
-- |------------------------------------------
0 | apple | elephant| 0.329873331859 | 7
1 | banana| fox | 0.751442750918 | 3
2 | cherry| giraffe | 0.510241492448 | 2
3 | date | hippo | 0.920831290307 | 0
使用 na_rep 参数处理丢失数据
您可以使用na_rep参数来表示将 DataFrame 导出到 Excel 文件时缺失的值。
让我们创建一个包含一些缺失值的 DataFrame 来演示这一点：
df_na = pd.DataFrame({
'A': 1.,
'B': pd.Timestamp('20130620'),
'C': pd.Series(1, index=list(range(4)), dtype='float32'),
'D': np.array([3] * 4, dtype='int32'),
'E': pd.Categorical(["test", "train", np.nan, "train"]),
'F': 'foo'
})
print(df_na)
Output:
A B C D E F
0 1.0 2013-06-20 1.0 3 test foo
1 1.0 2013-06-20 1.0 3 train foo
2 1.0 2013-06-20 1.0 3 NaN foo
3 1.0 2013-06-20 1.0 3 train foo
您可以看到“E”列中有一个 NaN 值。当我们将这个DataFrame写入Excel文件时，我们可以选择如何表示这个NaN值。
df_na.to_excel("example_na_rep.xlsx", na_rep='NULL')
在 Excel 文件中，“E”列中的 NaN 值被替换为“NULL”。
格式化浮点数
The float_format的参数to_excel函数提供对浮点数格式化的控制。
此参数采用格式化字符串或函数来指示如何将浮点数转换为字符串。让我们创建一个带有浮点数的 DataFrame：
df_float = pd.DataFrame({
'A': 1.123456789,
'B': np.array([3.1415926535] * 4, dtype='float64'),
}, index=list('abcd'))
print(df_float)
Output:
A B
a 1.123457 3.141593
b 1.123457 3.141593
c 1.123457 3.141593
d 1.123457 3.141593
例如，如果我们希望 DataFrame 中的所有浮点数四舍五入到小数点后两位，我们可以使用格式化字符串，如下所示：
df_float.to_excel("example_float_format.xlsx", float_format="%.2f")
在生成的 Excel 文件中，所有浮点数均以两位小数表示。
Excel 文件中的数据应如下所示：
| A B
--|-----------------
a | 1.12 3.14
b | 1.12 3.14
c | 1.12 3.14
d | 1.12 3.14
选择用于 Excel 导出的 DataFrame 列
您可以使用columns的参数to_excel函数导出 DataFrame 的特定列。考虑一个具有多列的 DataFrame：
df_multi = pd.DataFrame({
'A': pd.date_range(start='2020-01-01', periods=4),
'B': pd.Series(range(4), dtype='float32'),
'C': np.array([3] * 4, dtype='int32'),
'D': pd.Categorical(["test", "train", "check", "go"]),
'E': 'foo'
})
print(df_multi)
Output:
A B C D E
0 2020-01-01 0.0 3 test foo
1 2020-01-02 1.0 3 train foo
2 2020-01-03 2.0 3 check foo
3 2020-01-04 3.0 3 go foo
如果我们只想将‘A’和‘D’列导出到Excel文件中，我们可以在to_excel功能：
df_multi.to_excel("example_select_columns.xlsx", columns=['A', 'D'])
生成的 Excel 文件将仅包含 DataFrame 中的“A”和“D”列：
| A D
--|-----------------
0 | 2020-01-01 test
1 | 2020-01-02 train
2 | 2020-01-03 check
3 | 2020-01-04 go
自定义列标题
The header的参数to_excel函数允许您自定义 Excel 文件的列标题。
您可以将字符串列表传递给header参数，假定它们是列名的别名。
让我们考虑一下之前创建的 DataFrame，假设我们想要在导出到 Excel 文件时更改列标题：
print(df_multi)
Output:
A B C D E
0 2020-01-01 0.0 3 test foo
1 2020-01-02 1.0 3 train foo
2 2020-01-03 2.0 3 check foo
3 2020-01-04 3.0 3 go foo
我们可以按如下方式更改列标题：
df_multi.to_excel("example_custom_headers.xlsx", header=['Date', 'Value_1', 'Value_2', 'Category', 'Label'])
在生成的 Excel 文件中，列标题将替换为列表中提供的名称：
| Date Value_1 Value_2 Category Label
--|-------------------------------------------
0 | 2020-01-01 0.0 3 test foo
1 | 2020-01-02 1.0 3 train foo
2 | 2020-01-03 2.0 3 check foo
3 | 2020-01-04 3.0 3 go foo
操纵索引
The index参数允许您控制 DataFrame 的索引是否应包含在生成的 Excel 文件中。
默认情况下，索引写入 Excel 文件。如果你想排除它，可以设置index=False.
我们以 DataFrame 为例：
df_index = pd.DataFrame({
'A': ['foo', 'bar', 'baz', 'qux'],
'B': ['alpha', 'beta', 'gamma', 'delta'],
'C': np.random.rand(4)
}, index=['I1', 'I2', 'I3', 'I4'])
print(df_index)
Output:
A B C
I1 foo alpha 0.647687
I2 bar beta 0.377563
I3 baz gamma 0.013572
I4 qux delta 0.658623
我们可以将此 DataFrame 导出到不带索引的 Excel 文件：
df_index.to_excel("example_no_index.xlsx", index=False)
生成的 Excel 文件将仅包含数据，不包含索引：
A B C
foo alpha 0.647687
bar beta 0.377563
baz gamma 0.013572
qux delta 0.658623
控制 Excel 输出位置
The to_excel函数提供了控制 DataFrame 在 Excel 工作表中开始写入位置的选项。
您可以使用startrow and startcol参数来调整 DataFrame 所在的位置。
让我们将 DataFrame 写入 Excel 文件，但这次我们将从第三行第二列开始：
df_position = pd.DataFrame({
'A': ['foo', 'bar', 'baz'],
'B': ['alpha', 'beta', 'gamma']
})
df_position.to_excel("example_output_location.xlsx", startrow=2, startcol=1, index=False)
在生成的 Excel 文件中，DataFrame 中的数据从第三行（行索引为 0）和第二列（列索引为 0）开始：
| 1 | 2 | 3 |
--|------------------------
1 | | | |
2 | | | |
3 | | A | B |
4 | | foo | alpha |
5 | | bar | beta |
6 | | baz | gamma |
正如你所看到的，DataFramedf_position以 Excel 表示法从单元格 B3 开始写入（第 3 行，第 2 列）。
使用 to_excel 控制单元格合并
默认情况下，处理使用 MultiIndex 的 DataFrame 时会合并单元格。
但是，您可以使用merge_cells参数来控制是否在生成的 Excel 文件中合并具有相同值的单元格。让我们创建一个带有 MultiIndex 的 DataFrame：
df_merge = pd.DataFrame({
'A': [1, 2, 3, 4],
'B': [5, 6, 7, 8],
'C': [9, 10, 11, 12]
}, index=[['a', 'a', 'b', 'b'], ['foo', 'bar', 'foo', 'bar']])
print(df_merge)
Output:
A B C
a foo 1 5 9
bar 2 6 10
b foo 3 7 11
bar 4 8 12
如果我们将此 DataFrame 写入 Excel 文件merge_cells=True，具有相同索引值的单元格将被合并：
df_merge.to_excel("example_merge_cells_true.xlsx", merge_cells=True)
但是，如果我们设置merge_cells=False，单元格不会被合并：
df_merge.to_excel("example_merge_cells_false.xlsx", merge_cells=False)
Output:
| 1 | 2 | 3 | 4 |
--|---------------------------
a | foo | 1 | 5 | 9 |
a | bar | 2 | 6 | 10 |
b | foo | 3 | 7 | 11 |
b | bar | 4 | 8 | 12 |
在第一种情况下，“a”和“b”索引值将在生成的 Excel 文件中各跨两行，因为它们在“foo”和“bar”子索引中重复。在第二种情况下，‘a’和‘b’值将出现在每一行中，而不被合并。
Excel导出救星
我与一家金融科技公司签约，该公司向市场推出了一种新的金融产品。经过六个月的运营后，该公司希望分析绩效数据以得出见解并制定未来战略。
任务是在 Python 中执行复杂的分析，然后将结果以 Excel 报告形式呈现给我们的非技术利益相关者。我使用 Python 的 psycopg2 连接我们的 PostgreSQL 数据库和 Pandas 进行数据操作。
我的工作是将分析结果转换为 Excel，同时保留数据的结构和格式。这就是to_excelPandas 的功能变得无价。
考虑一个数据框df_customer_demographics包含详细的基于人口统计的分析。我将其导出到 Excel 文件：
df_customer_demographics.to_excel("analysis_report.xlsx", sheet_name='Customer Demographics', index=False)
接下来，我有另一个 DataFramedf_product_performance，我需要将其放入同一个 Excel 文件中，但放在不同的工作表上：
with pd.ExcelWriter('analysis_report.xlsx', engine='openpyxl', mode='a') as writer:
df_product_performance.to_excel(writer, sheet_name='Product Performance', index=False)
在几个小时内，我使用 Pandas 根据我们的大规模数据创建了一份全面的多表 Excel 报告to_excel.
进一步阅读
https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html
