sqldfライブラリを使ってRでSQLコマンドを使う 日本語もOK

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を使いたい時に使います。

参考になったページ

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