顔妻です。
今回はRでSQLの記法を使って集計することができる、sqldfというRのライブラリについてです。
私の会社もそうですが社内に溜められているログは商用のAWSやGCPやオープソースのPostgreSQL、MySQL等で管理されていることが多いのではないでしょうか？そのような環境の中で必要な分のデータだけを抽出することも多いと思います。そして、Rには集計のための優れたパッケージが多くありますがSQL文法を使えたらなーなんていう、そんな願いを叶えるこのライブラリについてご紹介をさせて頂きます。ちなみに今回はSQL文法の説明は一切する予定はありませんが、分析でSQLを活用したい方はこちらにおすすめの書籍がありますので参考にしてみてください。
sqldfのインストール
# パッケージのインストール
install.packages("sqldf")
# パッケージの呼び出し
library(sqldf)
sqldfの使い方その①〜SQLite〜
それでは、早速Rに組み込まれてるirisのデータを使って集計のテストを行って見ましょう。
デフォルトで組み込まれているのはSQLiteになります。少し利用頻度が下がるような関数とサブクエリ、joinが使えますが、window関数が使えないという欠点があります。（代わりにRで処理してしまえばそんなに問題でもないですが。。。）
また、このパッケージを使うときの注意点ですが、Rで保持しているテーブルの列名にカンマが入っているとSQLの仕組みの関係でエラーとして処理が行われなくなってしまいますので事前に下処理を行うようにしましょう。
library(sqldf)
# クエリの記載
sqldf("
select
*
from
datamart
limit 10
")
# エラーを回避するために列名に入ってる「.」を「_」を変更
colnames(datamart) <- c("Sepal_Length","Sepal_Width","Petal_Length","Petal_Width","Species")
sqldf("
select
Species
, sum(Petal_Width) as Petal_Width_sum
, avg(Petal_Width) as Petal_Width_avg
, min(Petal_Width) as Petal_Width_min
from
datamart
group by
Species
")
sqldfの使い方その②〜PostgreSQL〜
次はSQLiteに代わるドライバーとしてPostgreSQLを利用する方法のご紹介です。SQLiteでは関数の種類が相対的に少なく、window関数も使えないため普段の実務で使っているSQLと比較すると痒い所に手が届かない気がする方もいるのではないでしょうか。もちろん、オープンソースとしてMySQLも使えるのですが今回は私が分析実務で利用している方でお伝えいたします。
本来は元々組み込まれているSQLite以外を利用する場合には「user」「password」「host」をそれぞれ指定してあげないといけないのですが、利用するドライバーが「PostgreSQL」の場合には別途PostgreSQL側の設定が必要ですがデフォルトでそれぞれ「postgres」「postgres」「test」として利用することができます。
また、PostgreSQL特有なのですが、列名やテーブル名は基本的に小文字として扱うため、R内で作成したテーブルに大文字が使われている場合は「” “」で囲ってあげる必要があります。※カラム内のデータは影響ありません。そして、クエリは「’ ‘」で囲ってあげてください。
library(sqldf)
datamart02 <- sqldf('
select
*
, tblA."Sepal_Length" - "Sepal_Length_avg" as Residual_error
from(
select
"Species"
, "Sepal_Length"
, avg("Sepal_Length")over(partition by "Species") as "Sepal_Length_avg"
from
datamart
) as tblA
', drv="PostgreSQL")
head(datamart02 ,50) # 50行まで出力
sqldf利用のまとめ
このパッケージは普段SQLに慣れているという前提での利用が主になりますが、普段使っている方はかなり便利なツールとなるのではないでしょうか？SQLと組み合わせて使う方法としてODBC接続をしたり、SQL自体を操作するライブラリもあったりするのですが、テーブルを新規で作ったり、プロセスをまとめて実行できないのでヒューマンエラーにより気を付けなければいけなかったりと工数が雪だるまで多くなるためRのスクリプト内で完結できるのは様々なメリットだと思いますので是非使ってみてください。
