dplyr でDBのデータをUNIONする

R

dplyrというかdbplyrというべきか

Rでデータフレームを縦方向に結合するときは rbind か dplyr::bind_rows がよく使われる。 だが、dplyrでDBに接続しているとき、DBにあるデータを結合するときに上記の関数ではうまくいかない。

結論から述べると、dplyr::union を使えばOK。SQLのUNIONと同じなので覚えやすい。 dplyr::union_all もあり、SQLのUNION ALLと同じで、重複を削除しない。

また、dplyr::union は引数を2つしか取らないため、3つ以上のデータをUNIONしようとしても、最初の2つ以外は無視される。 これにはいくつか対処方法があり、自分がやってみた方法をメモ代わりに書いてみる。

以下に例を記す。コンソールの結果を貼っているので読みにくいかもしれないが、RのソースはこちらのGistにもあるので、お好みでそちらもどうぞ。

まずはDBの準備。 ここでは例として、attenuデータを使う。

SQLiteにattenuデータフレームをいったん格納し、 もう一度Rから読み込む。

# install.packages("DBI")
# install.packages("RSQLite")
library(dplyr)

# DB接続
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")

# DBにローカルのデータフレームをコピー
dplyr::copy_to(con, attenu, "attenu", temporary = FALSE)

# テーブル読み込み（copy_toの返り値を使ってもよい）
tbl_attenu <- dplyr::tbl(con, "attenu")

ではまず、eventカラムの値別にデータを読み込み、3種類のオブジェクトを作ってみる。 これらを縦方向に結合したい。

> # 3種類のデータを読み込んでみる
> (tbl_e1 <- tbl_attenu %>%
+ filter(event==1))
# Source: lazy query [?? x 5]
# Database: sqlite 3.19.3 []
  event mag station dist accel
  <dbl> <dbl> <chr> <dbl> <dbl>
1 1 7 117 12 0.359
>
> (tbl_e8 <- tbl_attenu %>%
+ filter(event==8))
# Source: lazy query [?? x 5]
# Database: sqlite 3.19.3 []
  event mag station dist accel
  <dbl> <dbl> <chr> <dbl> <dbl>
1 8 5.3 111 19 0.086
2 8 5.3 116 21 0.179
3 8 5.3 290 13 0.205
4 8 5.3 112 22 0.073
5 8 5.3 113 29 0.045
>
> (tbl_e11 <- tbl_attenu %>%
+ filter(event==11))
# Source: lazy query [?? x 5]
# Database: sqlite 3.19.3 []
  event mag station dist accel
  <dbl> <dbl> <chr> <dbl> <dbl>
1 11 7.7 2714 45 0.11
2 11 7.7 2708 145 0.01
3 11 7.7 2715 300 0.01

rbind や dplyr::bind_rows ではうまくいかない

> rbind(tbl_e1, tbl_e8)
  src ops
tbl_e1 List,2 List,4
tbl_e8 List,2 List,4

> dplyr::bind_rows(tbl_e1, tbl_e8)
Error in bind_rows_(x, .id) :
  Argument 1 must be a data frame or a named atomic vector, not a tbl_dbi/tbl_sql/tbl_lazy/tbl

dplyr::union だとうまく結合できる。 ご存知かとは思うが、UNION と UNION ALLの結果の違いも載せておく。

> # Success
> dplyr::union(tbl_e1, tbl_e8)
# Source: lazy query [?? x 5]
# Database: sqlite 3.19.3 []
  event mag station dist accel
  <dbl> <dbl> <chr> <dbl> <dbl>
1 1 7.0 117 12 0.359
2 8 5.3 111 19 0.086
3 8 5.3 112 22 0.073
4 8 5.3 113 29 0.045
5 8 5.3 116 21 0.179
6 8 5.3 290 13 0.205
>
> # UNION と UNION ALL の違い
> dplyr::union_all(tbl_e1, tbl_e1)
# Source: lazy query [?? x 5]
# Database: sqlite 3.19.3 []
  event mag station dist accel
  <dbl> <dbl> <chr> <dbl> <dbl>
1 1 7 117 12 0.359
2 1 7 117 12 0.359
> dplyr::union(tbl_e1, tbl_e1)
# Source: lazy query [?? x 5]
# Database: sqlite 3.19.3 []
  event mag station dist accel
  <dbl> <dbl> <chr> <dbl> <dbl>
1 1 7 117 12 0.359

で、ここからが主題なのだけど、dplyr::union は引数を2つだけしかとらず、 3つめ以降は無視される。無視て。

以下の例では、event=11 が結合できていない。

> # 注意：UNIONは最初の2つの引数しか受け付けない
> dplyr::union(tbl_e1, tbl_e8, tbl_e11)
# Source: lazy query [?? x 5]
# Database: sqlite 3.19.3 []
  event mag station dist accel
  <dbl> <dbl> <chr> <dbl> <dbl>
1 1 7.0 117 12 0.359
2 8 5.3 111 19 0.086
3 8 5.3 112 22 0.073
4 8 5.3 113 29 0.045
5 8 5.3 116 21 0.179
6 8 5.3 290 13 0.205

こういうときの解決策1として、いったんリストにするという方法がある。 リストをまとめてUNIONするには、Reduce 関数を使う。他にも方法ありそう。

> # ３つ以上をUNIONしたいときは、リストをReduceでUNIONする
> tbl_list <- list(tbl_e1, tbl_e8, tbl_e11)
> Reduce(dplyr::union, tbl_list)
# Source: lazy query [?? x 5]
# Database: sqlite 3.19.3 []
  event mag station dist accel
  <dbl> <dbl> <chr> <dbl> <dbl>
1 1 7.0 117 12 0.359
2 8 5.3 111 19 0.086
3 8 5.3 112 22 0.073
4 8 5.3 113 29 0.045
5 8 5.3 116 21 0.179
6 8 5.3 290 13 0.205
7 11 7.7 2708 145 0.010
8 11 7.7 2714 45 0.110
9 11 7.7 2715 300 0.010

うまくできている。

さて、リストとReduceと聞いて思い浮かべるのは、foreach パッケージである。 わざわざ各々のオブジェクトを作らなくても、for文のように以下のように書くと便利だ。

> # foreachを使うと便利
> library(foreach)
> events <- c(1, 8, 11)
> tbl_list <- foreach::foreach(
+ x=events,
+ .combine = list,
+ .multicombine = T # 3つ以上をリストにする場合はTRUEにする必要がある
+ ) %do% {
+ dplyr::tbl(con, "attenu") %>%
+ filter(event == x)
+ }
> Reduce(dplyr::union, tbl_list)
# Source: lazy query [?? x 5]
# Database: sqlite 3.19.3 []
  event mag station dist accel
  <dbl> <dbl> <chr> <dbl> <dbl>
1 1 7.0 117 12 0.359
2 8 5.3 111 19 0.086
3 8 5.3 112 22 0.073
4 8 5.3 113 29 0.045
5 8 5.3 116 21 0.179
6 8 5.3 290 13 0.205
7 11 7.7 2708 145 0.010
8 11 7.7 2714 45 0.110
9 11 7.7 2715 300 0.010

ここで、foreach関数の .combine に union を入れることで、いきなり UNION できるので非常におすすめ。 ちなみに、先ほどとは違って、ここでは .multicombine=F とする必要があるので注意。

> # foreachを使うと便利
> tbl_result <- foreach::foreach(
+ x=events,
+ .combine = dplyr::union,
+ .multicombine = F # 今度はFALSEにしないと、最初の2つしかUNIONされない
+ ) %do% {
+ dplyr::tbl(con, "attenu") %>%
+ filter(event == x)
+ }
> tbl_result
# Source: lazy query [?? x 5]
# Database: sqlite 3.19.3 []
  event mag station dist accel
  <dbl> <dbl> <chr> <dbl> <dbl>
1 1 7.0 117 12 0.359
2 8 5.3 111 19 0.086
3 8 5.3 112 22 0.073
4 8 5.3 113 29 0.045
5 8 5.3 116 21 0.179
6 8 5.3 290 13 0.205
7 11 7.7 2708 145 0.010
8 11 7.7 2714 45 0.110
9 11 7.7 2715 300 0.010

生成されるSQLを見てみても、しっかりUNIONされていることがわかる（ムダにサブクエリがあるのはもう仕方ない）。

> # show_queryで作られるSQLが見られる
> tbl_result %>%
+ dplyr::show_query()
<SQL>
SELECT *
FROM (SELECT *
FROM `attenu`)
WHERE (`event` = 1.0)
UNION
SELECT *
FROM (SELECT *
FROM `attenu`)
WHERE (`event` = 8.0)
UNION
SELECT *
FROM (SELECT *
FROM `attenu`)
WHERE (`event` = 11.0)

また、dbplyr でクエリを作ると、ついついパイプでつなげすぎてしまい、非常に重いクエリになることがよくある。多分悪いクセである。 そんなときは、いったん dplyr::compute で中間データをテーブルに入れてしまうと良い。

> # 結果を一度computeしてテーブルに格納しておくと便利
> tbl_result <- tbl_result %>%
+ dplyr::compute(name="attenu_union")
> tbl_result %>%
+ dplyr::show_query()
<SQL>
SELECT *
FROM `attenu_union`

クエリを見ても、格納したテーブルから始まっている。

ということは、foreach関数の .final 引数に dplyr::compute を指定すると、 UNIONした結果を即座にテーブルに格納してくれて便利なのではないか。

> # .final を使って一気にUNION->格納もできる
> tbl_result <- foreach::foreach(
+ x=events,
+ .combine = dplyr::union,
+ .multicombine = F,
+ .final = function(y){dplyr::compute(y, name="attenu_union2")}
+ ) %do% {
+ dplyr::tbl(con, "attenu") %>%
+ filter(event == x)
+ }
> tbl_result
# Source: table<attenu_union2> [?? x 5]
# Database: sqlite 3.19.3 []
  event mag station dist accel
  <dbl> <dbl> <chr> <dbl> <dbl>
1 1 7.0 117 12 0.359
2 8 5.3 111 19 0.086
3 8 5.3 112 22 0.073
4 8 5.3 113 29 0.045
5 8 5.3 116 21 0.179
6 8 5.3 290 13 0.205
7 11 7.7 2708 145 0.010
8 11 7.7 2714 45 0.110
9 11 7.7 2715 300 0.010

無事に出来ている。 コーディングに自信があればおすすめしたい。
というのも、筆者はコーディングがヘボく、間違ったクエリを発行して間違ったデータを長時間かけてテーブルに格納してしまう糞尿以下の存在であるため、 .final でいきなり compute するのは控えている。

以上、d(b)plyr でUNIONする方法をまとめた。 筆者がdbplyrを真面目にやりだしたのはここ最近であるため、もっと良い方法があればぜひ教えてほしい。

Rパッケージにスターを送るパッケージ ThankYouStars を作成しました

R

先日 @teppeis さんが 依存しているnpmライブラリにGitHubスターを送るツール を作成されました。

それに触発され、R言語バージョンを作ってみました。

github.com

スターでパッケージ作者に感謝の気持ちを送りましょう。

続きを読む

mapeditパッケージで、地図上のメッシュを選択する

R

前回は地域メッシュのシェープファイルを入手し可視化するところまでやりました。 今回はその続きです。

前回↓

ksmzn.hatenablog.com

今日の環境はMacです。

前回、新宿近辺のメッシュのみを抽出しましたが、では新宿近辺のメッシュをどうやって調べればよいのでしょうか。

おそらく最も手軽なのはWebサービスを使って、ブラウザ上で確認することだと思います。 「Geocode Viewer」 や 「地図上で標準地域メッシュを確認するページ」 が便利です。

しかし、本シリーズはRでチャレンジすることが裏テーマなので、メッシュの選択もRでやってみましょう。 mapedit パッケージ を使うことで、地図上でフィーチャーを選択できます。

mapedit パッケージは、フィーチャーを選択するだけでなく、線や図形を書いてsfクラスとして取得することができるので、 メッシュ以外でも便利です。

くわしくは公式のページをご覧ください。

mapedit - interactively edit spatial data in R

それでは mapedit パッケージをインストールしましょう。 mapedit のREADME に従い、インストールします。 mapview パッケージは develop 版であることに注意してください。

devtools::install_github("bhaskarvk/leaflet")
devtools::install_github("bhaskarvk/leaflet.extras")
devtools::install_github("r-spatial/mapview@develop")
devtools::install_github("r-spatial/mapedit")

次に、 sf クラスのオブジェクトを用意します。 詳しくは、前回の記事を参照してください。

library(tidyverse)
library(sf)
d1 <- sf::read_sf("./Downloads/L03-a-14_5339-tky_GML/L03-a-14_5339-tky.shp", options=c("ENCODING=CP932”))

それでは、mapedit パッケージを使っていきましょう。 今回は地図上のフィーチャーを選択したいので、selectFeatures() 関数を使います。 selectFeatures() 関数は v0.2.0 から追加された、比較的新しい機能のようです。

使い方は簡単で、単純に sf クラスのオブジェクトを selectFeatures() 関数の第一引数に入れるだけです。 めっちゃラクだ…。

library(mapview)
library(mapedit)
mesh_shinjuku <- d1 %>% 
  mapedit::selectFeatures()

これを実行すると、mapview と同様に地図が開きます。

f:id:ksmzn:20170629123428j:plain

欲しいフィーチャーをクリックして選択し、右下のDoneボタンを押せば完了です。

返り値として sf クラスが返ってくるので、その後も普通に分析可能です。 すんごいラクだ…。

> mesh_shinjuku
Simple feature collection with 4 features and 13 fields
geometry type:  POLYGON
dimension:      XY
bbox:           xmin: 139.6875 ymin: 35.68333 xmax: 139.7125 ymax: 35.7
epsg (SRID):    4326
proj4string:    +proj=longlat +datum=WGS84 +no_defs
     メッシュ 田 他農用地  森林 荒地 建物用地   道路   鉄道 他用地 河川湖沼 海浜 海水域 ゴルフ場
3626 53394525  0        0 73182    0   710914 219547      0  31364    10455    0      0        0
3627 53394526  0        0 94091    0   606364 146364 188181  10455        0    0      0        0
3637 53394536  0        0     0    0   919905  62721  62721      0        0    0      0        0
3636 53394535  0        0     0    0   951272  41814      0  10454    41814    0      0        0
                           geometry
3626 POLYGON((139.6875 35.683333...
3627 POLYGON((139.7 35.683333333...
3637 POLYGON((139.7 35.691666666...
3636 POLYGON((139.6875 35.691666...
> class(mesh_shinjuku)
[1] "sf"         "data.frame"

新宿の4メッシュだけが入っています。

当然、このオブジェクトを mapview すれば、該当メッシュだけが可視化されています。

mesh_shinjuku %>% 
  mapview

f:id:ksmzn:20170627124654p:plain

完璧ですね。 だいぶスムーズにデータ分析できそうです。

GIFアニメを作ってみました。 地図を開いてメッシュを選択し、新宿メッシュのみを可視化する様子です。 容量ギリギリだったので少し見にくいのはご容赦ください…

f:id:ksmzn:20170629124601g:plain

EDIT [解説・ボーナストラック付き国内盤] (BRC-192)

EDIT [解説・ボーナストラック付き国内盤] (BRC-192)