dplyr でデータベースにクエリを投げるときに CASE 式を使いたくなりましたが、はて、どうやるんだろうと悩みました。
まずは検証用の DB を用意します。
nycflights13 パッケージの flights データを使いましょう。
library(dplyr)
library(nycflights13) # flights データを使用
db <- src_sqlite("my_db.sqlite3", create = TRUE)
copy_nycflights13(db)
show(db)
src: sqlite 3.8.6 [my_db.sqlite3]
tbls: airlines, airports, flights, planes, sqlite_stat1, weather
flights_tbl <- tbl(db, "flights")
glimpse(flights_tbl)
Observations: 336776
Variables:
$ year (int) 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 20...
$ month (int) 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,...
$ day (int) 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,...
...
flights データは飛行機の発着データですが、今回は month カラムのみを使います。
month カラムには 1～12 の数字が入りますが、これを上半期(first_half)と下半期(second_half)に変換したいとします。
R では通常 ifelse() を使って行います。
month <- 1:12
half_year <- ifelse(month <= 6, "first_half", "second_half")
print(half_year)
[1] "first_half" "first_half" "first_half" "first_half" "first_half" "first_half"
[7] "second_half" "second_half" "second_half" "second_half" "second_half" "second_half"
そこで、dplyr でも ifelse() 関数を使って書いてみましょう。
q <- flights_tbl %>%
select(month) %>%
mutate(half_year = ifelse(month <= 6, "first_half", "second_half"))
data <- q %>% collect
Error in sqliteSendQuery(conn, statement) :
error in statement: no such function: IFELSE
エラーが出ました。IFELSE という関数は無いという内容のようです。
クエリを見てみましょう。
q$query
<Query> SELECT "month" AS "month", IFELSE("month" <= 6.0, 'first_half', 'second_half') AS "half_year"
FROM "flights"
<SQLiteConnection>
ifelse() の部分がそのまま SQL に変換されています。これでは通らないはずですよね。
解答
解答は下記の Stack Overflow に載っていました
Try using if(A=="foo") B else 0 - dplyr will try to convert that to a SQL case statement, which might work for you. – hadley
なんと、神 Hadley から回答がついていました。
dplyr で CASE 式を使うには if 文を使えとのことです。
やってみましょう。
q <- flights_tbl %>%
select(month) %>%
mutate(half_year = if(month <= 6) "first_half" else "second_half")
data <- q %>% collect
data %>% sample_n(6) %>% arrange(month)
month half_year
1 1 first_half
2 2 first_half
3 6 first_half
4 7 second_half
5 9 second_half
6 12 second_half
おー、できました！
クエリを見てみましょう。
q$query
<Query> SELECT "month" AS "month", CASE WHEN "month" <= 6.0 THEN 'first_half' ELSE 'second_half' END AS "half_year"
FROM "flights"
<SQLiteConnection>
ちゃんと CASE 式に変換されています！
注意点
ところで、R では if() は長さ1のベクトルしか受け取ることができないので、次のように書くと警告が出ます。
month <- 1:12
half_year <- if(month <= 6) "first_half" else "second_half"
Warning message:
In if (month <= 6) "first_half" else "second_half" :
the condition has length > 1 and only the first element will be used
この警告は、if() は長さ1のベクトルしか評価できないので、month ベクトルの最初の値しか評価しないよ、ということです。
したがって、次のように書くと、おかしなことが起こります。
q <- flights_tbl %>% select(month)
data <- q %>% collect %>%
mutate(half_year = if(month <= 6) "first_half" else "second_half")
data %>% sample_n(6) %>% arrange(month)
month half_year
1 1 first_half
2 4 first_half
3 5 first_half
4 8 first_half
5 9 first_half
6 10 first_half
これは、half_year に最初の一個の評価値が入るため、すべて同じ値になってしまっているのです。
dplyr でこれを書きたいときは、今度は ifelse() を使います。
q <- flights_tbl %>% select(month)
data <- q %>% collect %>%
mutate(half_year = ifelse(month <= 6, "first_half", "second_half"))
data %>% sample_n(6) %>% arrange(month)
month half_year
1 4 first_half
2 5 first_half
3 6 first_half
4 9 second_half
5 10 second_half
6 10 second_half
おー、できました！
どうやら、クエリの中では if()、外では ifelse() を使い分けなければいけないみたいです。
はまりどころかと思います。
以上です。
追記 2016/05/13
dplyr 次バージョンで
if_else()という関数が追加されるようで、これは data.frame と DB を統一的に扱えるようです。
http://notchained.hatenablog.com/entry/2016/04/09/123059