Tableau Prep Builder であれこれデータ整形備忘録

2023年10月31日 21:58

とあるデータ準備で、あれこれやったときの備忘録です。
テスト用にダミーデータを作成する（正確性は問わないが、それっぽい感じに仕上げたい）、という目的で。

Tableau Prep Builderは、GUI操作で様々なデータの加工・整形が行なえます。データの中身や、変更内容を、視覚的にわかりやすく確認できます。
今回は計算フィールドに式を書く形での加工になりましたが、データの中身や、変更内容がわかりやすく確認できるメリットを、大いに享受しています。

使用したデータ

こんな感じの、温泉にまつわるデータ。
「泉温」「湧出量」「泉質」を、整えて使いたい。

正規表現を利用し、数値を取り出す

泉温や湧出量は、数値で欲しいのですが、図2のように、ズバリ数字だけのものもあれば、「温度44.9度」とか「毎分144リットル」とか、日本語混じりのものがあります。

そんなときは正規表現。今回使用したのは、

REGEXP_EXTRACT(string, pattern)
- patternにマッチした文字列を抽出
REGEXP_REPLACE(string, pattern, replacement)
- patternにマッチした文字列をreplacementで置き換える

泉温

#泉温
REGEXP_EXTRACT([泉温],'(\d+(\.\d+)?)')

\d：半角数字1文字
+：直前のパターンの1回以上繰り返し
- 「\d+」で、数字が1文字以上
\.：ピリオド . のこと（\は、エスケープ文字）
- 「\.\d+」で、ピリオド＋数字が1文字以上　（小数点以下を示す）
?：直前の文字または ()で囲まれたものが0個または1個にマッチ
- 「(\.\d+)?」で、小数点以下は無い場合も有る場合もマッチ

湧出量

湧出量も同じようにやろうとしたら、「毎分36,000リットル」のように、桁区切り文字が邪魔をする…。

#湧出量
REGEXP_EXTRACT(REGEXP_REPLACE([湧出量],'\,',''),'(\d+(\.\d+)?)')

今回は、先に、REGEXP_REPLACE([湧出量],','\,'') で、桁区切り文字のカンマ, を削除してから、泉温と同じ要領で抽出しました。（ピリオドまたはカンマ（\. | \, ）、のように、正規表現を工夫する、等、他にもやり方はあるかと思います）

CONTAINS() で文字列を取り出す

泉質

次に、泉質について。（ここは普通のIF文なので特筆するほどでもないですが…）

値は「塩化物質」や「旧泉質分類では含硝酸・・・」のように、単語だったり、文章の記述など、まちまちです。
日本温泉協会によると、泉質の名前は改定があり、新しい分類（掲示用新泉質名）では、全部で10種類。

文字列を見ながら、CONTAINS() で、マッチさせていく。なるべく特徴的なキーワードをIF文の上の方にもってくるように。
※温泉の定義上、正しくない可能性がありますが、ここではダミーデータなので正確性は問いません。

#泉質
IF CONTAINS([泉質], "よう素")  THEN "08含よう素泉"
ELSEIF CONTAINS([泉質], "ヨウ素")  THEN "08含よう素泉"
...
ELSEIF CONTAINS([泉質], "酸性")  THEN "07単純酸性泉"
ELSEIF CONTAINS([泉質], "アルカリ性")  THEN "01単純温泉"
ELSE [泉質]
END

CONTAINS()で抽出して名付けたものは頭に数字を付け、IF 〜 ELSEIFでマッチしなかったものは、最後のELSEで、泉質の値をそのまま返すので、変換漏れがないかどうかの確認に利用。最終的には頭の数字は削除します。
Tableau Prep Builderならどんな値が入っているか見ながら確認できるので、計算フィールド内の式が書きやすいですね。

重複排除（PARTITION BY利用）

元データを収集した際の都合で、同じ温泉(onsen フィールド)で、他のフィールドの値が若干異なるデータが、複数行入っています。

どれか1行だけ残したかったので、今回は address 列の長さが一番長いレコードだけを残すようにしました。

address列の長さ というフィールドを用意し、

partition by 時に onsen フィールドでpartition化し、
各partition内は、address列の長さ フィールドで order by desc してから行番号を付与(row_number())

#address列の長さ
len([address])

#partitionby
{ PARTITION [onsen]:
{ORDERBY [address列の長さ] desc : ROW_NUMBER()}
}

最後に、フィルターで partitionby 列 = 1 (つまりrow_number()=1) のものだけ残すようにすればOKです。
（クリーニングステップを細かく3つに分けることが必須かどうかは未確認ですが、とりあえず分けました）

行番号利用＋ NULL値埋め

ここで使う行番号は、Excelファイルまたはテキストファイルを読み込んだときに、自動的に付与することが可能な、一意な行番号です。これはTableau 2023.1 で登場した新機能です。
（上で使った重複排除で利用した、各parition内の行番号ではなくて）

何らか新しいフィールドを設けて、値を任意の割合で割り振りたい。
そんな場合は、連番に対する割り算の余りをよく使いますよね。
[Source Row Number] に連番が入っているとして、
例えば　オススメ度フィールドを新たに設けて、★1つ、★2つ、★3つの3種類の値を取る。それぞれ 1:2:1の割合で用意したいなら、

IF [Source Row Number] % 4 = 1 THEN "★"
ELSEIF [Source Row Number] % 4 = 2 THEN "★★★"
ELSE "★★"  // 4で割った余りが3のときと、0のとき
END

みたいな。

今回のケースでは、既存のフィールド泉質がNULLの場合に、2種類の値で埋める、ということをしました。

IF ISNULL([泉質]) 
THEN
	IF [Source Row Number] % 2 = 1 THEN "単純温泉"
    ELSEIF [Source Row Number] % 2 = 0 THEN "塩化物泉"
    END
ELSE [泉質]
END

割り算の余りを使う、という方法はわりと一般的かと思いますが、Tableau 2023.1 で登場した新機能「データソースの行番号を取得する」について書きたかったのでこれを書きました。

参考リンク

REGEXP_EXTRACT(string, pattern)などの正規表現

Tableau Prep - ランクまたは行番号を計算する

Tableau Prep Builder で あれこれデータ整形 備忘録