dplyr でDBのデータをUNIONする
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
関数を使う。他にも方法ありそう。
> # 3つ以上を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 を作成しました
先日 @teppeis さんが 依存しているnpmライブラリにGitHubスターを送るツール を作成されました。
それに触発され、R言語バージョンを作ってみました。
スターでパッケージ作者に感謝の気持ちを送りましょう。
続きを読むmapeditパッケージで、地図上のメッシュを選択する
前回は地域メッシュのシェープファイルを入手し可視化するところまでやりました。 今回はその続きです。
前回↓
今日の環境は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
と同様に地図が開きます。
欲しいフィーチャーをクリックして選択し、右下の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
完璧ですね。 だいぶスムーズにデータ分析できそうです。
GIFアニメを作ってみました。 地図を開いてメッシュを選択し、新宿メッシュのみを可視化する様子です。 容量ギリギリだったので少し見にくいのはご容赦ください…
EDIT [解説・ボーナストラック付き国内盤] (BRC-192)
- アーティスト: マーク・スチュワート
- 出版社/メーカー: BEAT RECORDS
- 発売日: 2008/04/26
- メディア: CD
- 購入: 1人 クリック: 27回
- この商品を含むブログ (10件) を見る