On this page:
2.1 SQLite as "Data  Frame"
2.2 Source Code
2.3 How to use
6.8

2 SQLite on Racket

2.1 SQLite as "DataFrame"

統計解析ではデータを変数ごとの列ベクトルとして一覧の状態にした「データフレーム」を頻繁に用います。このデータ形式はCSVのような一般的なテキストデータでも表すことができるとともに、Stataのような統計解析ソフトウェアではデータフレームが操作できる唯一のデータ形式であるなど、統計に向いています。

一般的にはデータフレームをCSVの形式からインポートしたのち、必要な変数を残したり、あるいは別々のデータフレームを特定のキーに従って結合(統計では「接続」という)したり、様々な作業を行います。この作業を行いやすいように統計解析ソフトウェアは高額な料金でライセンスされ、Rのような統計解析言語は独特な文法を持ち、PythonにもPandasのような統計処理専用のライブラリが構築されます。

しかし、私は趣味の範囲でSQLを勉強したことがあり、INNER JOINなどの便利な作業が高速に処理できることを知っていますから、特定の言語の特定のパッケージに特化した文法を覚えるよりも、SQLiteのようなデータベースソフトを使って作業した方がスムーズである、ということに気づきました。

RacketはWindows版ならSQLiteを内蔵しており、macOS版やLinux版でもOSに付属のSQLiteを簡単に使うことができます。SQLiteの特徴として、メモリ上にデータベースを構築できるという点があり、CSVからデータフレームを読み込んで、加工してからまたCSVに戻すというような処理に大変向いていると思います。

ここでは、RacketからSQLiteを容易かつインタラクティブに操作するための簡易的なスクリプトを紹介します。

2.2 Source Code

先にソースコードを示します。
#lang racket/base
(require racket/string
         racket/list
         csv-reading
         db/base
         db/sqlite3)
(provide con
         dis
         exec
         create
         insert
         insert*
         tables
         rows
         col
         value
         read-csv
         insheet
         write-rows
         call/ts
         strq)
 
(define *dbc* #f)
 
(define (con)
  (set! *dbc* (sqlite3-connect #:database 'memory)))
 
(define (dis)
  (disconnect *dbc*)
  (set! *dbc* #f))
 
(define (exec state)
  (when *dbc*
    (query-exec *dbc* state)))
 
(define (create tb cols)
  (exec (string-append "create table "
                       tb " ("
                       (string-join cols ", ")
                       ");")))
 
(define (insert tb vals)
  (exec (string-append "insert into "
                       tb " values("
                       (string-join vals ", ")
                       ");")))
 
(define (insert* tb state)
  (exec (string-append "insert into "
                      tb " " state)))
 
(define (call/ts fn)
  (when *dbc*
    (call-with-transaction *dbc* fn)))
 
(define (tables)
  (when *dbc*
    (list-tables *dbc*)))
 
(define (rows state)
  (when *dbc*
    (query-rows *dbc* state)))
 
(define (col state)
  (when *dbc*
    (query-list *dbc* state)))
 
(define (row state)
  (when *dbc*
    (query-maybe-row *dbc* state)))
 
(define (value state)
  (when *dbc*
    (query-maybe-value *dbc* state)))
 
(define (read-csv filename)
  (call-with-input-file filename csv->list))
 
(define (insheet csv tb #:header [hd #t] #:map-fn [fn #f])
  (let ([data (read-csv csv)])
    (when hd (create tb (first data)))
    (call/ts
     (lambda ()
       (for ([line (rest data)])
         (if fn
            (insert tb (apply fn line))
            (insert tb line)))))))
 
(define (write-rows file data #:cols [col-list #f])
  (with-output-to-file file #:exists 'replace
    (lambda ()
      (when col-list
        (set! data (cons (list->vector col-list) data)))
      (let ([col-num (vector-length (first data))])
        (for ([line data])
          (for ([i (range col-num)])
            (if (= i (- col-num 1))
                (printf "~a" (vector-ref line i))
                (printf "~a," (vector-ref line i))))
          (printf "~%"))))))
 
(define (strq str)
  (string-append "\"" str "\""))

2.3 How to use

このライブラリは、標準ライブラリ以外にcsv-readingというCSVファイル読み込みモジュールを使用しますので、

raco pkg install csv-reading

であらかじめインストールしておいてください。Windowsの場合もraco.exeを実行することで簡単にサードパーティ製パッケージをインストールすることができます。

作業はデータベースの接続で始まり、解放で終わります。それぞれ、以下のようにします。

; Connect
(con)
; ...
; ...
; Disconnect
(dis)

ヘッダのあるCSVをそのままデータベースに挿入するには、以下のように使います。

(insheet "test.csv" "test_db")

データの内容が文字列の場合は、一旦その列をクォートする必要があります。そのための関数がstrqで、高階関数の中で使います。

(insheet "test.csv" "test_db"
         #:map-fn (lambda (v1 v2)
                    (list v1 (strq v2))))

この例の場合、データフレームは2変数で、2列目が文字列という状況です。

なお、このinsheet関数はトランザクションに対応しています。

テーブルを作成するのはcreateです。以下のように使います。

(create "df1" '("id" "year" "value"))

このように作ったテーブルに、他のテーブルから得た結果を挿入するには、insert*を使います。

(insert* "df1" "select id, 2012, va from test_db;")

単にデータを取得する場合はrowsを使います。

(define outdata (rows "select * from outdata;"))

得たデータをCSVに出力するには以下のようにします。

(write-rows "outdata.csv" outdata #:cols col-names)

#:colsキーワードでヘッダを文字列のリストとして指定すると、それも一緒に出力します。

基本的にSQLiteを知っていることを前提にしており、「データの操作においてSQLは優秀な言語である」という認識で設計していますので、細かい処理をラップするのではなく、ほとんどSQLのステートメントをそのまま単純に実行できるようにしています。このことでRのdplyrのような柔軟な処理もSQLiteの操作と同じ感覚で行うことができます。