置換で大量のデータをきれいにする

牧草地にしゃがんで草むしりしていたら魔法使いが一瞬できれいにしてくれた、みたいなお話。

はじめに

全体で1000万件を超えるデータ。1件の中身は10桁くらいの数字たちで区切りはセミコロン「;」。そういうデータが出てくるはずだったのに、開いてみたらば文字化けとかいらない数字とかカッコとかのいらないものがたくさん入り込んでいた。そのうちパターンが一定している約5万件はMicrosoftのAccessから取り出してExcelのFINDとMIDで消せたけれど、それではダメな約1000件をきれいにする方法は、どうにもこうにも分からなくて、しかたないのでDeleteキーでひとつひとつきれいにして回っていた。
手作業ではいつまでも終わりは見えなくて、もちろん夕方には肩も目も痛くなり、つらいようとこぼしたら魔法使いたちがやってきて、きれいにできるよ、簡単さ、と言った。そして、まばたきするうちにきれいにしてくれた。
魔法使いのひとりがこの技を記録しておきたまえと言うので、それからほかのひとが私みたいに無駄な肩こりにならなくてもいいように、メモを。

環境

私が使うことを許されていたツール（ソフト）は、TeraPadとExcel、だった。
魔法使いのひとりによれば「TeraPadは改行とタブの置換ができるので、Excelとの組み合わせ次第でけっこういろんなことができるのでお勧め」らしい。

魔法

  1. データをTeraPadにコピペ
  2. 置換：　\n　→　\n\n
  3. 置換：　;　→　\n
  4. 置換：　(　→　\t
  5. Excelにコピペして、1列目だけTeraPadにコピペ
  6. 置換：　\n　→　;
  7. 置換：　;;　→　\n

＊「\n」は改行1個、「\n\n」は改行2個、「\t」はタブ。
ただ、「6. 置換：　\n　→　;」でTeraPadが「1行8000文字制限を超えている可能性があってムリ」と言って動かなくなるので、「5. …TeraPadにコピペ」を100件ずつにしてちまちまと置換をしては貼り合わせる、というのを10回繰り返した。（8000文字を超えてもデータがなくなるわけではなく表示されないということのようだけれど、見えないと自分の操作でデータに何が起きているか把握できず不安になるので手作業でやった。）

対象データの例（実際のデータとは異なります）

  • 1590732743021 (逕イ鬪ィ蜊キ);1590732743663 (邇牙勣蜊キ);1590732706113 (逑キ蝎ィ蜊キ 譏惹サ」);1590732706172 (逑キ蝎ィ蜊キ 貂・サ」);2732706323 (譏取ク・。」譯亥キサ 譏惹サ」);1590732706121 (譏取ク・。」譯亥差 貂・サ」);2732706367 (扈倡判蜊キ蜴・彰逕サ);1590732706164 (扈倡判蜊キ 鬟惹ソ礼判)
  • 3121074603 (4);3121074611 (7);3121074327 (6);3121074683 (2);3121077237(3);3121077243 (1);1268121076603 (12);1268121076617 (13)3121174323 (1);3121174681 (2);3121170611 (3);3121170622 (4);3121174662(7);3121174627 (6);1268121174687 (2);1268121174312 (3)3131246068 ・ゥ・ュ・ィ・・?ク;3131246041 (・ゥ・ュ・・巡 ?ク);3131246177 (・菩寳・・?ク);1268131243142 (・ス・・お・・?ク);1268131243174 (・ス・・カ・巡?ク);1268131246802 (・懍」シ・・?ク);1268131241422 (・・攵・ィ・・?ク);1268131241458 (・・攵・・巡 ?ク)
  • 315903132X;315903211X (3;3159068017 (4);3159068423 (7);3159068731(6);3159068712 (2);3159058307 (3);3159058313 (1);3159058321 (10);315905868X(11);3159032363 (12)
  • 1590758241032（(隨ャ3霎・2);1590758241041（(隨ャ3霎・3));1590758241076 (隨ャ3霎・4);1590758241063 (隨ャ3霎・7;1590758241020 (隨ャ3霎・6);1590758241032 (隨ャ3霎・2);1590758241014 (隨ャ3霎・3);1590758241100 (隨ャ3霎・1);1590758241112(隨ャ3霎・10)

つまり、データのパターンはこんな感じ。

  • 各データは1件=1行
  • 必要な数字の後ろにいらないものが続く
  • 必要な数字といらないものの間にはスペースがある場合とない場合がある
  • いらないものの数は一定していない
  • いらないものの種類は一定しておらず文字、数字、記号、これらの組み合わせ、の場合がある
  • いらないものの前後にカッコがあったりなかったりする
  • カッコ内にカッコがあったりもする

魔法は何にでも効くわけじゃなかった

できた！と喜んだのはもちろん。…でも、ここまで書いておきながらアララという感じだけれども、じつは私が掃除しようとしていたデータは、これだけではきれいになってくれなかった。ということに気づいたのは翌日で、なんとなく不安になって数字以外のものが入っているデータを抽出して（250万件くらい）ひたすら見ていったら（といっても見切れなかったけれど）、直せていないものがあった。
たとえば、こんなパターン。

  • 数字と数字の間のいらない文字が「[1]」とか「[2]」とかの場合
  • 数字のすぐ後ろに（スペースなどなしで）アルファベットが続いている場合
  • 数字の後ろに「 '」（スペース、ダッシュ）が続いている場合

こんなふうに、拾えなかった＝きれいにできなかったデータはほかにもあるかも知れず、つまり掃除する対象のパターンをすべて洗い出さないと、思わぬ取りこぼしが起きたり、思わぬことが起きたり（セミコロンで区切られるはずの数字たちがくっついてしまったり）という危険があって、でも、私がきれいにしなくてはならなかったデータのうちあやしげな約250万件の全てを網羅するパターンを取りこぼしなく目で確認するのは非現実的で…うーむ…と、思っていたら、また別の魔法使いがやってきて、「その掃除、前にやったことあるからすぐできるよ」と言って、ものの10分くらいで1000万件超をさっときれいにしてくれて、ありがたいのと同時に私の生きている時間の無駄さがいやになった。魔法使いになりたい。


ちなみに、私のPCは弱っちいために大量のデータにはすぐ息切れする。ふっと画面が真っ白になってマウスも効かなくなって世界は終わったと思いながら茫然と復活を待つ、というのを繰り返す日々でアドベンチャーすぎるなぁと思っているところ。