第一時間獲取價值內(nèi)容

數(shù)據(jù)透視表是數(shù)據(jù)分析工作中經(jīng)常會用到的一種工具。Excel本身具有強(qiáng)大的透視表功能，Python中pandas也有透視表的實現(xiàn)。本文使用兩個工具對同一數(shù)據(jù)源進(jìn)行相同的處理，旨在通過對比的方式，幫助讀者加深對數(shù)據(jù)透視表的理解。

數(shù)據(jù)源簡介：

本文數(shù)據(jù)源來自網(wǎng)絡(luò)，很多介紹pandas的文章都使用了該數(shù)據(jù)。這是一份銷售數(shù)據(jù)，數(shù)據(jù)樣例如下：

在分析之前，需要確保你安裝了pandas(最好使用jupyter)和Excel(2016版)。接下來每一個環(huán)節(jié)，我們都將使用二者實現(xiàn)同樣的效果。Python代碼的部分，我都做了詳細(xì)的注釋，Excel操作流程我也做了比較詳細(xì)的說明。后臺回復(fù)“透視表”可以獲得數(shù)據(jù)和代碼。

處理過程

目標(biāo)1：讀取數(shù)據(jù)，查看數(shù)據(jù)樣例

1.pandas實現(xiàn)

運(yùn)行效果如下：

2.excel實現(xiàn)

直接打開文件即可查看數(shù)據(jù)。

目標(biāo)2：使用行索引，查看每一個Name的Quality，price匯總數(shù)據(jù)

1.pandas實現(xiàn)

pd.pivot_table(df, index=['Name'])

運(yùn)行結(jié)果：

當(dāng)我們只指定index時，就是指定了行標(biāo)簽，pivot_table函數(shù)會默認(rèn)按照平均值，匯總所有的數(shù)值字段。由于Account字段被pandas“理解”成了數(shù)值類型的(可以通過df.dtypes查看)，所以結(jié)果中出現(xiàn)了Account列。上面的結(jié)果表示每個Name的Account，Price，Quantity的平均值。

2.Excel實現(xiàn)

選中數(shù)據(jù)區(qū)域，插入，數(shù)據(jù)透視表，將Name字段拉倒“行”區(qū)域，Account，Price，Quantity拉入“值”區(qū)域，并將三者的字段匯總方式設(shè)置為平均值。整個步驟的流程及運(yùn)行結(jié)果如下圖所示：

可以看到Excel默認(rèn)會有一個匯總行。以Quantity為例，它的“總計”值是所有的Quantity求和之后，除以Name的個數(shù)。如果想用Pandas實現(xiàn)這種效果，可以加入margins=True參數(shù)，效果如下，出現(xiàn)了All行，由于Account和Price是整數(shù)，所以all行也是整數(shù)，Quantity是小數(shù)，相應(yīng)的All行也是小數(shù)。Excel的總計行也可以在“設(shè)計”選項卡，“總計”，“對行和列禁用去掉”。

目標(biāo)3：使用多個行索引，查看每個Manager的每個Rep對應(yīng)的Account，Price，Quantity匯總值

1.pandas實現(xiàn)

運(yùn)行效果如下：

2.Excel實現(xiàn)

在前面基礎(chǔ)上，將Manager，Rep拉到“行”的位置即可。效果如下圖，可以看到，在關(guān)鍵的數(shù)值上，兩個結(jié)果是一致的，只是在形式上有所不同。

為了在形式上更接近pandas的結(jié)果，可以設(shè)置透視表的布局。選擇“設(shè)計”選項卡，報表布局，選擇“大綱形式顯示”即可，效果如上圖所示。

仔細(xì)觀察，發(fā)現(xiàn)excel里對每一個Manager都做了匯總。這個可以通過“設(shè)計”選項卡，“分類匯總”，“不顯示分類匯總”去掉。pandas如何實現(xiàn)分類匯總，這個暫時還沒有找到相關(guān)資料。

目標(biāo)4：設(shè)置我們關(guān)心的匯總字段，此處設(shè)置price，去掉Account和Quantity

1.pandas實現(xiàn)

pd.pivot_table(df, index=['Manager', 'Rep'], values=['price], margins=True)

結(jié)果如下圖左側(cè)所示：

2.Excel實現(xiàn)

Excel中只需要在上面的基礎(chǔ)上，在“值”的地方刪掉Account，Quality即可。效果如上圖右側(cè)圖所示。

目標(biāo)5：實現(xiàn)對Price的求和

1.Pandas實現(xiàn)

效果如下圖左圖所示：

2.Excel實現(xiàn)

需要在上一步的基礎(chǔ)上，將Price的值字段設(shè)置改成求和即可，如上圖右圖中圖所示。結(jié)果如上圖中間所示。

注：Pandas可以同時對一個字段進(jìn)行多種匯總操作，(Excel貌似不行)

pd.pivot_table(df, index=['Manager', 'Rep'], values=['Price'], aggfunc=[np.sum, np.size] margins=True)#np.size可以換成len，效果一致

目標(biāo)6: 使用列索引，查看不同產(chǎn)品的數(shù)據(jù)情況

1、pandas實現(xiàn)

結(jié)果如下圖所示：

2.Excel實現(xiàn)

在上面的基礎(chǔ)上，將Product拉到“列”的位置即可。

可以看到，有些位置沒有對應(yīng)的值，Pandas默認(rèn)用NaN填充，Excel則采用置空處理。Pandas可以增加fill_value參數(shù)設(shè)置為0。(Excel貌似不可以設(shè)置）

pd.pivot_table(df, index=['Manager', 'Rep'], columns=['Product'], values=['Price'], aggfunc=np.sum, margins=True, fill_value=0)

目標(biāo)7：使用行索引和列索引，同時查看多個字段(Price,Quality)的匯總值

1.Pandas實現(xiàn)

2.Excel實現(xiàn)

在上面的基礎(chǔ)上，只需在“值”的位置加入Quantity，并將值字段設(shè)置為“求和”即可。值得一提的是，可以通過“列”的位置，“數(shù)值”和“Product”的上下關(guān)系，控制顯示的格式，下面顯示的結(jié)果和pandas的結(jié)果一致，讀者可以調(diào)整下看看效果。

目標(biāo)8：行列索引的轉(zhuǎn)換，把Product由列索引改為行索引

1.pandas實現(xiàn)

pd.pivot_table(df, index=['Manager', 'Rep', 'Product'], values=['Price', 'Quantity'], aggfunc=np.sum, margins=True, fill_value=0)

2.Excel實現(xiàn)

在上一步的基礎(chǔ)上，將Product從“列”位置拖到“行”位置即可。

目標(biāo)9：對Price和Quantity使用不同的匯總方式

1.pandas實現(xiàn)

通過字典的方式，為不同的字段傳入不同的聚合函數(shù)。

2.Excel 實現(xiàn)

只需在目標(biāo)7的基礎(chǔ)上，將Price和Quantity的值字段設(shè)置成相應(yīng)的聚合方式即可。如下圖所示。

注：同一個字段可以用列表方式傳多個函數(shù)。

pd.pivot_table(df, index=['Manager', 'Rep'], columns=['Product'], values=['Quantity', 'Price'], aggfunc={'Quantity':np.size, 'Price': [np.sum, np.mean] }, fill_value=0)

目標(biāo)10：實現(xiàn)透視表篩選功能，只查看Debra Henley的數(shù)據(jù)

1.pandas實現(xiàn)

使用query傳入篩選的參數(shù)即可，列表里可以傳入多個參數(shù)，如 table.query('Rep == ['Craig Booker', 'John Smith']')

2.excel實現(xiàn)

做好的數(shù)據(jù)透視表，具有行和列的篩選功能。我們在9的基礎(chǔ)上，對manager進(jìn)行篩選，保留Debra Henley即可。效果如下所示：

也可以將manager字段拉到“篩選器”的位置來實現(xiàn)，再選擇Manager的值為Debra Henley即可。

小結(jié)與備忘：

index-對應(yīng)透視表的“行”，columns對應(yīng)透視表的列，values對應(yīng)透視表的‘值’，aggfunc對應(yīng)值的匯總方式。用圖形表示如下：