プログラミング的思考── 分解・抽象化・パターン認識・アルゴリズム設計・検証を、実際の課題で順に

"""Tally a request ledger: days to finish and return rate by department, type and month."""
import pandas as pd

ledger = pd.read_excel("samples/request_ledger.xlsx", sheet_name="依頼台帳")
done = ledger[ledger["状態"].isin(["完了", "差戻し"])].copy()
done["日数"] = (pd.to_datetime(done["完了日"]) - pd.to_datetime(done["依頼日"])).dt.days
done["差戻し"] = done["状態"] == "差戻し"
done["依頼月"] = pd.to_datetime(done["依頼日"]).dt.to_period("M")

summary = (done.groupby(["依頼部署", "種別", "依頼月"])
               .agg(件数=("依頼ID", "count"), 日数の中央値=("日数", "median"), 差戻しの割合=("差戻し", "mean"))
               .reset_index())
summary["依頼月"] = summary["依頼月"].astype(str)
summary.to_excel("out_summary.xlsx", index=False)
print("rows in ledger:", len(ledger), "/ rows used:", len(done), "/ groups:", len(summary))
print(summary.head(8).to_string(index=False))

"""Check the ledger tally by a second route: invariants and one group counted by plain filtering."""
import pandas as pd

ledger = pd.read_excel("samples/request_ledger.xlsx", sheet_name="依頼台帳")
done = ledger[ledger["状態"].isin(["完了", "差戻し"])].copy()
done["日数"] = (pd.to_datetime(done["完了日"]) - pd.to_datetime(done["依頼日"])).dt.days
by_group = done.groupby(["依頼部署", "種別"]).size()

assert by_group.sum() == len(done), "group counts must add up to the rows used"
assert (done["日数"] >= 0).all(), "a finish date is earlier than its request date"

dept, kind = by_group.idxmax()  # the largest group
manual = ((ledger["依頼部署"] == dept) & (ledger["種別"] == kind) & ledger["状態"].isin(["完了", "差戻し"])).sum()
assert manual == by_group[(dept, kind)], "groupby and plain filtering disagree"
rate = (done["状態"] == "差戻し").mean()
assert 0 <= rate <= 1, "a rate must be between 0 and 1"
print("largest group checked by plain filtering:", dept, kind, int(manual), "rows")
print("all checks passed")