利用excel與Pandas完成實現資料透視表

數據分析那些事

10 min readNov 16, 2022

資料透視表是一種分類彙總資料的方法。本文章將會介紹如何用Pandas完成資料透視表的製作和常用操作。

1、製作資料透視表

製作資料透視表的時候，要確定這幾個部分：行欄位、列欄位、資料區，彙總函式。資料透視表的結構如圖1所示。

Excel製作資料透視表很簡單，選中表格資料，並點選工具欄上的“資料透視表”選單即可，如圖2所示。

Pandas裡製作資料透視表主要使用pivot_table方法。pivot_table方法的呼叫形式如下：


DataFrame.pivot(index, columns, values, aggfunc)

其實index引數對應行欄位，columns引數對應列欄位，values引數對應資料區。aggfunc的預設值是numpy.mean，也就是計算平均值。

下面結合例項講解pivot_table的用法，首先用以下程式碼匯入示例資料：


import pandas as pd
import xlwings as xw

path = "D:/chapter11/資料透視表.xlsx"
wb = xw.Book(path)
sheet = wb.sheets[0]
df = sheet.range("A1:E7").options(pd.DataFrame, index=False, header=True).value

用pivot_table方法制作資料透視表，商品作為行欄位，品牌作為列欄位，銷售額放在資料區，這樣設定：


pt1 = df.pivot_table(index='商品', columns='品牌', values='銷售額')
sheet.range("G1").options(index=True, header=True).value = pt1

結果如圖3所示。這個表格計算的是銷售額的平均值。

上面的程式碼修改一下，把數量放在資料區，設定彙總函式是sum：


pt2 = df.pivot_table(index='商品', columns='品牌', values='數量' , aggfunc='sum')
sheet.range("G8").options(index=True, header=True).value = pt2

結果如圖4所示。這個表格計算的是銷售數量的和。

可以看到這兩個資料透視表是有缺失值的，pivot_table有一個引數fill_value，就是用來填充這些缺失值的，例如：


df.pivot_table(index='商品', columns='品牌', values='數量', fill_value=0)

pivot_table方法還支援對透視表進行統計計算，而且會新建一個列來存放計算結果。這個統計需要用到以下兩個引數：

q margins，設定是否新增彙總列，一般設定為True。

q margins_name，彙總列的名稱。

示例程式碼如下：


pt3 = df.pivot_table(index='商品', columns='品牌', values='銷售額', fill_value=0, aggfunc='sum', margins=True, margins_name="彙總")
sheet.range("L1").options(index=True, header=True).value = pt3

計算結果如圖5所示。

引數index和values都可以是列表型別，例如：


pt4 = df.pivot_table(index=['品牌', '商品'], values=['銷售額', '利潤'], aggfunc='sum')
sheet.range("L8").options(index=True, header=True).value = pt4

統計結果如圖6所示。

這個資料透視表可以對利潤和銷售額進行不同的彙總計算，這時候aggfunc是字典型別，例如對銷售額計算平均值，對利潤計算總和，可以這樣：


pt5 = df.pivot_table(index=['品牌', '商品'], values=['銷售額', '利潤'],  aggfunc={
    '銷售額':'mean', '利潤':'sum'})
sheet.range("L15").options(index=True, header=True).value = pt5

統計結果如圖7所示。

對於同一個指標可以設定多個彙總函式，例如：

pt6 = df.pivot_table(index=['品牌', '商品'], values=['銷售額', '利潤'],  aggfunc={
    '銷售額':['mean', 'sum'], '利潤':['mean', 'sum']})
sheet.range("L22").options(index=True, header=True).value = pt6

統計結果如圖8所示。

2、篩選資料透視表中的資料

pivot_table的運算結果是一個DataFrame型別，所以可以用DataFrame擷取資料的方法篩選資料透視表中的資料。本節用於示例的資料透視表如下：

pt = df.pivot_table(index='商品', columns='品牌', values='銷售額', fill_value=0, aggfunc='sum', margins=True, margins_name="彙總")

在jupyter中輸出pt如圖9所示。

下面給出幾個篩選資料的例子，這些例子的結果都可以透過Range物件的options方法轉換成Excel表格資料。

（1）僅保留彙總列的資料。

pt['彙總']

結果是一個Series，如下所示。

商品
洗衣機     24000.0
電風扇     62000.0
空調      81000.0
彙總     167000.0
Name: 彙總, dtype: float64

要提取洗衣機的彙總資料，可以用以下表達式：

pt['彙總']['洗衣機']

（2）獲取品牌A、B、C的彙總資料。

pt[['A', 'B', 'C']]

結果如圖10所示。

（3）僅保留商品洗衣機的彙總資料。

pt.loc['洗衣機']

結果如下所示。

品牌
A     11000.0
B         0.0
C     13000.0
彙總    24000.0
Name: 洗衣機, dtype: float64

（4）僅保留商品洗衣機和電風扇的彙總資料。

pt.loc[['洗衣機', '電風扇']]

結果如圖11所示。

（5）仅保留汇总数据某些行和列。

pt[['A', 'B', 'C']].loc[['洗衣機', '電風扇']]

輸出結果如圖12所示。

3、使用欄位列表排列資料透視表中的資料

資料透視表是一個DataFrame，所以可以用sort_values方法來按某列排序，示例程式碼如下：

pt = df.pivot_table(index='商品', columns='品牌', values='銷售額', fill_value=0, aggfunc='sum', margins=True, margins_name="彙總")
pt.sort_values(by="彙總")

結果如圖13所示。

從結果可以看出洗衣機的總銷售額是最低的。

4、對資料透視表中的資料進行分組

在Excel中還支援對資料透視表中的資料進行分組，例如可以把風扇和空調的資料分為一組來計算，如圖14所示。

用Pandas也可以實現類似的統計，示例程式碼如下：

程式碼11–9 對資料透視表中的資料進行分組統計

import pandas as pd
import xlwings as xw

path = "D:/chapter11/資料透視表.xlsx"
wb = xw.Book(path)

pt = df.pivot_table(index='商品',  values='銷售額', fill_value=0, aggfunc='sum', 
                   margins=True, margins_name="總計")
pt.loc['分組1'] = pt.loc['電風扇'] + pt.loc['空調']
pt.loc['分組2'] = pt.loc['洗衣機']
# reindex方法重新排列表格
grouppt = pt.reindex(['分組1', '電風扇', '空調', '分組2', '洗衣機', '總計'])
sheet.range("A9").options(index=True, header=True).value = grouppt

輸出結果如圖15所示。