RでデータフレームをSQLで操るsqldfの紹介 | wdkzのブログ

wdkzのブログ

ブログの説明を入力します。

R Advent Calendar 2011の24日目です。さて一昨日、R2.14.1がリリースされたのですが皆さんはもう使っていますか?この新しいバージョンでは遂にparallelライブラリのdetectCores()メソッドをSolarisOSで使用した時でも、物理コアの数字を正確に返すようになりました。Solarisユーザの皆さんやほーいですね。

さて、冗談はさておき今日はRのsqldfライブラリについての話をします。

  RやSASなどの統計解析言語(というか)実行環境は多数の関数が実装されていてどのような手法であれとりあえずすぐに使ってみることができるというのが魅力であるのは間違いありません。しかし、とりあえず使ってみようとしても手持ちのデータをまずは関数に突っ込める形に変形しなければいけません。ここのデータ変形がうまくいかなくてイライラして窓からWindowsマシンを投げ捨ててしまった人が何人もいることを私は知っています。


頭痛はRで乗り越えろ

  最近、「頭痛はRで乗りこえろ」ってCMが頻繁にされてますが、むしろこのRが頭痛のタネになってる人が相当数いるんじゃないかって思ってます。ということで、この頭痛のタネをみんなで一緒にRで乗りこえていきましょう。

  そのために私が強力に推すのはsqldfというライブラリです。このライブラリはその名の通り、「SQLでデータフーレムを操ろう」というものでしてGoogle Summer Codeの成果物の一つです。では早速使ってみましょう。
  と、その前にサンプルデータを作成します。


#仮想ユーザ数を設定

user_num <- 1000 #1000人のユーザ

 

#1000人の仮想idを作成
set.seed(111224);user_ids <- apply(data.frame(round(runif(user_num)*15)+2),1,function(x){paste(letters[round(runif(x)*26)],sep="",collapse="")})

 

#1000人分の仮想login情報を作成
#1ユーザあたりmax365個ぐらい(1年分を想定)

set.seed(111224);login_list <- apply(data.frame(round(runif(user_num)*365)),1,function(x){as.character(as.Date("2011-12-24") - sort(round(runif(x)*365),decreasing=TRUE))})
user_login <- data.frame(id=rep(user_ids,(unlist(lapply(login_list,length)))), login_day=unlist(login_list))

 

#1000人分の仮想プロフィール作成
set.seed(111224);sex <- round(runif(user_num));region <- round(runif(user_num)*47);age <- round(runif(user_num)*35)+15
user_profile <- data.frame(id=user_ids, sex=sex, region=region, age=age)

 

 

ここまではサンプルデータを生成しただけなので深く考えないで下さい。これでuser_loginとuser_profileという2つのデータフレームが生成されました。ちょっと中身を見てみましょう。


head(user_profile)

               id sex region age
1    rjwwqekzxrsy   1     33  29
2 mylghtlziserhpv   1     18  36
3  kvpwtbtnaiqsji   1     42  32
4    eflgllbnbduj   1     42  28
5             ahe   0     31  16
6      ysdvtvmvhq   1      9  46


 

user_profileデータフレームにはユーザのid、性別、居住地、年齢といったプロフィール情報が入っています。1ユーザ1行のデータフレームとなっています。


head(user_login, 239)
                 id  login_day
1      rwurcmdtzprp 2011-09-12
2      rwurcmdtzprp 2011-09-13
3      rwurcmdtzprp 2011-09-14

71  mkhausgantkzpux 2011-09-12
72  mkhausgantkzpux 2011-09-13
73  mkhausgantkzpux 2011-09-13
74  mkhausgantkzpux 2011-09-15

158  wtrrtyexrqsooi 2011-09-13
159  wtrrtyexrqsooi 2011-09-13
160  wtrrtyexrqsooi 2011-09-13

237    imancodawcva 2011-09-12
238    imancodawcva 2011-09-12
239    imancodawcva 2011-09-14

 

user_loginデータフレームにはユーザのidとログイン日の情報が入っています。実際の現場では時間の情報もあると思いますがめんどくさかったので省略です。1ユーザ複数行のデータフレームとなっています。
  ここからsqldfの登場です。後述しますが、このパッケージは外部dllとかも使っているのにインストールがinstall.packagesだけで良いというのも魅力の一つです。


install.packages("sqldf")
require(sqldf)



まずはユーザ毎にログインした数をカウントしてみましょう。


user_login_cnt <- sqldf("select id, count('login_day') login_cnt from user_login group by id")
head(user_login_cnt)
                  id login_cnt
  1            aams        40
  2 aaxkgmebwgnerfd       315
  3   abebkiijjctvi       264
  4   abrwfhycfvwcp       266
  5             aca        28
  6            acid        39


↑この例のようにRのデータフレーム名をRDBのテーブル名、Rのデータフレーム中のカラム名をRDBのテーブルのカラム名として考えてSQL文を書き、それをsqldf("")の中に入れて実行すればそのSQL実行結果がRのデータフレームとして返ってきます。これは捗るなって思った人、その通りです早速今日から使ってみましょう。


ちなみに、このsqldfですがどう動いているかと言うと内部でSQLiteというRDBを(ユーザからは見えないように)使用しています。Rのデータフレームを一度SQLite内のテーブルとしてインポートして、そのテーブルに対してsqldf()内のSQL文を実行して得られたテーブルをRのデータフレームとして返しています。このSQLite中に作られるテーブルは通常の場合だと都度都度消されるので、インデックスを作成して高速にデータを取得したい時は以下のようにします。


user_login_cnt <- sqldf(c("create index user_login_idx on user_login(id)", "select id, count(login_day) login_cnt from user_login group by id"))


 

まずsqldf("create index ...")して、、次にsqldf("select ....")して、、、って書いてもcreate indexした側からそのindexが消されるので注意して下さい。

なお、sqldfはデフォルトだと内部的にSQLiteを利用しているのですがその他にH2DB, MySQL, PostgreSQLも使用することができるようです。試しにH2DBを使ってみましょう。


require("RH2")
sqldf("select id, count('login_day') login_cnt from user_login group by id")
head(user_login_cnt, drv="H2")


 

system.time()とかで計測するとわかるのですが、H2DBを利用する場合はSQLiteを利用する場合に比べてだいぶ遅いです。ですので、普通にSQLiteを使っておくほうがいいでしょう。H2DBの利点は関数が豊富な点なので、あえてSQL内でsd値を算出したい時などはH2DBを使う意味があるでしょう。


話がちょっと逸れましたね、ユーザ毎のログイン数を出すという処理をやったわけですがこれをsqldfを使わずに書くとこうなります。


user_login_cnt2 <- data.frame(table(user_login$id))

 


次に、ユーザ毎のログイン数を2011年度に限ってカウントしてみましょう。


user_login2011cnt <- sqldf("select id, count('login_day') login_cnt from user_login where login_day >= '2011-04-01' group by id")
               id login_cnt
1            aams        26
2 aaxkgmebwgnerfd       221
3   abebkiijjctvi       201
4   abrwfhycfvwcp       201
5             aca        19
6            acid        32

 

#どうでもいいことですが4月1日は私の結婚記念日です。

#この処理をsqldfを使わずに書くと

user_login2011cnt2 <- data.frame(unlist(by(user_login, as.factor(user_login$id), function(x){nrow(x[as.Date(x$login_day) >= as.Date('2011-04-01'),])}, simplify=FALSE))); colnames(user_login2011cnt2) <- "login_cnt"



ユーザ毎のlogin数を算出した上で、プロフィール情報と結合してみましょう。


user_login_cnt_profile <- sqldf("select a.id, b.sex, b.region, b.age, a.login_cnt from (select id, count(login_day) login_cnt from user_login group by id ) a, user_profile b where a.id = b.id")
                 id sex region age login_cnt
  1            aams   0     47  27        40
  2 aaxkgmebwgnerfd   1     33  28       315
  3   abebkiijjctvi   1     18  31       264
  4   abrwfhycfvwcp   1     45  43       266
  5             aca   0     39  42        28
  6            acid   0     33  41        39

 
 

#この処理をsqldfを使わずに書くと
user_login_cnt <- data.frame(table(user_login$id)); colnames(user_login_cnt) <- c("id", "login_cnt")
user_login_cnt_profile2 <- cbind(user_profile[which(user_profile$id %in% user_login_cnt$id),], login_cnt=user_login_cnt[rank(user_profile[which(user_profile$id %in% user_login_cnt$id),"id"]),"login_cnt"])



ユーザ毎に1日にログインした数が最も多い日のログイン数を取得してみましょう。


user_login_max <- sqldf(c("create index id_day_idx on user_login(id, login_day)", "select a.id, max(login_cnt) max_cnt from (select id, login_day, count(1) login_cnt from user_login group by id, login_day) a group by a.id"))
                 id max_cnt
  1            aams       2
  2 aaxkgmebwgnerfd       4
  3   abebkiijjctvi       5
  4   abrwfhycfvwcp       4
  5             aca       1
  6            acid       2

 

#この処理をsqldfを使わずに書くと
user_login_max2 <- data.frame(apply(table(user_login$id, user_login$login_day),1,max))



ユーザ毎に1日にログインした数が最も多い日のログイン数と、該当日が何日あったかを算出してみます。こういった混み入った処理の場合、一旦テンポラリテーブルとか作った方がやりやすいのですね。sqldfは通常はテーブル等を作った側から消し去っていくんですが、テンポラリテーブルとか使いたい場合は以下のようにすればテーブルを保持したままにしてくれます。


sqldf() #よくわからんけど、これでNULLが返ってこなくなるまで実行する(たぶん2回)
sqldf("attach 'mydb' as new") #mydbという名でデータベースを作成して
sqldf("create table mytab as select id, login_day, count(1) login_cnt from user_login group by id, login_day", dbname = "mydb") #日にち毎のログイン数をカウントしたテンポラリテーブルを作成しておいて
user_login_info <- sqldf("select a.id, a.max_cnt, count(b.login_day) day_cnt from ( select id, max(login_cnt) max_cnt from mytab group by id) a, mytab b where a.id = b.id and a.max_cnt = b.login_cnt group by a.id, a.max_cnt", dbname="mydb")



最後の例ですが、ユーザ毎の最後のログイン空白日数の算出をしてみましょう。最後のログイン空白日数とはつまり最後にログインした日と最後から2番目にログインした日の日差ですね。このくらい複雑になるとSQLでやるには限界があるのでsqldfを使わずに何とかしなければいけなくなります。


user_login$id <- as.factor(user_login$id)
user_login$login_day <- as.Date(user_login$login_day)
user_last_blank <- data.frame(tapply(user_login$login_day, user_login$id, function(x){as.integer(diff(sort(unique(x),decreasing=TRUE)[2:1]))}))


↑この例を見るとうっすらわかるのですが、SQLでいうところのgroup byはtapplyを使って書くことができます。そして実はこういったtaaply処理はplyrライブラリ中のddply関数を使えば並列処理化できて速く結果を得ることができます。(しかも、プログレスバー付き)


require(plyr)
require(doSMP)
workers <- startWorkers(2)
#物理コア数に合わせる
registerDoSMP(workers)
user_last_blank <- ddply(.data=user_login, .variables="id", .fun=function(x){as.integer(diff(sort(unique(x$login_day),decreasing=TRUE)[2:1]))}, .parallel=TRUE, .progress="text")
stopWorkers(workers)

 

                user_last_blank
aams                          9
aaxkgmebwgnerfd               1
abebkiijjctvi                 1
abrwfhycfvwcp                 1
aca                          11
acid                          3


 

sqldfライブラリには今まで散々使ってきたsqldf関数の他にread.csv.sqlという関数もあります。これはread.csvみたいな関数なのですが、読み込み対象行をSQLの文法で指定できるという関数です。



write.csv(user_login, file="user_login.csv", row.names=FALSE, quote=FALSE) #サンプルCSVファイルを書き出す
user_login2011 <- read.csv.sql(file="user_login.csv", sql="select * from file where login_day >= '2011-04-01'") #2011年度の行だけを読み込む



ただし、この関数は該当行だけ読み込むのでメモリ節約になるというわけでは ありません。 一旦、すべてのデータをRDB上のテーブルに(Rのデータフレームを介することなく)インポートしておいてからSQLで抽出し該当行だけを含んだRのデータフレームとして返しています。じゃあread.csvで全件をRのデータフレームに読み込んでおいてsqldf()でいいんじゃ?という声も聞こえてきそうですね。同じ量のデータをRのデータフレームとしておくより、SQLiteのテーブルとしておくほうがメモリ使用量が少ないようですので、大きなサイズのcsvファイルがあるんだけどその中で解析対象とすべき行数はあらかじめ絞れることが明確な場合は使った方がいいかもしれません。しかし、ダブルクォーテーションが含まれてるとうまく動作しなかったり、何より私の環境ではexampleの通りにやってもまったく動かなかったりといった致命的な何かがあります。

 

最後になりますが、魅力的なライブラリのsqldfですが使用するにあたって注意すべき点が2つあります。①カラム名とデータフレーム名にピリオドが含まれていると何かと大変なことになる。②カラム名とデータフレーム名に2バイトコード文字が含まれているとうまく動作しないことが多々ある。ということです。両者に共通することですが特にindexを作成して云々しようとしたり、group byしたりするときに特にハマることになるのではじめから1バイト文字列でピリオドを使わない文字列に変更しておくほうが捗ります。


今日の記事はここまでです。
それでは皆様に素敵なサンタクロースが現れますように!