"""The same cleaning and summary as wrangle.py, written with pandas.

Install pandas first:  pip install pandas
Run it:                python wrangle_pandas.py

Compare this file with wrangle.py. pandas is shorter for table work. The standard
library version is easier to read line by line when you are learning, and needs
no installs. Both give the same numbers.

Dedicated to the public domain under CC0 1.0.
"""
from __future__ import annotations

from pathlib import Path

import pandas as pd

HERE = Path(__file__).parent
BRANCHES = {"central": "Central", "harbor": "Harbor", "hillside": "Hillside", "riverside": "Riverside"}
CATEGORIES = {
    "fiction": "Fiction",
    "non-fiction": "Non-fiction",
    "non fiction": "Non-fiction",
    "children": "Children",
    "audio": "Audio",
    "reference": "Reference",
}

# 1. Load everything as text so nothing is guessed, and keep empty cells empty.
df = pd.read_csv(HERE / "loans.csv", dtype=str, keep_default_na=False)
rows_read = len(df)

# 2. Clean.
df["date"] = df["date"].str.strip()
df["month"] = df["date"].str[:7]
df["branch"] = df["branch"].str.strip().str.lower().map(BRANCHES)
df["category"] = df["category"].str.strip().str.lower().map(CATEGORIES)
df["notes"] = df["notes"].str.strip()
for column in ["loans", "late_returns", "avg_wait_days"]:
    text = df[column].str.replace(",", "", regex=False).str.strip()
    df[column] = pd.to_numeric(text.where(text != ""))
unknown = int(df[["branch", "category"]].isna().any(axis=1).sum())
df = df.dropna(subset=["branch", "category"])

before = len(df)
df = df.drop_duplicates()
duplicates = before - len(df)

before = len(df)
df = df.dropna(subset=["loans"])
dropped = before - len(df)

print("CLEANING REPORT")
print(f"  rows read               {rows_read}")
print(f"  duplicates removed      {duplicates}")
print(f"  dropped missing loans   {dropped}")
print(f"  unknown names           {unknown}")
print(f"  rows clean              {len(df)}")

# 3. Summarize.
print("\nTOTAL LOANS BY BRANCH")
for branch, total in df.groupby("branch")["loans"].sum().sort_values(ascending=False).items():
    print(f"  {branch:<10}{int(total):>8,}")

known = df.dropna(subset=["late_returns"])
sums = known.groupby("branch")[["late_returns", "loans"]].sum()
print("\nLATE-RETURN RATE BY BRANCH")
for branch, rate in (sums["late_returns"] / sums["loans"]).sort_values(ascending=False).items():
    print(f"  {branch:<10}{rate:>7.1%}")

print("\nAVERAGE WAIT (DAYS) BY CATEGORY")
for category, wait in df.groupby("category")["avg_wait_days"].mean().sort_values().items():
    print(f"  {category:<12}{wait:>6.2f}")