Azure Synapse - Data Flow で pandas の ffill や bfill 的な欠損値埋めをする方法

はじめに

Azure Data Factory や Azure Synapse Pipeline の Data Flow (GUIとノーコードでデータ加工できるツール）を使ってデータ集計や予測モデル用のデータ加工をするときのテクニックを紹介しています

Azureのサービス使ってデータ分析をする際に、予測モデルの構築や運用は Azure Machine Learning を使いますが、
モデル用のデータセットをゴリゴリデータ加工する際は、Azure Synapse Pipeline 側に処理を寄せることをお勧めしております

既存で Python でモデル用のデータセット作成コードを実装していたけど、Azure への移行を機に Azure Synapse Pipeline の Data Flow で実装再現するクライアントもたくさん支援してきました
たいていの処理は、Azure Synapse Pipeline の Data Flowでシンプルに実装できるのですが、今まで唯一全く同じ処理が再現できなかったものがあるので、その内容と代替方法を紹介します

それは、pandasにおけるffill（forward fill）や bfill（backward fill）で欠損値(null)を前後の値で埋める処理です
これに該当する関数は、Azure Data Factory や Azure Synapse Pipeline の Data Flow にはありません
もっと言うと、Data Flow の裏側は Spark で処理されていまして、pyspark（Spark SQL）のコード実装にもありません

厳密には全く同じ処理ではないですが、限りなく近い処理は実装できますので、その方法を紹介します

やりたいこと

顧客の架電オプトアウト情報を月ごとにスナップしているデータがあって、
欠損の場合は前後の月の情報で埋めたいという例で説明します

以下のロジックで欠損値埋めしたいとします
①「電話可否」が欠損値(null)でなければ、その値を採用
②「電話可否」が欠損値(null)の場合、ffill 補完値 がnullでなければ、ffill 補完値を採用
③②の補完結果が欠損値(null)の場合、 bfill 補完値 がnullでなければ、bfill 補完値を採用
④③の補完結果が欠損値(null)の場合、"制限なし"を代入する

Python のpandas の ffill（forward fill）や bfill（backward fill）だとこんな感じで実装できますね

＜データイメージ＞
意図した欠損補完が実装できるか確認するためにいくつかのパターンのテストデータで確認します

Data Flow で実装再現方法

Window モジュールと 派生列 モジュールを組み合わせて実装します

１．Window モジュールで、ffill 補完値列を作成する

① Window モジュールを繋げて、[1. Over] タブで「顧客CD」を選択
※本ケースでは、顧客毎に前後の年月の値で欠損補完を行いたいため

② [2. 並べ替え] タブで、「年月」の「降順」を設定
※ffill の再現のため、最近の年月の値で欠損補完を行いたいため

③ [3. 範囲] タブで、「無制限」にチェック
※本ケースでは、「年月」が文字型のため、列値による範囲指定はできない

④ [4. ウィンドwの列] タブで、以下関数式で欠損補完を設定する
first({電話可否}, true())
※列の最初＝年月の最降順の値が欠損でなければその値で補完するというロジック

※集計関数及び式関数は、リファレンスページを参照して引数等の使い方を確認しながら設定しましょう

２．Windowモジュールで、bfill 補完値列を作成する

もうひとつ Window モジュールを繋げて、
１と同じ要領で「年月」を「昇順」に並べて、bfillを再現する
※ [2. 並べ替え] 以外は、１と全く同じ内容を設定

３．派生列モジュールで、欠損埋めの優先順位ロジックを実装し、欠損埋め完成

① 派生列モジュールを繋げて、以下の式関数列を作成する
iif(isNull({電話可否}),
coalesce({電話可否_ffill},{電話可否_bfill},'制限なし'),
{電話可否})
※「電話可否」が欠損の場合、ffill, bfillの順で欠損でない値で補完し、それでも欠損の場合は、'制限なし'の値で補完するというロジック

結果は、このように上述のデータイメージと同じ欠損補完が再現できました！

さいごに

本ケースでは、Python でたった 3行 で処理できることが、Synapse Data Flow だと 3モジュールも使って面倒くさいと思うかもしれませんが、他の多くの処理では Data Flow で1モジュールで一気に処理できてシンプルかつ結果も確認しやすいですよ