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

数据透视表是 excel 最常用的分析工具。pandas 的 pivot_table 一行代码生成。

一、读取与透视

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="合计"
)

二、多级透视

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

三、多个统计值

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

四、知识扩展

使用pandas处理excel数据透视表，核心思路是用 pivot_table 函数将手工拖拽的“规则”转化为可复用的代码。

准备工作：安装与导入

在开始前，请确保已安装pandas和excel读写引擎。

pip install pandas openpyxl

在python脚本中导入库：

import pandas as pd
import glob
import os

核心：pandas的 pivot_table 函数

pandas的 pivot_table 函数是生成数据透视表的核心。它与excel透视表的对应关系如下：

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

实战场景：批量处理

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

当数据分散在多个结构相同的excel文件中时，可以先将它们合并，再生成透视表。

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

如果一个excel文件包含多个结构相同sheet，每个sheet代表不同维度，可将所有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

如果需要生成多个不同维度的透视表，可将它们写入同一个excel文件的不同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原生透视表一样操作，可使用 xlwings 库。它需要本地安装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
        # 清洗数值列（去除货币符号等）[reference:16]
        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)
        # 生成透视表[reference:17]
        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[reference:18]
        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.save()
    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透视表的行、列、值和计算方式一一对应。

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

选择合适的工具：