クロス集計の練習
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とかのコマンドを併用するのだろうか？分からん・・・・
