クーポンが使えるお店はどこだ？ データアナリストへの道＃５
デジテック for YAMAGUCHI 運営事務局 兼 Ｙ－ＢＡＳＥスタッフのハラマルです。昨日、18時に何か発表があるとの事前予告がありましたので、ドキドキしていましたよね？あ、レノファ山口のことです。内容は、チーム史上初！夏限定ユニフォームの発表でした。これまでにない、ブラック×ゴールドという色使いや、選手ごとに声の波形をデザイン（つまり、少しずつデザインが異なる）というあたり、欲しくなるような工夫がされています。ぜひ、チェックしてみてください。
現状はPDF
さて、先日、「旅々やまぐち割」を利用する機会がありました。宿泊代金が割引になるほか、県内の登録店舗での買い物に使えるクーポンをいただきました。このクーポンを使ってみようと思ったのですが、利用できる施設はこちらです！
・・・えっ？エリア別のリスト・・・？
すごーく嫌な予感がします。Ｙ－ＢＡＳＥ（山口市）の近くだと、湯田温泉の周辺に利用可能店舗があるのかな？ということで、試しに「山口市」をクリックしてみます。
嫌な予感が的中です。
市町別にリストになってＰＤＦで提供されています。山口市では６ページもあります。
いや、クーポンが利用可能なお店がたくさんあることは大変いいことですが、これ、探すの大変ですよ！検索機能があるから大丈夫、と思われているのかもしれませんが、お店の名前から検索するならそうでしょうが、そうでない探し方する人は、全部目を通さないといけない設定になってませんか？
掲載の順序にもルールがないようなので、申し込み順とかなんでしょうか？
しかも、私はこの時点でスマホで操作していましたので、一回ＰＤＦをダウンロードしないといけないし、文字が小さいし、です。う～ん、まいった。
これ、ちょっと、どうにかならないの？と思いましたが、データアナリストを目指している身としては、自分でやれるのでは？と思い立ちました。
そりゃあ、お店を検索するんだったら、リストじゃなくて、マッピングでしょ！と。普段は、あまり役に立たないマッピングばかりやっていますが、今回は、きっと、いいマッピングができそうな予感がします！
PDFからデータへ
さて、目標はTableauを使ったマッピングに定めましたが、現状、店舗リストはＰＤＦです。これをデータに変換する必要がありますね。
「Adobe Acrobat Export PDF」というツールがあれば、ＰＤＦからWordやExcelに変換できるようですが、生憎、そんな便利なツールを持ち合わせていません。地道な作業で挑戦することにします。
まず、PDF上の文字を選択してコピーしてみます。
次に、新しいExcelファイルを開き、貼り付けてみます。
おっ、うまく貼り付けできたみたいですね。
あっ、いや、これは、致命的な欠陥がありますね。PDFでは表形式だったのに、Excelでは、全部、A列にだけ入力されています。列の分かれ目が認識できてないということなんでしょうか。
そうですか。う～んと、じゃあ、これを、関数を使って、表形式に戻すしかありませんね。（最初からExcelで提供してくれていたら、こんな作業しなくて済むのに・・・）
では、どうやって表形式に戻すか、考えてみます。
A列に文字列として全部の情報が入っているので、ここから、関数を使って、店舗名や住所などを抽出していくことにします。
思いつくのは、文字列の左から〇文字を抽出する「LEFT関数」でしょうか。少しずつ文字を抽出していけば、うまくいきそうな気がします。
では、作業に着手します。
今回の作業、文字数が重要になると思うので、まず、B列に、A列に貼り付けた文字の文字数を数えるため、「LEN関数」を入力しておきます。
次に、A列から「No」を抜き出す作業をします。C列に、「LEFT関数」でA列から文字を抽出してきたいのですが、Noの桁によって文字数が異なってくるので、文字数を固定数字で指定することができません。
ここで目を付けたのが、A列をよく見てみると、Noの後、店舗名の前に半角スペースがあるようです。なので、半角スペースまでの文字を抽出することにします。「FIND関数」を使えばうまくできそうな気がします。
ということで、セルC2には、２つの関数を組み合わせて「=LEFT(A2,FIND(" ",A2)-1)」と入力します。A２の文字列の、左から「半角スペースの一つ前まで」の文字を抽出する、ということですね。
うまくいったようです。C列にはNoだけが抽出されました。
さて、次の作業は、A列から店舗名だけを抽出します。
関数を組み合わせていくと、自分でも何をやっているのか分からなくなるので、一旦、「A列からNoを抜いた文字列」を作り、そこから、さっきと同じ作業をすることにします。
「No」の文字数をカウントする作業列をD列に作ります。「LEN」関数を使いました。
E列に必要なのは、全体の文字数（B列）から「Noで使用している文字数」（D列）を除いた文字数です。
今回は、A列の右から抽出するので、「LEFT関数」の代わりに「RIGHT関数」を使います。
おっ、Noと店舗名の間にスペースがあったので、もう１文字削除しないといけませんでしたね。
結局、E2に「=RIGHT(A2,B2-D2-1)」と入力すると、うまくいきました。
C列・D列・E列で、A列から「No」を抽出して、抽出した後の文字列を作成しました。
これを繰り返していって、A列にまとめて入力されている文字列から、「No」、「店舗名」、「郵便番号」、「住所」、「電話番号」を抽出していく作戦です。
おおよそうまくいったのですが、Noの時のように、必ずしも間にスペースがないところもありましたので、そこが苦労した点です。
店舗名と郵便番号を区切るときは、山口市の郵便番号が「7」から始まるので、スペースの代わりに「7」を使ったり、郵便番号や電話番号は桁数が決まっているのでそれを条件にしたり、です。
「LEFT関数」と「RIGHT関数」を使って、うまく表形式に整理し直すことができました。
座標データの取得
ここまでは、単にPDFからExcelに変換しただけに過ぎません。（だいぶ苦労していますが）。
マッピングに必要な緯度経度の座標情報が欠けているので、そのデータを作成する必要があります。
以前（下の記事）ではGoogleマップで調べたのですが、山口市のリストは384件もあります。１件ずつ調べるのは大変なので、一括変換ツールを使ってみたいと思います。
今度は、国土地理院が提供している「地理院マップシート」というものを使ってみることにします。こちらから入手できます。
仕組みとしては、Excelの「地理院マップシート」に住所情報を貼り付け、「住所→座標値」を押すと、マクロが動いて緯度経度が自動出力されるということです。オンライン接続の環境が必要なようです。
試しに、山口市の384件をまとめて実行してみたところ、５件だけエラーが発生しましたが、その他は座標データを取得することができました。
エラーが発生した５件だけは、Googleマップから座標データを取得しました。リストに書かれていた住所・電話番号と合っていたので、実在の場所の座標データと思います！
最後に、ぐちゃぐちゃしたExcelから、必要なデータだけをコピーして新しいExcelファイルにしました。これが、PDFからExcelに変換し、さらに緯度経度データを追加した状態です。
マッピング
さて、面倒なデータ処理が終わったので、後はサクッとTableauでやってしまいましょう！
いつものように、「緯度」「経度」のデータに地理的役割を与えると、はい、もう出来上がりです！データ処理に比べると、あっけなさすぎます。
今回はちょっと範囲が広いので、まずは、どの辺にクーポン利用可能店舗が多いか、「ヒートマップ」で表示してみます。こんなカンジですね。
次に、位置情報がどのくらい正確にプロットされているのか確かめてみます。湯田温泉周辺にズームしていくと、このようなカンジになりました。
パッと見た感じ、結構、正確なような気がします。道路のどっち側か、みたいなことも分かりますね。
Tableauで操作している場面では、カーソルを合わせると、店舗名と電話番号も表示されるので、非常に便利だと思います。
まとめ
今回、PDFで提供されている住所情報が含まれているリストから、緯度経度情報を補完して、マッピングすることに成功しました。全件について正確かどうか調べていないので、はいどうぞ、と皆さんに安易に提供するのは控えたいと思いますが、文字情報の一覧表と比べて、かなり便利になったのではないでしょうか？
関数も作成したし、緯度経度情報の一括取得ツールもあるので、PDFからのデータコピーさえしてしまえば、他の市町分も結構簡単にできる気がします。・・・えっと、やってみましょうか？じゃあ、他の市町分も追加してみます。
時間的には１時間程度でした。主にはPDFからExcelへのデータ貼り付けで、貼り付けてしまえば、あとは比較的簡単でした。
中には、郵便番号がないものがあったり、電話番号が携帯電話で桁数が違っているところがあったりで、郵便番号の加筆や関数の修正などをちょっとするなどのイレギュラー対応をすれば完成です。
作業が成功したことに満足する一方、私でもできたのだから、最初から、こういう情報提供の仕方をしてくれたらいいのになぁとも思います。
デジテックで、こうしたマッピング活動をしてみたら面白いかもしれませんね！