Spark實現行列轉換pivot和unpivot

wait4friend發表於2019-03-03
Spark

背景

做過資料清洗ETL工作的都知道，行列轉換是一個常見的資料整理需求。在不同的程式語言中有不同的實現方法，比如SQL中使用case+group，或者Power BI的M語言中用拖放元件實現。今天正好需要在pyspark中處理一個資料行列轉換，就把這個方法記錄下來。

首先明確一下啥叫行列轉換，因為這個叫法也不是很統一，有的地方叫轉置，有的地方叫透視，不一而足。我們就以下圖為例，定義如下：

  • 從左邊這種變成右邊這種，叫透視（pivot）
  • 反之叫逆透視（unpivot）

image-20180611160900344

Spark實現

構造樣本資料

首先我們構造一個以行格式儲存資料的資料集

from pyspark.sql import SparkSession
spark = SparkSession.builder.appName('JupyterPySpark').enableHiveSupport().getOrCreate()

import pyspark.sql.functions as F

# 原始資料 
df = spark.createDataFrame([('2018-01','專案1',100), ('2018-01','專案2',200), ('2018-01','專案3',300),
                            ('2018-02','專案1',1000), ('2018-02','專案2',2000), ('2018-03','專案x',999)
                           ], ['年月','專案','收入'])
複製程式碼

樣本資料如下，我們可以看到，每一個專案在指定月份都只有一行記錄，並且專案是稀疏的。即，不是每個專案都會出現在每一個月份中，如專案2僅出現在2018-01當中。

+-------+---+----+
|  年月| 專案|  收入|
+-------+---+----+
|2018-01|專案1| 100|
|2018-01|專案2| 200|
|2018-01|專案3| 300|
|2018-02|專案1|1000|
|2018-02|專案2|2000|
|2018-03|專案x| 999|
+-------+---+----+
複製程式碼

透視Pivot

透視操作簡單直接，邏輯如下

  • 按照不需要轉換的欄位分組，本例中是年月；
  • 使用pivot函式進行透視，透視過程中可以提供第二個引數來明確指定使用哪些資料項；
  • 彙總數字欄位，本例中是收入；

程式碼如下

df_pivot = df.groupBy('年月')\
                .pivot('專案', ['專案1','專案2','專案3','專案x'])\
                .agg(F.sum('收入'))\
                .fillna(0)
複製程式碼

結果如下

+-------+----+----+---+---+
| 年月| 專案1| 專案2|專案3|專案x|
+-------+----+----+---+---+
|2018-03|   0|   0|  0|999|
|2018-02|1000|2000|  0|  0|
|2018-01| 100| 200|300|  0|
+-------+----+----+---+---+
複製程式碼

逆透視Unpivot

Spark沒有提供內建函式來實現unpivot操作，不過我們可以使用Spark SQL提供的stack函式來間接實現需求。有幾點需要特別注意：

  • 使用selectExpr在Spark中執行SQL片段；
  • 如果欄位名稱有中文，要使用反引號**`** 把欄位包起來；

程式碼如下

df_pivot.selectExpr("`年月`", 
                    "stack(4, '專案1', `專案1`,'專案2', `專案2`, '專案3', `專案3`, '專案x', `專案x`) as (`專案`,`收入`)")\
            .filter("`收入` > 0 ")\
            .orderBy(["`年月`", "`專案`"])\
            .show()
複製程式碼

結果如下

+-------+---+----+
|     年月| 專案|  收入|
+-------+---+----+
|2018-01|專案1| 100|
|2018-01|專案2| 200|
|2018-01|專案3| 300|
|2018-02|專案1|1000|
|2018-02|專案2|2000|
|2018-03|專案x| 999|
+-------+---+----+複製程式碼

相關文章