目的
以下の入力データのようなアクセス履歴データから、ユーザー毎にどのタイプのお店に何回訪問したか、というクロス集計表を作成します。これはその後、Rで分析する入力データになります。
ファイル群の容量が巨大すぎてローカルPCでのSQLソフトの利用が非現実的な場合、Perlを使うことで至って高速にクロス集計が可能と知り、試作してみました。
私はPerl初心者なので、１）まず、ユーザー毎のショップ毎のアクセス数という中間アウトプットファイルを作成する、２）中間アウトプットファイルから最終的なアウトプットであるクロス集計を作成する、と２つのプロセスにスクリプトを分けています。
入力データ
| uid | datetime | shop_name |
|---|---|---|
| x00001 | 20141205050508 | サプリメント |
| x00001 | 20141205050701 | 健康グッズ |
| x00001 | 20150102170501 | 収納家具 |
| x00002 | 20141120160521 | 書籍 |
| x00002 | 20150205170530 | スキンケア |
| x00002 | 20150205175002 | ファッション |
| ... | ... | ... |
条件
- shop_nameは、1万個以上存在しますが、集計するのは100個のshop_nameに限定します。集計対象のshop_nameリストはshoplistファイルから読み込みます。
- YYYYMMDDHHMMSS形式のdatetimeフィールドを読み、YYYYが2015年のレコードのみ集計対象とします。
プロセス１〜クロス表の元になる中間ファイルを作成
中間ファイルは、以下の形式です。
| uid | shop_name | count |
|---|
ここで全てのユーザーについてshop_nameは100個分集計値が入るようにします。
備考）SQLだと、countが０になるカテゴリ名は出力されません。
sample.pl
#!/usr/bin/perl
use 5.014;
use strict;
use warnings;
#アウトプット形式
#userid, カテゴリ名,アクセス数
#使い方：gzcat ログファイル名*.tsv.gz |./sample.pl >temporary_cross.tsv
#----------------------------------------------------------------------
# 入力データを標準入力から読み、ハッシュでユーザー毎ショップ毎のアクセス数をカウントする
#----------------------------------------------------------------------
my $user_cate_data ={};#user_cate_dataという無名ハッシュ（無名配列生成子で宣言された）を生成し、$を付けてリファレンスを宣言する
my @rec =();#読み込んだ１レコードを格納する配列を初期化
my $uids={};#出現したuid全てを格納するハッシュを初期化
while (<>){
chomp;
#タブ区切りの文字列を、recという配列に区切って格納する;
my @rec = split /\t/, $_, -1;
#uid,date,category_nameという変数を配列から取得
my $uid = $rec[0];
my $date = $rec[1];
#datestrは、YYYYMMDDhhmmssの日付文字列から、YYYYMMの部分を抽出する
my $datestr = substr($date,0,4);
#2015年1月以降のみ集計の対象とする。
if ($datestr ge '2015'){
my $category = $rec[2];
#user_cate_dataは、$をつけているのでハッシュのリファレンス
#ハッシュuser_cate_dataの１つめのキーワード$category_name と2つめのキーワード $uidとで参照される要素の値を１つインクリメントする
$user_cate_data->{ $uid }->{ $category }++;
#uidsハッシュの$uidキーに１を格納する
$uids->{$uid} = 1;
}
}
#---------------------------------------------------
# 集計対象のショップ名ファイルの読み込みとハッシュ格納
#---------------------------------------------------
my $file="対象１００ショップリスト.tsv";
my %dic;#ハッシュを宣言
open my $fh,'<',$file;
while(my $line =<$fh>){
chomp($line) ;
$dic{$line} = 1; #ハッシュ%dicで、読み込んだショップ名キーワードに対して、値1を割り当てる
}
close $fh;
#---------------------------------------------------
#全てのユーザーについて、対象ショップ100個分の集計値を出力
# 対象ショップへのアクセスがない場合も、値0を出力
#---------------------------------------------------
# $uidsは、ハッシュのリファレンス
# %{$uids｝は、ハッシュのリファレンスをデリファレンス
for my $user_id (keys %{$uids}) {
#全てのuid名を書き出す。
# $user_cate_dataは、ハッシュのリファレンス。
for my $cate (keys %dic){
#集計対象の100店舗名全てを書き出す
#カウント結果ハッシュの要素へアクセスするには、$user_cate_data->{$user_id}{$cate}
my $value = $user_cate_data->{$user_id}{$cate} || 0 ;
#ハッシュのキーワードは$user_id、$cateで参照
print "$user_id\t$cate\t$value\n";
}
}
中間ファイル
| uid | shop_name | count |
|---|---|---|
| x00001 | サプリメント | 1 |
| x00001 | 健康グッズ | 1 |
| x00001 | 収納家具 | 1 |
| x00001 | 書籍 | 0 |
| x00001 | スキンケア | 0 |
| x00001 | ファッション | 0 |
| x00001 | ... | 0 |
| x00002 | サプリメント | 0 |
| x00002 | 健康グッズ | 0 |
| x00002 | 収納家具 | 0 |
| x00002 | 書籍 | 1 |
| x00002 | スキンケア | 1 |
| x00002 | ファッション | 1 |
| x00002 | ... | 0 |
プロセス２〜中間ファイルを読み込んで、クロス表の完成形を作成する
make_uid_shop_cross.pl
#!/usr/bin/perl
use 5.014;
use strict;
use warnings;
#使い方 ./make_uid_shop_cross.pl temprary_cross.tsv > cross_table.tsv
my %shops ;#ショップ名が全て格納されるハッシュ
my %uids; #uidが全て格納されるハッシュ
my %cross_org; #ユーザー毎、店舗名ごとのアクセス数が格納されるハッシュ
#標準入力から読み込むのは、sample.plで作成したユーザー、店舗名、アクセス数の3列のデータ
while (<>){
chomp;
my @rec = split /\t/, $_, -1;
my $shop_name = $rec[1] ; #店舗名はレコードの２フィールド目から取得
#ハッシュshopsの$shop_nameキーに値＝１をセットする
$shops{ $shop_name }=1;
my $uid = $rec[0]; #ユーザー名はレコードの１フィールド目から取得
#ハッシュuidsに$uidのキーワードを登録し、値＝１をセットする
$uids{$uid} = 1;
my $value = $rec[2]; #アクセス数はレコードの3フィールド目から取得
#ハッシュcross_orgの１こめのキーワード$uidと、２個目のキーワード $shop_nameの値に、$valueをセットする
$cross_org{$uid}{$shop_name} = $value;
}
#ハッシュshopsのキーワードの数を確認する。
my $val = keys %shops;
#print "ショップ数=\t$val\n";
#ハッシュ%uidsのキーワードの数を確認する。
my $uu = keys %uids;
#print "ユーザー数=\t$uu\n";
#uidをソートしたものを配列にする。
my @uid_list = sort keys %uids;
#カテゴリをソートしたものを配列にする。
my @shop_list = sort keys %shops;
#最初にヘッダを出力
#join関数は、join(区切り文字、配列）で、配列の中身を１つずつ区切り文字で区切って出力
print join("\t",'uid',@shop_list) ."\n";#ピリオド(.)は、文字列の結合演算子
#-----クロス表の形式に出力する
foreach my $i (@uid_list){
#１ユーザーについて、１人分ずつcross_tableという配列をつくる
my @cross_table;
# @cross_tableの１要素目にユーザー名を格納
push @cross_table,$i;
foreach my $j (@shop_list){
#次に、カテゴリ要素のアルファベット順ごとに、ハッシュ%cross_orgを参照する
my $nn = $cross_org{$i}{$j};
push @cross_table,$nn;
}
#1ユーザー分の値の出力
print join("\t",@cross_table)."\n";
}
最終的なアウトプット
| uid | サプリメント | 健康グッズ | 収納家具 | 書籍 | スキンケア | ファッション | ... |
|---|---|---|---|---|---|---|---|
| x00001 | 1 | 1 | 1 | 0 | 0 | 0 | 0 |
| x00002 | 0 | 0 | 0 | 1 | 1 | 1 | 0 |
| x00003 | ... | ... | ... | ... | ... | ... | ... |
SQLではなくPerlで集計処理を行うメリット
- 集計が必要なファイル群の総容量が巨大すぎてローカルPCデータベースソフトに入れるのが現実的ではない場合の処理が可能です。
- SQLソフトに比べてかなり高速に結果を得られます。
- 入力データのフィールド数が可変だとSQLソフトにインポートするためには、いちいち整形処理を行わねばなりませんが、最初からPerlで集計ができればその分速く処理できます。
- SQLでクロス集計は可能ですが、データが重たいと遅いです。
- （このページの例にある入力データは可変のものを固定フィールドに整形してあります）
参考資料
- 木本裕紀『業務に役立つPerl』、技術評論社、２０１5年第2刷版、pp.125〜pp.126.
- 13. ハッシュの活用：柔軟なデータ集計