月別ファイルの薬剤使用量データをPythonで一括集計する

一定期間に外来で処方されている薬の使用量を把握したいと思い、医事課の方にデータを依頼したところ、月ごとに分かれたExcelファイルで複数いただきました。こうした場合、月をまたいだ集計や薬ごとの比較を行うには、ファイルを一つにまとめる作業が必要になります。

ExcelのPower Queryなどでも統合・集計はできますが、ファイル数が多かったり、自動化したい場合にはPythonで処理を書く方が効率的です。
特に同じ形式のファイルが複数ある場合、pandasを使えばシンプルなコードで一括処理が可能です。

今回作成するプログラム
先にExcel → CSV変換する
pandasを使った集計処理
まとめ

今回作成するプログラム

（ExcelまたはCSV形式の）月別ファイルをもとに、薬品ごとの外来使用量を集計する

各ファイルは同じ列構成で、またファイル名には月情報（○○1月.xlsx, ○○2月.xlsx… のような）が入っているとします。

先にExcel → CSV変換する

今回は手に入ったファイルがExcelファイルですが、このExcelファイルには表タイトルや空行が入っていました。ここでは一旦CSVファイルに変換する処理を書きます。

Excelファイルの構成とイメージ：
・ファイル名：診療行為別集計〇月
・先頭行：表タイトル、２行目：空行：３行目：列名、４行目以降：データ

ここではinput_excelフォルダに入っている複数のExcelファイルをタイトル・空行を飛ばした後、CSVファイルに変換してconverted_csvフォルダに保存します。
位置関係は以下のとおりです。
└── 適当なフォルダ
　　　├── to_csv.py
　　　├── input_excel
　　　│　　　├── 診療行為別集計4月.xlsx
　　　│　　　├── 診療行為別集計5月.xlsx
　　　└── converted_csv
　　　　　　├── 4月.csv
　　　　　　 ├── 5月.csv

import pandas as pd
import os
 
# -- to_csv.py --
 
input_dir = "input_excel"
output_dir = "converted_csv"
 
os.makedirs(output_dir, exist_ok=True)
 
for file in os.listdir(input_dir):
    if file.endswith(".xlsx"):
        month = file.replace("診療行為別集計", "").replace(".xlsx", "").strip()
        filepath = os.path.join(input_dir, file)
        df = pd.read_excel(filepath, skiprows=2)
        df["月"] = month
        outpath = os.path.join(output_dir, f"{month}.csv")
        df.to_csv(outpath, index=False, encoding='cp932')

pandasの read_excel() でファイルを読み込み行数のスキップを行っています（skiprows=2）
またファイル名から「〇月」部分を取り出し、列名として「月」を加えています。
このようにその行のデータが何月のデータなのかがわかるようにしておくことで、後で使用量の最小値や最大値なども把握ができ、より分析がしやすくなります。
ここで保存されたcsvファイルは以下のような構成になります。
【4月.csv】

コード	名称	外来使用量	単位	月	...
123456	アセトアミノフェン	100	錠	4月	...
234567	セフカペンピボキシル	80	錠	4月	...
123456	アセトアミノフェン	200	錠	4月	...
...	...	...	...	...	...

ひとつのファイル中に同じコード（薬品）が重複していました。

pandasを使った集計処理

集計処理といっても、pandasを使って書くとあっけないくらい簡単に書けます。今回は全ファイルに渡る各薬品ごとの総使用量、月平均、最大・最小値を出してみました。

import pandas as pd
import glob
 
# 複数csvの読み込み
csv_files = glob.glob("converted_csv/*.csv")
 
dfs = []
for file in csv_files:
    df = pd.read_csv(file, encoding="cp932")
    dfs.append(df)
 
# すべてのデータを結合
all_data = pd.concat(dfs, ignore_index=True)
 
# ステップ1：月ごとの薬品の重複を合計する
monthly_sums = \
    all_data.groupby(["コード", "名称", "単位", "月"]) \
    ["外来使用量"].sum().reset_index()
 
# ステップ2：得られたmonthly_sumsを元に最終集計を行う
summary = (
    monthly_sums
    .groupby(["コード", "名称", "単位"])
    .agg(
        全月合計=("外来使用量", "sum"),
        月平均=("外来使用量", "mean"),
        最小値=("外来使用量", "min"),
        最大値=("外来使用量", "max"),
    )
    .reset_index()
)
# 集計結果を出力
summary.to_csv("summary.csv", index=False, encoding="cp932")

フォルダに入っているすべてのcsvファイルを読み込んだ後、データを結合してgroupbyで集計を行っています。
今回のデータではファイル内に薬品の重複があったため、先に合算（ステップ1）しておくことが必要になります。
この部分の処理について少し詳しく書きます。
すべてのcsvファイルが結合されたall_dataに対して、groupby(["コード", "名称", "単位", "月"])でグループ化を行い、「外来使用量」列の値を合計しています。この結果、月ごとに薬品の重複部分が合計されます。

ステップ1の処理イメージ

コード	名称	外来使用量	単位	月	...
123456	アセトアミノフェン	100	錠	4月	...
234567	セフカペンピボキシル	80	錠	4月	...
123456	アセトアミノフェン	200	錠	4月	...
123456	アセトアミノフェン	400	錠	5月	...
345678	ロキソプロフェン	50	錠	5月	...
...	...	...	...	...	...

↓　同じ月の同じデータは合計される

コード	名称	外来使用量	単位	月	...
123456	アセトアミノフェン	300（100+200）	錠	4月	...
234567	セフカペンピボキシル	80	錠	4月	...
123456	アセトアミノフェン	420	錠	5月	...
345678	ロキソプロフェン	50	錠	5月	...
...	...	...	...	...	...

次のステップ2では、得られた結果に対して全月を通した薬品ごとの合計 / 月平均 / 最小 / 最大を算出しています。.groupby().agg()を使うことで複数の合計や平均などの処理を一括で行っています。
先に各ファイルの列に「〇月」を加えておくことで、このように月ごとのデータを利用した集計を出すことができます。

ステップ2処理後のイメージ

コード	名称	単位	全月合計	月平均	最小値	最大値
123456	アセトアミノフェン	錠	720	240	100	420
...	...	...	...	...	...	...

ちなみにですが、もし最初から同じファイル内に薬品（コード）の重複がないことが保証されていれば、ステップ1の処理は必要ありません。

しかし、今回のように最終的に平均や最大・最小などを出したい場合は、月単位の情報を保持したまま.agg()の処理に移る必要があります。最初に「月」を除いた（薬品だけの）グループでsum()を行ってしまうと、月ごとの情報が失われてしまい後続の処理に支障が出てしまいます。
　
　

まとめ

今回のように月別のデータを統合・集計できると、前年同月比の変化をみたり、使用量の多い薬のランキングを出したりと感覚的にではなく、より具体的な数字を知ることができます。
またデータの量が多いと、より詳細な情報や傾向を得ることができます。このへんの可視化やグラフ描画についてはまた別記事でご紹介できればと思っています。
　