Python 数据处理 —— pandas Excel 文件读写

Excel 文件

read_excel() 方法可以使用 openpyxl 模块读取 Excel 2007+（.xlsx）文件

可以使用 xlrd 读取 Excel 2003（.xls）文件。可以使用 pyxlsb 读取二进制 Excel（.xlsb）文件

to_excel() 实例方法可以用于将 DataFrame 保存到 Excel 文件

注意：

用于写出旧式 .xls 文件的 xlwt 软件包不再维护。 xlrd 软件包现在仅用于读取旧式 .xls 文件

对于默认参数 engine=None, read_excel() 将使用 xlrd 引擎。

如果安装了 openpyxl，则现在许多情况下将默认使用 openpyxl 引擎。因此，强烈建议安装 openpyxl 来读取 Excel 2007+ (.xlsx)文件

1 读取 Excel 文件

在最基本的用例中，read_excel 采用一个指向 Excel 文件的路径，而 sheet_name 指示要解析的工作表。

# Returns a DataFrame
pd.read_excel("path_to_file.xls", sheet_name="Sheet1")

1.1 ExcelFile 类

为了方便处理来自同一文件的多个工作表，可以使用 ExcelFile 类包装文件并将其传递到 read_excel 中。

由于仅将文件读入内存一次，因此读取多个工作表会带来性能上的好处。

xlsx = pd.ExcelFile("path_to_file.xls")
df = pd.read_excel(xlsx, "Sheet1")

ExcelFile 类也可以用作上下文管理器

with pd.ExcelFile("path_to_file.xls") as xls:
    df1 = pd.read_excel(xls, "Sheet1")
    df2 = pd.read_excel(xls, "Sheet2")

sheet_names 属性将会获取文件中工作表名称列表

ExcelFile 的主要作用是用不同的参数解析多个工作表

data = {}
# For when Sheet1's format differs from Sheet2
with pd.ExcelFile("path_to_file.xls") as xls:
    data["Sheet1"] = pd.read_excel(xls, "Sheet1", index_col=None, na_values=["NA"])
    data["Sheet2"] = pd.read_excel(xls, "Sheet2", index_col=1)

请注意，如果所有工作表都使用相同的解析参数，则可以将工作表名称列表简单地传递给 read_excel，且不会降低性能。

# using the ExcelFile class
data = {}
with pd.ExcelFile("path_to_file.xls") as xls:
    data["Sheet1"] = pd.read_excel(xls, "Sheet1", index_col=None, na_values=["NA"])
    data["Sheet2"] = pd.read_excel(xls, "Sheet2", index_col=None, na_values=["NA"])

# equivalent using the read_excel function
data = pd.read_excel(
    "path_to_file.xls", ["Sheet1", "Sheet2"], index_col=None, na_values=["NA"]
)

还可以使用 xlrd.book.Book 对象作为参数来调用 ExcelFile。这允许用户控制如何读取 excel 文件。

例如，可以通过使用 xlrd.open_workbook() 函数并设置参数 on_demand=True，来按需加载工作表。

import xlrd

xlrd_book = xlrd.open_workbook("path_to_file.xls", on_demand=True)
with pd.ExcelFile(xlrd_book) as xls:
    df1 = pd.read_excel(xls, "Sheet1")
    df2 = pd.read_excel(xls, "Sheet2")

1.2 指定工作表

第二个参数是 sheet_name，不要与 ExcelFile.sheet_names 混淆

ExcelFile 的属性 sheet_names 用于访问所有工作表

• sheet_name 参数用于指定要读取的一个或多个工作表
• sheet_name 的默认值是 0，表示读取第一个工作表
• 传递一个字符串来引用工作簿中特定工作表的名称
• 传递一个整数来引用工作表的索引，索引从 0 开始
• 传递一个字符串或整数列表，以返回指定工作表的 DataFrame 字典
• 传递一个 None 来返回包含所有可用工作表的 DataFrame 字典

传入工作表名称

# Returns a DataFrame
pd.read_excel("path_to_file.xls", "Sheet1", index_col=None, na_values=["NA"])

传入工作表索引

# Returns a DataFrame
pd.read_excel("path_to_file.xls", 0, index_col=None, na_values=["NA"])

使用默认值

# Returns a DataFrame
pd.read_excel("path_to_file.xls")

使用 None 获取所有表

# Returns a dictionary of DataFrames
pd.read_excel("path_to_file.xls", sheet_name=None)

使用列表获取多个工作表

# Returns the 1st and 4th sheet, as a dictionary of DataFrames.
pd.read_excel("path_to_file.xls", sheet_name=["Sheet1", 3])

1.3 读取 MultiIndex

read_excel 可以通过对 index_col 传递一个列表读取多级索引，通过向 header 传递一个列表读取列名多级索引

例如，读取不带名称的 MultiIndex 索引

In [315]: df = pd.DataFrame(
   .....:     {"a": [1, 2, 3, 4], "b": [5, 6, 7, 8]},
   .....:     index=pd.MultiIndex.from_product([["a", "b"], ["c", "d"]]),
   .....: )
   .....: 

In [316]: df.to_excel("path_to_file.xlsx")

In [317]: df = pd.read_excel("path_to_file.xlsx", index_col=[0, 1])

In [318]: df
Out[318]: 
     a  b
a c  1  5
  d  2  6
b c  3  7
  d  4  8

如果索引具有级别名称，它们也将使用相同的参数进行解析

In [319]: df.index = df.index.set_names(["lvl1", "lvl2"])

In [320]: df.to_excel("path_to_file.xlsx")

In [321]: df = pd.read_excel("path_to_file.xlsx", index_col=[0, 1])

In [322]: df
Out[322]: 
           a  b
lvl1 lvl2      
a    c     1  5
     d     2  6
b    c     3  7
     d     4  8

如果源文件同时具有 MultiIndex 索引和列名，则应将它们对应的列表传递给 index_col 和 header 参数

In [323]: df.columns = pd.MultiIndex.from_product([["a"], ["b", "d"]], names=["c1", "c2"])

In [324]: df.to_excel("path_to_file.xlsx")

In [325]: df = pd.read_excel("path_to_file.xlsx", index_col=[0, 1], header=[0, 1])

In [326]: df
Out[326]: 
c1         a   
c2         b  d
lvl1 lvl2      
a    c     1  5
     d     2  6
b    c     3  7
     d     4  8

1.4 解析特定的列

read_excel 使用 usecols 关键字来允许您指定要读取的列的子集。

为 usecols 传入一个整数将不再有效。请将一个从 0 开始的列表传递给 usecols

您也可以将以逗号分隔的一组 Excel 列或数据表范围的字符串传递给 usecols

pd.read_excel("path_to_file.xls", "Sheet1", usecols="A,C:E")

如果 usecols 是整数列表，则假定它是文件的列索引

pd.read_excel("path_to_file.xls", "Sheet1", usecols=[0, 2, 3])

元素顺序并不重要，即 usecols=[0，1] 与 [1，0] 相同

如果 usecols 是一个字符串列表，则假定每个字符串都对应于文件的列名或从文档标题行推断出来的列名。

pd.read_excel("path_to_file.xls", "Sheet1", usecols=["foo", "bar"])

同样也会忽略列表元素的顺序

如果 usecols 是可调用函数，传入的是列名，返回可调用函数计算结果为 True 的列名

pd.read_excel("path_to_file.xls", "Sheet1", usecols=lambda x: x.isalpha())

1.5 解析日期

在读取 excel 文件时，类似 datetime 的值通常会自动转换为适当的 dtype。

但是，如果你有一列字符串看起来像日期(但实际上不是格式化为 excel 中的日期)，你可以使用 parse_dates 关键字将这些字符串解析为日期时间格式

pd.read_excel("path_to_file.xls", "Sheet1", parse_dates=["date_strings"])

1.6 单元格转换

可以通过 converters 选项转换 Excel 单元格的内容。例如，要将列转换为布尔值

pd.read_excel("path_to_file.xls", "Sheet1", converters={"MyBools": bool})

这个选项可以处理缺失值，并将转换时出现的异常作为缺失数据处理。变换是逐个单元格而不是对整个列进行的，所以不能保证数组的 dtype

def cfun(x):
    return int(x) if x else -1

pd.read_excel("path_to_file.xls", "Sheet1", converters={"MyInts": cfun})

1.7 指定类型

作为转换器的替代方法，可以使用 dtype 关键字指定整个列的类型，该关键字通过将字典的名称映射到列，值对应列的类型。

pd.read_excel("path_to_file.xls", dtype={"MyInts": "int64", "MyText": str})

2 写出 Excel 文件

2.1 将 Excel 文件写入磁盘

要将 DataFrame 对象写入 Excel 文件的工作表中，可以使用 to_excel 实例方法。

其参数与 to_csv 基本相同，第一个参数是 excel 文件的名称，第二个可选参数是数据写入到的工作表的名称。例如:

df.to_excel("path_to_file.xlsx", sheet_name="Sheet1")

扩展名为 .xls 的文件将使用 xlwt 写出，扩展名为 .xlsx 的文件将使用 xlsxwriter（如果可用）或 openpyxl 编写。

可以使用 index_label 参数设置索引名称，如果是多级索引，需要传递名称列表

merge_cells 参数可以设置多级索引是否合并单元格，默认为 TRUE，如果为 False，则会写出所有的索引值

df.to_excel("path_to_file.xlsx", index_label="label", merge_cells=False)

为了在一个 Excel 文件中写入不同的 DataFrame，可以传递一个 ExcelWriter

with pd.ExcelWriter("path_to_file.xlsx") as writer:
    df1.to_excel(writer, sheet_name="Sheet1")
    df2.to_excel(writer, sheet_name="Sheet2")

注意：
因为 Excel 默认将所有数字存储为浮点数，在不损失精度的情况下，pandas 默认会尝试将整数转换为浮点数。

你可以设置 convert_float=False 来取消这一行为，可能会带来性能的提升

2.2 将 Excel 文件写入内存

pandas 支持使用 ExcelWriter 将 Excel 文件写入到类似缓冲区的对象中，如 StringIO 或 BytesIO。

from io import BytesIO

bio = BytesIO()

# By setting the 'engine' in the ExcelWriter constructor.
writer = pd.ExcelWriter(bio, engine="xlsxwriter")
df.to_excel(writer, sheet_name="Sheet1")

# Save the workbook
writer.save()

# Seek to the beginning and read to copy the workbook to a variable in memory
bio.seek(0)
workbook = bio.read()

注意：
engine 参数是可选的，但是推荐为该参数指定值

设置 engine='xlrd' 将生成 excel2003 格式的工作簿（xls）。使用 "openpyxl" 或 "xlsxwriter" 将生成 excel2007 格式的工作簿（xlsx）。

如果省略，则生成 Excel 2007 格式的工作簿

3 Excel 写出引擎

在 1.2.0 版本后，由于 xlwt 包不再维护，xlwt 引擎将从 pandas 的未来版本中移除。

这是 pandas 中唯一支持写入 .xls 文件的引擎

pandas 通过两种方法选择 Excel 写出引擎

1. engine 参数
2. 文件扩展名（通过配置选项中指定的默认值）

默认情况下，pandas 对 .xlsx 使用 XlsxWriter，对 .xlsm 使用 openpyxl，对 .xls 文件使用 xlwt。

如果您安装了多个引擎，则可以通过设置配置选项 io.excel.xlsx.writer 和 io.excel.xls.writer 来设置默认引擎

如果 Xlsxwriter 不可用，pandas 将使用 openpyxl 来获取 .xlsx 文件。

要指定要使用哪个写出引擎，可以将 engine 关键字参数传递给 to_excel 和 ExcelWriter。内置引擎为

• openpyxl: 需要 2.4 或更高版本
• xlsxwriter
• xlwt

# By setting the 'engine' in the DataFrame 'to_excel()' methods.
df.to_excel("path_to_file.xlsx", sheet_name="Sheet1", engine="xlsxwriter")

# By setting the 'engine' in the ExcelWriter constructor.
writer = pd.ExcelWriter("path_to_file.xlsx", engine="xlsxwriter")

# Or via pandas configuration.
from pandas import options  # noqa: E402

options.io.excel.xlsx.writer = "xlsxwriter"

df.to_excel("path_to_file.xlsx", sheet_name="Sheet1")

4 样式和格式

可以使用 to_excel 方法中的以下参数来修改创建的 Excel 工作表的外观和感觉

• float_format: 浮点数的格式化字符串（默认为 None）
• freeze_panes: 包含两个整数的元组，表示冻结最底行和最后列范围内的单元格。这些数值是 1 起始的，因此 (1, 1) 将冻结第一行和第一列（默认为 None）

使用 Xlsxwriter 引擎可以提供许多选项来控制用 to_excel 方法创建的 Excel 工作表的格式

具体可参考 https://xlsxwriter.readthedocs.io/working_with_pandas.html