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値の変換をしたりと結構めんどくさい。
もうちょっと簡単に読み込みたいなあという感想。