Python?pandas批量處理Excel數(shù)據(jù)透視表

更新時(shí)間：2026年07月29日 08:51:31 作者：張老師技術(shù)棧

數(shù)據(jù)透視表是?Excel?最常用的分析工具,本文會(huì)使用pandas的pivot_table函數(shù),一行代碼生成透視表并且支持多級透視和多個(gè)統(tǒng)計(jì)值,讓你的數(shù)據(jù)分析效率翻倍

一、讀取與透視

import pandas as pd

df = pd.read_excel("銷售數(shù)據(jù).xlsx")

pivot = pd.pivot_table(
    df,
    values="銷售額",
    index="城市",
    columns="品類",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="合計(jì)"
)

二、多級透視

pivot = pd.pivot_table(
    df,
    values="銷售額",
    index=["城市", "銷售員"],
    columns="季度",
    aggfunc=["sum", "mean"]
)

三、多個(gè)統(tǒng)計(jì)值

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

四、知識(shí)擴(kuò)展

使用Pandas處理Excel數(shù)據(jù)透視表，核心思路是用 pivot_table 函數(shù)將手工拖拽的“規(guī)則”轉(zhuǎn)化為可復(fù)用的代碼。

準(zhǔn)備工作：安裝與導(dǎo)入

在開始前，請確保已安裝Pandas和Excel讀寫引擎。

pip install pandas openpyxl

在Python腳本中導(dǎo)入庫：

import pandas as pd
import glob
import os

核心：pandas的 pivot_table 函數(shù)

Pandas的 pivot_table 函數(shù)是生成數(shù)據(jù)透視表的核心。它與Excel透視表的對應(yīng)關(guān)系如下：

Excel 透視表區(qū)域	pandas pivot_table 參數(shù)	說明
行	`index`	作為行標(biāo)簽的列，可以是單列或多列。
列	`columns`	作為列標(biāo)簽的列。
值	`values`	需要進(jìn)行聚合計(jì)算的數(shù)值列。
計(jì)算方式	`aggfunc`	聚合函數(shù)，如 `'sum'`(求和), `'mean'`(平均), `'count'`(計(jì)數(shù))。
總計(jì)	`margins`	`True` 或 `False`，是否顯示總計(jì)行/列。
總計(jì)名稱	`margins_name`	自定義總計(jì)行/列的標(biāo)簽，默認(rèn)為 `'All'`。
空值填充	`fill_value`	將透視表中的空值替換為指定值。

實(shí)戰(zhàn)場景：批量處理

場景一：合并多個(gè)Excel文件后生成透視表

當(dāng)數(shù)據(jù)分散在多個(gè)結(jié)構(gòu)相同的Excel文件中時(shí)，可以先將它們合并，再生成透視表。

import pandas as pd
import glob
# 1. 獲取所有Excel文件路徑
file_paths = glob.glob('銷售數(shù)據(jù)_*.xlsx')  # 假設(shè)所有文件都以"銷售數(shù)據(jù)_"開頭
# 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. 生成數(shù)據(jù)透視表
pivot_table = pd.pivot_table(all_data,
                             values='銷售額',
                             index='地區(qū)',
                             columns='產(chǎn)品',
                             aggfunc='sum',
                             margins=True,
                             margins_name='總計(jì)',
                             fill_value=0)
# 4. 導(dǎo)出結(jié)果
pivot_table.to_excel('匯總透視表.xlsx')
print("透視表已生成！")

場景二：處理一個(gè)Excel文件中的多個(gè)Sheet

如果一個(gè)Excel文件包含多個(gè)結(jié)構(gòu)相同Sheet，每個(gè)Sheet代表不同維度，可將所有Sheet合并后再處理。

import pandas as pd
# 1. 讀取所有Sheet，sheet_name=None會(huì)返回一個(gè)字典
all_sheets = pd.read_excel('全年銷售數(shù)據(jù).xlsx', sheet_name=None)
# 2. 合并所有Sheet，并添加來源Sheet名作為新列
all_data = pd.DataFrame()
for sheet_name, df in all_sheets.items():
    df['月份'] = sheet_name  # 新增一列標(biāo)記數(shù)據(jù)來源
    all_data = pd.concat([all_data, df], ignore_index=True)
# 3. 生成透視表（按月份和地區(qū)匯總）
pivot_table = pd.pivot_table(all_data,
                             values='銷售額',
                             index=['月份', '地區(qū)'],
                             columns='產(chǎn)品',
                             aggfunc='sum',
                             fill_value=0,
                             margins=True,
                             margins_name='總計(jì)')
# 4. 導(dǎo)出結(jié)果
pivot_table.to_excel('月度銷售透視表.xlsx')

場景三：批量生成多個(gè)透視表并導(dǎo)出到一個(gè)Excel

如果需要生成多個(gè)不同維度的透視表，可將它們寫入同一個(gè)Excel文件的不同Sheet中，方便對比。

import pandas as pd
df = pd.read_excel('源數(shù)據(jù).xlsx')
with pd.ExcelWriter('多維度透視報(bào)告.xlsx', engine='openpyxl') as writer:
    # 1. 按地區(qū)匯總
    pivot1 = pd.pivot_table(df, values='銷售額', index='地區(qū)',
                            aggfunc='sum', margins=True, margins_name='合計(jì)')
    pivot1.to_excel(writer, sheet_name='地區(qū)匯總')
    # 2. 按產(chǎn)品匯總
    pivot2 = pd.pivot_table(df, values='銷售額', index='產(chǎn)品',
                            aggfunc='sum', margins=True, margins_name='合計(jì)')
    pivot2.to_excel(writer, sheet_name='產(chǎn)品匯總')
    # 3. 地區(qū) × 產(chǎn)品 交叉透視
    pivot3 = pd.pivot_table(df, values='銷售額', index='地區(qū)', columns='產(chǎn)品',
                            aggfunc='sum', fill_value=0, margins=True, margins_name='合計(jì)')
    pivot3.to_excel(writer, sheet_name='地區(qū)_產(chǎn)品交叉')

場景四：結(jié)合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
        # 讀取數(shù)據(jù)
        data_range = sheet.range('A1').expand('table')
        df = data_range.options(pd.DataFrame, header=1).value
        # 清洗數(shù)值列（去除貨幣符號等）[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='總計(jì)')
        # 將透視表寫入?yún)R總sheet[reference:18]
        summary_sheet.range(row_offset + 1, 1).value = f'--- {sheet.name} 的透視結(jié)果 ---'
        summary_sheet.range(row_offset + 2, 1).value = pivot
        row_offset += len(pivot) + 4  # 為下一個(gè)透視表留出空行
    wb.save()
    wb.close()
    app.quit()
    print(f"批量透視完成，結(jié)果已寫入 '透視匯總' sheet。")
# 調(diào)用函數(shù)
batch_pivot_in_workbook('數(shù)據(jù)文件.xlsx', index_col='地區(qū)', value_col='銷售額', columns_col='產(chǎn)品')

總結(jié)

pivot_table 是核心：index、columns、values和aggfunc這四個(gè)參數(shù)與Excel透視表的行、列、值和計(jì)算方式一一對應(yīng)。

批量處理三步走：批量處理通常遵循“讀取 → 合并 → 透視”的模式。

選擇合適的工具：

僅需計(jì)算結(jié)果，用 pandas + to_excel 即可。
需要在Excel中保留原生透視表功能，可使用 xlwings。

到此這篇關(guān)于Python pandas批量處理Excel數(shù)據(jù)透視表的文章就介紹到這了,更多相關(guān)Python處理Excel數(shù)據(jù)透視表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: