当前位置：首页 > news >正文

11. Pandas ：操作Excel文件（Excel报表的案例研究）

news 来源：原创 2025/8/20 14:06:13

从一个装有各种 Excel 文件的文件夹开始，这些文件需要被整合到 Excel 报表中。

它们包含了虚构的电信运营商在全美各营业厅的套餐（金、银、铜）销售情况。每个月有两个文件，子文件夹 new 中的是新用户，子文件夹 existing 中的是老用户。

由于这些报表来自不同的系统，因而它们的格式也不相同：新用户的数据以 xlsx 文件格式

交付，老用户的数据则以旧的 xls 格式交付。每个文件最多包含了 10000 次交易。

一目标

生成一张 Excel 报表，在报表中展示每个营业厅每月的总体销售情况。

文件夹 new 中的 January.xlsx 文件：

二方法：写一个脚本文件

脚本文件会从两个目录中读取 Excel 文件、汇总数据，最后将总结表写入一个新的 Excel 文件。将脚本文件和 sales_data 文件夹放到一起。

读取所有Excel → 合并 → 按日期+店铺汇总 → 按月统计 → 导出报表。

from pathlib import Pathimport pandas as pd

Path：用于处理文件路径（跨平台兼容）。

通过标准库 pathlib 模块中的 Path 类，你可以使用多种强大的工具：路径对象可以让你轻松地通过斜杠连接路径的分量来构造路径，就像在 this_dir / "sales_data" 及其下面 4 行代码中所展示的那样。这些路径对象是可以跨平台工作的。也可以使用 rglob 之类的过滤器。

补充：Path 对象支持用 / 拼接路径分量。
from pathlib import Path# 构造路径：当前目录下的 sales_data 子目录
this_dir = Path.cwd()  # 获取当前工作目录
data_path = this_dir / "sales_data"  # 拼接路径
print(data_path)  
输出：

/home/user/project/sales_data（Linux）

或 C:\project\sales_data（Windows）

/ 操作符会自动处理不同操作系统的路径分隔符（如 \ 或 /），无需手动调整。

支持多级拼接，例如 data_path / "2025" / "report.csv"

补充：rglob 过滤器在下面说明。

# 文件的目录
this_dir = Path(__file__).resolve().parent

获取当前脚本所在目录。

Path(__file__)：获取当前脚本文件路径。__file__ 表示源代码文件运行时所在路径。

parent：返回上级目录路径（即脚本所在文件夹）。

若脚本路径为 /home/user/project/scripts/analyze.py，则 this_dir 为 /home/user/project/scripts/。

resolve()：解析符号链接，获取绝对路径。在 parent 前面调用的 resolve 方法会将 parent路径转换为绝对路径。

# 从sales_data的所有子文件夹中读取Excel文件
parts = []
for path in (this_dir / "sales_data").rglob("*.xls*"):print(f'Reading {path.name}')part = pd.read_excel(path, index_col="transaction_id")parts.append(part)

合并所有Excel文件。

(this_dir / "sales_data")：定位到 sales_data 子目录。

rglob("*.xls*")：递归搜索所有 .xls 或 .xlsx 文件。读取某个目录中所有 Excel 文件。通过通配符来展开路径名。? 通配符表示某单个字符，而 * 表示任意多个字符（包括 0 个）。将 *.xls* 作为 globbing 表达式可以确保新旧两种格式的 Excel 文件都能被发现。也可以写成 [!~$]*.xls*。这样就可以忽略临时的 Excel 文件（文件名以～ $ 开头）。

补充：表达式 [!~$]*.xls*

[!...] 是排除模式，表示不匹配方括号内指定的字符。

[!a] 表示排除以 a 开头的文件名。

[!~$] 表示排除以 ~ 或 $ 开头的文件名。

pd.read_excel：读取 Excel 文件，并将 transaction_id 列设为 DataFrame 的索引。

parts：存储所有子文件的DataFrame，后续合并。

parts.append(part)：

将所有子 DataFrame 存储到 parts 列表中，后续可通过 pd.concat(parts) 合并为单一 DataFrame。

注意：

要求每个Excel文件都有 transaction_id 列，否则报错。

文件需有相同列名才能正确合并。

补充：rglob 过滤器
# 递归查找所有 .csv 文件
csv_files = list(data_path.rglob("*.csv"))  
# 输出类似：[PosixPath('/sales_data/2025/report.csv'), ...]# 查找所有以 "temp" 开头的文件（含子目录）
temp_files = list(data_path.rglob("temp*.*"))  
glob("*.csv")：仅搜索当前目录下的 CSV 文件。glob 会忽略子目录。

rglob("*.csv")：等同于 glob("**/*.csv")，递归所有子目录。对所有子目录也进行匹配。

# 将从Excel文件生成的DataFrame结合成单个DataFrame
# pandas会负责对列进行对齐
df = pd.concat(parts)

合并DataFrame。

pd.concat(parts)：将多个DataFrame按行拼接。

合并逻辑：默认按行合并（axis=0），要求所有 Excel 文件的列结构一致。

自动对齐列名：不同文件的列名若不一致，合并后会产生NaN。若文件1有列 [A, B]，文件2有列 [A, C]，合并后列名为 [A, B, C]，缺失值填充 NaN。

# 对每个营业厅进行数据透视，将同一天产生的交易全部加起来
pivot = pd.pivot_table(df,index="transaction_date", columns="store",values="amount", aggfunc="sum")

创建数据透视表：通过 pivot_table 函数，将原始数据按以下规则重组。

index：行索引 → 交易日期。以 transaction_date（交易日期）为行索引，自动去重并排序所有日期。指定行索引为交易日期，实现按日期分组。

columns：列分类 → 店铺。以 store（营业厅名称）为列索引，自动去重并生成列标题。

单元格值：对同一日期、同一营业厅的 amount（交易金额）进行求和操作（sum）。

values="amount"：指定需要聚合的数值列。amount是列名。若 values 未指定，默认聚合所有数值型列；若指定非数值列，会引发错误。

aggfunc="sum"：定义聚合函数为求和。

# 按月重采样，并赋予一个索引名称
summary = pivot.resample("M").sum()
summary.index.name = "Month"

resample("M")：按月末日期分组（如 2023-01-31）。

.sum()：每月各店铺销售额求和。

index.name：重命名索引为 Month。

summary.to_excel(this_dir / "sales_report_pandas.xlsx")

导出到Excel。

生成文件路径：this_dir/sales_report_pandas.xlsx。

文件内容：月度各店铺销售总额报表。

三实践记录

python版本：3.12.4

pandas版本：2.2.2

报错原因：sales_data 文件夹中包含 .xls 文件（旧版 Excel 格式）。从 Pandas 1.2.0 开始，默认引擎（openpyxl）只支持 .xlsx 文件，无法读取 .xls 文件。这会导致在尝试读取 .xls 文件时报错。

修改：需要安装 xlrd 库（支持 .xls 格式），并在读取 .xls 文件时显式指定引擎。

第1步：pip install --upgrade xlrd

xlrd 新版本只支持 .xls 文件。

在 pandas 1.2.0 及以后的版本中，默认情况下不再支持直接使用 xlrd 来读取 .xls 文件，因为 xlrd 从 2.0.0 版本开始已移除对 Excel .xlsx 文件的支持，并且只支持 .xls 文件。

第2步：更新代码。

结果：

警告信息是一个 FutureWarning，这是 pandas 提前通知用户某些功能或参数将在未来版本中被移除或更改。具体来说，这条警告是关于时间序列重采样频率字符串 'M' 的使用。在 pandas 中，'M' 一直用于表示“按月”重采样（即每个月的最后一天）。然而，为了更清晰和一致，pandas 团队引入了新的频率字符串 'ME'，表示“按月末”（Month End）。在未来的 pandas 版本中，'M' 将被移除，建议使用 'ME' 来避免潜在的兼容性问题。

需要将代码中的 'M' 替换为 'ME'

summary = pivot.resample("ME").sum()

生成了sales_report_pandas.xlsx文件。

文件内容：

完整代码：

from pathlib import Pathimport pandas as pd# 文件的目录
this_dir = Path(__file__).resolve().parent# 从sales_data的所有子文件夹中读取Excel文件
parts = []
for path in (this_dir / "sales_data").rglob("*.xls*"):print(f'Reading {path.name}')if path.suffix == ".xls":  # 如果是 .xls 文件part = pd.read_excel(path, index_col="transaction_id", engine="xlrd")else:  # 如果是 .xlsx 文件part = pd.read_excel(path, index_col="transaction_id")parts.append(part)# 将从Excel文件生成的DataFrame结合成单个DataFrame
# pandas会负责对列进行对齐
df = pd.concat(parts)# 对每个营业厅进行数据透视，将同一天产生的交易全部加起来
pivot = pd.pivot_table(df,index="transaction_date", columns="store",values="amount", aggfunc="sum")# 按月重采样，并赋予一个索引名称
summary = pivot.resample("M").sum()
summary.index.name = "Month"# 将总结报表写入Excel文件
summary.to_excel(this_dir / "sales_report_pandas.xlsx")

一 目标

二 方法：写一个脚本文件

三 实践记录

相关文章：

一目标

二方法：写一个脚本文件

三实践记录