SQLiteにRSQLiteで接続し、R Scriptの中でdplyrを使ってクエリーする

SQLite は埋め込みSQLデータベースエンジンです。 SQLiteは通常のディスクファイルを直接読み書きし、データベースファイル形式はクロスプラットフォームです。32ビットと64ビットのシステム間、またはビッグエンディアンとリトルエンディアンのアーキテクチャ間でデータベースを自由にコピーできます。このコンパクトさと移植性のおかげで、 SQLite形式で利用可能な多くのデータがあります。たとえば、このKaggleサイトに行くと、国別の国際貿易と、良いデータの種類をSQLiteデータファイルとして見ることができます。

この記事では、RSQLiteとdplyrを使ってクエリを実行し、SQLiteデータから直接結果を得る方法を説明します。

RSQLiteパッケージのインストール

まず、dplyrを使用してデータベースにアクセスするために、CRANからRSQLiteパッケージをインストールする必要があります。 Exploratoryの中で以下のようにすることができます：

Rスクリプトデータソースを作る

その後、KaggleからSQLiteデータをダウンロードした後、カスタムRスクリプトデータソースを使ってRSQLiteを使用してSQLiteデータベースにアクセスできます。

カスタムRスクリプトでは、SQLiteデータベースに接続するために、以下のように記述します。ご推測の通り /Users/hidekoji/Downloads/year_1988_2015.db は私がKaggleからダウンロードしたファイルのパスを示しています。

library(DBI)
library(RSQLite)
con <- dbConnect(SQLite(), "/Users/hidekoji/Downloads/year_1988_2015.db")

次のステップとして、作成した接続を使って、このデータベースにどんなテーブルがあるかを確認します。

# テーブルの一覧を取得
as.data.frame(dbListTables(con))

そうすると、このデータベースには year_1988_2015というテーブルが一つだけあることがわかるので、このテーブルを以下のように選択し、取得します。

# テーブルを取得
year_1988_2015 <- dbReadTable(con2015, 'year_1988_2015')

まず初めに、各国のごとの合計取引額（1988年から2015年まで）を計算するdplyrクエリを作成しましょう。

# データは取得されたので接続を閉じます。
dbDisconnect(con)
# Cancluate Total trade amount per country
year_1988_2015 %>% group_by(Country) %>% 
summarize(total_vy = sum(VY))

データの取得ボタンをクリックし、結果を確認します。良ければ、保存ボタンをクリックしてデータフレームとして保存します。

保存されると、サマリ情報を以下のように確認できます。

どうやら名前の代わりに「国」列の国コードが表示されるので、 Kaggleから国名マッピングcsv fleを取得しましょう

読み込んだら、 country_eng と year_1988_2015 をジョインします。

すると国名がわかるようになります。

国名は United_States_of_Americaのようにアンダースコアで結ばれているので、アンダースコア_をスペースで置き換えて世界地図上に投影することができます。

このためには テキストを操作 のメニューの下にある 全置換 を使います。

第2引数に _を、第3引数に" "を渡し、_を空白に置き換えます。

以下のように、地図上に日本の取引国ごとのトレードされた総額を視覚化することができます。そして、日本は米国と中国との間で多くの輸出入を行っているのがわかります。

さて、それぞれの年で、日本がビジネスを行っている上位10カ国を計算しましょう。 dplyrクエリは次のようになります。

year_1988_2015 %>% group_by(Year, Country) %>% 
summarize(total_vy = sum(VY)) %>%
top_n(10, total_vy)

Once you save the data, it looks like this in Exploratory.

ともあれ、計算結果を国のテーブルにジョインさせた後、X軸にYear、色にCountryNameにを割り当てることで、各年の上位10カ国を線グラフで視覚化できます。そして、中国が急速に日本との取引を拡大し、2007年に米国から第1位の地位を獲得したのがわかります。

まとめ

ExploratoryでRSQLiteとdplyrを活用することで、複雑なSQLクエリを書く必要なく、SQLiteデータベースのデータを効果的に扱うことができます。これにより、自分の生産性だけでなく、分析結果を共有する他の人たちの生産性も改善されます。

データをこちらにEDF (Exploratory Data Format)でシェアしたので、よろしければお手元のExploratory Desktopで試してみてください。

まだExploratory Desktopをお持ちでない場合は、こちらから30日間の無料試用をご利用いただけます。現在学生・教員であれば、無料でご利用いただけます！