gikoha’s blog

個人的メモがわり

クソデータの処理

  • 昔々あるところに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;
  • おじいさんはCPANでinstall Spreadsheet::ParseExcelをした後、下記のようなperlを組みました
#!/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);
			}
			
     	}

    }
    
}

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)から下記の素晴らしいパッケージをインストー
    • Epi ROCカーブのパッケージ
    • ggplot2 きれいなグラフパッケージ
    • RMySQL RとMySQLを接続するパッケージ
    • readxl 直接xlsを読む
  • 読み込み 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)

R

  • また統計処理が必要なことがあった
  • Excelでとりあえずテーブル作って
  • 行が100程度ならExcel→Rでよいが、増えるならExcelMySQL→R

見えている地雷

  • 先月のことですがLMT病変だった aVR上昇が特徴的ですね

TESO

12/12のことでした
A「あのボッタ価格のTESOパッケが今(12/12)なら半額でamazonで売ってるよ!」

B「はぁ?どうせ嘘なんだろ?」

A「いや、ホントなんだって!この画像を見ろ!」

B「どのサイト?」

A「このamazon.co.jpってサイトだ!」

B「…やってみたらマジだった!おいお前ら急げ!(終了)」

というわけで最低限のパッケージでESOの世界へ入りました
oblivion懐かしい... 面白い... サーバメンテ大杉

イワナ

  • お出かけ便利ツールで釣り..
  • イワナ:2年魚以降で18cm-22cmを超える (wikipediaより)
  • んんん!?