データエンスージアストにとって天敵とされる「神エクセル」。ぶつくさ言いながらも必要な人はひたすらセル結合の解除や省略値の補完やコピペ、そして検索と置換に勤しむ訳ですが、そんな人はぜひいちど、苦しみをみんなで乗り越えるためにコミュニティによって活発に開発されているOpenRefineを使ってみてください。神エクセルの難易度も様々ですが、表計算ソフトやテキストエディタだけで頑張るよりもはるかに効率的にクレンジングでき、さらには肩凝りも減らすことができます。※個人の感想です。
1 元データ
セル結合はもとより均等割付、縦書き、名称の省略、区切り行などなかなか見事な神っぷりです。エクセルの行で596行あります。難易度は中級というところでしょうか。
2 整形/クレンジング
2.1 エクセルデータを読み込む
上級レベルになると、事前にエクセル側である程度整形しないとどうにもならない場合がありますが、今回のケースはほぼOpenRefine側で何とかなります。PCローカルにダウンロードしたエクセルをそのまま読み込みます。
r1ichiran.xls
デフォルトで全シートが読み込まれます。ここではいったん「全選択解除」し、スクロールダウンして小中学校のシートをひとつだけ選びます。
スクロールアップして上に戻ります。9行目までがヘッダーになってカラム名が表現されているので「カラム名として9行分解釈」と設定します。（1を9に変えます）
ヘッダーの各カラムに冗長ながらもそれらしい見出しが入りました。この見出しは後で自由に変えられます。
1行目などに空白行があり、不要なので「□空白行も保存」のチェックを外します。
このプロジェクトを後から検索しやすくするためのタグを適当に付けて「プロジェクトを作成」
これで読み込みと前処理は終わりです。
2.2 整形
データの過不足調整などを行い、整った表形式のデータにします。
2.2.1 行モードとレコードモード
画面左上で「レコード」が自動選択された状態で表示されています。「行」に切り替えてみると分かりやすいのですが、「レコード」モードでは左端列の値に応じて行単位ではなく、グループ単位で操作することができます。詳細はOpenRefine Wikiのこちらあたりを参照してください。
ページあたり表示件数は多いほど見やすいですが、レコードモードで増やすと重たくなる場合があるのでご注意ください。
2.2.2 市町村名と学校分類のカラムを分ける
左端は制御用のカラムで、データは2カラム目から始まります。最初の「3小・中・義務教」と見えているカラムを下に見ていくと、市町村ごとに小学校、中学校、義務教育学校、、と学校の分類順に並んでいる2階層の構造がひとつのカラムで表現されていることが分かります。これはそういうものと言われれば人間はなんとなく理解できますが、機械には市町村名と学校の分類名はそのルールが分からないことには区別できません。
機械に理解しやすい表形式にするには市町村名と学校の分類名を別カラムにし、さらに同じ値であっても省略せずに行単位で値を埋める必要があります。
2.2.2.1 カラム追加
まず該当カラムを値ごとコピーして「学校分類」という名前のカラムを追加します。
「▼3小・中・義務教」の▼をクリックして カラム編集＞このカラムに基づいてカラムを追加 と選びます。
新しいカラム名：学校分類
式：value（初期表示のまま）
上記のように入力して「OK」
「プレビュー」タブで式（GREL）実行前後のカラム値が比較できます。（この場合は値は同じ）
「ヘルプ」タブでGRELの文法を参照できます。
2.2.2.2 市町村カラムの整備
「▼3小・中・義務教」の▼をクリックして カラム編集＞カラム名を変更する と選んでカラム名を「市町村」に変更します。
「市町村」カラムにどんな値が入っているか調べます。
「▼市町村」の▼をクリックして ファセット＞テキストファセット と選びます。
左サイドに市町村ファセットのウィンドウが現れ、市町村カラムに入っている値とその件数が表示されます。それぞれの値をクリックすると赤い文字に変わり、その値を持つ行/レコードだけが表示されます。
不要な値を消し込んで行きます。
「(2」というところをクリックするとそのレコードだけが2表示され、分校であることが分かります。
この値は分校であることを示しており、市町村名には不要です。「(2」というところにマウスカーソルを当てると「編集」の文字が表示され、
これをクリックするとこの値自体を編集できるので
空白にして「適用」をクリックすると一覧から消えます。
以下<義務教育学校後期>など、市町村名以外の値を全て空白にしていきます。その際、ファセットウィンドウ内で文字列を選んで赤い状態にせずとも（青字のままでも）値の書き換えは可能です。また、改ページ時の見出しと思われる「一関市（つづき）」といった値も市町村名としては不要なので空白にします。
ファセットウィンドウの一番下に「(blank) 33」と現れ、空白のレコードが33件あることが分かります。
これで不要な値が無くなりましたので、それぞれの市町村名を、それぞれ各行の空白欄にコピーします。
「▼市町村」の▼をクリックして セル編集＞下方向の空白を埋める と選びます。
すると全ての行に市町村名がコピーされ、空白セルはなくなりました。
2.2.2.3 学校分類カラムの整備
分かりやすくするために左サイドの市町村ファセットウィンドウはいったん左上のXで消して素の状態に戻します。
「▼学校分類」の▼をクリックして ファセット＞テキストファセット と選びます。
市町村カラムの初期状態と同じ状態です。自治体名は不要ですが、後で（下記2.2.3）まとめて消すことにして、まずはゴミっぽい値や、学校分類名に不要な<>などの値を学校分類ファセット上で消し込みます。義務教育学校は後期/前期を消して「義務教育学校」に揃えます。（セル編集からGRELで一括編集することもできます）
最終的に空白行が484件になりました。
画面左上で「レコード」モードから「行」モードに切り替えます。
「▼学校分類」の▼をクリックして セル編集＞下方向の空白を埋める と選びます。
学校分類が下方向にコピーされ、空白セルが無くなりました。
2.2.2.4 4番目のColumnカラムの整備
分かりやすくするために左サイドの学校分類ファセットウィンドウはいったん左上のXで消して素の状態に戻します。
「▼Column」の▼をクリックして ファセット＞テキストファセット と選びます。
空白行以外に「)2」とあり、学校名を()で括って分校が表されているので、これを選んで、表示された2件について学校分類を「分校」に書き換えます。
左サイドのColumnファセットウィンドウはいったん左上のXで消して素の状態に戻します。
「▼Column」の▼をクリックして カラム編集＞このカラムを取り除く と選びます。
不要なColumnカラムが削除されました。
2.2.3 学校カラムの整備
「▼学校名」の▼をクリックして ファセット＞カスタムファセット＞空白ファセット と選びます。
左サイドに現れた学校名の空白ファセットから「true」を選ぶと、学校名が空欄のものが一覧表示されます。
これらを元のエクセルと照らし合わせると、自治体名の区切り行と、自治体・学校分類ごとの学級数などの集計値行であることが分かります。表形式のアトミックなデータとしては不要なので削除します。
制御列「▼全て」の▼をクリックして 行を編集＞マッチした行を削除 と選ぶと現在表示されている行が全て削除されます。
分かりやすくするために左サイドの学校名ファセットウィンドウはいったん左上のXで消して素の状態に戻します。
きれいな表形式になりましたね！
でも値をよく見るとまだ改善点があります。もう少し頑張ってみます。
2.2.4 学校名の補完
学校名が省略されて「仁王」のようになっています。フルだと「仁王小学校」ですよね。また、全国のデータとして見ると管理者／設置者がプレフィックスとして付いていたほうが一意に識別しやすいので「盛岡市立仁王小学校」という表記にしましょう。
「▼学校名」の▼をクリックして セル編集＞変換 と選びます。
式の欄に以下のGRELスクリプトを記述して「OK」。
cells["市町村"].value+"立"+value+cells["学校分類"].value
2.2.5 読みの補完
「しょうがっこう」や「ちゅうがっこう」が省略されているので分類ごとに手で補完します。
「▼学校分類」の▼をクリックして ファセット＞テキストファセット と選びます。
左サイドにファセットウィンドウが開くので上の「義務教育学校」から順に選んで読みの後ろに「ぎむきょういくがっこう」などを追加します。
おっと不要なコメント行が残っていることに気づいてしまいました。こうした邪魔な行は、該当行の制御カラムに旗（フラグ）を立てて、旗ファセットで一覧表示して一気に消します。
改めてざっと調べたところ3件のコメント行が見つかりました。
「▼全て」の▼をクリックして ファセット＞旗ファセット と選びます。
さらに「▼全て」の▼をクリックして 行を編集＞マッチした行を削除 で削除します。
併せて()の付いた学校名が1件見つかったので、学校名を修正し、分類を分校に変更しました。
ふうっ。気を取り直して読みの補完に戻ります。
改めて「▼学校分類」の▼をクリックして ファセット＞テキストファセット と選びます。
左サイドのファセットウィンドウで「義務教育学校」を選んで、さらに
「▼読み」の▼をクリックして セル編集＞変換 と選びます。
式の欄に以下のGRELスクリプトを記述して「OK」。
value+"ぎむきょういくがっこう"
以下、小学校、中学校、分校それぞれについても同様に操作します。
2.2.6 住所の整形
画面右側にスクロールすると「Column2」という名前の付いた住所欄があります。
よく見ると住所に半角スペースが含まれ、県名も省略されています。住所を元にジオコーディングで座標を取得する場合など、正規化しておいたほうが良いので以下のように整形します。
「▼Column2」の▼をクリックして カラム編集＞カラムの名前を変更する と選び「住所」に変更します。
「▼住所」の▼をクリックして セル編集＞変換 と選び下記GRELスクリプトを記述して「OK」。
replace(value," ","")
「▼住所」の▼をクリックして セル編集＞変換 と選び下記GRELスクリプトを記述して「OK」。
"岩手県"+value
2.2.7 電話番号の整形
現在の「(019) 623-4214」のような表記でも判読可能ですが、"-"区切りが一般的であり、さらに国際電話番号表記をする場合には先頭に"+81-"を追加し、1桁目の0は削除します。よろしければGRELの文字列操作機能ヘルプなどを参考にreplaceやsubstring関数などで試してみてください。
#2.3 出力
OpenRefineのインポート/エクスポート機能はかなり充実しており、データ形式の変換にも使えたりしますが、今回整形した表をエクセル形式で改めて出力してみたものが下記。最初からこうなっていれば。。
3 最後に
この元データの形式についてはあまりよく分かっていませんが、他の自治体でも見かけたので、おそらく報告を集める際の収集フォーマットになっているように思われました。
これは集める側も全体の集計はたいへんなのではないでしょうか。このように人間可読な表現（神エクセル）はあっても良いけど、元データを単純な表形式で持つことは簡単にできる、もしくは既にあるはずなので、そちらの形式も併せて公開して頂けると、上で縷々説明して来た作業が不要になるので、日本全国で多くの人が幸せになれると思います。
なお、クレンジングの題材として取り上げさせて頂きましたが、この表の公開そのものは非常に有益です。都道府県によってはそもそも直轄の学校以外、小中学校の一覧がまとまっては公開されていなかったりするので、バラバラな形式の基礎自治体ごとのフォーマットを集めることから比べれば、こうした神エクセルであっても住所付きで一覧になっているのは利用者としては使い勝手が良い方で、ありがたいです。
次回はPDFと戦う編を予定 :)
(2020/6/5追記)
#4 シリーズ記事