01 數據透視表簡介

數據透視表，顧名思義，就是通過對數據執行一定的"透視"，完成對復雜數據的分析統計功能，常常伴隨降維的效果。例如在Excel工具欄數據透視表選項卡中通過懸浮鼠標可以看到這樣的描述：

在上述簡介中，有兩個關鍵詞值得注意：排列和匯總，其中匯總意味著要產生聚合統計，即groupby操作；排列則實際上隱含著使匯總后的結果有序。當然，如果說只實現這兩個需求還不能完全表達出數據透視表與常規的groupby有何區別，所以不妨首先看個例子：

給定經典的titanic數據集，我們需要統計不同性別下的生還人數，則可以進行如下設置：

而后，即可得到如下結果：

通過上表，明顯可以看出女性中約有2/3的人得以生還，而男性中則僅有不到20%的人得以生還。當然，這是數據透視表的最基本操作，大家應該也都熟悉，本文不做過多展開。

值得補充的是：實際上為了完成不同性別下的生還人數，我們完全可以使用groupby(sex, survived)這兩個字段+count實現這一需求，而數據透視表則僅僅是在此基礎上進一步完成行轉列的pivot操作而已。理解了數據透視表的這一核心功能，對于我們下面介紹數據透視表在三大工具中的適用將非常有幫助！

02 Pandas實現數據透視表

在三大工具中，Pandas實現數據透視表可能是最為簡單且又最能支持自定義操作的工具。首先，給出一個自定義的dataframe如下，僅構造name，sex，survived三個字段，示例數據如下：

基于上述數據集實現不同性別下的生還人數統計，運用pandas十分容易。這里給出Pandas中數據透視表的API介紹：

可以明顯注意到該函數的4個主要參數：

•  values：對哪一列進行匯總統計，在此需求中即為name字段；
•  index：匯總后以哪一列作為行，在此需求中即為sex字段；
•  columns：匯總后以哪一列作為列，在此需求中即為survived；
•  aggfunc：執行什么聚合函數，在此需求中即為count，該參數的默認參數為mean，但只適用于數值字段。

而后，分別傳入相應參數，得到數據透視表結果如下：

上述需求很簡單，需要注意以下兩點：

•  pandas中的pivot_table還支持其他多個參數，包括對空值的操作方式等；
•  上述數據透視表的結果中，無論是行中的兩個key（"F"和"M"）還是列中的兩個key（0和1），都是按照字典序排序的結果，這也呼應了Excel中關于數據透視表的介紹。

03 Spark實現數據透視表

Spark作為分布式的數據分析工具，其中spark.sql組件在功能上與Pandas極為相近，在某種程度上個人一直將其視為Pandas在大數據中的實現。在Spark中實現數據透視表的操作也相對容易，只是不如pandas中的自定義參數來得強大。

首先仍然給出在Spark中的構造數據：

而后，前面已分析過數據透視表的本質其實就是groupby操作+pivot，所以spark中剛好也就是運用這兩個算子協同完成數據透視表的操作，最后再配合agg完成相應的聚合統計。完整的實現數據透視表及其結果如下：

當然，注意到這里仍然是保持了數據透視表結果中行key和列key的有序。

04 SQL中實現數據透視表

這一系列的文章中，一般都是將SQL排在首位進行介紹，但本文在介紹數據透視表時有意將其在SQL中的操作放在最后，這是因為在SQL中實現數據透視表是相對最為復雜的。實際上，SQL中原生并不支持數據透視表功能，只能通過衍生操作來曲線達成需求。

上述在分析數據透視表中，將其定性為groupby操作+行轉列的pivot操作，那么在SQL中實現數據透視表就將需要groupby和行轉列兩項操作，所幸的是二者均可獨立實現，簡單組合即可。

仍然是在SQL中構造臨時數據表，如下：

而后我們采取逐步拆解的方式嘗試數據透視表的實現：

1. 利用groupby實現分組聚合統計，這一操作非常簡單：

2.對上述結果執行行轉列，實現數據透視表。這里，SQL中實現行轉列一般要配合case when，簡單的也可以直接使用if else實現。由于這里要轉的列字段只有0和1兩種取值，所以直接使用if函數即可：

上述SQL語句中，僅對sex字段進行groupby操作，而后在執行count(name)聚合統計時，由直接count聚合調整為兩個count條件聚合，即：

•  如果survived字段=0，則對name計數，否則不計數（此處設置為null，因為count計數時會忽略null值），得到的結果記為survived=0的個數；
•  如果survived字段=1，則對name計數，否則不計數，此時得到的結果記為survived=1的個數。

這樣，得到的結果就是最終要實現的數據透視表需求。

值得指出，這里通過if條件函數來對name列是否有實際取值+count計數實現聚合，實際上還可以通過if條件函數衍生1或0+sum求和聚合實現，例如：

當然，二者的結果是一樣的。

以上就是數據透視表在SQL、Pandas和Spark中的基本操作，應該講都還是比較方便的，僅僅是在SQL中需要稍加使用個小技巧。希望能對大家有所幫助，如果覺得有用不妨點個在看！