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