【Tableau】ファイル名にある、(数字+文字)で構成された都道府県名からマップを作る方法。#オープンデータの接続例
【こんな人におすすめ】
カラム名が結合されているデータをEXCELで加工している人
エクセルデータの上の行にタイトルや説明文が記載されていて、行を都度削除していいる人
分割したいが、数字+文字の羅列になっているため、カスタム分割が使えない人
【この記事を読むとできるようになること】
カラム名が結合されていても、エクセルデータの上に余分な行があっても加工せずに読み込めるようになる
数字＋文字をカスタム分割できる
都道府県が掲載されているファイル名からマップが作成できる
この記事で紹介するのは「ワイルドカード」「データインタープリンター」「カスタム分割」「REGEXP_REPLACE関数」です。
はじめに
オープンデータを使ってみて感じたのが、
「可視化したいけど、そもそもデータの形がそろっていない！」でした。
下のエクセルみたいなデータがふつう。
※このエクセルデータが辛いところ
・表の上にあるタイトルや空白行などが邪魔
・表のカラム名が結合されている
・都道府県名の前に数字ついてる、そもそも文字と文字の間に空白がある(都道府県名をマップに使えない)
・移動後の住所情報はファイル名に掲載されていて、都道府県ごとにファイルが分かれている(都道府県ごとのデータを横比較できない)
ただ、こんなデータでも可視化できる方法があるんです！
このデータをもとに、２つ(「移動前の都道府県」「移動後の都道府県」)のマップで使えるディメンションを作成する方法を紹介します。
【作り方】
1.まずはデータの接続設定から
1-1.一括でデータ接続をするため「ワイルドカード」設定
データは移動後の都道府県別にファイルが分かれている状態なので、「ワイルドカード」を使用してフォルダ内のファイルをすべて接続できるように設定します。
①フォルダを新規で作成し、都道府県別のデータを入れます。
②フォルダの中から一つファイルを選び、Tableauへドラック＆ドロップします。
③テーブル(画像の「01000北海道計」)の「▼」→「ユニオンの編集」→タブ「ワイルドカード」の順にクリック
④ワークブックの「01北海道_第４表_2023年.xlsx」を「*2023年.xlsx」に書き換え、「OK」をクリック
※「*」は任意の文字という意味。
1-2.余分な行を削除する「データインタープリンター」の設定
⑤テーブル名の前にマーク(ユニオン)がつけばOKです。
ただ、このままでは表の上にあるタイトルや空白行などが邪魔で上手にデータが読み込めていません。(表もNULLばかり・・・)
余分な行を削除するために使うのが「データインタープリンター」です。
画面左側にある「データインタープリンターの使用」にチェックを付けます。
⑥余分な行が削除され、表になっていた箇所のみの読み込みに成功しました。
コードなど使わない列は非表示に設定します。
列を選択し「▼」→「非表示」の順にクリック
データの接続設定は以上です。
2.都道府県のディメンションを2つ作成
2-1.カスタム分割を使って余分な文字を排除
⑦「Path」をカスタム分割して都道府県名に設定します。
※「Path」はワイルドカードを設定したことで自動的に生成されるディメンションです。データの中身はフォルダ名とファイル名です。
例：「DATA2/01北海道_第４表_2023年.xlsx」
「Path」を選択し「▼」→「変換」→「カスタム分割」の順にクリック
⑧区切り文字の使用に「/」をいれ、分割を「最後」、「１」列に設定をしてOKをクリックします。
カスタム分割の設定解説
設定：区切り文字の使用に「/」をいれ、分割を「最後」、「１」列
意味：「１」つめの「/」で区切ったときの後(「最後」)の文字を持ってくる
そうすると、「Path - 分割済み 1」が自動で生成されます。
「Path」と並べて比較すると、「Path」から、「DATA/」が除かれたデータが作成されました。
都道府県名の後ろについている「_ (アンダーバー)」も必要ないので分割をします。
「Path - 分割済み 1」を選択し「▼」→「変換」→「カスタム分割」の順にクリック
区切り文字の使用に「_」をいれ、分割を「最初」、「１」列に設定をしてOKをクリックします。
カスタム分割の設定解説
設定：区切り文字の使用に「_」をいれ、分割を「最初」、「１」列
意味：「１」つめの「_」で区切ったときの前(「最初」)の文字を持ってくる
※参考：「_」の2つ目を設定したときは？
設定：区切り文字の使用に「_」をいれ、分割を「最初」、「2」列
意味：「2」つめの「_」で区切ったときの前(「最初」)の文字を持ってくる
→結果、２個のディメンションが生成されます。
1個目は上の設定と一緒で、2個目は2つめの「_」の前にある”第４表”が分割されます。
そうすると、「Path - 分割済み 1 - 分割済み 1」が自動で生成されます。
並べて比較すると、「Path - 分割済み 1」から、「_第４表_2023年.xlsx」が除かれたデータが作成されました。
この状態でも地理的役割を設定するとマップとしてつかえる(Tableauスゴイ)のですが、表示する時には気持ちが悪いので、都道府県名の前にある数字も分割します。
2-2.区切り文字が使用できないときは、REGEXP_REPLACEを使って余分な数字を排除
⑨新しく計算フィールド(「都道府県(移動前)」)を作成し、以下の関数を入力します。
REGEXP_REPLACE(([Path - 分割済み 1 - 分割済み 1]),"[0-9]","" )
REGEXP_REPLACE関数の解説
REGEXP_REPLACEは正規表現関数で、REGEXP_REPLACE(“ディメンション（文字列型）”,”正規表現”,”置換文字列”)を設定することで、”置換された文字列”が表示されます。
今回は
・ディメンション「[Path - 分割済み 1 - 分割済み 1]」にある、
・数字「[0-9]」を、
・空白「""」に
置き換えるという指示をだしています。
並べてみてみると、きれいに都道府県名に変わりました！
(一つ目の都道府県クリア！)
２つ目の都道府県はエクセルデータから作成します。
⑩データの中身を見直してみると、１列目の「移動後の住所地」には都道府県と市町村/区が入っていることがわかります。
都道府県以外は不要なので削除します。
実施方法は、２列名の「移動後の住所地コード」を見ると規則性があることがわかるので、こちらを使ってフィルターを実施します。
※規則性
・末尾３桁「０００」：都道府県名
・末尾２桁「００」：市町村名
・末尾１桁数字(1~9)：区名
画面右上にある「追加」→ポップアップに出てくる「追加」→「移動後の住所地コード」→「OK」を順にクリック
フィルターの「ワイルドカード」で「000」を入力し、後方一致を選択。
(=末尾3桁「000」のみをフィルター)
そうすると、1列目「移動後の住所地」が都道府県のみになりました。
2-3.数字だけではなく「余白」もREGEXP_REPLACEを使えば排除可能
⑪ディメンション「移動後の住所地」を使って都道府県名を作成していきます。
手順⑨と同じく、新しく計算フィールド(「都道府県(移動後)」)を作成し、REGEXP_REPLACEの関数を使います。
REGEXP_REPLACE((REGEXP_REPLACE(([移動後の住所地]),"[0-9]","" ))," ","" )
※REGEXP_REPLACEの解説
上の式は、以下の2つが組み合わさってます。
・あ：(REGEXP_REPLACE(([移動後の住所地]),"[0-9]","" )：都道府県の前の数字を空白に置き換え
・い：REGEXP_REPLACE((あ)," ","" )：「あ」で作成した都道府県名の間にあるスペース(" ")を空白("")に置き換え
という2つの指示を一つの計算式で表しています。
分割前と並べてみると、こちらも都道府県名のみに並びました。
(2つめもクリア！)
おまけ：地図(マップ)の設定方法
⑫最後にマップを作っていきます。
先ほど作成した「都道府県(移動前)」「都道府県(移動後)」のディメンションに地理的役割を与えます。
「都道府県(移動後)」を選択し「ABC」→「地理的役割」→「都道府県」の順にクリック
⑬「ABC」が「地球儀マーク」になればOK。
「都道府県(移動後)」を新しいシートにドラック＆ドロップすると、日本地図が完成しました！
手順は以上です。
2つのマップ情報があると、2つの地点をつないだダッシュボードも作成することができます。
作り方の解説は別の記事で書こうと思います。
https://public.tableau.com/shared/P5N6R96KP?:display_count=n&:origin=viz_share_link
ここまで読んでくださった方、ありがとうございました！