Python pandas 批量处理Excel数据透视表

数据透视表，但凡用过 Excel 的人，基本都离不开它。行拖一拖、列拽一拽、值拉一拉，几秒钟就能把一堆杂乱的数据归纳成清晰的报表。而在 Python 的 pandas 里，同样的事情一行代码就能搞定——pivot_table 函数，干净利落。

下面直接从一个最简单的例子开始，看看 pandas 是怎么把 Excel 透视表的那套逻辑搬到代码里的。

一、读取与透视

import pandas as pd

df = pd.read_excel("销售数据.xlsx")

pivot = pd.pivot_table(
    df,
    values="销售额",
    index="城市",
    columns="品类",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="合计"
)

这段代码干了什么？读取 Excel 文件，然后指定“销售额”作为聚合值，按“城市”做行标签，按“品类”做列标签，聚合方式用求和，空值补零，最后加上合计行和合计列。整个过程就是 Excel 里拖拽鼠标的翻版，但一旦写成代码，就可以无限复用。

二、多级透视

实际业务中，往往需要更细的维度。比如不仅要看城市，还要看每个城市下面的销售员；不仅看年度，还要按季度拆分。这时候把 index 参数换成列表即可。

pivot = pd.pivot_table(
    df,
    values="销售额",
    index=["城市", "销售员"],
    columns="季度",
    aggfunc=["sum", "mean"]
)

这样一来，行标签变成了多级索引，列标签只显示季度，而聚合函数同时用了求和与平均值，结果是一个带层级索引的 DataFrame，结构清晰，非常适合做进一步分析。

三、多个统计值

还有更灵活的用法：如果只想按城市汇总，但需要同时看到销售额的总和、均值、计数和最大值，可以直接把 aggfunc 写成一个字典，为每个值列分别指定函数。

pivot = pd.pivot_table(
    df,
    values="销售额",
    index="城市",
    aggfunc={"销售额": ["sum", "mean", "count", "max"]}
)

注意这里 values 参数其实可以省略，因为 aggfunc 字典的键已经指明了要计算的列。输出结果里，每个城市对应一行，销售额下面自动展开四个子列，一目了然。

四、知识扩展

到此为止，基本用法已经覆盖了大部分场景。但真正让 pivot_table 大放异彩的地方，是在批量处理 Excel 文件时。比如手头有几十个结构相同的销售报表，每张表一个月份，或者一个分店，手工拖拽透视表会累到崩溃，而用 pandas 只需要几行循环。

准备工作：安装与导入

确保环境里已经装好了 pandas 和 openpyxl（负责读写 xlsx 文件）。

pip install pandas openpyxl

导入时顺手把 glob 和 os 也带上，后面批量处理文件路径要用。

import pandas as pd
import glob
import os

核心：pandas 的 pivot_table 函数

前面已经演示了基本用法，这里再把参数跟 Excel 透视表区域的对应关系整理成表格，方便查阅：

Excel 透视表区域	pandas pivot_table 参数	说明
行	index	作为行标签的列，可以是单列或多列。
列	columns	作为列标签的列。
值	values	需要进行聚合计算的数值列。
计算方式	aggfunc	聚合函数，如 'sum'(求和), 'mean'(平均), 'count'(计数)。
总计	margins	True 或 False，是否显示总计行/列。
总计名称	margins_name	自定义总计行/列的标签，默认为 'All'。
空值填充	fill_value	将透视表中的空值替换为指定值。

实战场景：批量处理

场景一：合并多个 Excel 文件后生成透视表

假设数据分散在多个以“销售数据_”开头的 xlsx 文件中，每个文件结构完全一致。先用 glob 找到所有文件，逐个读取并拼接到一个 DataFrame 里，最后统一生成透视表。注意：如果文件名里包含了月份之类的信息，可以提取出来作为新列，增加透视维度。

import pandas as pd
import glob
# 1. 获取所有Excel文件路径
file_paths = glob.glob('销售数据_*.xlsx')  # 假设所有文件都以"销售数据_"开头
# 2. 读取并合并所有文件
all_data = pd.DataFrame()
for file in file_paths:
    df = pd.read_excel(file)
    all_data = pd.concat([all_data, df], ignore_index=True)
# (可选) 如果文件名包含月份信息，可以提取出来作为新列
# all_data['月份'] = all_data['文件名'].apply(lambda x: x.split('_')[1])
# 3. 生成数据透视表
pivot_table = pd.pivot_table(all_data,
                             values='销售额',
                             index='地区',
                             columns='产品',
                             aggfunc='sum',
                             margins=True,
                             margins_name='总计',
                             fill_value=0)
# 4. 导出结果
pivot_table.to_excel('汇总透视表.xlsx')
print("透视表已生成！")

场景二：处理一个 Excel 文件中的多个 Sheet

有些报表习惯把不同月份的数据放在同一个工作簿的不同 Sheet 里。用 pd.read_excel 的 sheet_name=None 参数可以一次性读入所有 Sheet，返回一个字典，键是 Sheet 名，值是 DataFrame。遍历字典，合并时顺手把 Sheet 名作为新列“月份”加进去，之后就能按月份做透视了。

import pandas as pd
# 1. 读取所有Sheet，sheet_name=None会返回一个字典
all_sheets = pd.read_excel('全年销售数据.xlsx', sheet_name=None)
# 2. 合并所有Sheet，并添加来源Sheet名作为新列
all_data = pd.DataFrame()
for sheet_name, df in all_sheets.items():
    df['月份'] = sheet_name  # 新增一列标记数据来源
    all_data = pd.concat([all_data, df], ignore_index=True)
# 3. 生成透视表（按月份和地区汇总）
pivot_table = pd.pivot_table(all_data,
                             values='销售额',
                             index=['月份', '地区'],
                             columns='产品',
                             aggfunc='sum',
                             fill_value=0,
                             margins=True,
                             margins_name='总计')
# 4. 导出结果
pivot_table.to_excel('月度销售透视表.xlsx')

场景三：批量生成多个透视表并导出到一个 Excel

如果需要从同一份源数据里产生多个维度的透视表（比如按地区汇总、按产品汇总、按地区×产品交叉汇总），可以借助 pd.ExcelWriter 把它们写到同一个工作簿的不同 Sheet 里，方便对比查看。

import pandas as pd
df = pd.read_excel('源数据.xlsx')
with pd.ExcelWriter('多维度透视报告.xlsx', engine='openpyxl') as writer:
    # 1. 按地区汇总
    pivot1 = pd.pivot_table(df, values='销售额', index='地区',
                            aggfunc='sum', margins=True, margins_name='合计')
    pivot1.to_excel(writer, sheet_name='地区汇总')
    # 2. 按产品汇总
    pivot2 = pd.pivot_table(df, values='销售额', index='产品',
                            aggfunc='sum', margins=True, margins_name='合计')
    pivot2.to_excel(writer, sheet_name='产品汇总')
    # 3. 地区 × 产品 交叉透视
    pivot3 = pd.pivot_table(df, values='销售额', index='地区', columns='产品',
                            aggfunc='sum', fill_value=0, margins=True, margins_name='合计')
    pivot3.to_excel(writer, sheet_name='地区_产品交叉')

场景四：结合 xlwings 在原有 Excel 中生成透视表（需安装 Excel）

有些场景下，希望直接在一个已有的 Excel 工作簿里，遍历所有 Sheet，对其中的每个 Sheet 生成透视表，并把结果写回到同一个工作簿的新 Sheet 里。这时候 xlwings 派上了用场，它可以在本地 Excel 应用程序中打开文件，像操作 VBA 一样操控工作表。注意：这种方法需要本地安装 Excel，且运行时会打开 Excel 后台。

import pandas as pd
import xlwings as xw
def batch_pivot_in_workbook(input_xlsx, index_col, value_col, columns_col=None, aggfunc='sum'):
    """遍历Excel中的所有工作表，生成透视表并写入新sheet"""
    app = xw.App(visible=False)
    wb = app.books.open(input_xlsx)
    summary_sheet = wb.sheets.add('透视汇总')
    row_offset = 0
    for sheet in wb.sheets:
        if sheet.name == '透视汇总':
            continue
        # 读取数据
        data_range = sheet.range('A1').expand('table')
        df = data_range.options(pd.DataFrame, header=1).value
        # 清洗数值列（去除货币符号等）
        df[value_col] = df[value_col].astype(str).str.replace(r'[￥$,]', '', regex=True)
        df[value_col] = pd.to_numeric(df[value_col], errors='coerce').fillna(0)
        # 生成透视表
        pivot = pd.pivot_table(df,
                               index=index_col,
                               columns=columns_col if columns_col else None,
                               values=value_col,
                               aggfunc=aggfunc,
                               fill_value=0,
                               margins=True,
                               margins_name='总计')
        # 将透视表写入汇总sheet
        summary_sheet.range(row_offset + 1, 1).value = f'--- {sheet.name} 的透视结果 ---'
        summary_sheet.range(row_offset + 2, 1).value = pivot
        row_offset += len(pivot) + 4  # 为下一个透视表留出空行
    wb.sa ve()
    wb.close()
    app.quit()
    print(f"批量透视完成，结果已写入 '透视汇总' sheet。")
# 调用函数
batch_pivot_in_workbook('数据文件.xlsx', index_col='地区', value_col='销售额', columns_col='产品')

总结

pivot_table 是核心：index、columns、values 和 aggfunc 这四个参数与 Excel 透视表的行、列、值和计算方式一一对应。

批量处理三步走：批量处理通常遵循“读取 → 合并 → 透视”的模式。

选择合适的工具：

• 仅需计算结果，用 pandas + to_excel 即可。
• 需要在 Excel 中保留原生透视表功能，可使用 xlwings。