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|
+-------+---+----+複製程式碼

相關文章