日々之迷歩

世の中わからんことだらけ

ITが複雑で難しくなっていく様に翻弄される日々です

クロス集計の練習

Excelのピボットテーブルって何??

どうやらExcel使うならピボットテーブル使わにゃ損、とまで言われているスゴイ機能らしい。クロス集計ってやつが出来る機能のようだ。

ところでユニケージ開発で利用するTukubaiコマンドの使い方を少しずつ覚えているが、クロス集計用のコマンドがあるらしい。

すっかりスルー気味でしたが、クロス集計用のコマンドがあります。 | 上田ブログ

mapっていうコマンドが秘密兵器のようだ。では練習してみる。環境はいつものように、OSX Yosemite + GNU awk + GNU grep + Open usp Tukubai。

【問題と解答例】第16回春だからログ解析するぞシェル芸勉強会 | 上田ブログ

先日のシェル芸勉強会で、NASAのWebサーバアクセスログをいじくったが、これを使ってみようかしらね。日付と時刻が正規化済みのログを利用する。

まずは画像ファイル、CGI関連のファイルを集計から外し、日付と時刻の時までを切り出す。yyyymmdd HHの形式。

$ < access_log ggrep -v -Ei '(\.gif\>|\.jpg\>|\.jpeg\>|\.xbm\>|\.wav\>|\.mpg\>|\.pl\>|cgi-bin)' |\
> awk '{print $1,substr($2,1,2)}' | head
19950701 00
19950701 00
19950701 00
19950701 00
19950701 00
19950701 00
19950701 00
19950701 00
19950701 00
19950701 00

日付と時刻の時までをcountコマンドで集計

$ < access_log ggrep -v -Ei '(\.gif\>|\.jpg\>|\.jpeg\>|\.xbm\>|\.wav\>|\.mpg\>|\.pl\>|cgi-bin)' |\
> awk '{print $1,substr($2,1,2)}' |\
> count 1 2 | head
19950701 00 1185
19950701 01 1000
19950701 02 755
19950701 03 602
19950701 04 493
19950701 05 489
19950701 06 486
19950701 07 482
19950701 08 647
19950701 09 673

この結果をmapコマンドに食わせる。第一キーは1カラム目までなのでnum=1を指定。みやすくするためketaコマンドで桁揃えする。

$ < access_log ggrep -v -Ei '(\.gif\>|\.jpg\>|\.jpeg\>|\.xbm\>|\.wav\>|\.mpg\>|\.pl\>|cgi-bin)' |\
> awk '{print $1,substr($2,1,2)}' |\
> count 1 2 |\
> map num=1 | keta | head
   *   00   01  02  03  04  05  06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23
19950701 1185 1000 755 602 493 489 486  482  647  673  981 1201  881 1124 1103 1046  975 1215 1087  913  973 1174 1071 1018
19950702 1008  923 807 542 436 440 448  419  465  633  705  824 1034  995 1014  907 1067 1215 1124  925 1036 1131 1274 1324
19950703 1065  820 810 700 570 649 585 1000 1134 1563 1415 1545 1821 1972 1872 1868 1813 1527 1068 1198 1313 1347 1120 1298
19950704 1083 1061 931 700 675 656 587  731  982 1009 1176 1187 1082 1203 1095 1542 1177 1273  991 1034  944  955 1013 1299
19950705  905  722 707 729 505 556 606  996 1210 1387 1559 1959 2098 1602 2620 2125 2373 1570 1475 1368 1040  987 1160 1090
19950706  910  695 617 549 521 607 600  842 1288 1584 1742 2080 2049 2231 2273 2215 2140 1822 1593 1417 1288 1156 1398 1377
19950707  916  922 789 558 491 338 394  725 1557 1696 1815 2062 2178 1956 1501 2046 1910 1718 1181  880  975  807  851  973
19950708  845  604 401 368 245 304 276  331  332  409  588  516  597  672  892  645  831  805  616  583  557  638  685  725
19950709  568  510 459 425 306 137 145  169  227  363  419  650  681  587  692  579  813  649  627  607  858  785  678  601

おお、なんかそれらしいのが出てきたぞ!しかし、はてなブログだと空白が等幅フォントになってくれないのか・・・微妙だ。

気を取り直して、もう少し出力をカッコよく?Tukubaiコマンドには曜日追加するyobiコマンドや、日付のフォーマットを指定するdayslashコマンドがあるので使ってみる。桁ももう少し綺麗に整理。

$ < access_log ggrep -v -Ei '(\.gif\>|\.jpg\>|\.jpeg\>|\.xbm\>|\.wav\>|\.mpg\>|\.pl\>|cgi-bin)' |\
> awk '{print $1,substr($2,1,2)}' |\
> count 1 2 |\
> yobi -j 1 |\
> dayslash yyyy/mm/dd 1 |\
> map num=2 |\
> keta 10 2 4xNF-2
     *  *   00   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23
1995/07/01 土 1185 1000  755  602  493  489  486  482  647  673  981 1201  881 1124 1103 1046  975 1215 1087  913  973 1174 1071 1018
1995/07/02 日 1008  923  807  542  436  440  448  419  465  633  705  824 1034  995 1014  907 1067 1215 1124  925 1036 1131 1274 1324
1995/07/03 月 1065  820  810  700  570  649  585 1000 1134 1563 1415 1545 1821 1972 1872 1868 1813 1527 1068 1198 1313 1347 1120 1298
....
1995/07/28 金  543  363  340  402  262  308  355  469  729  890 1011 1013  988  517    0    0    0    0    0    0    0    0    0    0
1995/08/01 火  550  472  288  409  284  368  309  578  862  929  977 1193 1259 1047  660    0    0    0    0    0    0    0    0    0
....
1995/08/30 水  569  275  467  389  440  349  378  640 1065 1169 1398 1338 1110 1341 1380 1583 1386 1215  857  937 1108  930 1040 1067
1995/08/31 木  753  577  645  454  494  590  569 1047 1240 1425 1683 1691 1478 1612 1544 1462 1032 1127  973  880  752  740  784  781

なんかそれっぽくなったぞ!!だがしかし・・・よく見るとアクセスが無い日付の行が無い。7月29日とかの行が無い。このままでいいかもしれんが、やっぱり補完しておきたい。

補完のための日付と時刻データを作るにも、Tukubaiコマンドが便利。今扱っているログの範囲について、あらかじめ日時のリストを作っておけば良さそう。1995年7月1日00時〜1995年8月31日23時までのリストを作っておく。補完データ作るには、mdateコマンドとloopxコマンドが便利。

$ mdate -e 19950701 19950831 | tateyoko > date
$ seq -w 00 23 > hour
$ loopx date hour > date_hour
$ head date_hour 
19950701 00
19950701 01
19950701 02
19950701 03
19950701 04
19950701 05
19950701 06
19950701 07
19950701 08
19950701 09
$ tail date_hour
19950831 14
19950831 15
19950831 16
19950831 17
19950831 18
19950831 19
19950831 20
19950831 21
19950831 22
19950831 23

さて、このリストを使って、秘密兵器loopjコマンドで補完してやれば良さそうである。曜日が入るので、mapコマンドのオプションはnum=2に変更。

$ < access_log ggrep -v -Ei '(\.gif\>|\.jpg\>|\.jpeg\>|\.xbm\>|\.wav\>|\.mpg\>|\.pl\>|cgi-bin)' |\
> awk '{print $1,substr($2,1,2)}' |\
> count 1 2 |\
> loopj num=2 date_hour - |\
> yobi -j 1 |\
> dayslash yyyy/mm/dd 1 |\
> map num=2 |\
> keta 10 2 4xNF-2
     *  *   00   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23
1995/07/01 土 1185 1000  755  602  493  489  486  482  647  673  981 1201  881 1124 1103 1046  975 1215 1087  913  973 1174 1071 1018
1995/07/02 日 1008  923  807  542  436  440  448  419  465  633  705  824 1034  995 1014  907 1067 1215 1124  925 1036 1131 1274 1324
1995/07/03 月 1065  820  810  700  570  649  585 1000 1134 1563 1415 1545 1821 1972 1872 1868 1813 1527 1068 1198 1313 1347 1120 1298
....
1995/07/28 金  543  363  340  402  262  308  355  469  729  890 1011 1013  988  517    0    0    0    0    0    0    0    0    0    0
1995/07/29 土    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
1995/07/30 日    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
1995/07/31 月    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
1995/08/01 火  550  472  288  409  284  368  309  578  862  929  977 1193 1259 1047  660    0    0    0    0    0    0    0    0    0
1995/08/02 水    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
1995/08/03 木    0    0    0    0    6   22   35   14   25   12   20  678 1509 1182 1190 1334 1289 1161  865  534  794  602  741  566
....
1995/08/30 水  569  275  467  389  440  349  378  640 1065 1169 1398 1338 1110 1341 1380 1583 1386 1215  857  937 1108  930 1040 1067
1995/08/31 木  753  577  645  454  494  590  569 1047 1240 1425 1683 1691 1478 1612 1544 1462 1032 1127  973  880  752  740  784  781

アクセスが無かった7月29日とかの行も出ている。これで一応完成!Tukubaiコマンド使うと、forなどの繰り返し的な表現が一切出てこ無いのがスッキリしていい。個人的にはループ的なのがいっぱい出るよりわかりやすいのだ。

ところで、縦横の小計とかを計算するには??横はysumとか使うのかな?根本的にはsm4とかsm5とかのコマンドを併用するのだろうか?分からん・・・・