Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
7
Help us understand the problem. What is going on with this article?
@hoxo_m

dplyr で DB にクエリを投げる時に CASE 式を使いたい #rstatsj

More than 5 years have passed since last update.

dplyr でデータベースにクエリを投げるときに CASE 式を使いたくなりましたが、はて、どうやるんだろうと悩みました。

まずは検証用の DB を用意します。
nycflights13 パッケージの flights データを使いましょう。

R
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
R
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() を使って行います。

R
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() 関数を使って書いてみましょう。

R
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 という関数は無いという内容のようです。
クエリを見てみましょう。

R
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 文を使えとのことです。

やってみましょう。

R
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

おー、できました!
クエリを見てみましょう。

R
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のベクトルしか受け取ることができないので、次のように書くと警告が出ます。

R
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 ベクトルの最初の値しか評価しないよ、ということです。

したがって、次のように書くと、おかしなことが起こります。

R
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() を使います。

R
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

関連

7
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
hoxo_m
ホクソエム (hoxo_m) は架空のデータ分析者であり、日本の若手のデータ分析者集団のペンネームである。当初このデータ分析者集団は秘密結社として活動し、ホクソエムを一個人として活動させ続けた。
hoxom
Machine Learning and Data Analysis Company for Your Smiles :)

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
7
Help us understand the problem. What is going on with this article?