クソデータの処理
- 昔々あるところにExcelで"入,3F,000000004101,hogehoge,男,076,才,2016.5.1,5:24,AST,2292"みたいな行が延々ならんでいるクソデータがありました しかも検査項目名が全角野郎でした
- それを下記のデータベースに落とし込みたいとおもいました
CREATE TABLE `Labo2016` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `Department` varchar(20) DEFAULT NULL, `PatID` varchar(13) DEFAULT NULL, `PatName` varchar(30) DEFAULT NULL, `PatAge` int(11) DEFAULT NULL, `PatSex` varchar(4) DEFAULT NULL, `TestDate` varchar(12) DEFAULT NULL, `AST` float DEFAULT NULL, `ALT` float DEFAULT NULL, `LDH` float DEFAULT NULL, `Na` float DEFAULT NULL, `K` float DEFAULT NULL, `BUN` float DEFAULT NULL, `Cr` float DEFAULT NULL, `BNP` float DEFAULT NULL, `NTproBNP` float DEFAULT NULL, `WBC` float DEFAULT NULL, `Hb` float DEFAULT NULL, `Ht` float DEFAULT NULL, `Plt` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2982 DEFAULT CHARSET=utf8;
#!/usr/bin/perl -w use Spreadsheet::ParseExcel; use strict 'refs'; use utf8; use Encode; use DBI; binmode STDOUT, ":utf8"; binmode STDIN, ":utf8"; { my $FileName = $ARGV[0];; # Excel(xls)ファイル名 my $ExcelObj = Spreadsheet::ParseExcel->new(); # ParseExcelのオブジェクト定義 my $Book = $ExcelObj->parse($FileName); # xlsファイル読み込み/book扱い our $DB_NAME = "databasename"; our $DB_USER = "username"; our $DB_PASS = "password"; $dbh = DBI->connect("dbi:mysql:dbname=$DB_NAME","$DB_USER","$DB_PASS") or die "$!\n Error: failed to connect to DB.\n"; for my $Sheet ($Book->worksheets()) { # worksheetオブジェクト取得 &GetValuesFromSheet($Sheet); # worksheetデータ取得へ last; # Sheet1で終了 } $dbh->disconnect; } sub updateDB { my ($patID, $testDate, $item, $value) = @_; $itemCol = $item; $itemCol =~ tr/0-9A-Za-z/0-9A-Za-z/; $itemCol =~ s/HCT/Ht/; $itemCol =~ s/HGB/Hb/; $itemCol =~ s/PLT/Plt/; $itemCol =~ s/CRE/Cr/; $sql = sprintf("UPDATE Labo2016 SET %s=? WHERE PatID=? AND TestDate=?", $itemCol); $sth = $dbh->prepare($sql); $sth->execute($value,$patID,$testDate); } sub GetValuesFromSheet { # worksheetデータ取得 my ($Sheet) = @_; my ($Rmin, $Rmax) = $Sheet->row_range(); # 行のデータ範囲(最小,最大) my ($Cmin, $Cmax) = $Sheet->col_range(); # 列のデータ範囲(最小,最大) for($i=$Rmin+1;$i<$Rmax;$i++) { $cell = $Sheet->get_cell($i, 2); if (defined($cell)) { $patID = substr($cell->value(),2); $cell = $Sheet->get_cell($i, 7); $testDate = $cell->value(); $rc = $dbh->do("SELECT PatName FROM Labo2016 WHERE PatID=? AND TestDate=?",undef,$patID,$testDate); if($rc eq "0E0") { # not defined in DB. register it. $cell = $Sheet->get_cell($i, 1); $patDep = $cell->value(); $cell = $Sheet->get_cell($i, 3); $patName = $cell->value(); $cell = $Sheet->get_cell($i, 4); $patSex = $cell->value(); $cell = $Sheet->get_cell($i, 5); $patAge = $cell->value(); $sql = "INSERT INTO Labo2016 (`Department`, `PatID`, `PatName`, `PatAge`, `PatSex`, `TestDate`) "; $sql = $sql . "VALUES (?, ?, ?, ?, ?, ?)"; # placeholders $sth = $dbh->prepare($sql); $sth->execute($patDep, $patID, $patName, $patAge,$patSex, $testDate); $cell = $Sheet->get_cell($i, 9); $itemName = $cell->value(); $cell = $Sheet->get_cell($i, 10); $itemValue = $cell->value(); &updateDB($patID, $testDate, $itemName, $itemValue); } else { # patient exist. update item $cell = $Sheet->get_cell($i, 9); $itemName = $cell->value(); $cell = $Sheet->get_cell($i, 10); $itemValue = $cell->value(); &updateDB($patID, $testDate, $itemName, $itemValue); } } } }
- おばあさんはforeach i (*.xls); perl -f hoge.pl $i; endをした後Rで読み込んでご満悦でした
- 参考:まんが日本オラオラ昔話「ジョジョ太郎」 https://www.youtube.com/watch?v=NO7RI1xDNqw
DBD::mysql
- 大量のデータをMySQL+Sequel Proで処理して、それを一行ずつ取り出してperlで処理したいと思った
- なんとなくRは文字列に弱そうだったから
- 久しぶりにperlをやろうとしたら DBIやDBD::mysqlが入っていなかった
- sudo perl -MCPAN -e shell, install DBIは問題なかった
- install DBD::mysqlでエラーで止まるため以下を行った
cd /usr/local/Cellar/mysql/5.6.19/lib ln -s /usr/local/opt/openssl/lib/libssl.a . ln -s /usr/local/opt/openssl/lib/libcrypto.a .
- これがclean El capitanかどうかは知らないです
Rのインストール
- OSX版はhttp://r.research.att.com/
- R-3.3branch, Mac OS X GUI rev. 7321 for R 3.3.xの2つのバイナリをダウンロード、インストール
- Rを立ち上げ、パッケージメニューからパッケージインストーラを起動、CPAN(binary)から下記の素晴らしいパッケージをインストール
- 読み込み xls直の場合
setwd('/path/to/xlsdata/') library(readxl) d<-read_excel("hogehoge.xls")
- 読み込み MySQLからの場合
library(RMySQL) drv<-dbDriver("MySQL") conn<-dbConnect(drv,user="hoge", password="hoge", dbname="dbName") d<-dbReadTable(conn,"tableName") dbDisconnect(conn)
TESO
12/12のことでした
A「あのボッタ価格のTESOパッケが今(12/12)なら半額でamazonで売ってるよ!」
B「はぁ?どうせ嘘なんだろ?」
A「いや、ホントなんだって!この画像を見ろ!」
B「どのサイト?」
A「このamazon.co.jpってサイトだ!」
B「…やってみたらマジだった!おいお前ら急げ!(終了)」
というわけで最低限のパッケージでESOの世界へ入りました
oblivion懐かしい... 面白い... サーバメンテ大杉