SQLiteデータベースのテーブルをRに読み込む

データベースは私が作ったソフトウェアJVData To SQLiteで作成しました。

試しにデータベースのテーブルの1つであるレーステーブルをRに読み込みたいと思います。レーステーブルをDB Browser for SQLiteで開くとこんな感じです。1行が1レースの情報を表していて、出走頭数などの条件を含んでいます。

RAレコードから作成したレーステーブルの一部

RからSQLiteデータベースを操作するにはRSQLiteパッケージを使います[1]。パッケージをインストールしてロードします。

install.packages("RSQLite")
library(RSQLite)
## Warning message:
## パッケージ ‘RSQLite’ はバージョン 3.4.1 の R の下で造られました

WindowsにインストールしてあるRのバージョンは3.4.0でしたので、Warningが出ました。念のため3.4.1にアップデートしました。

次にdbConnect()でデータベースに接続します。データベースファイルのパスは、私の場合、C:\home\JVDataToSQLite\setup.sqlite3です。

con = dbConnect(SQLite(), "C:\home\JVDataToSQLite\setup.sqlite3", synchronous="off")
## Error: '\h' is an unrecognized escape in character string starting ""C:\h"

'\h' is an unrecognized escape...というエラーが出ました。

Windowsのパスは\で区切られています。Rでは\がエスケープ文字になってることがエラーの原因だと思ったので(\がエスケープ文字になっていることはよくあるから)、パスの\を\\にして再トライしました。

con = dbConnect(SQLite(), "C:\\home\\JVDataToSQLite\\setup.sqlite3", synchronous="off")

今度はエラーが出ません。

動作確認のためにデータベース上のテーブルの一覧を得ます。

dbListTables(con)
## [1] "CodeIndex"                 "Cグレード"                 "Cトラック"             
## [4] "C品種"                     "C天候"                     "C性別"               
## [7] "C曜日"                     "C東西所属"                 "C毛色"                 
## [10] "C異常区分"                 "C着差"                     "C競走条件"           
## [13] "C競走種別"                 "C競走記号"                 "C競馬場"             
## [16] "C重量種別"                 "C馬場状態"                 "C馬記号"             
## [19] "C騎乗資格"                 "C騎手見習"                 "JVDataRecordFormat"   
## [22] "JVOpen引数DataSpec"        "JVOpen引数Option"          "JVOpen引数組み合わせ" 
## [25] "test_table"                "オッズワイド"              "オッズ三連単"         
## [28] "オッズ三連複"              "オッズ単勝"                "オッズ枠連"           
## [31] "オッズ複勝"                "オッズ馬単"                "オッズ馬連"           
## [34] "コーナー通過順位"          "ラップタイム"              "レース"               
## [37] "取得済みjvdファイルリスト" "払戻フラグ"                "払戻ワイド"             
## [40] "払戻三連単"                "払戻三連複"                "払戻単勝"            
## [43] "払戻枠連"                  "払戻複勝"                  "払戻馬単"             
## [46] "払戻馬連"                  "票数ワイド"                "票数三連単"           
## [49] "票数三連複"                "票数単勝"                  "票数枠連"             
## [52] "票数複勝"                  "票数馬単"                  "票数馬連"             
## [55] "賞金"                      "馬毎レース"               

うまくいっています。次はdbReadTable()レーステーブルを読み込みます。

dbReadTable(con, "レース")
## Error in make.names(names(out), unique = TRUE) :
##  invalid multibyte string 1
## In addition: Warning message:
## In rsqlite_fetch([email protected], n = n) :
##  Column `繝ャ繝シ繧ケID`: mixed type, first seen values of type integer64, coercing other values of type real, string

invalid multibyte stringというエラーが出ました。

JVData To SQLiteのデータベースは、テーブル名と列名を日本語で命名しています。どうやら、RSQLiteで読み込むには英数字の列名でないといけないようです。

英数字の名前に変更しようとも思ったのですが、SQLのASで名前を変えればうまくいくことを見つけました。dbGetQuery()で直接SQLでクエリします。

Race <- dbGetQuery(con, "SELECT レースID AS RaceID, 距離 AS Distance, 出走頭数 AS HorseCount FROM レース")
head(Race)
##             RaceID Distance HorseCount
## 1 1986010506010101     1800         13
## 2 1986010506010102     1800          7
## 3 1986010506010103     1200         16
## 4 1986010506010104     1600         16
## 5 1986010506010105     1200         15
## 6 1986010506010106     2700         10

無事、テーブルをRに読み込んでデータフレームが作れました。

このレーステーブルは1986年から2014年までに開催されたレース情報で構成されています。

とりあえず、レースの距離と出走頭数の基本統計量を求めます。

summary(Race)
##                      RaceID       Distance       HorseCount
## Min.   :           19860329   Min.   : 750   Min.   : 0.00 
## 1st Qu.:   1996032406020212   1st Qu.:1300   1st Qu.:10.00 
## Median :   2002102944140206   Median :1500   Median :12.00 
## Mean   :   2025296149304575   Mean   :1568   Mean   :11.93 
## 3rd Qu.:   2008110203030409   3rd Qu.:1800   3rd Qu.:14.00 
## Max.   :9223372036854775807   Max.   :6400   Max.   :33.00 

最も短いトラックの距離は750m、最も長い距離は6400m、中央値1500mだとわかりました。最も少ない出走頭数は0になっていますが、中止されたレースか何かだと思います。最も多い出走頭数は33。

時間かけて作ってきたJVData To SQLiteのデータベースをちゃんとRに読み込めました(^^)

2017-11-25 JRA-VANのデータ利用について補足

JRA-VAN利用規約を読むと、JRA-VANデータラボのデータは私的利用に限り使うことができて、ウェブ公開などはできないと書いてあります。

このことをJRA-VANのソフトウェア・サポートに連絡をすると、ソフトウェア開発者はソフトウェアの説明の一貫として、過去のデータの一部を公開することができるとのことです。過去と書いたのは、開催スケジュールや予測などの未来のことは公開できない規約になっているからです。

JVData To SQLiteの機能は、SQLiteデータベースを作ることだけですが、ユーザーはデータベースを使い、有益な情報を得ることが目的でデータベースを使います。

私がデータ解析してわかったことすべてをこのサイトで公開することは規約上できませんが、データの一部をJVData To SQLiteの説明と宣伝として公開することはできます。

今後もタグ競馬データ解析で可能な範囲でデータ解析の進捗を書いていきます。


  1. 奥村先生のRからSQLiteを使うに習いました。 ↩︎

Kosuke Maeda / まえだこうすけ

「機械学習で競馬予想して勝てるのか?」をテーマに活動中! QiitaにはR、VBAなどのTipsを投稿しています。