2012年3月11日日曜日

PostgreSQLで統計解析 PL/R 基礎編3 : CRAN(Rライブラリ)利用


CRAN

perlにCPANがあるように、CRANというRのモジュールを公開するネットワークがあり、多くのプログラムが公開されている。
The Comprehensive R Archive Network

下記記事を参考に、PL/RでCRANから取得したモジュールを利用してみる。
zooパッケージを使って行列の欠損値を補間する(R Advent Calendar 2011) - My Life as a Mock Quant


Install R Package

パッケージのinstallはRから
r #R起動
> install.packages("zoo")


Use R Package

パッケージはlibrary(zoo)で読み込み。
行列の欠損を補完する関数を作成
CREATE OR REPLACE FUNCTION na_locf(matrix int4[][]) RETURNS int4[][] AS '
    library(zoo);
    return(na.locf(matrix))
' LANGUAGE 'plr';
SELECT na_locf(ARRAY[ARRAY[1,2,3],ARRAY[4,null,6],ARRAY[7,8,9]]);
-- nullが2で補完される。

CREATE OR REPLACE FUNCTION na_approx(matrix int4[][]) returns int4[][] as ' 
    library(zoo);
    return(0.5 * (na.approx(matrix) + t(na.approx(t(matrix)))))
' LANGUAGE 'plr';
SELECT na_approx(ARRAY[ARRAY[1,2,3],ARRAY[4,null,6],ARRAY[7,8,9]]);
--nullが5で補完される。
PostgreSQLとRのデータ構造の違いに気をつければライブラリの使用は比較的簡単

PostgreSQLで統計解析 PL/R 基礎編2 : R用データ読み込み


前回 に続き、
今回はR用のサンプルデータを読み込んでPostgreSQLのデータとして受け取ってみる。


Test Data

データ集めは下記を参考に
統計を学びたい人へ贈る、統計解析に使えるデータセットまとめ - ほくそ笑む

今回は、世界の48都市の経済状態を記録したデータのcsvを読み込んでみる。


Composite Type Sample

読み込むcsvは文字列と数値が含まれており、Rではデータフレームという形式で読み込まれる。
データフレーム形式のデータはPostgreSQLでは複合型として受け取れる。
PL/Rのマニュアル記載のサンプルは下記の通り。
CREATE TYPE emp AS (
    name text
    ,age int2
    ,salary int4
);
CREATE OR REPLACE FUNCTION get_emps() RETURNS SETOF emp AS '
   names <- c("Joe","Jim","Jon")
   ages <- c(41,25,35)
   salaries <- c(250000,120000,50000)
   df <- data.frame(name = names, age = ages, salary = salaries)
   return(df)
' LANGUAGE 'plr';

SELECT * FROM get_emps();
/*
name | age | salary
text | smallint | integer
------+-----+-----------
Joe | 41 | 250000
Jim | 25 | 120000
Jon | 35 | 50000
*/
複合型を作成するのが手間なので、下記のようにしてみたらうまくいった。
CREATE OR REPLACE FUNCTION get_emps(out name text, out age int4, out salary int4) RETURNS SETOF record AS '
   names <- c("Joe","Jim","Jon")
   ages <- c(41,25,35)
   salaries <- c(250000,120000,50000)
   df <- data.frame(name = names, age = ages, salary = salaries)
   return(df)
' LANGUAGE 'plr';
SELECT * from get_emps();
が、引数を与えた場合はうまく動作しない模様…。
マニュアルにもデータフレームをsetof recordで返せるとは明示されていないので、対応範囲外なのかもしれない。


Get CityEcon

では、本題のcityecon.csvを読み込んでみる。
CREATE TYPE cityecon AS (
    city text
    ,work int4
    ,price float8
    ,salary float8
);
CREATE OR REPLACE FUNCTION get_cityecon(csvpath text) RETURNS setof cityecon AS '
    x <- read.csv(csvpath)
    return(x)
' LANGUAGE 'plr';
SELECT * FROM get_cityecon('/path-to-csv/cityecon.csv');
-- ERROR:  invalid input syntax for integer: "-"
元データにnull値として"-"が入っているためエラー。
Rで"-"をNAに置き換えておく。
CREATE OR REPLACE FUNCTION get_cityecon(csvpath text) RETURNS setof cityecon AS '
    x <- read.csv(csvpath)
    x$Work <- ifelse(x$Work=="-", NA, x$Work) 
    x$Salary <- ifelse(x$Salary=="-", NA, x$Salary) 
    return(x)
' LANGUAGE 'plr';
SELECT * FROM get_cityecon('/path-to-csv/cityecon.csv');
-- OK
さらに、CSV以外のファイル形式にも対応できるようにしてみる。
CREATE OR REPLACE FUNCTION get_cityecon(path text, header boolean, separator text) RETURNS setof cityecon AS '
    if(is.null(separator)){
        sep <- ""
    } else sep <- separator    
    if(header){
        x <- read.table(path, header = T, sep = sep)
    }else{
        x <- read.table(path, header = F, sep = sep)
    }
    x$Work <- ifelse(x$Work=="-", NA, x$Work) 
    x$Salary <- ifelse(x$Salary=="-", NA, x$Salary) 
    return(x)
' LANGUAGE 'plr';
SELECT * FROM get_cityecon('/path-to-csv/cityecon.csv', true, ',')
-- OK
データの区切り文字とヘッダーが含まれるかどうかを指定可能。

読み込むデータに合わせて複合型を作成したり、NULL値の変換をしたりと結構めんどくさい。
もうちょっと簡単に読み込みたいなあという感想。