救急病院集計

import pandas as pd


def str2time(s):

    df0 = s.str.extract("(翌日)?(\d{1,2}):(\d{1,2})").rename(
        columns={0: "day", 1: "hour", 2: "minute"}
    )

    df0["day"] = df0["day"].map({"翌日": 1})

    df1 = df0.fillna(0).astype(int)

    result = df1.apply(
        lambda s: pd.to_timedelta(s["day"], unit="day", errors="coerce")
        + pd.to_timedelta(s["hour"], unit="hour", errors="coerce")
        + pd.to_timedelta(s["minute"], unit="minute", errors="coerce"),
        axis=1,
    )

    return result


def working_time(s):

    df0 = s.str.split("〜|~", expand=True).rename(columns={0: "start", 1: "end"})

    df1 = df0.apply(str2time)

    df1["time"] = df1["end"] - df1["start"]

    return df1["time"]


url = "https://docs.google.com/spreadsheets/d/e/2PACX-1vRGFmYn7lNo2K8JemgyxzQJF9NoixxOHfa-K71XE8LI1mTlbYWJvBS040AM5HvWOcms38Ao-dyd1MI9/pub?gid=1718166270&single=true&output=csv"

df0 = pd.read_csv(
    url,
    parse_dates=["日付"],
    date_parser=lambda s: pd.to_datetime(s, format="%Y年%m月%d日", errors="coerce"),
)

df0

df0["時間"] = working_time(df0["日中"]) + working_time(df0["夜間"])

# 島しょ部は科目を島しょ部に変更

for i in ["吉海町", "宮窪町", "伯方町", "上浦町", "大三島町", "関前"]:

    df0["科目"] = df0["科目"].mask(df0["住所"].str.contains(i), "島しょ部")

df0["科目"].unique()

# CSV

df0[df0["科目"] == "小児科"].to_csv("小児科.csv")

df0[df0["科目"] == "島しょ部"].to_csv("島しょ部.csv")

df0[df0["科目"] == "内科"].to_csv("内科.csv")

df1 = df0[df0["科目"] == "指定なし"].copy()

df2 = df1.groupby(by="日付")["時間"].sum()

# 期間指定
dt_index = pd.date_range("2021-01-01", "2021-12-31")

# 期間補完
df3 = df2.reindex(dt_index)

# 期間中確認
df3[df3 != pd.Timedelta(days=1)]
import matplotlib.pyplot as plt
import japanize_matplotlib

df1["時間"] = df1["時間"].astype("timedelta64[m]")

df2 = df1.groupby(by="病院名")["時間"].sum().sort_values()

df2

df2.plot.barh()

df2.plot.pie()

df1 = df0[df0["科目"] == "指定なし"].copy()

df1["時間"] = df1["時間"].astype("timedelta64[m]")

tb1 = pd.pivot_table(
    df1, values="時間", index=["病院名"], columns=["曜日"], fill_value=0, aggfunc=sum
)
tb1

tb1.plot.barh(subplots=True, layout=(4, 2), figsize=(20, 30))

# グラフを保存
plt.savefig("tb1.png", dpi=200, bbox_inches="tight")
plt.show()

tb2 = pd.pivot_table(
    df1, values="時間", index=["曜日"], columns=["病院名"], fill_value=0, aggfunc=sum
)
tb2

tb2.plot.barh(subplots=True, layout=(5, 2), figsize=(20, 20))

# グラフを保存
plt.savefig("tb2.png", dpi=200, bbox_inches="tight")
plt.show()