004 - GwentFindMorePlayers.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting stats for more players\n",
    "\n",
    "On playgwent you can only find the stats for the top 2860 players of each season. However, there are many more players in Pro Rank we can't find in this bracket. While it is impossible to get a complete picture of everyone, there is a trick you we'll try to use here to get some additional data.\n",
    "\n",
    "While players below 2860 aren't listed on the website, you can pull up their details by going to a URL like this:\n",
    "\n",
    "[https://masters.playgwent.com/en/rankings/masters-2/season-of-the-dryad/1/1/sepro](https://masters.playgwent.com/en/rankings/masters-2/season-of-the-dryad/1/1/sepro)\n",
    "\n",
    "If we know a players name, we can pull up the rank, country, number of games and the total MMR, regardless of where they are on ladder. So we'll take all players that were on Pro Rank in the top 2860 and check, for seasons they were not featured on the website, if they were residing somewhere lower on ladder. This will, hopefully, give us a much more complete picture how may players there are."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm import tqdm\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rank</th>\n",
       "      <th>name</th>\n",
       "      <th>country</th>\n",
       "      <th>matches</th>\n",
       "      <th>mmr</th>\n",
       "      <th>season</th>\n",
       "      <th>previous_top500</th>\n",
       "      <th>national_rank</th>\n",
       "      <th>efficiency</th>\n",
       "      <th>lei</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>kolemoen</td>\n",
       "      <td>Germany</td>\n",
       "      <td>431</td>\n",
       "      <td>10484</td>\n",
       "      <td>M2_01 Wolf 2020</td>\n",
       "      <td>no</td>\n",
       "      <td>1</td>\n",
       "      <td>2.051044</td>\n",
       "      <td>42.580782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>kams134</td>\n",
       "      <td>Poland</td>\n",
       "      <td>923</td>\n",
       "      <td>10477</td>\n",
       "      <td>M2_01 Wolf 2020</td>\n",
       "      <td>no</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950163</td>\n",
       "      <td>28.866807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>TailBot</td>\n",
       "      <td>Poland</td>\n",
       "      <td>538</td>\n",
       "      <td>10472</td>\n",
       "      <td>M2_01 Wolf 2020</td>\n",
       "      <td>no</td>\n",
       "      <td>2</td>\n",
       "      <td>1.620818</td>\n",
       "      <td>37.594590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Pajabol</td>\n",
       "      <td>Poland</td>\n",
       "      <td>820</td>\n",
       "      <td>10471</td>\n",
       "      <td>M2_01 Wolf 2020</td>\n",
       "      <td>no</td>\n",
       "      <td>3</td>\n",
       "      <td>1.062195</td>\n",
       "      <td>30.416639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Adzikov</td>\n",
       "      <td>Poland</td>\n",
       "      <td>1105</td>\n",
       "      <td>10442</td>\n",
       "      <td>M2_01 Wolf 2020</td>\n",
       "      <td>no</td>\n",
       "      <td>4</td>\n",
       "      <td>0.761991</td>\n",
       "      <td>25.329753</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rank      name  country  matches    mmr           season previous_top500  \\\n",
       "0     1  kolemoen  Germany      431  10484  M2_01 Wolf 2020              no   \n",
       "1     2   kams134   Poland      923  10477  M2_01 Wolf 2020              no   \n",
       "2     3   TailBot   Poland      538  10472  M2_01 Wolf 2020              no   \n",
       "3     4   Pajabol   Poland      820  10471  M2_01 Wolf 2020              no   \n",
       "4     5   Adzikov   Poland     1105  10442  M2_01 Wolf 2020              no   \n",
       "\n",
       "   national_rank  efficiency        lei  \n",
       "0              1    2.051044  42.580782  \n",
       "1              1    0.950163  28.866807  \n",
       "2              2    1.620818  37.594590  \n",
       "3              3    1.062195  30.416639  \n",
       "4              4    0.761991  25.329753  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read list of players\n",
    "players_df = pd.read_excel('./output/player_stats.xlsx').drop(columns=['Unnamed: 0'])\n",
    "players_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['kolemoen', 'kams134', 'TailBot', ..., 'k_pastuszka', 'liome10',\n",
       "       'The_SaltyCaptain'], dtype=object)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get unique list of players' names\n",
    "all_players = players_df.name.unique()\n",
    "all_players"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "./output/season_of_the_wolf_2020_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_love_2020_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_bear_2020_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_elf_2020_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_viper_2020_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_magic_2020_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_griffin_2020_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_draconid_2020_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_dryad_2020_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_cat_2020_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_mahakam_2020_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_wild_hunt_2020_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_wolf_2021_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_love_2021_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_bear_2021_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_elf_2021_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_viper_2021_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_magic_2021_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_griffin_2021_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_draconid_2021_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_dryad_2021_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_cat_2021_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_mahakam_2021_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_wild_hunt_2021_extra.xlsx exists, loading file instead of downloading ...\n",
      "./output/season_of_the_wolf_2022_extra.xlsx exists, loading file instead of downloading ...\n"
     ]
    }
   ],
   "source": [
    "seasons = [\n",
    "    ('M2_01 Wolf 2020', 'https://masters.playgwent.com/en/rankings/masters-2/season-of-the-wolf/1/1/{user}', './output/season_of_the_wolf_2020_extra.xlsx'),\n",
    "    ('M2_02 Love 2020', 'https://masters.playgwent.com/en/rankings/masters-2/season-of-love/1/1/{user}', './output/season_of_love_2020_extra.xlsx'),\n",
    "    ('M2_03 Bear 2020', 'https://masters.playgwent.com/en/rankings/masters-2/season-of-the-bear/1/1/{user}', './output/season_of_the_bear_2020_extra.xlsx'),\n",
    "    ('M2_04 Elf 2020', 'https://masters.playgwent.com/en/rankings/masters-2/season-of-the-elf/1/1/{user}', './output/season_of_the_elf_2020_extra.xlsx'),\n",
    "    ('M2_05 Viper 2020', 'https://masters.playgwent.com/en/rankings/masters-2/season-of-the-viper/1/1/{user}', './output/season_of_the_viper_2020_extra.xlsx'),\n",
    "    ('M2_06 Magic 2020', 'https://masters.playgwent.com/en/rankings/masters-2/season-of-the-magic/1/1/{user}', './output/season_of_magic_2020_extra.xlsx'),\n",
    "    ('M2_07 Griffin 2020', 'https://masters.playgwent.com/en/rankings/masters-2/season-of-the-griffin/1/1/{user}', './output/season_of_the_griffin_2020_extra.xlsx'),\n",
    "    ('M2_08 Draconid 2020', 'https://masters.playgwent.com/en/rankings/masters-2/season-of-the-draconid/1/1/{user}', './output/season_of_the_draconid_2020_extra.xlsx'),\n",
    "    ('M2_09 Dryad 2020', 'https://masters.playgwent.com/en/rankings/masters-2/season-of-the-dryad/1/1/{user}', './output/season_of_the_dryad_2020_extra.xlsx'),\n",
    "    ('M2_10 Cat 2020', 'https://masters.playgwent.com/en/rankings/masters-2/season-of-the-cat/1/1/{user}', './output/season_of_the_cat_2020_extra.xlsx'),\n",
    "    ('M2_11 Mahakam 2020', 'https://masters.playgwent.com/en/rankings/masters-2/season-of-the-mahakam/1/1/{user}', './output/season_of_the_mahakam_2020_extra.xlsx'),\n",
    "    ('M2_12 Wild Hunt 2020', 'https://masters.playgwent.com/en/rankings/masters-2/season-of-the-wild-hunt/1/1/{user}', './output/season_of_the_wild_hunt_2020_extra.xlsx'),\n",
    "    ('M3_01 Wolf 2021', 'https://masters.playgwent.com/en/rankings/masters-3/season-of-the-wolf/1/1/{user}', './output/season_of_the_wolf_2021_extra.xlsx'),\n",
    "    ('M3_02 Love 2021', 'https://masters.playgwent.com/en/rankings/masters-3/season-of-the-love/1/1/{user}', './output/season_of_love_2021_extra.xlsx'),\n",
    "    ('M3_03 Bear 2021', 'https://masters.playgwent.com/en/rankings/masters-3/season-of-the-bear/1/1/{user}', './output/season_of_the_bear_2021_extra.xlsx'),\n",
    "    ('M3_04 Elf 2021', 'https://masters.playgwent.com/en/rankings/masters-3/season-of-the-elf/1/1/{user}', './output/season_of_the_elf_2021_extra.xlsx'),\n",
    "    ('M3_05 Viper 2021', 'https://masters.playgwent.com/en/rankings/masters-3/season-of-the-viper/1/1/{user}', './output/season_of_the_viper_2021_extra.xlsx'),\n",
    "    ('M3_06 Magic 2021', 'https://masters.playgwent.com/en/rankings/masters-3/season-of-the-magic/1/1/{user}', './output/season_of_magic_2021_extra.xlsx'),\n",
    "    ('M3_07 Griffin 2021', 'https://masters.playgwent.com/en/rankings/masters-3/season-of-the-griffin/1/1/{user}', './output/season_of_griffin_2021_extra.xlsx'),\n",
    "    ('M3_08 Draconid 2021', 'https://masters.playgwent.com/en/rankings/masters-3/season-of-the-draconid/1/1/{user}', './output/season_of_the_draconid_2021_extra.xlsx'),\n",
    "    ('M3_09 Dryad 2021', 'https://masters.playgwent.com/en/rankings/masters-3/season-of-the-dryad/1/1/{user}', './output/season_of_the_dryad_2021_extra.xlsx'),\n",
    "    ('M3_10 Cat 2021', 'https://masters.playgwent.com/en/rankings/masters-3/season-of-the-cat/1/1/{user}', './output/season_of_the_cat_2021_extra.xlsx'),\n",
    "    ('M3_11 Mahakam 2021', 'https://masters.playgwent.com/en/rankings/masters-3/season-of-mahakam/1/1/{user}', './output/season_of_the_mahakam_2021_extra.xlsx'),\n",
    "    ('M3_12 Wild Hunt 2021', 'https://masters.playgwent.com/en/rankings/masters-3/season-of-the-wild-hunt/1/1/{user}', './output/season_of_the_wild_hunt_2021_extra.xlsx'),\n",
    "    ('M4_01 Wolf 2022', 'https://masters.playgwent.com/en/rankings/masters-4/season-of-the-wolf/1/1/{user}', './output/season_of_the_wolf_2022_extra.xlsx'),\n",
    "   #  ('M4_02 Love 2022', 'https://masters.playgwent.com/en/rankings/masters-4/season-of-the-love/1/1/{user}', './output/season_of_love_2022_extra.xlsx'),\n",
    "\n",
    "]\n",
    "\n",
    "for season, url_template, output_path in seasons:\n",
    "    if os.path.exists(output_path):\n",
    "        print(f\"{output_path} exists, loading file instead of downloading ...\")\n",
    "        df = pd.read_excel(output_path).drop(['Unnamed: 0'], axis=1)\n",
    "    else:\n",
    "        output = []\n",
    "        known_players = players_df[players_df.season == season].name.values\n",
    "        unknown_players = [n for n in all_players if n not in known_players]\n",
    "        \n",
    "        for player in tqdm(unknown_players):       \n",
    "            url = url_template.replace('{user}', str(player))\n",
    "            try:\n",
    "                r = requests.get(url)\n",
    "                soup = BeautifulSoup(r.text, 'html.parser')\n",
    "                rows = soup.find_all(\"div\", {\"class\": \"c-ranking__inner-frame-found\"})\n",
    "                for row in rows[:1]:\n",
    "                    flag = row.find(\"i\", {\"class\": \"flag-icon\"})[\"class\"][1]\n",
    "                    new_record = {\n",
    "                        'rank': int(row.find(\"div\", {\"class\": \"td-number\"}).text.strip()),\n",
    "                        'name': row.find(\"div\", {\"class\": \"td-nick\"}).text.strip(),\n",
    "                        'country': flag.replace('flag-icon-', '').upper(),\n",
    "                        'matches': int(row.find(\"div\", {\"class\": \"td-matches\"}).text.strip().replace(' matches', '')),\n",
    "                        'mmr': int(row.find(\"div\", {\"class\": \"td-mmr\"}).text.strip().replace(',', '')),\n",
    "                        'season': season\n",
    "                    }\n",
    "                    if 0 < new_record['matches']:\n",
    "                        output.append(new_record)\n",
    "            except:\n",
    "                pass\n",
    "            \n",
    "        df = pd.DataFrame(output).drop_duplicates()\n",
    "        df.to_excel(output_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rank</th>\n",
       "      <th>name</th>\n",
       "      <th>country</th>\n",
       "      <th>matches</th>\n",
       "      <th>mmr</th>\n",
       "      <th>season</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2920</td>\n",
       "      <td>wlsgml</td>\n",
       "      <td>KR</td>\n",
       "      <td>87</td>\n",
       "      <td>1844</td>\n",
       "      <td>M2_01 Wolf 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2979</td>\n",
       "      <td>浩歌</td>\n",
       "      <td>CN</td>\n",
       "      <td>10</td>\n",
       "      <td>965</td>\n",
       "      <td>M2_01 Wolf 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2909</td>\n",
       "      <td>loemydew</td>\n",
       "      <td>US</td>\n",
       "      <td>21</td>\n",
       "      <td>2018</td>\n",
       "      <td>M2_01 Wolf 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2997</td>\n",
       "      <td>Ghostfacekillah_21</td>\n",
       "      <td>RU</td>\n",
       "      <td>1</td>\n",
       "      <td>96</td>\n",
       "      <td>M2_01 Wolf 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2983</td>\n",
       "      <td>莫如人</td>\n",
       "      <td>CN</td>\n",
       "      <td>6</td>\n",
       "      <td>577</td>\n",
       "      <td>M2_01 Wolf 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4743</th>\n",
       "      <td>3028</td>\n",
       "      <td>CuteRacer</td>\n",
       "      <td>BY</td>\n",
       "      <td>186</td>\n",
       "      <td>9639</td>\n",
       "      <td>M4_01 Wolf 2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4744</th>\n",
       "      <td>9733</td>\n",
       "      <td>ChesterST</td>\n",
       "      <td>PL</td>\n",
       "      <td>96</td>\n",
       "      <td>5594</td>\n",
       "      <td>M4_01 Wolf 2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4745</th>\n",
       "      <td>6280</td>\n",
       "      <td>Timh11</td>\n",
       "      <td>RU</td>\n",
       "      <td>139</td>\n",
       "      <td>7846</td>\n",
       "      <td>M4_01 Wolf 2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4746</th>\n",
       "      <td>3535</td>\n",
       "      <td>siddharth021</td>\n",
       "      <td>IN</td>\n",
       "      <td>143</td>\n",
       "      <td>9620</td>\n",
       "      <td>M4_01 Wolf 2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4747</th>\n",
       "      <td>14162</td>\n",
       "      <td>Noobaz</td>\n",
       "      <td>US</td>\n",
       "      <td>182</td>\n",
       "      <td>2476</td>\n",
       "      <td>M4_01 Wolf 2022</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>108382 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       rank                name country  matches   mmr           season\n",
       "0      2920              wlsgml      KR       87  1844  M2_01 Wolf 2020\n",
       "1      2979                  浩歌      CN       10   965  M2_01 Wolf 2020\n",
       "2      2909            loemydew      US       21  2018  M2_01 Wolf 2020\n",
       "3      2997  Ghostfacekillah_21      RU        1    96  M2_01 Wolf 2020\n",
       "4      2983                 莫如人      CN        6   577  M2_01 Wolf 2020\n",
       "...     ...                 ...     ...      ...   ...              ...\n",
       "4743   3028           CuteRacer      BY      186  9639  M4_01 Wolf 2022\n",
       "4744   9733           ChesterST      PL       96  5594  M4_01 Wolf 2022\n",
       "4745   6280              Timh11      RU      139  7846  M4_01 Wolf 2022\n",
       "4746   3535        siddharth021      IN      143  9620  M4_01 Wolf 2022\n",
       "4747  14162              Noobaz      US      182  2476  M4_01 Wolf 2022\n",
       "\n",
       "[108382 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_df = pd.concat(pd.read_excel(f) for _,_,f in seasons).drop(columns=['Unnamed: 0']).dropna()\n",
    "full_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>max_rank</th>\n",
       "      <th>min_mmr</th>\n",
       "      <th>top 500 (%)</th>\n",
       "      <th>top 200 (%)</th>\n",
       "      <th>top 64 (%)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>season</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>M2_01 Wolf 2020</th>\n",
       "      <td>2997</td>\n",
       "      <td>96</td>\n",
       "      <td>16.683350</td>\n",
       "      <td>6.673340</td>\n",
       "      <td>2.135469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_02 Love 2020</th>\n",
       "      <td>4883</td>\n",
       "      <td>96</td>\n",
       "      <td>10.239607</td>\n",
       "      <td>4.095843</td>\n",
       "      <td>1.310670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_03 Bear 2020</th>\n",
       "      <td>6632</td>\n",
       "      <td>96</td>\n",
       "      <td>7.539204</td>\n",
       "      <td>3.015682</td>\n",
       "      <td>0.965018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_04 Elf 2020</th>\n",
       "      <td>10209</td>\n",
       "      <td>96</td>\n",
       "      <td>4.897639</td>\n",
       "      <td>1.959056</td>\n",
       "      <td>0.626898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_05 Viper 2020</th>\n",
       "      <td>10079</td>\n",
       "      <td>96</td>\n",
       "      <td>4.960810</td>\n",
       "      <td>1.984324</td>\n",
       "      <td>0.634984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_06 Magic 2020</th>\n",
       "      <td>9919</td>\n",
       "      <td>96</td>\n",
       "      <td>5.040831</td>\n",
       "      <td>2.016332</td>\n",
       "      <td>0.645226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_07 Griffin 2020</th>\n",
       "      <td>14791</td>\n",
       "      <td>96</td>\n",
       "      <td>3.380434</td>\n",
       "      <td>1.352174</td>\n",
       "      <td>0.432696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_08 Draconid 2020</th>\n",
       "      <td>13800</td>\n",
       "      <td>96</td>\n",
       "      <td>3.623188</td>\n",
       "      <td>1.449275</td>\n",
       "      <td>0.463768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_09 Dryad 2020</th>\n",
       "      <td>14554</td>\n",
       "      <td>96</td>\n",
       "      <td>3.435482</td>\n",
       "      <td>1.374193</td>\n",
       "      <td>0.439742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_10 Cat 2020</th>\n",
       "      <td>16011</td>\n",
       "      <td>96</td>\n",
       "      <td>3.122853</td>\n",
       "      <td>1.249141</td>\n",
       "      <td>0.399725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_11 Mahakam 2020</th>\n",
       "      <td>16752</td>\n",
       "      <td>96</td>\n",
       "      <td>2.984718</td>\n",
       "      <td>1.193887</td>\n",
       "      <td>0.382044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_12 Wild Hunt 2020</th>\n",
       "      <td>22464</td>\n",
       "      <td>96</td>\n",
       "      <td>2.225783</td>\n",
       "      <td>0.890313</td>\n",
       "      <td>0.284900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_01 Wolf 2021</th>\n",
       "      <td>13428</td>\n",
       "      <td>96</td>\n",
       "      <td>3.723563</td>\n",
       "      <td>1.489425</td>\n",
       "      <td>0.476616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_02 Love 2021</th>\n",
       "      <td>16016</td>\n",
       "      <td>96</td>\n",
       "      <td>3.121878</td>\n",
       "      <td>1.248751</td>\n",
       "      <td>0.399600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_03 Bear 2021</th>\n",
       "      <td>13636</td>\n",
       "      <td>192</td>\n",
       "      <td>3.666764</td>\n",
       "      <td>1.466706</td>\n",
       "      <td>0.469346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_04 Elf 2021</th>\n",
       "      <td>16049</td>\n",
       "      <td>96</td>\n",
       "      <td>3.115459</td>\n",
       "      <td>1.246184</td>\n",
       "      <td>0.398779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_05 Viper 2021</th>\n",
       "      <td>17623</td>\n",
       "      <td>579</td>\n",
       "      <td>2.837201</td>\n",
       "      <td>1.134881</td>\n",
       "      <td>0.363162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_06 Magic 2021</th>\n",
       "      <td>17442</td>\n",
       "      <td>96</td>\n",
       "      <td>2.866644</td>\n",
       "      <td>1.146657</td>\n",
       "      <td>0.366930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_07 Griffin 2021</th>\n",
       "      <td>16772</td>\n",
       "      <td>96</td>\n",
       "      <td>2.981159</td>\n",
       "      <td>1.192464</td>\n",
       "      <td>0.381588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_08 Draconid 2021</th>\n",
       "      <td>17523</td>\n",
       "      <td>385</td>\n",
       "      <td>2.853393</td>\n",
       "      <td>1.141357</td>\n",
       "      <td>0.365234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_09 Dryad 2021</th>\n",
       "      <td>17486</td>\n",
       "      <td>96</td>\n",
       "      <td>2.859430</td>\n",
       "      <td>1.143772</td>\n",
       "      <td>0.366007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_10 Cat 2021</th>\n",
       "      <td>14048</td>\n",
       "      <td>96</td>\n",
       "      <td>3.559226</td>\n",
       "      <td>1.423690</td>\n",
       "      <td>0.455581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_11 Mahakam 2021</th>\n",
       "      <td>19902</td>\n",
       "      <td>96</td>\n",
       "      <td>2.512310</td>\n",
       "      <td>1.004924</td>\n",
       "      <td>0.321576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_12 Wild Hunt 2021</th>\n",
       "      <td>21651</td>\n",
       "      <td>96</td>\n",
       "      <td>2.309362</td>\n",
       "      <td>0.923745</td>\n",
       "      <td>0.295598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M4_01 Wolf 2022</th>\n",
       "      <td>15520</td>\n",
       "      <td>192</td>\n",
       "      <td>3.221649</td>\n",
       "      <td>1.288660</td>\n",
       "      <td>0.412371</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      max_rank  min_mmr  top 500 (%)  top 200 (%)  top 64 (%)\n",
       "season                                                                       \n",
       "M2_01 Wolf 2020           2997       96    16.683350     6.673340    2.135469\n",
       "M2_02 Love 2020           4883       96    10.239607     4.095843    1.310670\n",
       "M2_03 Bear 2020           6632       96     7.539204     3.015682    0.965018\n",
       "M2_04 Elf 2020           10209       96     4.897639     1.959056    0.626898\n",
       "M2_05 Viper 2020         10079       96     4.960810     1.984324    0.634984\n",
       "M2_06 Magic 2020          9919       96     5.040831     2.016332    0.645226\n",
       "M2_07 Griffin 2020       14791       96     3.380434     1.352174    0.432696\n",
       "M2_08 Draconid 2020      13800       96     3.623188     1.449275    0.463768\n",
       "M2_09 Dryad 2020         14554       96     3.435482     1.374193    0.439742\n",
       "M2_10 Cat 2020           16011       96     3.122853     1.249141    0.399725\n",
       "M2_11 Mahakam 2020       16752       96     2.984718     1.193887    0.382044\n",
       "M2_12 Wild Hunt 2020     22464       96     2.225783     0.890313    0.284900\n",
       "M3_01 Wolf 2021          13428       96     3.723563     1.489425    0.476616\n",
       "M3_02 Love 2021          16016       96     3.121878     1.248751    0.399600\n",
       "M3_03 Bear 2021          13636      192     3.666764     1.466706    0.469346\n",
       "M3_04 Elf 2021           16049       96     3.115459     1.246184    0.398779\n",
       "M3_05 Viper 2021         17623      579     2.837201     1.134881    0.363162\n",
       "M3_06 Magic 2021         17442       96     2.866644     1.146657    0.366930\n",
       "M3_07 Griffin 2021       16772       96     2.981159     1.192464    0.381588\n",
       "M3_08 Draconid 2021      17523      385     2.853393     1.141357    0.365234\n",
       "M3_09 Dryad 2021         17486       96     2.859430     1.143772    0.366007\n",
       "M3_10 Cat 2021           14048       96     3.559226     1.423690    0.455581\n",
       "M3_11 Mahakam 2021       19902       96     2.512310     1.004924    0.321576\n",
       "M3_12 Wild Hunt 2021     21651       96     2.309362     0.923745    0.295598\n",
       "M4_01 Wolf 2022          15520      192     3.221649     1.288660    0.412371"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_count = full_df.groupby(['season']).agg(\n",
    "    max_rank = pd.NamedAgg('rank', 'max'),\n",
    "    min_mmr = pd.NamedAgg('mmr', 'min')\n",
    ")\n",
    "player_count['top 500 (%)'] = (500 * 100)/player_count['max_rank']\n",
    "player_count['top 200 (%)'] = (200 * 100)/player_count['max_rank']\n",
    "player_count['top 64 (%)'] = (64 * 100)/player_count['max_rank']\n",
    "player_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_count.to_excel('./output/extra_stats.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rank</th>\n",
       "      <th>name</th>\n",
       "      <th>country</th>\n",
       "      <th>matches</th>\n",
       "      <th>mmr</th>\n",
       "      <th>season</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2084</th>\n",
       "      <td>3259</td>\n",
       "      <td>sepro</td>\n",
       "      <td>BE</td>\n",
       "      <td>142</td>\n",
       "      <td>9617</td>\n",
       "      <td>M2_05 Viper 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2237</th>\n",
       "      <td>12816</td>\n",
       "      <td>sepro</td>\n",
       "      <td>BE</td>\n",
       "      <td>97</td>\n",
       "      <td>3360</td>\n",
       "      <td>M2_09 Dryad 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2515</th>\n",
       "      <td>12856</td>\n",
       "      <td>sepro</td>\n",
       "      <td>BE</td>\n",
       "      <td>80</td>\n",
       "      <td>7407</td>\n",
       "      <td>M2_12 Wild Hunt 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1784</th>\n",
       "      <td>9353</td>\n",
       "      <td>sepro</td>\n",
       "      <td>BE</td>\n",
       "      <td>63</td>\n",
       "      <td>5384</td>\n",
       "      <td>M3_03 Bear 2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2553</th>\n",
       "      <td>14501</td>\n",
       "      <td>sepro</td>\n",
       "      <td>BE</td>\n",
       "      <td>40</td>\n",
       "      <td>2836</td>\n",
       "      <td>M3_04 Elf 2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       rank   name country  matches   mmr                season\n",
       "2084   3259  sepro      BE      142  9617      M2_05 Viper 2020\n",
       "2237  12816  sepro      BE       97  3360      M2_09 Dryad 2020\n",
       "2515  12856  sepro      BE       80  7407  M2_12 Wild Hunt 2020\n",
       "1784   9353  sepro      BE       63  5384       M3_03 Bear 2021\n",
       "2553  14501  sepro      BE       40  2836        M3_04 Elf 2021"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_df[full_df['name'] == 'sepro']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>max_rank_x</th>\n",
       "      <th>max_rank_y</th>\n",
       "      <th>placed_percentage</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>season</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>M2_04 Elf 2020</th>\n",
       "      <td>4562</td>\n",
       "      <td>10209</td>\n",
       "      <td>44.686061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_05 Viper 2020</th>\n",
       "      <td>3679</td>\n",
       "      <td>10079</td>\n",
       "      <td>36.501637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_06 Magic 2020</th>\n",
       "      <td>3361</td>\n",
       "      <td>9919</td>\n",
       "      <td>33.884464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_07 Griffin 2020</th>\n",
       "      <td>5746</td>\n",
       "      <td>14791</td>\n",
       "      <td>38.847948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_08 Draconid 2020</th>\n",
       "      <td>4671</td>\n",
       "      <td>13800</td>\n",
       "      <td>33.847826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_09 Dryad 2020</th>\n",
       "      <td>5063</td>\n",
       "      <td>14554</td>\n",
       "      <td>34.787687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_10 Cat 2020</th>\n",
       "      <td>5866</td>\n",
       "      <td>16011</td>\n",
       "      <td>36.637312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_11 Mahakam 2020</th>\n",
       "      <td>6042</td>\n",
       "      <td>16752</td>\n",
       "      <td>36.067335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M2_12 Wild Hunt 2020</th>\n",
       "      <td>8466</td>\n",
       "      <td>22464</td>\n",
       "      <td>37.686966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_01 Wolf 2021</th>\n",
       "      <td>3682</td>\n",
       "      <td>13428</td>\n",
       "      <td>27.420316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_02 Love 2021</th>\n",
       "      <td>5295</td>\n",
       "      <td>16016</td>\n",
       "      <td>33.060689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_03 Bear 2021</th>\n",
       "      <td>3699</td>\n",
       "      <td>13636</td>\n",
       "      <td>27.126723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_04 Elf 2021</th>\n",
       "      <td>5423</td>\n",
       "      <td>16049</td>\n",
       "      <td>33.790267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_05 Viper 2021</th>\n",
       "      <td>6026</td>\n",
       "      <td>17623</td>\n",
       "      <td>34.193951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_06 Magic 2021</th>\n",
       "      <td>5117</td>\n",
       "      <td>17442</td>\n",
       "      <td>29.337232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_07 Griffin 2021</th>\n",
       "      <td>4763</td>\n",
       "      <td>16772</td>\n",
       "      <td>28.398521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_08 Draconid 2021</th>\n",
       "      <td>5128</td>\n",
       "      <td>17523</td>\n",
       "      <td>29.264395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_09 Dryad 2021</th>\n",
       "      <td>5355</td>\n",
       "      <td>17486</td>\n",
       "      <td>30.624500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_10 Cat 2021</th>\n",
       "      <td>3121</td>\n",
       "      <td>14048</td>\n",
       "      <td>22.216686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_11 Mahakam 2021</th>\n",
       "      <td>7236</td>\n",
       "      <td>19902</td>\n",
       "      <td>36.358155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M3_12 Wild Hunt 2021</th>\n",
       "      <td>7353</td>\n",
       "      <td>21651</td>\n",
       "      <td>33.961480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M4_01 Wolf 2022</th>\n",
       "      <td>4062</td>\n",
       "      <td>15520</td>\n",
       "      <td>26.172680</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      max_rank_x  max_rank_y  placed_percentage\n",
       "season                                                         \n",
       "M2_04 Elf 2020              4562       10209          44.686061\n",
       "M2_05 Viper 2020            3679       10079          36.501637\n",
       "M2_06 Magic 2020            3361        9919          33.884464\n",
       "M2_07 Griffin 2020          5746       14791          38.847948\n",
       "M2_08 Draconid 2020         4671       13800          33.847826\n",
       "M2_09 Dryad 2020            5063       14554          34.787687\n",
       "M2_10 Cat 2020              5866       16011          36.637312\n",
       "M2_11 Mahakam 2020          6042       16752          36.067335\n",
       "M2_12 Wild Hunt 2020        8466       22464          37.686966\n",
       "M3_01 Wolf 2021             3682       13428          27.420316\n",
       "M3_02 Love 2021             5295       16016          33.060689\n",
       "M3_03 Bear 2021             3699       13636          27.126723\n",
       "M3_04 Elf 2021              5423       16049          33.790267\n",
       "M3_05 Viper 2021            6026       17623          34.193951\n",
       "M3_06 Magic 2021            5117       17442          29.337232\n",
       "M3_07 Griffin 2021          4763       16772          28.398521\n",
       "M3_08 Draconid 2021         5128       17523          29.264395\n",
       "M3_09 Dryad 2021            5355       17486          30.624500\n",
       "M3_10 Cat 2021              3121       14048          22.216686\n",
       "M3_11 Mahakam 2021          7236       19902          36.358155\n",
       "M3_12 Wild Hunt 2021        7353       21651          33.961480\n",
       "M4_01 Wolf 2022             4062       15520          26.172680"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "placed_df = full_df[full_df.mmr >= 9600].groupby('season').agg(\n",
    "    max_rank = pd.NamedAgg('rank', 'max')\n",
    ")\n",
    "players_df = full_df.groupby('season').agg(\n",
    "    max_rank = pd.NamedAgg('rank', 'max')\n",
    ")\n",
    "merged_df = pd.merge(placed_df, players_df, left_index=True, right_index=True)\n",
    "merged_df[\"placed_percentage\"] = merged_df[\"max_rank_x\"]*100/merged_df[\"max_rank_y\"]\n",
    "merged_df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}