11. Pandas ：操作Excel文件（Excel报表的案例研究）
从一个装有各种 Excel 文件的文件夹开始，这些文件需要被整合到 Excel 报表中。
它们包含了虚构的电信运营商在全美各营业厅的套餐（金、银、铜）销售情况。每个月有两个文件，子文件夹 new 中的是新用户，子文件夹 existing 中的是老用户。
由于这些报表来自不同的系统，因而它们的格式也不相同：新用户的数据以 xlsx 文件格式
交付，老用户的数据则以旧的 xls 格式交付。每个文件最多包含了 10000 次交易。
一 目标
生成一张 Excel 报表，在报表中展示每个营业厅每月的总体销售情况。
文件夹 new 中的 January.xlsx 文件：
二 方法：写一个脚本文件
脚本文件会从两个目录中读取 Excel 文件、汇总数据，最后将总结表写入一个新的 Excel 文件。将脚本文件和 sales_data 文件夹放到一起。
读取所有Excel → 合并 → 按日期+店铺汇总 → 按月统计 → 导出报表。
from pathlib import Path
import pandas as pdPath：用于处理文件路径（跨平台兼容）。
通过标准库 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 Path
import 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")