【Excel】データの重複を簡単に排除する&Apacheログから好きな部分を抽出する

シェアする

ノンエンジニアのディレクターともなると、データ解析するにも一苦労なのはお互い様。そんな社畜たちにピッタリのツールが方眼紙的な利用方法でお馴染みのMicrosoft Excelです。たまには役立つコンテンツをと思い、書いてみたいと思います。

在りし日のガラケー時代、UIDという物質があったのはアラサーモバイル出身ディレクターの皆様はご存知のはず。まだ駆け出しだった私に上司から理不尽な命令が。

数GB分のApacheのログからUIDをユニークに抽出して抽選してくれ。

また適当なキャンペーンやりやがって…と思いつつ悩むわけです。でも簡単、UIDさえ抽出出来れば、あとはExcelのピボットテーブルにぶっこんだら重複消してユニークにしてくれるよ!

というわけで、1列に重複を消したいデータを並べます。全部選択します。データ→ピボットテーブルを選び、OKを押します。行ラベルへUIDの列を移動します。出来上がりです。


↑こんな具合です。

実はフィルタで重複を消す方法もあるのですが、単純に重複データを消して抽出出来ればいいなら、ピボットテーブルの方が速いです。

察しの良い方は気づいていると思いますが、数GB分のApacheのログがどうやってExcelに格納できるまでにダイエットされたのかが気になるかと。簡単にフローにすると

  1. GREPで指定行を抽出(この場合は応募ボタンを押した先にしかない画像ファイルのパスを含む行。JGREP使うと楽)
  2. Excelにぶち込んでデータの区切り位置機能を使ってセル分割
  3. 不要な列を削除
  4. UIDだけの列ができる
  5. ピボットで重複を消す

という感じです。抽選するならログの中から適当に選んでくりゃいいじゃんという話もありますが、そこは誠実な対応を心がける私です。なお、抽出してもExcel2003の限界を超える場合はテキストファイルを分割します。

まぁ5回で済むぐらいなら上等でしょう。1日かかるぐらいなら発注して抽選システム作ってもらうか適当に選んだほうがいいです。区切り位置で分割する前に、テキストエディタ上で区切りたいデータを置換しておくと便利。必ずスペースで区切られているわけではないですからね。


xxx.xxx.xx.xx – – [15/Feb/2013:16:44:14 +0900] “GET /wp-includes/js/comment-reply.min.js?ver=3.5.1 HTTP/1.1” 200 786 “https://blog.seamonkey-delivery.com/smartphone/108/” “Opera/9.80 (Windows NT 6.1; WOW64) Presto/2.12.388 Version/12.14″

こっからUAだけ抽出するとしたら、「” “」を「Å」(これはログに絶対はいらない文字ならなんでもいい)に置換。その後、「”」を「∀」に変換します。この状態でエクセルにぶっこんで、Åと∀で2回区切ったらUAが分割できます。この置換ワークのセンスで効率に差がでるので、置換する順番と文字列はとても重要です。

こんな阿呆な作業ドヤ顔でブログに書くのもなんなんですが、何かの参考になればと思いまして。

思いの外泥臭い作業って多いですよね。大体誰かの後始末が多いですが。

スポンサードリンク
 
シェアする