01 數(shù)據(jù)透視表簡介

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

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

給定經(jīng)典的titanic數(shù)據(jù)集，我們需要統(tǒng)計不同性別下的生還人數(shù)，則可以進行如下設(shè)置：

而后，即可得到如下結(jié)果：

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

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

02 Pandas實現(xiàn)數(shù)據(jù)透視表

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

基于上述數(shù)據(jù)集實現(xiàn)不同性別下的生還人數(shù)統(tǒng)計，運用pandas十分容易。這里給出Pandas中數(shù)據(jù)透視表的API介紹：

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

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

而后，分別傳入相應參數(shù)，得到數(shù)據(jù)透視表結(jié)果如下：

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

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

03 Spark實現(xiàn)數(shù)據(jù)透視表

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

首先仍然給出在Spark中的構(gòu)造數(shù)據(jù)：

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

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

04 SQL中實現(xiàn)數(shù)據(jù)透視表

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

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

仍然是在SQL中構(gòu)造臨時數(shù)據(jù)表，如下：

而后我們采取逐步拆解的方式嘗試數(shù)據(jù)透視表的實現(xiàn)：

1. 利用groupby實現(xiàn)分組聚合統(tǒng)計，這一操作非常簡單：

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

上述SQL語句中，僅對sex字段進行g(shù)roupby操作，而后在執(zhí)行count(name)聚合統(tǒng)計時，由直接count聚合調(diào)整為兩個count條件聚合，即：

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

這樣，得到的結(jié)果就是最終要實現(xiàn)的數(shù)據(jù)透視表需求。

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

當然，二者的結(jié)果是一樣的。

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