RとPythonによるデータマイニング

企業でデータ分析などやっています。主にRやPythonによるデータマイニング・機械学習関連の話題と日々の日記やその他備忘録について書いてます。

[R]SQLクエリでRのデータを加工・集計できるパッケージ「sqldf」

私が分析を行う際、データ加工や集計作業は基本的にSQLで行い、分析やモデル作成はRで行うことが多いです。
しかし、DBが使えないような場合やちょっとした集計などRでデータを加工・集計したい場合があります。

RでSQLで行うようなデータ加工・集計を行うには、基本的にsubsetやorder、merge、aggregateといった関数を利用します。
SQLとRの関係については、以下のページがわかりやすいです。
 http://d.hatena.ne.jp/a_bicky/20110529/1306667230
ただ、こういった関数はよく使い方を忘れてしまい、Webなりヘルプなりを使うたびに調べるなんてことが起こります。
正直、かなり面倒なわけです。

そこでSQLクエリを使って直接Rのデータを加工できたら良いなーと思うわけですが、
「sqldf」パッケージを用いれば直接SQLを利用してデータを扱うことが可能です。

基本的な使い方は、以下の通りです。

sqldf("SQLクエリ")

SQLクエリ」部分にクエリを書くわけですが、データテーブルとしてR内で生成したデータフレームを用いることができます。

以下、sqldfを利用したデータ加工のサンプルです。

install.packages("sqldf")
library(sqldf)

head(CO2)
summary(CO2)

# 10件抽出
sqldf("SELECT * FROM CO2 limit 10")

# WHEREとORDER BY
sqldf("SELECT Plant, conc, uptake FROM CO2 WHERE Plant IN ('Qn1','Mn1') ORDER BY uptake")

# GROUP BY
sqldf("SELECT Type, COUNT(*) AS CNT FROM CO2 GROUP BY Type")
sqldf("SELECT Plant, Type, COUNT(*) AS CNT, SUM(uptake) AS sum_uptake FROM CO2 GROUP BY Plant, Type")

# DISTINCT
sqldf("SELECT DISTINCT Plant FROM CO2")

set.seed(123)
dat1 <- CO2[sample(1:nrow(CO2),10),]
dat2 <- CO2[sample(1:nrow(CO2),10),]
dat1
dat2

# INNER JOIN
sqldf("SELECT * FROM dat1 AS A INNER JOIN dat2 AS B ON A.Plant = B.Plant")
# LEFT JOIN
sqldf("SELECT * FROM dat1 AS A LEFT JOIN dat2 AS B ON A.Plant = B.Plant")


データをどのように処理しているかというと内部でSQLiteを動かしているらしいので、DB依存の関数には注意しましょう。
もしくはオプションを用いて利用するDBをSQLiteから外部DBに変更することで対応可能です。
その辺の話やsqldfについての詳細はhelpを見るか、以下のページの解説がわかりやすいと思います。
 http://ameblo.jp/wdkz/entry-11115437591.html

注意点としては、以下のようなものがあります。
カラム名にカンマが含まれている場合エラーが起こる。
カラム名に日本語が含まれているとエラーが起こる場合がある。
・RPostgreSQLなどのDB関連パッケージがロードされている場合、内部DBとしてSQLiteよりそちらが優先される。
 (外部DBの利用を意図していない場合、ライブラリをunloadすれば良いです。コマンドはdetach("package:RPostgreSQL", unload=TRUE))


SQLでの集計に慣れている人は、このパッケージで作業が大分楽になるのではないでしょうか。