個性的なcsvからデータを取り出した話

data mysql gis

コンピュータシステム周辺に関わっているとCSVファイルとのお付き合いは避けて通れないものと言えるでしょう。みなさんはどんなCSVとお付き合いしたことがありますか。
　セパレータが明確でない（というか明確なのだけど例外例外の積み重ねが意外とややこしい）のがcsvとのお付き合いで気を使うところですよね。

　単純に考えれば、コンマが出てきたら次の項目、改行により次のレコードだと思うじゃないですか。ところが、コンマが出てきても次の項目に移らなかったり、1つのレコードが複数行に亘って記述されたりすることもあります。そう、クォーテーションされている場合ですね。

　でも、この日記で紹介しようとしているのは、そんなレベルではない「個性的なCSVファイル」です。実際には「クソCSV」と呼んでいたのですが、日記に書くには品のない言葉なので、ここでは「個性的なCSV」と呼ぶことにします。　

ことの起こり

　先日参加したハッカソン、Geospatial Hackers Japan での開発で、名古屋地区の観光地情報が必要となり、教えてもらいました。ハッカソン参加に関する日記は、以下。

sakaik.hateblo.jp

　このファイルから、名古屋市内の観光地情報を抜き出して使える状態にする、というのが私の役割。
最低限、名称と緯度経度は取得したいが、状況に応じて他の項目も抜き出せるよう、「抜き出し作業」を行うことを目的とせずに、「抜き出すしくみ」を作ることにしました。　ハッカソンという限られた時間内での作業ですので、最高最適な方法であるかよりも、時間内での実現性を優先しての作業となります。

ザ・データの取得

　私を楽しませてくれることになるCSVファイルは、総務省の「公共クラウドシステム」で公開しているものです。このページは

本サイトは、2020年３月末日をもって閉鎖となりますので、ご利用にあたってはご注意願います。

　ということで、とりあえずデータを実際にみてみたいかたは、ダウンロードを急げ！

公共クラウドシステム | API公開サイト
f:id:sakaik:20200329181452p:plain

ザ・データの概要

　CSVファイルです。ざっと雰囲気だけキャプチャ画像で紹介すると、こんな感じ。

f:id:sakaik:20200329181344p:plain

　テキストでも紹介しておきます（膨大なので、間引き加工しています。画像のほうは間引きなし）。

"tourspots[0]","name","name1","written",,,,,,"夏まつり桂川２０２０"
"tourspots[0]","name","name1","spoken",,,,,,"なつまつりけいせん２０２０"
"tourspots[0]","genres[0]","L",,,,,,,"イベント"
"tourspots[0]","views[0]","copyright",,,,,,,"有"
"tourspots[0]","views[0]","fid",,,,,,,"base/40/421/40421KANKO2080556001.jpg"
"tourspots[0]","place","coordinates","longitude",,,,,,"130.6788888"
"tourspots[0]","place","coordinates","latitude",,,,,,"33.5791666"
"tourspots[0]","place","postal_code",,,,,,,"820-0696"
"tourspots[0]","place","pref","written",,,,,,"福岡県"
"tourspots[0]","place","city","written",,,,,,"嘉穂郡桂川町"
"tourspots[0]","place","street","written",,,,,,"大字土居424番地8"
"tourspots[0]","place","street","spoken",,,,,,"おおあざとい424ばんち8"
"tourspots[0]","visit","guide","note",,,,,,"8/22（土）【昨年内容】18：00王塚太鼓～
8/23（日）【昨年内容】16：50吹奏楽～17：20戦隊ヒーロー～18：05SUPER・BOWL～ 20：00閉会式"
"tourspots[0]","visit","service","periods[0]","type",,,,,"公開"
"tourspots[0]","visit","service","periods[0]","st_date",,,,,"2020年8月22日"
"tourspots[0]","visit","service","periods[0]","day_of_week",,,,,"土"
"tourspots[0]","visit","service","periods[0]","hours",,,,,"18:00~20:40"
"tourspots[0]","visit","service","periods[0]","note",,,,,"王塚太鼓演奏、盆踊り大会、お化け屋敷"
"tourspots[0]","wifi","established",,,,,,,"無し"
"tourspots[0]","mng","lgcode",,,,,,,"40421"
"tourspots[0]","mng","refbase",,,,,,,"40421KANKO2080556"
"tourspots[0]","mng","refsub",,,,,,,"2080556"
"tourspots[0]","mng","status","update",,,,,,"2019-10-23 13:46:46"
"tourspots[0]","mng","data_source",,,,,,,"福岡県嘉穂郡桂川町"
"tourspots[1]","name","name1","written",,,,,,"土師の獅子舞＜土師老松神社＞"
"tourspots[1]","name","name1","spoken",,,,,,"はじのししまい＜はじおいまつじんじゃ＞"
"tourspots[1]","genres[0]","L",,,,,,,"イベント"
"tourspots[1]","genres[0]","M",,,,,,,"祭事"
"tourspots[1]","genres[0]","S",,,,,,,"行事・祭事"
"tourspots[1]","views[0]","name","written",,,,,,"獅子舞（2014春　上土師地区当番）"
"tourspots[1]","views[0]","name","spoken",,,,,,"ししまい(2014はる　かみはじちくとうばん)"
"tourspots[1]","views[0]","where",,,,,,,"土師老松神社"
"tourspots[1]","views[0]","copyright",,,,,,,"有"
"tourspots[1]","views[0]","fid",,,,,,,"base/40/421/40421KANKO2059825001.jpg"

　

　tourspots[] ごとに1つの観光地を表しています。ナニコレCSV。

とりあえずMySQLに入れる

　いやもう、「こんなCSVがあるよ」と紹介したかっただけなので、続きを書くモチベーションもさほど高くないのだけど、折角なので、苦労話の一端だけでも書いておこうと思います。まずは、とりあえずMySQLに突っ込むことを目標にしました。 bash上で加工する手も考えましたが、「とりあえずRDBMSに突っ込めばなんとかなる」という自信というか、なんとなくSQL文は頭に浮かんでいたからです。

　そのためには、まずこの「個性的なCSV」をプレ処理する必要があります。改行が含まれていたりクォーテーション文字列内にコンマが入っているようなCSVファイルに対しては、Excelが非常に強力です。いきなりCSVファイルをダブルクリックしちゃだめですよ。「テキストファイルの読み込み」機能を使います。
　読み込ませたら、改行が含まれている項目の改行コードを除去して、INSERT文を作ります。この作業はさくっと使い捨てVBAマクロを書いて実施。

Sub MakeIns()
  Dim o As Worksheet
  Set o = ActiveWorkbook.Worksheets(2)
  Set ws = ActiveWorkbook.Worksheets(1)
  For i = 2 To 553444
    s = "INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ("
    s = s + "'" + ws.Cells(i, 1) + "',"
    s = s + "'" + ws.Cells(i, 2) + "',"
    s = s + "'" + ws.Cells(i, 3) + "',"
    s = s + "'" + ws.Cells(i, 4) + "',"
    s = s + "'" + ws.Cells(i, 5) + "',"
    s = s + "'" + ws.Cells(i, 6) + "',"
    tmpstr = Replace(ws.Cells(i, 10), vbCrLf, "\n")
    s = s + "'" + tmpstr + "');"
    o.Cells(i, 1) = s
  Next
  
  Set o = Nothing
  Set ws = Nothing
End Sub

できあがったSQLの例(冒頭数行)：

INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','name','name1','written','','','夏まつり桂川２０２０');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','name','name1','spoken','','','なつまつりけいせん２０２０');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','genres[0]','L','','','','イベント');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','genres[0]','M','','','','祭事');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','genres[0]','S','','','','行事・祭事');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[0]','copyright','','','','有');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[0]','fid','','','','base/40/421/40421KANKO2080556001.jpg');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[1]','name','written','','','夏まつり桂川　盆踊り');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[1]','copyright','','','','有');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','views[1]','fid','','','','base/40/421/40421KANKO2080556002.jpg');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','coordinates','longitude','','','130.6788888');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','coordinates','latitude','','','33.5791666');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','postal_code','','','','820-0696');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','pref','written','','','福岡県');
INSERT INTO kanko (c1, c2, c3, c4, c5,c6,c10) VALUES ('tourspots[0]','place','pref','spoken','','','ふくおかけん');

　このINSERT文のファイルを UTF-8 で保存し、目視でざーっと眺めて、おかしな気がするところを修正します（1行まるごとが クォーテーションされているものがいくつかあったり、無駄な改行が除去しきれていなかったものがあったりしました）。このファイルは約58万行、スポット数で2万2千件ほどありますので、作業のやり方としては、テキストファイルを高速でスクロールさせながら視界の中に入る「なんかへんだぞ」を発見していく感じになります。少し経験が必要になる作業かもしれませんが、見落としても大概後続の処理でエラーになってくれるだけなので、適当なところでやめて、先に進みます。

　以下のテーブルを作って、作成したINSERT文を流し込みます。エラーが出たら原因を調べてやり直し、の繰り返し。

DROP TABLE IF EXISTS kanko;
CREATE TABLE kanko (
  c1 varchar(40),
  c2 varchar(40),
  c3 varchar(40),
  c4 varchar(40),
  c5 varchar(40),
  c6 varchar(40),
  c10 text);
CREATE INDEX idxc1 ON kanko (c1);

　なお、LOAD DATA を使わないのか、と思った人もいるかもしれませんが、（私にとっては）未知のデータに対しては DMLを使って実施したほうが、問題点の把握と追跡に利があると判断してのことです。ロードツールを使うと、エラーになったときに、何が気に入らなくてエラーにされたのかが見えにくいケースがあり、今回はその泥沼リスクを少しでも低減させる意図もあり、DMLを使いました（今回に限らず、SQL文を作成して作業することが比較的多いですね、私は）。

データの取り出し

　よく考えたら全然 CTE 使う必要がない（サブクエリで十分）だったり、もっとスマートな書き方がありそうだったりするので、我こそはという方はぜひお試しいただきたいところですが、私の考えた「とりあえずデータを取得できるSQL例」はこんな感じになります。

WITH CTE_name AS (SELECT * FROM kanko WHERE c2='name' AND c3='name1' AND c4='written')
    ,CTE_lon  AS (SELECT * FROM kanko WHERE c2='place' AND c3='coordinates' AND c4='longitude')
    ,CTE_lat  AS (SELECT * FROM kanko WHERE c2='place' AND c3='coordinates' AND c4='latitude')
SELECT n.c1, substr(n.c10,1,15) name, lon.c10 longitude, lat.c10 latitude
  FROM CTE_name n
    LEFT OUTER JOIN CTE_lon lon ON (n.c1=lon.c1)
    LEFT OUTER JOIN CTE_lat lat ON (n.c1=lat.c1);

　最低限の項目(番号と名前と緯度経度）だけを取得する例ですが、もっと必要な項目がある場合は適宜、CTE/SELECT句/JOIN を増やせば良いです。

いざデータ活用へ

　冒頭の話を覚えているでしょうか。今回は、この中から名古屋市の観光地情報だけが欲しかったのでした。ということで住所欄に「名古屋市」が含まれているものだけを抽出対象にすれば良いです。SQL、ラクですね。万歳。
　・・・・・あれ？　ない。。。1件もマッチしない。。。？？
　SQLの書き方を間違えたのかと驚くも、もとのテキストファイルをgrepもしてみて、原因判明。


　名古屋市のデータ、入ってない！！


　想像するに、どうも総務省のこのデータ、各自治体に「データ提出依頼」でも出したりして、それに乗っかってくれた自治体の情報だけをそのまま載せたのではないかという気がします。名古屋市は誘いに乗らなかったのではないかと。


　教訓。　データを加工する前に、中に欲しいデータが含まれているかを確認しよう。