DPC入院中に行った診療行為を探すSQL（ついでにパラメータの変え方も）
以前、DPC中に使用した注射薬剤を探すSQLを書きました。
これはちょっと修正するだけで、あらゆる診療行為に拡張できます。
このブログではSQLの解説はあんまり真面目にしてきませんでしたので、今回はゆっくり解説をしてみましょう。
なお、SQLってなんだろ、という人には、POSTDの「初心者のためのSQLガイド」が分かりやすくコンパクトにまとまっています。オススメです。
というか私が勉強し始めたときにこの記事があれば･･･
以前紹介したSQL
select e.データ識別番号,e.実施年月日,e.診療明細名称,SUM(e.明細点数・金額*e.行為回数) from ef1601 AS e Where e.データ区分 BETWEEN 30 AND 33 AND (データ識別番号,実施年月日) IN (select データ識別番号,実施年月日 from d1601 Where データ区分 = 93) group by e.データ識別番号,e.実施年月日,e.診療明細名称 HAVING SUM(e.明細点数・金額*e.行為回数) > 0
いきなりこれを見せられてもちんぷんかんぷんだと思います。職場で後輩に見せたときは、辛そうな顔をされました。
反省しています。
さて、ここにWhere e.データ区分 BETWEEN 30 AND 33という一文がありますね。
英語を素直に読むと、「データ区分が３０と３３の間にあるところ」です。
SQLはその通りに動いて、データ区分３０から３３の間のデータ、即ち注射薬剤を引っこ抜いてきます。
さて、こんなSQLをでっち上げてデータを上に見せたところ、「じゃあ次は検査のデータを持ってきて」と言われたとします。
どうしましょうか？
検査データを引っこ抜くSQL
select e.データ識別番号,e.実施年月日,e.診療明細名称,SUM(e.明細点数・金額*e.行為回数) from ef1601 AS e Where e.データ区分 = 60 AND (データ識別番号,実施年月日) IN (select データ識別番号,実施年月日 from d1601 Where データ区分 = 93) group by e.データ識別番号,e.実施年月日,e.診療明細名称 HAVING SUM(e.明細点数・金額*e.行為回数) > 0
貴様、コピペをしたな！
はい。コピペをしました。
でもよく見てください。
3行目がWhere e.データ区分 = 60になっています。日本語にすれば「データ区分が６０のところ」です。
これでデータが出来上がりですが、どうせなので珈琲でも一杯淹れて、PCの前で考えるポーズをするとよいでしょう。
その後見せに行けば、たぶん「お、仕事をしたな」と思ってくれます。
冗談はさておき、SQLでデータ範囲を変更するのはかように簡単です。しかも平易な英文にしか見えません。
上記のコードの意味が全体としてちんぷんかんぷんでも、3行目を修正するだけなら全く難しくないはずです。
このブログで何度が書いていますが、DPCデータのような、一ヶ月で数十万行規模になるデータを扱うなら、まず手を出すのはSQLです。
皆でDPCデータのSQLクエリを共有しあう場所があれば、本当は一番いいのですが･･･
点数算定のノウハウを共有するサイトはあるようですし、探せばそんなサイトもあるのでしょうか？
【雑談】 「Python 機械学習プログラミング」を読み始めるなど
半分趣味ですが、「Python 機械学習プログラミング」を読み始めてます。
最初はscikit-learnのドキュメントを読もうとしましたが、あっさり挫折したので･･･
OK、少しばかり言い訳させてください。
チュートリアルはやりましたよ。
でも、私の限られた知的能力では、こんなドキュメントを理解するのは不可能なんです。
この手の入門書は「わかってる」ことが前提になってるのが多くて、例えば「データサイエンティスト養成講座」なんかは明らかに事前知識があることを前提してます。
オブラートに包みすぎましたね。何が言いたいかといえば、ちんぷんかんぷんってことです。
話を戻すと、本書は前提に高校数学程度の知識を要求します。
残念ながら、このエントリを書いてる人間は、それすら欠けていたので必死こいて復習してますが。
私は大学でドイツ哲学を専攻し、数学に関しては必死に敬して遠ざけるばかりでした。
そろそろ逃げ切れなくなったようです。
高校の勉強は大切ですね。高校時代、私は数学をやっていて「これが将来何の役に立つのだろう」と生意気言っていましたが、今となってははっきり言えます。
おまえが就職してから、巨大な行列データを扱う羽目になれば、自然と使うハメになるのだと。
（ついでに言うと、最近このブログの内容が殆ど「DPCデータに使えるSQLクエリ紹介」と化してきたので、ちゃんと「分析」をやろうと思った、という動機もあります。 エントリで紹介出来るようになるまで、何週間かかるか分かりませんが…）
相関係数で疾患別の特徴を見る
DPCデータを見ていると、残念ながら「件数は多いのに赤字」という疾患に出くわすことがあります。
なぜ赤字なのか、理由は疾患によりますが、理由の一つに
在院日数が伸びても資源投入に変化がない
というものがります。
DPC入院料は在院日数が伸びるにつれ下がりますが、資源投入がずっと横ばいならば、赤字の原因となるわけですね。
さて、ではどうやってそんな疾患を探せばいいのでしょうか？
| 分類番号 | 在院日数 | 出来高点数 |
|---|---|---|
| 040080x099x0xx | 1 | 3200 |
| 040080x099x0xx | 2 | 3100 |
| ... | ... | ... |
このようなデータがあるとします。
Pandasには素晴らしい機能がいくつも実装されていますが、今回は.corr()メソッドを使ってみましょう。
該当のデータをdfとすると、
df.corr()
と入力するだけで、以下のような表が出力されます。
| 在院日数 | 出来高点数 | |
|---|---|---|
| 在院日数 | 1.0000 | 0.2388 |
| 出来高点数 | 0.2388 | 1.0000 |
この表の意味は、「出来高点数と在院日数のあいだの相関係数は0.2388である」ということです。
･･･うーん。どういうことでしょうか。
相関係数は、-1から+1の間を取る指標で、±0.7以上なら強い相関があると認められます。
マイナスなら負の相関、プラスなら正の相関です。
この例では、負の相関があれば在院日数が伸びるほど出来高点数は下がるわけですね。
一方、正の相関があれば在院日数が伸びるほど出来高点数は上がることになってしまいます（！）
つまり、正の相関があるとか、相関が見られない場合、DPCで想定される通りには推移していない疾患だ、ということです。
もちろん、これはあくまで目安ですし、実際にデータを見るととんでもない外れ値がいて全体を歪めている、なんてこともザラですので、散布図を書くなどして確かめるのがよいでしょう。
Pandasには散布図を手っ取り早く描くメソッドも用意されています。
df.plot(kind='scatter', x='在院日数', y='差異')と入力すれば、すぐに散布図が見れます。とても捗りますね。
アプリケーションについて幾つか
データ分析をやっていると、当然、分析結果をプレゼンすることがあります。
大体はパワーポイントをでっち上げますが、パワポは人間の「理解しよう」という気を失わせる道具です。
スライドが１枚進むたびに、聴衆の興味は失われていきます。
（もちろん、あなたがプレゼンの名手なら話は別ですが）
それに、その場でデータを動かしながら話そうとすると、パワポではとても対応出来ません。
ということで、アプリケーションの出番です。
が、最初にWindowsで走るexeファイルを作ろうとして、あっさり挫折しました。
私は殆どの分析作業をPython＋Pandasで行います。Windowsアプリケーションにするためのツールとしては、Pyinstallerなどがありますが、Pandasを含むスクリプトはビルドに失敗してしまうのです。
その手のツールは何種類か試しましたか、全滅でした。
（もし成功例があったら教えてください･･･）
結局、FlaskというWebアプリケーションのライブラリを使って、Webアプリをでっち上げました。
その時の教訓は･･･
配布しないのであれば、Webアプリケーションを書いた方がずっと早いし、簡単である。
てゆーか、ローカルアプリケーションの開発が盛んなのってモバイルだけでは？
という感じです。まあ、今更な話ですね。
Windowsのことを綺麗さっぱり忘れるなら、Swiftは是非試したいのですが、現在の環境だと中々難しいです。
PostgreSQL : crosstabの使い方
遅れましたがcrosstab関数の紹介です。
まず前回使ったコードをおさらいしましょう。
select *
from
crosstab('select データ識別番号,連番,ペイロード9
from ff1test
Where コード = ''A006040''',
'select m from generate_series(1,10) m')
AS(
データ識別番号 int,
病名1 text,
病名2 text,
病名3 text,
病名4 text,
病名5 text,
病名6 text,
病名7 text,
病名8 text,
病名9 text,
病名10 text);
ではcrosstab部分の構造を見ていきましょう。 この関数は、3つの部分を持ちます。
データソースを取り出すSQL構文
整形したい表に必要なカテゴリ項目の列挙
2で列挙した項目の定義付け
1) については、ドキュメントに
source_sqlは元となるデータ集合を生成するSQL文です。 このSQL文はrow_name列を1つcategory列を1つ、value列を1つ返さなければなりません。
とあります。
crosstab('select データ識別番号,連番,ペイロード9
from ff1test
Where コード = ''A006040''',
の部分ですね。
この例では、データ識別番号がrow_name、連番がcategory列、ペイロード9がvalue列です。
2)は
'select m from generate_series(1,10) m')
の部分ですが、見ればわかる通り、ほぼドキュメントの例の引き写しです。ペイロード9に保持される併存病名は上限10なので、1から10の数列を用意します。
仕組みとしては、カテゴリー列を持つデータを用意して、カテゴリー列を元に横向きに並び替える、という動きをします。本来はクロス集計に用いる関数なので、このカテゴリ列ごとに集計を行うのですが、今回は単にデータを横持ちにするだけです。
3)で、出来上がる表のデータ形式を定義します。
AS( データ識別番号 int, 病名1 text, 病名2 text, 病名3 text, 病名4 text, 病名5 text, 病名6 text, 病名7 text, 病名8 text, 病名9 text, 病名10 text);
テーブルの定義文にそっくりですね。
今回は普通にintとtextを使いました。
以上でざっと紹介を終えますが、公式ドキュメントとにらめっこするのは必須でしょう。
あんまり複雑になるようならスクリプトで加工した方がいいと思いますが、今回のように「複数行にまたがるデータを１行に圧縮して欲しい」というケースでは大変便利です
様式１ファイルを扱ってみる（２）
<注> 今回のSQLはPostgreSQLの拡張機能を使います！Postgre以外では動きません！
今回は前回作った様式１データから、横持ちのデータを取り出してみます。
やってみたいのは、以下のようなデータ抽出です。
元データ
| データ識別番号 | 連番 | ペイロード9 |
|---|---|---|
| 001 | 1 | 心不全 |
| 001 | 2 | 肺炎 |
| 001 | 3 | 狭心症 |
| 001 | 4 | 心房細動 |
取り出すデータ
| データ識別番号 | 病名1 | 病名2 | 病名3 | 病名4 |
|---|---|---|---|---|
| 001 | 心不全 | 肺炎 | 狭心症 | 心房細動 |
さて、どうしたものでしょうか。 ここでCASE式を活用したSQLを書けたら格好いいのですが、生憎上手く行かなかったので、PostgreSQLの拡張機能を使うことにします。 まずは以下のコードを実行してください。
CREATE EXTENSION tablefunc;
ドキュメントはこちらです。
今回はこの中のcrosstab(text, text)関数を使います。
実際のSQLはこのようになります。
select *
from
crosstab('select データ識別番号,連番,ペイロード9
from ff1test
Where コード = ''A006040''',
'select m from generate_series(1,10) m')
AS(
データ識別番号 int,
病名1 text,
病名2 text,
病名3 text,
病名4 text,
病名5 text,
病名6 text,
病名7 text,
病名8 text,
病名9 text,
病名10 text);
…何が起きたのでしょうか。
半分ドキュメントからの引き写しですが、どうやら上手く行ったようです。
結果は
| データ識別番号 | 病名1 | 病名2 | 病名3 | 病名4 | —病名10 |
|---|---|---|---|---|---|
| 001 | 心不全 | 肺炎 | 狭心症 | 心房細動 | —病名ラスト |
のようになり、しかも（賢いことに）病名が１０個も入っていない場合は、自動的にNULLが入ります。
確かに目的通りの結果ですが、今まで書いてきたSQLとは大分毛色が違うシロモノで、何が何だかわからないという人も多いでしょう。
（告白すると、私はドキュメントを前にしばらく頭を抱えていました）
次回はcrosstab関数の簡単な解説をしようと思います。
様式１ファイルを扱ってみる（１）
今までのエントリでは、DPCデータのうちEFファイルとDファイルに焦点を絞って話してきました。
当然、こう思う方もいるでしょう。
「あれ、様式１はどこいったの？」
面倒くさいので扱いたくなかった試行錯誤の途中なのでエントリにしていませんでしたが、今回は様式１の話をしてみます。
まずは基本的な考え方から。
- 旧様式1
| id | 項目１ | 項目２ | 項目３ | 項目４ | 項目5 | …項目n |
|---|---|---|---|---|---|---|
| 001 | 中身１ | 中身２ | 中身３ | 中身４ | 中身５ | …中身N |
| 002 | 中身１ | 中身２ | 中身３ | 中身４ | 中身５ | …中身N |
横１行に全てのデータが入っています。 これをデータの横持ちと呼び、SQLなどで別のデータと結合するのにとても便利です。 問題は、データ項目が膨れ上がった際、横700列など、人間にもDBにも優しくない構造になることでしょうか。
- 新様式1
流石にこれはいかん、ということで様式１はこんな形になりました。
| id | カテゴリ番号 | ペイロード1 | ペイロード2 | …ペイロード9 |
|---|---|---|---|---|
| 001 | カテゴリ１ | 中身1 | 中身2 | …中身9 |
| 001 | カテゴリ２ | 中身1 | 中身2 | …中身9 |
| 001 | カテゴリ３ | 中身1 | 中身2 | …中身9 |
| 001 | カテゴリ４ | 中身1 | 中身2 | …中身9 |
カテゴリー項目を持たせ（本物の様式１ではコードと呼ばれます）、それに対応する中身がペイロード１〜９に記載されます。 例えば病名カテゴリであれば、ペイロード1~９にICD10や日本語病名が入り、患者情報であれば、身長体重が入ったりします。 このように、idを重複させて縦方向にデータを伸ばすのを縦持ちと呼び、多様なデータを保存できますが、そのままSQLなどに読み込むには不向きです。
現在の様式１は縦持ちデータです。さて、どう扱ったらいいものでしょうか？
方法１ テーブルを横持ちに変換する
メリット
深く考える必要がありません。 データは１入院１レコードですので、EF／Dファイルで集計したデータとの結合が容易です。デメリット
当然ながら、事前に横持ちに変換する必要があります。 様式１のサイズは小さいので、Excelのマクロでも対処可能ですし、スクリプトを書いてもよいでしょう。 また、将来的に新しい項目が追加された場合、テーブル定義を書き直すことになります。 （残念ながら、大変ありそうな話です）
方法２ テーブルを縦持ちのまま読み込む
メリット
事前処理は不要です。そのまま読み込んですぐ使えます。 また、将来的の新項目追加にも、テーブル定義の書き直しなどは不要でしょう。 現在でも様式１はスカスカですので、ペイロード１０番とかが追加される可能性は低いと考えられます。デメリット
他のテーブルと結合する際、横持ちに変換する必要があります。 これはSQLで書いてもよいですし、スクリプト側で処理してもよいでしょう。
ここではまず、方法２のテーブル定義を用意しました。次回から、これをどう扱うか考えていきます。
CREATE TABLE FF1607 (施設コード VARCHAR(255), データ識別番号 INTEGER, 入院年月日 DATE, 回数管理番号 INTEGER, 統括診療情報番号 VARCHAR(255), コード VARCHAR(255), バージョン DATE, 連番 INTEGER, ペイロード1 VARCHAR(255), ペイロード2 VARCHAR(255), ペイロード3 VARCHAR(255), ペイロード4 VARCHAR(255), ペイロード5 VARCHAR(255), ペイロード6 VARCHAR(255), ペイロード7 VARCHAR(255), ペイロード8 VARCHAR(255), ペイロード9 VARCHAR(255), id SERIAL PRIMARY KEY);
2016-10-20 追記
コメント欄で方法１の実装を書いて欲しい、というご意見を貰いましたので、Pythonでスクリプトを書いてみました。
import pandas as pd
FF1 = pd.read_csv('/ファイルの/パス/様式1.txt',delimiter = '\t')
ptable = pd.pivot_table(FF1,values=['ペイロード１（日付等）','ペイロード２（コード等）','ペイロード３','ペイロード４','ペイロード５',
'ペイロード６','ペイロード７','ペイロード８','ペイロード９（可変長文字列）'],
index=['施設コード','データ識別番号','入院年月日','回数管理番号','統括診療情報番号','バージョン','連番'],
columns = ['コード'])
pd.pivot_tableメソッドは読み込んだデータの変形を行います。
ExcelのピボットテーブルのPandas版ですね。
Python + Pandasではこのように、ほぼワンライナーで完結しますが、Excelのマクロだと結構な行数になるかも知れません。
私はVBAがさっぱり分からないので、山勘ですが…
