{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 哪些联赛的补时进球占比最高？\n",
    "\n",
    "用 **Football Charts 开放数据集**（版本 2026.10，CC BY 4.0）复现\n",
    "[football-charts.com/zh-cn/dataset](https://www.football-charts.com/zh-cn/dataset) 上的补时进球排名。\n",
    "\n",
    "- 数据：92 个联赛最近三个已完赛季的 76,991 场比赛，含半场比分和每个进球的分钟\n",
    "- DOI：[10.5281/zenodo.22295583](https://doi.org/10.5281/zenodo.22295583)（本版本：10.5281/zenodo.23348754）\n",
    "- 本笔记本只用 pandas 和 matplotlib；从下载到出图约一分钟"
   ],
   "id": "fc-00"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "import hashlib, io, urllib.request, zipfile\n",
    "from collections import defaultdict\n",
    "from pathlib import Path\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "VERSION = \"2026.10\"\n",
    "SHA256 = \"08d1a0863d703550fe6e7fb1ef9faed4ea3cea95c664012373b1a1e1f159be10\"   # football_charts_matches.csv\n",
    "# 两个地址内容完全相同；zenodo.org 在中国大陆部分网络下可能打不开，所以先用镜像\n",
    "MIRROR = \"https://www.football-charts.com/zh-cn/files/football_charts_matches_v2026.10.zip\"\n",
    "ZENODO = \"https://zenodo.org/records/23348754/files/football_charts_matches.csv?download=1\"\n",
    "CSV = Path(\"football_charts_matches.csv\")"
   ],
   "id": "fc-01"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 下载并校验数据\n",
    "\n",
    "文件只下载一次；SHA-256 一致，说明你拿到的数据和本页、Zenodo 上的版本逐字节相同。"
   ],
   "id": "fc-02"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "if not CSV.exists():\n",
    "    try:\n",
    "        raw = urllib.request.urlopen(MIRROR, timeout=120).read()\n",
    "        with zipfile.ZipFile(io.BytesIO(raw)) as z:\n",
    "            CSV.write_bytes(z.read(\"football_charts_matches.csv\"))\n",
    "    except Exception as e:\n",
    "        print(\"镜像下载失败，改用 Zenodo：\", e)\n",
    "        CSV.write_bytes(urllib.request.urlopen(ZENODO, timeout=300).read())\n",
    "\n",
    "digest = hashlib.sha256(CSV.read_bytes()).hexdigest()\n",
    "assert digest == SHA256, f\"校验失败：{digest}\"\n",
    "df = pd.read_csv(CSV, dtype=str, keep_default_na=False)\n",
    "print(f\"SHA-256 校验通过；{len(df):,} 场比赛，{df.league.nunique()} 个联赛\")\n",
    "df.head(3)"
   ],
   "id": "fc-03"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. 统计口径\n",
    "\n",
    "`all_goal_times` 是一场比赛所有进球的分钟，如 `\"12,45,78\"`。数据源记录补时进球的方式决定了口径：\n",
    "\n",
    "- **上半场补时**：45+2 分钟的进球被记成 **47**，不是 45+2。所以 46–48 分钟记为上半场补时（`45+`）。\n",
    "  如果简单地在第 45 分钟切开，这些球都会被算进下半场，上半场进球占比因此被低估。\n",
    "- **下半场补时**：91 分钟及以后记为 `90+`。本页排名只用这一项：它没有歧义。\n",
    "- `111` 是“无进球”的占位值，`Not found` 等无法解析的值、超过 120 的分钟都不计入。\n",
    "- 每个联赛只取最近一个完整赛季：跨年赛制取 `2025-2026`，自然年赛制取 `2025`。\n",
    "- 进球数少于 200 的联赛不排名；进球分钟覆盖率（有分钟的进球 ÷ 比分里的进球）须在 95%–105% 之间。"
   ],
   "id": "fc-04"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "def minute_bin(raw):\n",
    "    t = str(raw or \"\").strip()\n",
    "    if not t or t == \"111\":\n",
    "        return None\n",
    "    if \"+\" in t:                      # 数据里也可能出现 \"45+2\" / \"90+3\" 这种写法\n",
    "        try:\n",
    "            base = int(t.split(\"+\")[0])\n",
    "        except ValueError:\n",
    "            return None\n",
    "        if base >= 90:\n",
    "            return \"90+\"\n",
    "        if base == 45:\n",
    "            return \"45+\"\n",
    "        t = str(base)\n",
    "    try:\n",
    "        m = int(t)\n",
    "    except ValueError:\n",
    "        return None\n",
    "    if m > 120:\n",
    "        return None\n",
    "    for hi, b in ((15, \"0-15\"), (30, \"15-30\"), (45, \"30-45\"), (48, \"45+\"),\n",
    "                  (60, \"46-60\"), (75, \"60-75\"), (90, \"75-90\")):\n",
    "        if m <= hi:\n",
    "            return b\n",
    "    return \"90+\"\n",
    "\n",
    "SEASONS = {\"2025-2026\", \"2025\"}\n",
    "rows = []\n",
    "for league, g in df[df.season.isin(SEASONS)].groupby(\"league\"):\n",
    "    bins = defaultdict(int)\n",
    "    for raw in g.all_goal_times:\n",
    "        for t in raw.split(\",\"):\n",
    "            b = minute_bin(t)\n",
    "            if b:\n",
    "                bins[b] += 1\n",
    "    n = sum(bins.values())\n",
    "    score_goals = pd.to_numeric(g.total_goals).sum()\n",
    "    rows.append({\n",
    "        \"league\": league, \"league_name\": g.league_name.iat[0], \"country\": g.country.iat[0],\n",
    "        \"season\": g.season.iat[0], \"matches\": len(g), \"goals\": n,\n",
    "        \"goals_90plus\": bins[\"90+\"], \"pct_90plus\": round(100 * bins[\"90+\"] / n, 1) if n else None,\n",
    "        \"goals_45plus\": bins[\"45+\"], \"pct_45plus\": round(100 * bins[\"45+\"] / n, 1) if n else None,\n",
    "        \"minute_coverage_pct\": round(100 * n / score_goals, 1) if score_goals else 0.0,\n",
    "    })\n",
    "\n",
    "table = pd.DataFrame(rows)\n",
    "table = table[(table.goals >= 200) & table.minute_coverage_pct.between(95, 105)]\n",
    "table = table.sort_values([\"pct_90plus\", \"league\"], ascending=[False, True]).reset_index(drop=True)\n",
    "table.insert(0, \"rank\", table.index + 1)\n",
    "total = table.goals.sum()\n",
    "print(f\"{len(table)} 个联赛，{total:,} 个进球；90 分钟后的进球占 {100 * table.goals_90plus.sum() / total:.1f}%\")\n",
    "table.head(12)"
   ],
   "id": "fc-05"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. 加上中文联赛名，并与网页上的数字核对\n",
    "\n",
    "中文名取自网页提供的汇总表；下载不到时用英文名，计算结果不受影响。"
   ],
   "id": "fc-06"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "AGG = \"stoppage_time_by_league_v2026.10.csv\"\n",
    "try:\n",
    "    src = AGG if Path(AGG).exists() else \"https://www.football-charts.com/zh-cn/files/\" + AGG\n",
    "    published = pd.read_csv(src, encoding=\"utf-8-sig\")\n",
    "    table = table.merge(published[[\"league\", \"name_zh\"]], on=\"league\", how=\"left\")\n",
    "    same = (table.set_index(\"league\").pct_90plus\n",
    "            == published.set_index(\"league\").pct_90plus.reindex(table.league)).all()\n",
    "    print(\"与网页数字一致\" if same else \"与网页数字不一致，请检查数据版本\")\n",
    "except Exception as e:\n",
    "    print(\"没有拿到汇总表，用英文名：\", e)\n",
    "    table[\"name_zh\"] = table.league_name + \"（\" + table.country + \"）\"\n",
    "table[[\"rank\", \"name_zh\", \"league_name\", \"goals\", \"pct_90plus\"]].head(12)"
   ],
   "id": "fc-07"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. 画图：最高的 20 个联赛和最低的 10 个联赛"
   ],
   "id": "fc-08"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "from matplotlib import font_manager\n",
    "\n",
    "# matplotlib 默认字体不含中文：使用系统里第一个可用的中文字体\n",
    "installed = {f.name for f in font_manager.fontManager.ttflist}\n",
    "for name in (\"PingFang SC\", \"Microsoft YaHei\", \"SimHei\", \"Noto Sans CJK SC\",\n",
    "             \"Source Han Sans SC\", \"WenQuanYi Zen Hei\", \"Heiti TC\", \"Arial Unicode MS\"):\n",
    "    if name in installed:\n",
    "        plt.rcParams[\"font.sans-serif\"] = [name]\n",
    "        break\n",
    "plt.rcParams[\"axes.unicode_minus\"] = False\n",
    "\n",
    "shown = pd.concat([table.head(20), table.tail(10)])\n",
    "colors = [\"#dc2626\" if lg == \"china\" else \"#f59e0b\" for lg in shown.league]\n",
    "fig, ax = plt.subplots(figsize=(8, 9))\n",
    "ax.barh([f\"{r} {n}\" for r, n in zip(shown[\"rank\"], shown.name_zh)], shown.pct_90plus, color=colors)\n",
    "ax.invert_yaxis()\n",
    "ax.set_xlabel(\"90 分钟后的进球占全部进球的比例（%）\")\n",
    "ax.set_title(\"补时进球占比：最高的 20 个和最低的 10 个联赛（2025/26 赛季）\")\n",
    "for y, v in enumerate(shown.pct_90plus):\n",
    "    ax.text(v + 0.1, y, f\"{v:.1f}%\", va=\"center\", fontsize=8)\n",
    "fig.text(0.99, 0.005, \"数据：Football Charts 开放数据集 v2026.10 · CC BY 4.0\", ha=\"right\", fontsize=8, color=\"#64748b\")\n",
    "fig.tight_layout()\n",
    "fig.savefig(\"stoppage_time_by_league.png\", dpi=150)\n",
    "plt.show()"
   ],
   "id": "fc-09"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. 保存结果"
   ],
   "id": "fc-10"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "table.to_csv(\"my_stoppage_time_by_league.csv\", index=False, encoding=\"utf-8-sig\")  # utf-8-sig：Excel 可直接打开\n",
    "print(\"已保存 my_stoppage_time_by_league.csv\")"
   ],
   "id": "fc-11"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 引用\n",
    "\n",
    "使用这份数据请注明来源。参考格式（GB/T 7714）：\n",
    "\n",
    "> Football Charts. Football Match Results and Goal Timing, 92 Leagues (2023–2026) [DS/OL]. Version 2026.10. Zenodo, 2026. DOI: 10.5281/zenodo.23348754.\n",
    "\n",
    "BibTeX：\n",
    "\n",
    "```bibtex\n",
    "@dataset{football_charts_2026_10,\n",
    "  author    = {{Football Charts}},\n",
    "  title     = {Football Match Results and Goal Timing, 92 Leagues (2023--2026)},\n",
    "  version   = {2026.10},\n",
    "  publisher = {Zenodo},\n",
    "  year      = {2026},\n",
    "  doi       = {10.5281/zenodo.23348754},\n",
    "  url       = {https://doi.org/10.5281/zenodo.23348754}\n",
    "}\n",
    "```\n",
    "\n",
    "许可：CC BY 4.0。需要更早的赛季（2020 年起）或通过 API 调用，见\n",
    "[football-charts.com/zh-cn/developers](https://www.football-charts.com/zh-cn/developers)。"
   ],
   "id": "fc-12"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}