Python 玩转 Excel 案例【第 10 期】
📌 案例说明
原始数据表（交叉报表）：
这是“销售部”的月度数据，还有“市场部”、“研发部”、“财务部”共4个Sheet，结构完全相同。
目标任务：
在日常工作中，我们经常遇到这种“交叉表”格式的数据——行是项目，列是月份，中间是数值。这种格式适合人看，但不适合机器分析。
本期案例将跟大家学习如何用 Python 将多个多级表头的交叉表一次性合并成一个规范的一维表，方便后续的数据分析和可视化：
核心操作：多表合并 + 交叉表转一维表
- • 关键函数：
pd.read_excel() + pd.concat() - • 核心逻辑：先按 Sheet 转换为临时 DataFrame，再用
pd.concat() 纵向合并
📜 完整代码
"""
将多个多级表头工作表合并为规范一维表（使用 pd.concat 标准写法）
"""
import pandas as pd
# 读取所有 sheet
file_path = "chapter-10.xlsx"
sheets_dict = pd.read_excel(file_path, sheet_name=None) # 返回 {sheet名: DataFrame}
# 用于存放每个 Sheet 转换后的 DataFrame
all_dfs = []
for sheet_name, df in sheets_dict.items():
# 1. 处理多级表头
# 获取列名：第一行（月份）
months = df.iloc[0, 1:].tolist() # 从第2列开始是月份
# 获取项目列（第一列）
projects = df.iloc[1:, 0].tolist() # 从第2行开始是项目
# 获取数据区域（数值）
values = df.iloc[1:, 1:].values
# 2. 为当前 Sheet 构建临时数据
temp_data = []
for i, project in enumerate(projects):
for j, month in enumerate(months):
temp_data.append({
"部门": sheet_name,
"项目": project,
"月份": month,
"金额": values[i, j]
})
# 3. 将当前 Sheet 的数据转为 DataFrame
temp_df = pd.DataFrame(temp_data)
all_dfs.append(temp_df)
# 4. 使用 pd.concat() 合并所有 Sheet 的数据
result_df = pd.concat(all_dfs, ignore_index=True)
# 5. 保存结果
result_df.to_excel("规范一维表.xlsx", index=False)
print("✅ 转换完成，已保存为 规范一维表.xlsx")
第一步：导入所需库
import pandas as pd
代码解释：
- •
import pandas as pd
导入 pandas 库，这是 Python 数据处理的核心工具。它提供了 read_excel() 读取 Excel 文件、DataFrame 数据结构、concat() 合并数据等强大功能。
第二步：读取所有工作表
file_path = "chapter-10.xlsx"
sheets_dict = pd.read_excel(file_path, sheet_name=None) # 返回 {sheet名: DataFrame}
代码解释：
- •
pd.read_excel(file_path, sheet_name=None)
sheet_name=None 是关键参数，表示读取 Excel 文件中的所有工作表。 - • 返回值
返回一个字典，结构如下：{
"销售部": DataFrame(销售部的数据),
"市场部": DataFrame(市场部的数据),
"研发部": DataFrame(研发部的数据),
"财务部": DataFrame(财务部的数据)
}
💡 小贴士：如果不指定 sheet_name，默认只读取第一个 Sheet。sheet_name=None 让我们一次性读取所有 Sheet，避免多次调用 pd.read_excel()。
第三步：创建列表存放转换后的 DataFrame
all_dfs = []
代码解释：
- •
all_dfs = []
创建一个空列表，用于存放每个 Sheet 转换后的一维表 DataFrame。最后用 pd.concat() 一次性合并所有数据。
第四步：遍历并转换每个工作表
for sheet_name, df in sheets_dict.items():
# 1. 处理多级表头
# 获取列名：第一行（月份）
months = df.iloc[0, 1:].tolist() # 从第2列开始是月份
# 获取项目列（第一列）
projects = df.iloc[1:, 0].tolist() # 从第2行开始是项目
# 获取数据区域（数值）
values = df.iloc[1:, 1:].values
关于 iloc 索引规则
pandas中DataFrame的索引规则：
- •
df.iloc[1:, 0] 表示从第2行到最后，第1列
关于 iloc 和 loc 索引规则
pandas 中 DataFrame 的两种核心索引方式：
逐行解释
months = df.iloc[0, 1:].tolist()
取第1行、从第2列开始的所有数据作为月份。
示例（销售部数据）：
原表第1行（行索引0）：
["项目", "1月", "2月", "3月", "4月", "5月", "6月"]
df.iloc[0, 1:] 取到：
["1月", "2月", "3月", "4月", "5月", "6月"]
最终 months = ["1月", "2月", "3月", "4月", "5月", "6月"]
projects = df.iloc[1:, 0].tolist()
取第2行到最后、第1列的所有数据作为项目。
示例：
原表第1列（列索引0）：
["项目", "收入", "成本", "利润", "管理费用", "销售费用"]
df.iloc[1:, 0] 取到（跳过第1行的"项目"）：
["收入", "成本", "利润", "管理费用", "销售费用"]
最终 projects = ["收入", "成本", "利润", "管理费用", "销售费用"]
values = df.iloc[1:, 1:].values
取第2行到最后、第2列到最后的数值区域。
示例：
原表数据区域（5行×6列）：
收入: [23795, 8860, 13390, 19964, 19284, 14265]
成本: [5466, 9426, 10578, 16636, 16363, 16111]
利润: [1130, 2685, 1769, 3391, 6611, 7949]
管理费用: [2933, 1715, 1455, 2824, 1684, 959]
销售费用: [3885, 521, 2800, 1247, 3404, 974]
.values 将其转换为 numpy 数组，方便按索引取值
第五步：为当前 Sheet 构建临时数据
# 2. 为当前 Sheet 构建临时数据
temp_data = []
for i, project in enumerate(projects):
for j, month in enumerate(months):
temp_data.append({
"部门": sheet_name,
"项目": project,
"月份": month,
"金额": values[i, j]
})
逐层解析
enumerate(projects)：
enumerate 为项目列表添加索引，返回 (索引, 值) 的配对。
示例：
projects = ["收入", "成本", "利润", "管理费用", "销售费用"]
list(enumerate(projects))
# 结果：[(0, "收入"), (1, "成本"), (2, "利润"), (3, "管理费用"), (4, "销售费用")]
外层 for 循环
for i, project in enumerate(projects):
- •
project：项目名称（"收入", "成本", ...）
内层 for 循环
for j, month in enumerate(months):
- •
j：月份索引（0, 1, 2, 3, 4, 5） - •
month：月份名称（"1月", "2月", ...）
第4层：构建记录
temp_data.append({
"部门": sheet_name,
"项目": project,
"月份": month,
"金额": values[i, j]
})
- •
values[i, j] 取出对应项目+月份交叉点的数值 - • 每循环一次生成一条记录（一个字典），追加到
temp_data 列表
执行过程示意（以“销售部”为例）：
总计：5个项目 × 6个月 = 30条记录
第六步：将当前 Sheet 转为 DataFrame 并收集
# 3. 将当前 Sheet 的数据转为 DataFrame
temp_df = pd.DataFrame(temp_data)
all_dfs.append(temp_df)
代码解释：
- •
pd.DataFrame(temp_data)
将字典列表转换为 DataFrame，字典的键变成列名，值变成行数据。 - •
all_dfs.append(temp_df)
将当前 Sheet 转换后的 DataFrame 添加到 all_dfs 列表中。
此时 all_dfs 的结构：
all_dfs = [
DataFrame_销售部, # 30行 × 4列
DataFrame_市场部, # 30行 × 4列
DataFrame_研发部, # 30行 × 4列
DataFrame_财务部 # 30行 × 4列
]
第七步：合并所有数据
# 4. 使用 pd.concat() 合并所有 Sheet 的数据
result_df = pd.concat(all_dfs, ignore_index=True)
代码解释：
- •
pd.concat(all_dfs, ignore_index=True)
pd.concat() 是 pandas 中用于合并多个 DataFrame 的核心函数。 - •
ignore_index=True 表示重新生成行索引（0,1,2...），避免多个 DataFrame 的原始索引冲突
合并过程示意：
DataFrame_销售部 (30行)
+
DataFrame_市场部 (30行)
+
DataFrame_研发部 (30行)
+
DataFrame_财务部 (30行)
↓
pd.concat()
↓
result_df (120行)
💡 小贴士：pd.concat() 默认是纵向堆叠（axis=0），这正好符合我们的需求——把多个 Sheet 的数据上下拼接起来。
第八步：保存结果
# 5. 保存结果
result_df.to_excel("规范一维表.xlsx", index=False)
print("✅ 转换完成，已保存为 规范一维表.xlsx")
代码解释：
- •
to_excel(..., index=False)
保存为 Excel 文件，index=False 表示不额外添加行号列。
运行结果预览
生成的 规范一维表.xlsx 内容如下（部分展示）：
总计：4个部门 × 5个项目 × 6个月 = 120行数据
为什么需要这种转换？
原始格式（交叉表）的局限
这种格式适合人阅读，但想分析“收入在3个月的趋势”时：
转换后（一维表）的优势
转换后，可以轻松实现：
# 按项目汇总
result_df.groupby("项目")["金额"].sum()
# 按月份汇总
result_df.groupby("月份")["金额"].sum()
# 画收入趋势图
df_income = result_df[result_df["项目"]=="收入"]
df_income.plot(x="月份", y="金额")
# 与其它表关联
pd.merge(result_df, 部门信息表, on="部门")
📚 本期核心知识点
📍 知识点 1：pd.read_excel() 的 sheet_name=None 参数
一次性读取 Excel 文件中的所有工作表，返回 {Sheet名: DataFrame} 字典，是批量处理多表数据的标准写法。
📍 知识点 2：df.iloc[] 整数位置索引
- •
df.iloc[0, 1:]：取第1行，从第2列开始 - •
df.iloc[1:, 0]：取第2行到最后，第1列 - •
df.iloc[1:, 1:]：取第2行到最后，第2列到最后
适用于处理固定格式的多级表头数据。
📍 知识点 3：enumerate() 同时获取索引和值
for i, project in enumerate(projects):
# i 是索引（0,1,2...）
# project 是项目名称
📍 知识点 4：字典列表转 DataFrame
temp_data = [{"部门": "销售部", "项目": "收入", ...}, ...]
temp_df = pd.DataFrame(temp_data) # 键变列名，值变行
📍 知识点 5：pd.concat() 纵向合并多个 DataFrame
all_dfs = [df1, df2, df3, ...] # DataFrame 列表
result = pd.concat(all_dfs, ignore_index=True) # 纵向堆叠
- •
ignore_index=True 重新生成行索引，避免冲突
📍 知识点 6：双层循环拆解交叉表
外层循环遍历项目，内层循环遍历月份，将每个“项目×月份”组合拆成独立的一行，是交叉表转一维表的通用逻辑。
🔄 本案例核心流程
① 读取所有 Sheet → ② 遍历每个 Sheet → ③ 提取月份和项目 → ④ 双层循环拆解 → ⑤ 转为临时 DataFrame → ⑥ 收集到列表 → ⑦ 用 pd.concat() 合并 → ⑧ 保存结果
读取所有 Sheet (pd.read_excel(sheet_name=None))
↓
创建空列表存放 DataFrame (all_dfs = [])
↓
遍历每个 Sheet (for sheet_name, df in sheets_dict.items())
↓
提取月份 (df.iloc[0, 1:].tolist())
提取项目 (df.iloc[1:, 0].tolist())
提取数值 (df.iloc[1:, 1:].values)
↓
双层循环拆解 (for i, project in enumerate(projects):
for j, month in enumerate(months))
↓
收集为临时字典列表 (temp_data.append({...}))
↓
转为临时 DataFrame (temp_df = pd.DataFrame(temp_data))
↓
添加到列表 (all_dfs.append(temp_df))
↓
合并所有 DataFrame (result_df = pd.concat(all_dfs, ignore_index=True))
↓
保存结果 (result_df.to_excel("规范一维表.xlsx", index=False))
📦 资源获取提示
关注「码农自习室」，后台回复关键词 PythonExcel案例，即可获取本文完整代码及配套练习数据集，一起动手掌握高效数据操作的核心技巧！
❤️ 支持我们
如果本文对你有帮助，欢迎点赞 + 关注 + 点亮小红心，你的支持就是我们持续创作的最大动力～
