新保研データベースの使い方 - 慶應義塾大学商学部・商学研究科

新保研データベースの使い方
新保一成 ∗
2015 年 5 月 19 日
1 データベース・サーバーのアカウント情報に関する守秘義務
新保研では、研究および教育に使うデータの多くをデータベース・サーバーで管理し、新保研メンバーの利
用に供しています。新保研メンバーは、安全な方法でデータベース・サーバーに接続してデータを使用しなけ
ればなりません。サーバーに接続するためには、ログインするアカウントのログイン名とパスワードが必要で
す。利用者は、「アカウント情報を第三者に漏らさない」という最低限の約束を守らなければなりません。ア
カウント情報が悪意の第三者に漏れた場合には、深刻なサイバー攻撃の踏み台にされ、新保研の枠を超えて三
田 ITC のみならず慶應全体のネットワークに甚大な影響を及ぼすリスクに曝されます。アカウント情報の漏
洩を原因とする攻撃に曝された場合には、データベース・サーバーを閉じなければならない事態に至るかもし
れません。それは新保研の研究遂行に非効率をもたらすことにほかなりませんから、アカウント情報を漏らさ
ないことを新保研の守秘義務と考え厳守してください。
2 準備
新保研では MySQL というデータベース管理システム (RDBMS, Relational Data Base Management System)
によって多くのデータを管理しています。MySQL で管理されたデータには様々なプログラムからアクセスす
ることができますが、みなさんにとっては R からアクセスするケースがほとんどだと思います。ここでは、三
田の新保研データベース・サーバーとの通信を確立して、みなさんの PC にインストールされた R から新保
研のデータを使う準備をします。以下に最小限必要なソフトウエアを列挙します。できるだけ Mac OS X と
Windows というプラットホームの違いを意識せずに PC を研究活動に利用できる環境を整えたいと思います。
2.1 必要なソフトウエア
2.1.1 Mac OS X, Windows 共通
• R: The R Projetc for Statistical Computing から最新版をインストールしてください。データベースを
使いこなすために以下の R パッケージが必要です。パッケージのインストールについては別途説明し
ます。
– RMySQL: R と MySQL を仲介します。
– dplyr: ロング形式のデータを処理するのに便利な道具です。
∗
慶應義塾大学商学部 E-mail address: [email protected]
1
– tidyr: ロング形式とワイド形式の変換に便利な道具です。
– ggplot2: dplyr、tidyr と一緒に使うと便利なグラッフィックパッケージで、異なる形式のグラフを
ほとんど同じ文法で描くことができます。
• RStudio: R のための統合開発環境 (IDE, Integrated Development Environment) です。The R Projetc for
Statistical Computing からインストールしたアプリケーションも統合開発環境として使えますが、Mac
OS X と Windows で若干使い勝手が違います。RStudio を使えば Mac OS X と Windows で全く同じ
環境を整えることができるだけでなく、RStudio は R を使う上で便利な機能を豊富に備えています。
RStudio プロジェクトから最新版をインストールしましょう。
2.1.2 Mac OS X
• XQuartz: R でも Tcl/Tk 言語を使ってグラフィカル・ユーザー・インターフェイス (GUI) を備えたプロ
グラムを書くことができます。新保研データベース・サーバーに接続するために dblogin という GUI
プログラムからユーザー名、パスワードなどを入力します。Mac OS X で R の GUI を使うためには
X11 という X ウインドウ・システムが必要です。Mac OS X には X11 が含まれていないので、XQuartz
プロジェクトから最新版を入手してインストールしてください。
2.1.3 Windows(Mac OS X ユーザーも読んでください)
• Git Bash: 新保研データベース・サーバーとは SSH という手続きにしたがって安全に通信します。Mac
OS X には、SSH を実装した OpenSSH というプログラムが組み込まれていますが、Windows では SSH
による通信を可能にするプログラムを別途インストールしなければなりません。Mac OS X ではター
ミナルという端末アプリケーションから SSH のコマンドを手入力することによって新保研サーバーと
の通信を確立します。それと同じ手続きを実現するために、ここでは Git Bash というアプリケーショ
ンを導入します。Bash(bourne-again shell) は Unix で使用するシェルです。シェルは、オペレーティン
グ・システム (OS) とユーザーを仲介する最も基本的なソフトウェアです。Mac OS X のシェルも Bash
です (Mac OS X は、BSD Unix をベースにした OS です)。Git Bash は、Windows という OS 上であた
かも OS が Unix であるかのように偽装させるアプリケーションです。Git for Windows から最新版を入
手してインストールしてください。
Windows にもコマンド・シェルという OS と直接やり取りするプログラムが準備されています。しか
しそのシェルは従来の MS-DOS を引き継ぐもので、Bash とは全く異なります。みなさんにとっても、
Windows という狭い狭い世界でしか役に立たない知識を蓄えるよりは、より広い世界で通用する知識
と技術を身に付けるべきです。計算は Excel でしかできないとか、文書は Word でしか書けない、プレ
ゼンは Pwer Point でやるものだという狭い了見では困ります。もっとひろ世界に目を向けましょう。
2.2 R パッケージのインストール
Windows では機種によって、R をインストールしたフォルダの直下にある library フォルダに権限上の理
由でパッケージをインストールできないという問題が生じるようです。最初にその問題について説明しますの
で、Mac OS X ユーザーは次の節を飛ばして、「CRAN ミラーサイトの選択」に進んでください。
2
2.2.1 Windows で R のライブラリ・フォルダにパッケージをインストールできない問題
上記の問題は、R for Windows FAQ の「4.2 I don’t have permission to write to the R-3.2.0\library directory.」
に説明があります。そこには大別して 2 種類の解決策が紹介されていますが、2 番目の方法がより簡単だと思
われます。それは、
C:\Users\ ユーザー名 \Documents\R\win-library\x.y
を個人的なライブラリ・フォルダとすることです。ここで x.y は、R のバージョンが x.y.z であることを示
します。たとえば、使用中の R のバージョンが R-3.2.0 のときには、
C:\Users\ ユーザー名 \Documents\R\win-library\3.2
となります。
2.2.2 CRAN ミラーサイトの選択
R パッケージは、CRAN(the Comprehensive R Archive Network) から取得することができます。CRAN の
ミラーサイトが各国に設けられているので、物理的な距離の近い日本のミラーサイトからダウンロードしま
しょう。
最初に RStudio を起動します。次のコマンドを実行して CRAN のミラーサイトを選択します。
CRAN ミラーサイトの選択
chooseCRANmirror()
各国のミラーサイトがリストアップされるので、日本のミラーサイトである 52:Japan(Tokyo)(統計数理研究
所)、53:Japan(Tsukuba)(筑波大学)、54:Japan(Yamagata)(山形大学) のいずれかを選択しましょう。
2.2.3 パッケージのインストール
R パッケージは、install.packages 関数でインストールします。パッケージによっては、他のパッケージ
が必要なものもありますから、それらが一緒にインストールされるように dependences = TRUE を忘れない
ようにします。
R パッケージのインストール
install.packages("RMySQL", dependencies = TRUE)
install.packages("dplyr", dependencies = TRUE)
install.packages("tidyr", dependencies = TRUE)
install.packages("ggplot2", dependencies = TRUE)
Fujitsu 社製の Windows PC の一部の機種に ggplot2 をインストールできないという問題も生じています。
この問題は、colorspace パッケージを ggplot2 パッケージに先立ってインストールしておけば解決するよ
うです。
3
2.3 .Rprofile に dblogin を設定する
新保研のデータベースに R から接続するためには、dblogin という R 関数を利用します。ここでは、R を
起動した時にいつでも dblogin を使えるように設定します。dblogin は、RMySQL パッケージに含まれる関数
ではなく、新保が書いた関数であり、かつ R パッケージにしていないので、それを使うときに library 関数に
よって何処からでも呼び出すということができません。R は、ユーザーのホームディレクトリに.Rprofile と
いうファイルを見つけると、起動時にそのファイルに記述されている内容を実行します。つまり、.Rprofile
に dblogin が正しく定義されていれば、いつでもデータベースに接続できるようになります。
R のホームディレクトリとは何処でしょうか。RStudio を起動した直後に、コンソール画面に現在の作業
ディレクトリを表示する関数 getwd を実行して表示されるディレクトリが R のホームディレクトリです。
現在の作業ディレクトリの出力
getwd()
以下に、オペレーティング・システム (OS) ごとに、OS のホーム・ディレクトリと R のホーム・ディレクト
リを示します。自分で特別な設定をしていなければ、その限りではありません。特別な設定をしている人は、
自ずとそれが何処であるかわかっているはずです。Windows では、OS のホームと R のホームが異なるので
注意してください。
表 1 ホームディレクトリ
OS
OS ホーム
R ホーム
Mac OS X
/Users/ユーザー名
/Users/ユーザー名
Windows
C:\Users\ ユーザー名
C:\Users\ ユーザー名 \Documents
ディレクトリの階層構造の表現方法に不慣れの人も多いのではないでしょうか。最近では、ディレクトリ
ではなくフォルダという呼び方をすることが多くなってきましたが、決してホームフォルダとは言わないよ
うです。Mac OS X でも Windows でもユーザー名の付いたディレクトリは Users ディレクトリの直下にあり
ます。また、Documents ディレクトリはユーザー名ディレクトリの直下にあります。このようなディレクト
リ階層構造の区切れを示す記号に、Mac OS X では’/’(スラッシュ記号)、Windows では’\’(バックスラッシュ
記号) を使います。ただし、日本語 Windows では、’Y’ をバックスラッシュの代わりに使うようです。Mac
OS X におけるディレクトリ階層構造の表現形式は Unix のもので、先頭の’/’ をルートディレクトリと呼び、
Users はルートディレクトリの直下にあることを示しています。一方、Windows の’C:’ は C ドライブである
ことを示しています。これを Unix ライクに表せば、\C\Users\ ユーザー名 \Documents となるでしょう。
2.3.1 dblogin 定義ファイルをダウンロードする
keio.jp の Google Apps ドライブに新保研共有ドライブ ShimpoLab を作りました。その中の「サブゼミ」
フォルダの下に「R」フォルダがあります。そこに dblogin を定義したファイルがあります。Mac OS X 版、
Windows 版を準備しているのは、2 つの OS で改行を表す記号に違うものを使っていて、Mac OS X のファイ
ルを Windows で見るとすべての行が繋がって見えてしまうからです。
4
表 2 dblogin 定義ファイル
オペレーティングシステム
ファイル名
Mac OS X
dblogin mac
Windows
dblogin win
Mac OS X でも Windows でも、表 1 ファイルに示した OS ホームの直下にある Downloads ディレクトリに
ダウンロードされます。これを R ホームに移動して.Rprofile に名前変更します。
2.3.2 ファイルの移動と名前変更
■問題点
その作業は、Max OS X でも Windows でも端末ソフトからコマンドを手入力することによって行
います。それは、先頭に’.’(ドット) を持つファイル名は特殊なものだからです。通常、ファイル名に付けられ
た最後のドットの後には、そのファイルがどのようなファイルであるのかを示す拡張子が付けられます。たと
えば、R のファイルであれば’.r’、Excel のファイルであれば’.xlsx’ などです。拡張子が’.txt’ となってい
るのはテキストファイル、’.csv’ となっているのは CSV(Comma Separated Variables) という項目がカンマで
区切られたファイルで、これら 2 種類のファイルは特別なソフトがなくても読み書きすることができます。
通常のファイル名は「ファイル名 + ’.’ + 拡張子」という構造になっています。したがって、Windows の
Explorer で dblogin win を.Rprofile に名前変更しようとすると拡張子しかないファイルだと見なされて
「ファイル名がありません」というエラーになり、名前の変更ができません。
一方、Mac OS X にとって先頭に「.(ドット)」が付くファイルは特別な意味があります。先に述べたように、
Mac OS X のベースは Unix です。Unix では先頭にドットがあるファイルをドット・ファイルと呼び、ドッ
ト・ファイルを隠しファイルとして様々なプログラムの設定ファイルとして使います。ユーザーは、ドット・
ファイルに好みの設定を書くことによってプログラムの挙動をカスタマイズすることができるわけです。Unix
上で開発された R もいくつかのドット・ファイルを持ち、.Rprofile はその中の一つで、このファイルで R
の起動時の挙動をユーザーが好きなようにカスタマイズできるわけです。問題は隠しファイルであるという点
です。設定ファイルを隠しファイルにしているのは、誤って削除してまうリスクを極力小さくするためだと思
われます。場合によっては、設定ファイルを失うことによってプログラムが想定外の挙動を示すこともあり得
るからです。当然、Mac OS X においてもドット・ファイルは Finder に表示されません。もちろん、ドット・
ファイルを Finder に表示させてしまうこともできるのですが、それでは隠しファイルの意味がなくなってし
まいます。
以上の理由から、Explorer(Windnows) と Finder(Mac OS X) で dblogin ファイルの名前を変えることは出
来ないあるいは不適切です。そもそも.Rprofile という名前のファイルを Google ドライブに置いておけば問
題ないではないかと考えるかもしれません。その場合、Windows では.Rprofile[1] という名前で保存され
るため結局名前変更できない、Mac OS X ではファイルが見えないという状況に変わりはありません。
■シェルを使う
そこで、Mac OS X においても Windows においてシェル (Bash) を使うことによって対処し
ます。端末プログラムを使ってコマンドを手入力し、直接 OS を操作します。みなさんがコンピューターを操
作するときには、ウインドウ・メニューから何かを選択して実行させることがほとんどで、自分の手でコマン
ドを入力するということはほとんどないと思います。これを GUI(Graphical User Interface) による方法と言い
ます。一方で、端末からコマンドを手で入力して PC を直接操作する方法を CUI(Command User Interface) と
5
言います。GUI プログラムは、ユーザーのウインドウからの入力を解釈して、シェル・コマンドを発生させて
PC を操作しているに過ぎません。GUI に慣れ親しんでいるみなさんにとっては、CUI は使い勝手の悪い方法
かもしれません。しかし、CUI から GUI へ移行してきたのがコンピューターの発展の歴史であり、現在にお
いても CUI でしかできないことも多々あります。
Mac OS X ユーザーはターミナル*1 を、Windows ユーザーは git Bash を起動してください。それぞれ端末プ
ログラムが起動します。$記号 (プロンプト、入力促進記号) の後ろにカーソルがあります。$に続けてコマン
ドをキーボードから入力し、[return] キーを叩いてその内容を実行します。
最初に、現在の作業ディレクトリが何処であるかを確認しましょう。そのために pwd(present working
directory) コマンドを入力します。
現在の作業ディレクトリの確認 (全員)
$ pwd
表 1 の OS ホームが表示されているはずです。つまり、OS ホームが現在の作業ディレクトリです。git Bash
は、Windows のディレクトリ構造を Unix ライクに表示するので、/c/Users/ユーザー名と表示されます。
次に Google ドライブからダウンロードした dblogin が定義ファイルが Downloads ディレクトリにあるか
どうかを確認します。ls(list) コマンドでディレクトリにあるファイルのリストを取ることができます。
ファイルのリスト (全員)
$ ls Downloads
これは、現在の作業ディレクトリ (OS ホーム) の直下にある Downloads ディレクトリのリストを取れという
命令です。表 2 に示した dblogin 定義ファイルがリストアップされていますか?
いよいよ、mv(move) コマンドでファイルの移動と.Rprofile へファイルの名前を変更します。
ファイルの移動とファイル名の変更 (Max OS X ユーザーの場合)
$ mv Download/dblogin mac .Rprofile
これは、Downloads ディレクトリにあるファイル dblogin mac を現在の作業ディレクトリつまり OS ホーム
に.Rprofile という名前で移動せよという命令です。
Windows の場合、R ホームと OS ホームが異なりますから次のようになります。
ファイルの移動とファイル名の変更 (Windows ユーザーの場合)
$ mv Download/dblogin win Documents/.Rprofile
これは、Downloads ディレクトリにあるファイル dblogin mac を現在の作業ディレクトリの直下にある
Documents ディレクトリに.Rprofile という名前で移動せよという命令です。
ファイル名の変更がうまくできたかどうか、もう一度 ls コマンドで確認してみましょう。その前に、
Windows ユーザーは、cd(Change Directory) コマンドで R ホームに移動しましょう。Mac OS X ユーザーは、
すでに R ホームに居ます。
*1
ターミナルを Finder から起動する場合、ターミナルは [ アプリケーション ] の中の [ ユーティリティ ] にあります。Launch パッ
ドから起動する場合、ターミナルは「その他」の中にあります。Launch パッドは、トラックパッドを親指と 3 本指でピンチすれば
開きます。
6
R ホームへの移動 (Windows ユーザーの場合)
$ cd Documents
ここで ls コマンドでファイル・リストを取っても.Rprofile は見つからないはずです。なぜならば、そ
れは隠しファイルだからです。隠しファイルを表示させるには a オプションを付けて ls コマンドを実行し
ます。
隠しファイルの表示 (全員)
$ ls -a
これで.Rprofile の存在が確認できれば成功です。
■すでに.Rprofile を使っているユーザー
もしもすでに.Rprofile を使って何らかの設定をしているユー
ザーは、dblogin 関数定義を使用中の.Rprofile に追加してください。OS ホームから次のコマンドを実行
すれば、.Rprofile の末尾に dblogin が追加されます。
既存の.Rprofile への dblogin の追加 (Max OS X ユーザーの場合)
$ cat Download/dblogin mac >> .Rprofile
既存の.Rprofile への dblogin の追加 (Windows ユーザーの場合)
$ cat Download/dblogin mac >> Documents/.Rprofile
■確認最後に、RStudio を再起動して.Rprofile が機能しているか確認します。ls 関数を使って R オブ
ジェクトのリストを取ります。
R オブジェクトのリストを表示
> ls()
リストの中に dblogin があれば成功です。
3 SSH ポート転送による MySQL サーバーへの接続
SSH(Secure SHell) とは、暗号や認証の技術を利用した安全なコンピューター間の通信を可能にする取り
決め (プロトコル) です。SSH の取り決めに従って、研究室のサーバーとみなさんの PC(クライアント) の間
で通信を行います。そのためには、みなさんの PC にはクライアント・プログラムが必要になります。Mac
OS X には、OpenSSH というプログラムがインストールされています。また、git Bash をインストールした
Windows PC でも OpenSSH が利用できます。
SSH によるポート転送 (トンネルとも呼ばれます) によって新保研データベース・サーバーに接続すると、
あたかもデータベース・サーバーがみなさんの PC 上にあるかの如くデータベースを使うことができるように
なります。つまり、インターネットを介して、みなさんの PC と三田キャンパスの新保研データベース・サー
バーを繋ぐトンネルを開通させることができます。ポート転送に必要なサーバーの IP アドレス、ログイン名、
パスワードは別途お知らせします。
7
3.1 端末ソフトからの SSH ポート転送
Mac OS X ではターミナルを、Windows では git Bash を起動して以下のコマンドを実行します。
SSH ポート転送
$ ssh -L 9999:localhost:3306 -N -f ログイン名@サーバー IP アドレス
新保研データベース・サーバーへのログイン・パスワードの入力が求められるので、パスワードを入力します。
データベースに接続している間は、端末を閉じてないでください。端末を閉じてしばらく時間が経過する
と、新保研データベース・サーバーとの接続が失われます。
上のコマンドは、
• IP アドレスに指定されたサーバーのログイン名で指定されたアカウントへ SSH で通信することを指示
しています。そこに、L、N、f という三つのオプションを付け加えています。
• L オプションでトンネルの入口と出口を指定します。指定の仕方は「ローカル PC(みなさんの PC) の
ポート番号 (これを入口とする):ローカル PC の IP アドレス:リモート PC(サーバー) のポート番号 (こ
れを出口とする)」になります。ローカル PC の IP アドレスには、自分自身を示す localhost を指定
します。入口のポート番号は未使用のポートの番号を指定します。ポート番号 9999 は、おそらくどの
PC でも未使用だと思うので、これを使っています。出口のポート番号には MySQL に開けられたポー
ト番号をしてします。それは 3306 番です。この設定でみなさんの PC の 9999 番ポートと新保研デー
タベース・サーバーの MySQL ポートを SSH で繋ぐトンネルが開通します。
• N と f は、SSH による通信を繋ぎっぱなしにするためのオプションです。N はサーバーにおいて接続後
のシェルプロンプトを出さないこと、f は、バックグランドで実行することを指示します。
新保研データベース・サーバーとの接続を切断するには、端末を閉じてください。
3.2 トンネルは 1 本だけ開通させます
上の手続きに入力ミスなどの誤りがなければドンネルは開通しています。打ち込んだコマンドに誤りがあれ
ばエラー・メッセージが帰ってきます。そのときにはトンネルが開通していませんので、誤りを訂正して再度
コマンドを入力しなければなりません。逆に、端末に正しいコマンドを打ち込んだときには何も反応がないの
で、不安に感じる人もいるようです。そして、R で何か作業しているときに何か問題が生じた時に、何度もト
ンネル開通のコマンドを叩いてしまうようです。トンネルを開通させることと、データベースと接続すること
は全く別の事です。R で何か失敗してデータベースへ接続できなかったり、接続が切断されてしまったとして
も、トンネルが封鎖されるわけではありません。焦ってトンネルを何本も開通させないように落ち着いて仕事
をしてください。同じポートを指定したトンネルが何本もあっては、R はどのトンネルを使うべきかわからな
くなり、トラブルの元になります。
Mac OS X では、開通しているトンネルがある場合には、以下のように 9999 番ポートが使用中であること
を告げるメッセージがが出力されます。
bind: Address already in use
channel_setup_fwd_listener: cannot listen to port: 9999
8
Could not request local forwarding.
これがまたエラーだと勘違いして、さらに同じコマンドを実行してしまう人もいるようですが、落ち着いて
メッセージの意味を解釈しましょう。残念ながら git Bash では上のような警告がありません。
それではトンネルが何本あるかをどのように確認したらよいでしょうか。そのためには端末から実行中のプ
ロセスを表示させる ps(Process) コマンドを使います。
実行中のプロセスを表示
$ ps ax | grep "ssh"
ここでは ax というオプションをつけて ps コマンドを実行しています。その出力結果は膨大なので、出力を
grep という文字列検索プログラムに渡しています。記号’|’ は、’|’ の左側のプログラムの実行結果を右側の
プログラムに渡すための記号でパイプといいます。文字列検索プログラム grep は、実行中のプロセスから
ssh という文字列が含まれるものを検索して表示します。実行結果は以下のようになります。
ps ax | grep "ssh"の結果 (Mac OS X の場合)
850 ?? S
0:00.67 /usr/bin/ssh-agent -l
22275 ?? Ss 0:00.00 ssh -L 9999:localhost:3306 -N -f ログイン名@サーバー IP アドレス
22334 ?? Ss 0:00.00 ssh -L 9999:localhost:3306 -N -f ログイン名@サーバー IP アドレス
ps ax | grep "ssh"の結果 (Windows の場合)
7144 1 7144 7144 ? 500 19:14:29 /usr/bin/ssh
6776 1 6776 6776 ? 500 19:14:26 /usr/bin/ssh
一番左にある番号はプロセス番号です。もしもトンネルを何本も開通させてしまった場合には、同じ内容のプ
ロセスが異なるプロセス番号で開通させたトンネルの本数分表示されます。
もし 2 本以上のトンネルを開通させてしまったことが発見されたならば、新しく開通させたトンネルのプロ
セウスを殺し、最初に開通させたトンネルのみを残します。そのためには kill コマンドを使います。
kill でプロセスを殺す
$ kill プロセス番号
4 R からデータベースに接続してデータを使う
SSH でみなさんの PC と三田の新保研データベース・サーバーを繋ぐトンネルを開通させたら、実際に R か
らデータベース・サーバーのデータにアクセスしてみましょう。
4.1 サンプルデータ
MySQL は、データベースとその中に含まれるテーブルという概念によってデータを管理します。新保研の
Web ページ「観測基地」には、様々なデータのテーブル定義を掲載しています。
9
4.1.1 具体的なデータのイメージ
ここで、仮想的な家計調査を使って実習してみよう。その内容は次の表に示した通りです。id は家計の識別
番号で、1∼8 の整数。sec は部門コードで、1 = 農村、2 = 都市。state は都道府県コードで、{001, 002, 003}
の 3 地域。income、food は 1 ヶ月あたりの所得と食料費で、いずれも単位は万円です。
id
sec
state
income
food
1
1
001
51.7
45.8
2
1
002
28.4
24.3
3
1
002
12.7
9.8
4
1
003
29.5
23.4
5
2
001
78.6
35.7
6
2
001
97.0
26.3
7
2
002
58.8
12.3
8
2
003
80.9
21.1
4.1.2 テーブル定義
MySQL は、上に示したデータをあるデータベースの中の一枚のテーブルとして管理します。新保研 Web
ページ「観測基地」には、次のような形式でテーブル定義を与えています。
データベース名
sample
テーブル名
household
変数名
タイプ
内容
id
INT
識別番号。
sec
INT
部門 (1: 農村、2: 都市)
state
CHAR(3)
都道府県コード
income
DOUBLE
所得 (万円)
food
DOUBLE
食料費 (万円)
4.1.3 MySQL の変数のタイプと R の変数タイプとの対応
RMySQL で MySQL 上のデータを読み込むと、下の表のようにデータのタイプが自動的に変換されます。
MySQL
R
タイプ
説明
タイプ
CHAR(n)
長さ n の固定長文字列
character
VARCHAR(n)
最大長 n の可変長文字列
character
TEXT
可変長文字列
character
INT
整数
numeric
FLOAT
単精度浮動小数点実数
numeric
DOUBLE
倍精度浮動小数点実数
numeric
10
4.2 データベースに接続する
dblogin を使って新保研データベースに接続します。
con <- dblogin()
次のウインドウが開きます。
このウインドウの「Database」に使用するデータベース名、「User」と「Password」に MySQL のユーザー
名とパスワード (新保研データベース・サーバーへのログインするためのものではありません) を入力して、
[Connect] ボタンをクリックすると新保研データベースに接続します。
この後の R セッションでは、dblogin からの返り値である con が R とデータベースとの橋渡しの役目を担
います。
babababababababababababababababababababab
RMySQL には、データベースと接続するための dbConnect 関数が含まれます。dbConnect 関数に
は、ユーザー名とパスワードを直接書かなければなりません。もし、dbConnect 関数を使ったプロ
グラムが流布すれば、結果としてユーザー名とそのパスワードが漏洩してしまうことになります。
dblogin 関数は、接続の度にユーザー名とパスワードの入力を要求し、特にパスワードの入力の際
には入力した文字が見えないようにしてあります。したがって、パスワード漏洩のリスクは極めて
低いと考えられます。新保研メンバーは、新保研データベースに接続するときには必ず dblogin を
使うことを遵守してください。
4.3 データベースとの接続を切断する
データベースとの接続を切断するには、次のコマンドを実行します。
dbDisconnect(con)
このコマンドが実行されると、con は R とデータベースの橋渡しの役目を完了し、意味のないオブジェクトに
11
なります。
4.4 データベースからデータを抽出する
RMySQL は、接続したデータベースに SQL(Structural Query Language) を発行して、データを抽出する．
data <- dbGetQuery(con, "SQL SELECT 文")
4.5 SQL SELECT 文
テーブル household から全ての変数について全てのレコードを抽出する
4.5.1
SQL 文
SELECT * FROM household
R コマンド
> sql <- "select * from household"
> data <- dbGetQuery(con, sql)
> data
id sec state income food
1
1
1
001
51.7 45.8
2
2
1
002
28.4 24.3
3
3
1
002
12.7
4
4
1
003
29.5 23.4
5
5
2
001
78.6 35.7
6
6
2
001
97.0 26.3
7
7
2
002
58.8 12.3
8 8
2
003
80.9 21.1
4.5.2
9.8
テーブル household から変数 (sec と food) の全てのレコードを抽出する
SQL 文
SELECT sec,food FROM household
12
R コマンド
> sql <- "select sec,food from household"
> data <- dbGetQuery(con,sql)
> data
sec food
1
1 45.8
2
1 24.3
3
1
4
1 23.4
5
2 35.7
6
2 26.3
7
2 12.3
9.8
8
2 21.1
4.5.3
テーブル household から条件に合うレコードを抽出する
■農村家計に関する全ての変数について全てのレコードを抽出する
SQL 文
SELECT * FROM husehold WHERE sec=1
R コマンド
> sql <- "select * from household where sec=1"
> data <- dbGetQuery(con,sql)
> data
id sec state income food
1
1
1
001
51.7 45.8
2
2
1
002
28.4 24.3
3
3
1
002
12.7
4 4
1
003
29.5 23.4
9.8
■州 001 に暮らす家計の全ての変数について全てのレコードを抽出する
SQL 文
SELECT * FROM husehold WHERE state=’001’
州コードは文字列なので、引用符または二重引用符で囲む。
13
R コマンド
> sql <- "select * from household where state=’001’"
> data <- dbGetQuery(con,sql)
> data
id sec state income food
1
1
1
001
51.7 45.8
2
5
2
001
78.6 35.7
3 6
2
001
97.0 26.3
■農村家計の食料支出に関する全てのレコードを抽出する
SQL 文
SELECT food FROM household WHERE sec=1
R コマンド
> sql <- "select food from household where sec=1"
> data <- dbGetQuery(con,sql)
> data
food
1 45.8
2 24.3
3
9.8
4 23.4
■州 001 の農村に暮らす家計の全ての変数について全てのレコードを抽出する
SQL 文
SELECT * FROM husehold WHERE state=’001’ AND sec=1
R コマンド
> sql <- "select * from household where sec=1 and state=’001’"
> data <- dbGetQuery(con,sql)
> data
id sec state income food
1 1
1
001
51.7 45.8
■州 001 または 002 に暮らす家計の全ての変数について全てのレコードを抽出する
14
SQL 文
SELECT * FROM husehold WHERE state=’001’ OR state=’002’
R コマンド
> sql <- "select * from household where state=’001’ or state=’002’"
> data <- dbGetQuery(con,sql)
> data
id sec state income food
1
1
1
001
51.7 45.8
2
2
1
002
28.4 24.3
3
3
1
002
12.7
4
5
2
001
78.6 35.7
5
6
2
001
97.0 26.3
6 7
2
002
58.8 12.3
9.8
次のように IN を使うこともできる。
SQL 文
SELECT * FROM husehold WHERE state IN(’001’,’002’)
R コマンド
> sql <- "select * from household where state in(’001’,’002’)"
> data <- dbGetQuery(con,sql)
> data
id sec state income food
1
1
1
001
51.7 45.8
2
2
1
002
28.4 24.3
3
3
1
002
12.7
4
5
2
001
78.6 35.7
5
6
2
001
97.0 26.3
6 7
2
002
58.8 12.3
9.8
また、IN を否定して使えば括弧に指定した集合に含まれないことを条件にデータを抽出することができる。
たとえば、州 001 と 002 以外のデータを次のように抽出することができる。
SQL 文
SELECT * FROM husehold WHERE state NOT IN(’001’,’002’)
15
R コマンド
> sql <- "select * from household where state not in(’001’,’002’)"
> data <- dbGetQuery(con,sql)
> data
id sec state income food
1
4
1
003
29.5 23.4
2 8
2
003
80.9 21.1
■州 001 または 002 に暮らす農村家計の全ての変数について全てのレコードを抽出する
SQL 文
SELECT * FROM husehold WHERE (state=’001’ OR state=’002’) AND sec=1
R コマンド
> sql <- "select * from household where (state=’001’ or state=’002’) and sec=1"
> data <- dbGetQuery(con,sql)
> data
id sec state income food
1
1
1
001
51.7 45.8
2
2
1
002
28.4 24.3
3 3
1
002
12.7
9.8
■所得が 50 万円未満の家計の全ての変数について全てのレコードを抽出する
SQL 文
SELECT * FROM husehold WHERE income < 50
R コマンド
> sql <- "select * from household where income < 50"
> data <- dbGetQuery(con,sql)
> data
id sec state income food
1
2
1
002
28.4 24.3
2
3
1
002
12.7
3 4
1
003
29.5 23.4
9.8
所得が 50 万円以下の場合には、
SQL 文
SELECT * FROM husehold WHERE income <= 50
16
R コマンド
> sql <- "select * from household where income <= 50"
> data <- dbGetQuery(con,sql)
> data
id sec state income food
1
2
1
002
28.4 24.3
2
3
1
002
12.7
3 4
1
003
29.5 23.4
9.8
また、50 万円以上の場合には次のように書く。
SQL 文
SELECT * FROM husehold WHERE income >= 50
R コマンド
> sql <- "select * from household where income >= 50"
> data <- dbGetQuery(con,sql)
> data
id sec state income food
1
1
1
001
51.7 45.8
2
5
2
001
78.6 35.7
3
6
2
001
97.0 26.3
4
7
2
002
58.8 12.3
5 8
2
003
80.9 21.1
■所得が 50 万以上 90 万円未満の家計の全ての変数について全てのレコードを抽出する
SQL 文
SELECT * FROM husehold WHERE income >= 50 AND income < 90
R コマンド
> sql <- "select * from household where income >= 50 and income < 90"
> data <- dbGetQuery(con,sql)
> data
id sec state income food
1
1
1
001
51.7 45.8
2
5
2
001
78.6 35.7
3
7
2
002
58.8 12.3
4 8
2
003
80.9 21.1
17
4.6 実例
インドの家計調査である第 61 回 NSS 調査で、農村・都市別に社会グループ (social group) の割合を調べて
みよう。使用するテーブルは、nss61ce level2 である。テーブル定義については、http://www.fbc.keio.
ac.jp/˜shimpo/db/nss61_tables.html を参照されたい。
dblogin 関数でデータベースに接続して、テーブル nss61ce level2 から、部門 (sec: 1=農村、2=都市)、
家計構成人員 (hhdsz)、社会グループ (sgrp: 1=指定部族、2=指定カースト、3=その他の後進階級、9=その
他)、標本抽出乗数 (cmbmults) を取得する。
> sql <- "SELECT sec, hhdsz, sgrp, cmbmults FROM nss61ce_level2"
> data <- dbGetQuery(con, sql)
data は、sec、hhdsz、sgrp、cmbmults を変数としてもつデータフレームになる。標本の大きさを調べてみ
ると、
> nrow(data)
[1] 124644
と表示するには大きすぎるので、10 件のレコードを無作為抽出して表示して見る。
> data[sample(nrow(data), 10),]
sec hhdsz sgrp cmbmults
115270
1
7
2 2140.750
11
1
7
3
74040
1
3
3 4756.510
68211
1
3
3
997.660
95155
2
2
2
306.000
7345
1
6
3
382.730
6361
1
15
95005
2
6
9
488.000
43362
2
5
1
9.000
78364
1
3
3 2138.380
330.835
3 1120.740
最初にデータを篩いに掛けてみよう。調査票に回答のない項目は、R では NA の扱いになっています。それ
がどの程度あるのかを調べてみます。まず、項目ごとに NA の件数を数える関数 numnas を定義します。
> numnas <- function(x) length(which(is.na(x)))
これと dplyr の summarize を使ってすべての項目の NA を数え上げる。
18
> library(dplyr)
> data %>%
summarize(sec = numnas(sec), hhdsz = numnas(hhdsz),
sgrp = numnas(sgrp), cmbmults = numnas(cmbmults)) -> nas
> nas
sec hhdsz sgrp cmbmults
1
0
0
41
0
標本抽出倍率 cmbmults は、調査された家計と同じ属性を持つ家計がどれほど存在するかを示している。し
たがって、調査された値に標本抽出倍率を乗じると全国レベルの値を得ることができる。各家計の家計構成人
員に標本抽出倍率を乗じて人口を求めよう。人口の変数名を pop とする。
> data %>%
filter(!is.na(sgrp)) %>%
mutate(pop = hhdsz * cmbmults) -> data
> data[sample(nrow(data), 10),]
sec hhdsz sgrp cmbmults
pop
851
1
2
3
7382.23 14764.46
103710
1
7
3
3036.21 21253.47
111732
1
4
3
851.59
3406.36
106988
2
6
3
631.25
3787.50
70935
1
5
9
2424.54 12122.70
89940
2
5
9
1171.88
5859.40
36096
1
3
9
244.33
732.99
119784
2
2
9
211.50
423.00
91937
2
5
3
1262.25
6311.25
100183
2
1
3
99.75
99.75
次に、農村と都市の人口割合を計算してみよう。こういう所で威力を発揮するのが dplyr パッケージだ。
19
> data %>%
group_by(sec) %>%
summarize(pop = sum(pop)) %>%
mutate(proportion = pop / sum(pop)) -> sum.sec
> sum.sec
Source: local data frame [2 x 3]
sec
pop proportion
1
1 732748886
0.7467816
2
2 248460142
0.2532184
さらに、農村、都市別に社会グループ人口の割合を計算してみよう。
> data %>%
group_by(sec, sgrp) %>%
summarize(pop = sum(pop)) %>%
group_by(sec) %>%
mutate(proportion = pop / sum(pop)) -> sum.sec.sgrp
> sum.sec.sgrp
Source: local data frame [8 x 4]
Groups: sec
sec sgrp
pop proportion
1
1
1
77480393
0.1057394
2
1
2 153384839
0.2093280
3
1
3 313408740
0.4277164
4
1
9 188474914
0.2572162
5
2
1
7262340
0.0292294
6
2
2
38873422
0.1564574
7
2
3
88473745
0.3560883
8
2
9 113850635
0.4582249
>
20

Download Report