僕はいつもこんな感じでざくっとデータをみている

CSVがドーンとあったとする。その際にどうやってデータを見るかってのは、なんとなくの手順がある。その手順を示しておく。

wc -l でみる

まずは行数をみる。10万行とか超えちゃうとExcelでは重すぎてみれない。

less コマンドでみる

そして中を確認する。lessコマンドでみれるのはutf8縛りなので、SJISの場合は別のエディタなどで開く。 中身が何かを確認する。　先頭にBOMがついているかどうかや、LF改行なのか、CRLF改行なのかくらいはここで分かる。 大きなファイルも早く開けるのがlessコマンド。先頭行と最終行はチェックする。

Excelでみる

文字コードを変換する

nkf -s utf8.csv > sjis.sv

で、SJISに変換する。UNICODEのままでExcelで開きたかったら、以下のコマンドでBOMをつける。

cat <(printf "\xEF\xBB\xBF") 元ファイル > BOMつきファイル

Excelで開く

数値の前ゼロが取れちゃうから、普通はCSVをダブルクリックしちゃだめなんだけど、参照専用と割り切って、ダブルクリックして開いちゃう。

フィルタをかける

フィルタをかけて、▼をクリックする、そうするとどういうデータがあるかが分かる。空白の存在などもここで分かる。ゴミデータが入ってないかが分かる。

ピボットテーブルを作る

ピボットテーブルを作ることで、データの分布が分かる。特に日付のフィールドがあれば、ピボットテーブルを日付でグループ化して、年単位や月単位の分布をみて、傾向をつかむ。

mysqlの場合

CSV単体が見づらい場合はmysqlなどのRDBでみる。

ひたすら並びかえる

データの異常値がないかどうかは、ひたすら並びかえて確認する。sequel proやphpmyadmin などのツールであれば、sqlを書くこと無く並び替えが用意なので、そこでみる。

ひたすらグループ化する

select DATE_FORMAT(updated_at,'%Y/%m/%d %H') as ymdh, count(updated_at) as cnt  from テーブル名 
group by ymdh;

こんな感じで、時間単位の更新時間を並べて見て、どういう処理ペースで動いているかを確認したりする。

そのままメールに出力結果を貼り付けたい時などは、FORMAT関数でカンマ区切りにしたり、LPAD関数で左空白詰めをして右揃えにしたりする。

BiqQueryの場合

MySQLだけだと、数百万件を超えるデータは扱いにくい。並び替えやグループ化をするデータはインデックスを貼ればいいのだが、インデックスをはるにもマシンパワーが余分にとられる。そこまでくれば、インデックスに気を使わなくてもいい、BigQueryをつかうのがいい。

ただ、CSVを参照可能にするにもそこそこの手間がかかる。

CSVはUTF8

UTF8のダブルクォート区切り。フィールド内ダブルクォートはダブルクォート二重でエスケープ。

CSVをgz形式にする

BiqQueryに入れるレベルのCSVファイルは、GBを超えるくらいなので、アップロードに時間をかけなくてもいいように、giz圧縮をする。

gzip -c data.csv > data.csv.gz

Google Cloud Strageにアップする

gsutil cp data.csv.gz gs://bucket/data.csv.gz

この段階で、Googleの認証が必要。gsutilもインストールしてる前提。認証は、gsutil auth で認証をする。

この際に、GoogleCloudStrageのプロジェクトIDが必要。GoogleCloudStrageのプロジェクトIDとBigQueryのプロジェクトIDは同じ方が権限的に楽。

bq コマンドでロードする

bq load --source_format=CSV  --skip_leading_rows=1 --project_id=XXX aaa.gz   gs://zzzz/aaa.gz \
id:integer,\
name:string

各カラム名とその型を、スキーマとして引数にしていしている。シェルとしては一行だけど、見づらいのでバックスラッシュで改行を入れている。

bq コマンドを最初につかうときにもGoogleの認証は聞かれる。 AWSみたいなアプリケーションキーを環境変数に入れておくというような手法でない。accountを切り替えるのは面倒。

BiqQueryで集計して、レポートにする

ひたすらグループ化のSQLをたたく。集計の結果は、SpreaSheetに出力できるので、ポンポン出力する。適当なファイル名で出力されるので、出力されるたびに、スプレッドシートのファイル名を変えたほうがいい。

スプレッドシートに出せば、グラフにポンポン変換できるので、グラフ化して、スライドの資料にポンポン貼っていけばいい。スプレッドシートには、SPARKLINEという関数があって、セルの中にちょっとしたグラフをかける。Excelでいうデータバーみたいなことができるので手軽。