dplyr : データベースを操作する¶
Last Change: 17-Dec-2014.
author : qh73xe
このページでは dplyr を使って sqlite のデータベースを操作します．
dplyr のコンセプトはあらゆるデータ形式を統一的な文法で処理をすることです
ので，ここに記述した内容は他のデータ形式（例えば, data.frame や:command:data.table ) でも使用可能です・
私の手元にある一番アクセスのしやすいデータベースは CSJ RDB <http://www.ninjal.ac.jp/corpus_center/csj/data/rdb-outline/> なのでこれを利用します．
- このデータベースを使用するには登録が必要です．
データベースにアクセスする¶
>>> library(dplyr)
>>> csj_db <- src_sqlite("~/Documents/data/csj.db")
>>> phoneme <- tbl(csj_db, "segPhoneme")
>>> csj_db
## src: sqlite 3.8.6 [~/Documents/data/csj.db]
## tbls: infoDialog, infoImpressionMpx, infoImpressionSpx, infoReadSpeech, infoSpeaker, infoTalk, linkDepBunsetsu, linkTone2AP, pointTone, relAP2IP,
## relBunsetsu2Clause, relLUW2Bunsetsu, relLUW2Clause, relMora2AP, relMora2Bunsetsu, relMora2Clause, relMora2IP, relMora2IPU, relMora2LUW, relMora2SUW,
## relPhone2Mora, relPhone2Phoneme, relPhone2SUW, relPhoneme2Mora, relPhoneme2SUW, relSUW2AP, relSUW2Bunsetsu, relSUW2Clause, relSUW2IP, relSUW2IPU,
## relSUW2LUW, segAP, segBunsetsu, segClause, segIP, segIPU, segLUW, segMora, segPhone, segPhoneme, segSUW, subsegLUW, subsegSUW
テーブルの選択 (select 文)¶
テーブルの選択には select を使用します．
>>> select(phoneme, StartTime, EndTime, PhonemeEntity)
## Source: sqlite 3.8.6 [~/Documents/data/csj.db]
## From: segPhoneme [1,685,445 x 3]
##
## StartTime EndTime PhonemeEntity
## 1 5.551385 5.632454 h
## 2 5.632454 5.698874 a
## 3 5.698874 5.760029 Q
## 4 5.760029 5.837566 py
## 5 5.837566 5.907903 o
## 6 5.907903 5.978241 H
## 7 5.978241 6.028153 s
## 8 6.028153 6.078064 i
## 9 6.078064 6.166530 m
## 10 6.166530 6.277931 a
## .. ... ... ...
データの選択 (filter)¶
ある条件に該当するデータを選択するには filter を使用します． 母音のみを抽出します．
>>> filter(phoneme, PhonemeEntity %in% c("a", "i", "u", "e", "o"))
## Source: sqlite 3.8.6 [~/Documents/data/csj.db]
## From: segPhoneme [816,348 x 6]
## Filter: PhonemeEntity %in% c("a", "i", "u", "e", "o")
##
## TalkID PhonemeID StartTime EndTime Channel PhonemeEntity
## 1 A01F0055 00005632454L 5.632454 5.698874 L a
## 2 A01F0055 00005837566L 5.837566 5.907903 L o
## 3 A01F0055 00006028153L 6.028153 6.078064 L i
## 4 A01F0055 00006166530L 6.166530 6.277931 L a
## 5 A01F0055 00006382126L 6.382126 6.532801 L u
## 6 A01F0055 00007033556L 7.033556 7.120680 L e
## 7 A01F0055 00007262850L 7.262850 7.317895 L a
## 8 A01F0055 00007380806L 7.380806 7.448371 L a
## 9 A01F0055 00007505970L 7.505970 7.551186 L u
## 10 A01F0055 00007620648L 7.620648 7.675693 L i
## .. ... ... ... ... ... ...
データの処理 (summarise)¶
何かを操作する際には summarise が便利です． group_by と併用すると 大体
plyr ライブラリの ddply のような感じになります．
>>> group_by(phoneme, PhonemeEntity) %>%
filter(., PhonemeEntity %in% c("a", "i", "u", "e", "o")) %>%
summarise(., time = mean(EndTime - StartTime))
## Source: sqlite 3.8.6 [~/Documents/data/csj.db]
## From: <derived table> [?? x 2]
##
## PhonemeEntity time
## 1 a 0.08305158
## 2 e 0.08742342
## 3 i 0.05898493
## 4 o 0.07464170
## 5 u 0.05457325
## .. ... ...
こんな事もできます（ここだけデータを iris に変更しました）．
>> iris %>%
group_by(Species) %>%
summarise_each(funs(mean))
## Source: local data frame [3 x 5]
##
## Species Sepal.Length Sepal.Width Petal.Length Petal.Width
## 1 setosa 5.006 3.428 1.462 0.246
## 2 versicolor 5.936 2.770 4.260 1.326
## 3 virginica 6.588 2.974 5.552 2.026
注釈
チェインに関して
この項目では チェイン( :command:’%>%’ )と呼ばれる演算子を使用しています． この演算子は大体，shell のパイプ( | ) を想定して貰えればよいかと思います． 左辺の実行結果を右辺の . の部分に渡しています．
To Data Frame¶
dplyr では読み込まれたデータは src_sqlite や tbl_sqlite といったクラスに変換されます．
これは dplyr 独自のクラスですので，他の関数との連携の際には
data.frame 型に変換した方がよい場合もあるかと思います．
データをデータフレームにするには collect 関数を使用します．
>>> df <- collect(group_by(phoneme, PhonemeEntity)) %>%
filter(., PhonemeEntity %in% c("a", "i", "u", "e", "o")) %>%
summarise(., time = mean(EndTime - StartTime))
>>> class(phoneme)
## [1] "tbl_sqlite" "tbl_sql" "tbl"
>>> class(df)
## [1] "tbl_df" "tbl" "data.frame"
処理の当てはめ (do)¶
do 関数を使うともう少しモデリングが楽になるかもしれません．
以下では segPhone を利用して，母音が無声化したか否かを従属変数に，
その際の母音の持続時間を説明変数にそれぞれの母音ごとに単回帰を行います．
>>> df.now <- tbl(csj_db, "segPhone") %>%
filter(., PhoneClass == "vowel") %>%
group_by(., PhoneEntity) %>%
do(lm=lm(Devoiced ~ (EndTime - StartTime), data = .))
>>> df.now %>% summarise(rsq = summary(lm)$r.squared)
## Source: local data frame [5 x 1]
##
## rsq
## 1 8.735047e-06
## 2 9.397947e-05
## 3 1.782875e-07
## 4 3.186034e-06
## 5 1.674483e-04
Multiple table verbs¶
今度は join 系の話です． 例えば inner join をします．
>>> phone <- tbl(csj_db, "segPhone")
>>> mora <- tbl(csj_db, "segMora")
>>> rel <- tbl(csj_db, "relPhone2Mora")
>>> now <- inner_join(phone, rel, by="PhoneID") %>%
inner_join(., mora, by="MoraID") %>%
collect(.)
>>> str(now)
## Classes 'tbl_df', 'tbl' and 'data.frame': 2059531 obs. of 20 variables:
## $ TalkID.x : chr "A01F0055" "A01F0055" "A01F0055" "A01F0055" ...
## $ PhoneID : chr "00005551385L" "00005632454L" "00005698874L" "00005760029L" ...
## $ StartTime.x : num 5.55 5.63 5.7 5.76 5.82 ...
## $ EndTime.x : num 5.63 5.7 5.76 5.82 5.84 ...
## $ Channel.x : chr "L" "L" "L" "L" ...
## $ PhoneEntity : chr "h" "a" "Q" "SclS" ...
## $ PhoneClass : chr "consonant" "vowel" "special" "others" ...
## $ Devoiced : int 0 0 0 0 0 0 0 0 1 0 ...
## $ StartTimeUncertain: int 0 0 0 1 0 0 1 0 1 0 ...
## $ EndTimeUncertain : int 0 0 1 0 0 1 0 1 0 0 ...
## $ TalkID.y : chr "A01F0055" "A01F0055" "A01F0055" "A01F0055" ...
## $ MoraID : chr "00005551385L" "00005551385L" "00005698874L" "00005760029L" ...
## $ nth : int 1 2 1 1 2 3 1 1 2 1 ...
## $ len : int 2 2 1 3 3 3 1 2 2 2 ...
## $ TalkID : chr "A01F0055" "A01F0055" "A01F0055" "A01F0055" ...
## $ StartTime.y : num 5.55 5.55 5.7 5.76 5.76 ...
## $ EndTime.y : num 5.7 5.7 5.76 5.91 5.91 ...
## $ Channel.y : chr "L" "L" "L" "L" ...
## $ MoraEntity : chr "ハ" "ハ" "ッ" "ピョ" ...
## $ PerceivedAcc : int 0 0 0 0 0 0 0 0 0 1 ...
join 系は以下の関数が用意されています．
- inner_join(x, y): matching x + y
- left_join(x, y): all x + matching y
- semi_join(x, y): all x with match in y
- anti_join(x, y): all x without match in y
実例¶
最近解いたパズルです． 問題は csj_RDB の中では k に続く母音が無声化している際に，境界がひられている例は何件あるのかです．
>>> phone <- tbl(csj_db, "segPhone")
>>> mora <- tbl(csj_db, "segMora")
>>> rel <- tbl(csj_db, "relPhone2Mora")
>>> ans <- inner_join(phone, rel, by="PhoneID") %>%
inner_join(., mora, by="MoraID") %>%
filter(., MoraEntity %in% c("カ", "キ", "ク", "ケ", "コ")) %>%
filter(., PhoneClass == "vowel" && StartTimeUncertain==0 && Devoiced==1) %>%
select(., StartTime.x, EndTime.x, TalkID) %>%
collect(.)
>>> write.csv(ans, file='result.csv')