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

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

1、製作資料透視表

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

圖1 資料透視表的結構

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

圖2 Excel製作資料透視表

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所示。這個表格計算的是銷售額的平均值。

圖3 商品銷售資料透視表

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


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

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

圖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所示。

圖5 資料透視表彙總計算

引數index和values都可以是列表型別,例如:


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

統計結果如圖6所示。

圖6 統計結果

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


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

統計結果如圖7所示。

圖7 統計結果

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

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

統計結果如圖8所示。

圖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所示。

圖9 輸出變數pt

下面給出幾個篩選資料的例子,這些例子的結果都可以透過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所示。

圖10 獲取品牌A、B、C的彙總資料

(3)僅保留商品洗衣機的彙總資料。

pt.loc['洗衣機']

結果如下所示。

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

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

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

結果如圖11所示。

图11 仅保留结果的某些行

(5)仅保留汇总数据某些行和列。

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

輸出結果如圖12所示。

圖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所示。

圖13 按彙總列升序排列

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

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

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

圖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所示。

圖15 資料透視表分組統計

程式碼中最關鍵的部分就是用loc屬性讀取資料透視表的行資料並進行相加運算得出分組統計結果。

文章來源於:可以叫我才哥

文章連結:https://mp.weixin.qq.com/s/2Sfxg1A92ryce6tenmul_Q

※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

我是「數據分析那些事」。常年分享數據分析乾貨,不定期分享好用的職場技能工具。各位也可以關注我的Facebook,按讚我的臉書並私訊「10」,送你十週入門數據分析電子書唷!期待你與我互動起來~

文章推薦

餅圖變形記,肝了3000字,收藏就是學會!

MySQL必須掌握4種語言!

太實用了!4種方法教你輕鬆製作互動式儀表板!

跟資料打交道的人都得會的這8種資料模型,滿足工作中95%的需求

妙呀!一行Python程式碼

--

--

這是一個專注於數據分析職場的內容部落格,聚焦一批數據分析愛好者,在這裡,我會分享數據分析相關知識點推送、(工具/書籍)等推薦、職場心得、熱點資訊剖析以及資源大盤點,希望同樣熱愛數據的我們一同進步! 臉書會有更多互動喔:https://www.facebook.com/shujvfenxi/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
數據分析那些事

這是一個專注於數據分析職場的內容部落格,聚焦一批數據分析愛好者,在這裡,我會分享數據分析相關知識點推送、(工具/書籍)等推薦、職場心得、熱點資訊剖析以及資源大盤點,希望同樣熱愛數據的我們一同進步! 臉書會有更多互動喔:https://www.facebook.com/shujvfenxi/