読者です 読者をやめる 読者になる 読者になる

一所懸命に手抜きする

監査の仕事をしています。ITを利用し、良い意味で「手抜き」することは効率化と精度アップに役立つと思うんです。部下への引き継ぎのためにまずは諸々頭から引っ張り出そうとブログを始めました。

[ Main ]  [ 別館 ]
当ブログで取り上げられている事案やデータベース、人物等はすべてフィクションです。

RデータフレームからSQLでデータを取得するsqldfライブラリ

Rではデータフレームが主要な処理対象

 Rではデータフレームというものが主要な処理対象となります。

社員名簿サンプルデータフレーム作成コード
データフレームのサンプル DF
社員番号部署氏名
11営業部山田太郎
22営業部鈴木花子
33総務部杉本一郎
44財務部支払益子
55経理部会計主水
66秘書室白鳥麗子

 これは行列のようにも見えますが、文字列や数値が混ざっていて列や行にラベルがあり、列ラベルは変数名となります、行ラベルは行番号です。ふつうはいろいろなデータベースからデータを取り込みRのデータフレームとしてから処理します。

R標準ではデータフレームをSQLで処理できない

 R標準ではデータフレームとなってからはSQLで処理することはできず、データの抽出はsubset()、集計はaggregate()等で行えます。

上記サンプルDFからR関数 subset()で抽出する例
subset(DF,DF[,1]==6,c(1,3,2))
  社員番号     氏名     部署
1       6  白鳥麗子  秘書室
# 変数1(社員番号)=6 について 項目1(社員番号),3(氏名),2(部署) の順に出力
# Select 社員番号,氏名,部署 from DF where 社員番号=6 とでもできれば良いが…

 これはこれで簡単なのですが、他の人が見た時にわかりにくいと思います。たとえば監査法人ソースコードを提出したとして、こんなコードが何をしているのかわかれと言っても困難でしょうし、私自身、後日見直したら理解に時間がかかりそうです。
 それに、VisualBasicVBAVB.netC++、RPGIII、SASSPSS・・・と使ってきて、その都度文法を使い分けるのも面倒になってきました。たいていの言語はなんらかの形でSQLを用いることができます。データフレームからSQLで抽出なり集計なりが出来れば、subset()やaggregate()に頼らなくてもよいですからわかりやすいです。各言語固有の文法を知らない人でもSQLさえ知っていれば理解できるのもありがたいです。なんとかRにおいてもSQLで処理できないものか。

データフレームをSQLで処理するライブラリ sqldf

 SQLでデータフレームを処理するpackageがありました。sqldfです。 使い方も至ってシンプルです。データフレームを対象にして

sqldf( " SQL文 " )

 とするだけです。事前にインストールし、library(sqldf)するまでは当然ですが。
 上記サンプルのsubsetと同じ処理を sqldf() で実行してみます。

上記サンプルDFから sqldf() で抽出する例
sqldf("select 社員番号,氏名,部署 from DF where 社員番号=6")
  社員番号     氏名   部署
1        6 白鳥麗子 秘書室

 データフレームからSQLによってデータを抽出できました。こちらの方が可読性が高いと思います。
 結果は1行ですが、データフレームです。

sqldfはSQLite

 sqldf ライブラリをロードすると RSQLite もロードされます。これはsqldfが RSQLite を利用しているからです。そのため select 以外のSQLも使えます。
 たとえば、フィールド項目一覧を得るには SQLiteでは PRAGMA TABLE_INFO(テーブル名) としますから

上記サンプルDFから sqldf() でフィールド項目一覧を得る例
sqldf("PRAGMA TABLE_INFO(DF)") 
  cid     name type notnull dflt_value pk
1   0 社員番号 REAL       0         0
2   1     部署 TEXT       0         0
3   2     氏名 TEXT       0         0

のようにsqldfでも実行可能です。

注意

日本語列名だとエラーが起こることもある。全角4文字が目安?
ピリオド(.)で始まる列名も駄目。

a-habakiri.hateblo.jp


 In R ,the most similar to a SQL table is a dataframe .
 Though dataframes are used very often,but once data is imported to R and translated to the dataframe ,there are few methods to extract data from R dataframes via SQL.
 Here is one package named sqldf that enables us to extract data from R dataframes via SQL.
 We can use sqldf very easily, only as follows.

library(sqldf)
 sqldf( " SQL statement " )
広告を非表示にする