SQLでの集計、抽出に慣れているとRのデータフレームの集計にややイライラを感じるのは私だけではないと思います。sqldfパッケージを使うと、Rのデータフレームに対してSQLコマンドが使えるのでとても便利です。Overlapさんのブログに詳しく書かれていますが、SELECTした結果は１列であってもカラム名付データフレームになり、取得後の使用に若干戸惑ったため、備忘録的にR内での使い方をまとめました。なお、日本語の扱いはOKでした。
sqldfパッケージの読み込み
sample.r
#--------------------------------------------------
#SQLコマンドをRで使う
#--------------------------------------------------
install.packages("sqldf")
library(sqldf)
SQL命令は（ ）の中にダブルクォーテーションで囲った上で記述します。
sqldf("SQL文")
日本語はシングルクォーテーションまたはダブルクォーテーションで囲います。以下は完全一致の条件文の書き方です（↓）
sqldf("SELECT ’日本語' AS var1")
sqldf("SELECT 列名 FROM データフレーム名 WHERE 列名 ==\'検索文字列\'")
sqldf("SELECT 列名 FROM データフレーム名 WHERE 列名 ==\"検索文字列\"")
RでSQLを使ってみた例
サンプルデータフレーム
shoplistというサンプルデータフレームを考えます。
連番(shop_id）と、カテゴリ付き店名の文字列からなるshopnameの列があります。
Step.1 「コーヒーチェーン店」という文字列を含むshopnameを検索し、条件に一致したshop_idを抽出し、res_1に格納します。
#---------------------------------------------------------
# SQLのコマンドをRで使うライブラリsqldfを使う
#---------------------------------------------------------
#「コーヒーチェーン店」を含む文字列を抽出する。
res_1<-sqldf("SELECt shop_id FROM shoplist WHERE shopname LIKE \"％コーヒーチェーン店%\"")
SELECTの結果、「コーヒーチェーン店」を含むshopname列に対応するshop_idを取得できました。結果はres_1というデータフレームの「shop_id」カラムに格納されています。
Step.2 shoplistデータフレームに「コーヒーチェーン店」カラムを作成します。
#コーヒーチェーン店という列を追加し、0をデフォルトで入れる
shoplist$コーヒーチェーン店<-0
Step.3 データフレームshoplistのコーヒーチェーン店列のうち、res_1$shop_idで取得したのと一致する行のみ、1のフラグを立てます。
shoplist$コーヒーチェーン店[res_1$shop_id]<-1
Step.4 確認します。
shoplistのコーヒーチェーン列＝＝１となっている行のみ画面に書かせます。
shoplist[shoplist$コーヒーチェーン店==1,]
無事、shopnameに「コーヒーチェーン店」を含む行のみ、フラグが立てられました。
この方法が役にたった課題
- 長い商品名や店舗名のデータがあるが、上位の階層のカテゴリ名が不規則に文字列に含まれているため、上位階層の文字列を決めて、カテゴリを割り当ててる必要がありました。そこで、各商品名や店舗名を上位カテゴリ名に読み替える対応テーブルを作成しました。
- SQLソフトウェアで対応テーブルを作ったほうが速いでしょうが、商品購買テーブルや利用店舗テーブルはRのデータフレームとして存在するため、対応テーブルもRで作成することで、カテゴリを読み替えた商品購買テーブル、利用店舗テーブルの再集計が手早く行えるようになりました。
備考
- postgreSQLに存在するテーブルをRから呼び出して使うはこちらです。RPostgreSQLライブラリを使います。
- sqldfライブラリはRのデータフレームに対してSQLを使いたい時に使います。