alltime_quarantaine_table.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import re\n",
    "import urllib.request\n",
    "\n",
    "from bs4 import BeautifulSoup\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "ROCHADE_URL = \"https://rochadeeuropa.com/lichess-turniere-beendet/\"\n",
    "BUNDESLIGA_REGEXP = r\"|\".join([\"(1. DE-Quarantäne Team Battle)\",\n",
    "                               \"([0-9]+\\. ?DE[ -]Quarantäne Teams 1-10)\",\n",
    "                               \"(5. Quarantäne-Liga Teams 1-10)\",\n",
    "                               \"([0-9]+\\. ?Quarantäne-Bundesliga)\",\n",
    "                               \"([0-9]+\\. ?Quarantäne-Welt-Bundesliga ?$)\",\n",
    "                               ])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_bundesliga_tournaments():\n",
    "    \"\"\"\n",
    "    scrape rochadeeuropa.com in order to find lichess quarantaine bundesliga matches.\n",
    "    Rochade URL and regex to determine which tournament was actually a bundesliga tournaments are \n",
    "    taken from configurable global variables\n",
    "    \"\"\"\n",
    "    # scrape rochade finished lichess tournaments\n",
    "    response = urllib.request.urlopen(ROCHADE_URL)\n",
    "    html = response.read()\n",
    "    soup = BeautifulSoup(html, 'html.parser')\n",
    "    \n",
    "    # parse table\n",
    "    data = []\n",
    "    table = soup.find('table', attrs={'class':'tablepress tablepress-id-3'})\n",
    "    table_body = table.find('tbody')\n",
    "    \n",
    "    rows = table_body.find_all('tr')\n",
    "    for row in rows:\n",
    "        cols = row.find_all('td')\n",
    "        data.append([ele.text.strip() for ele in cols])\n",
    "    # filter for bundesliga\n",
    "    buli_tournaments = [el for el in data if re.match(BUNDESLIGA_REGEXP, el[1])]\n",
    "    return buli_tournaments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_individual_results(tournament_id):\n",
    "    \"\"\"\n",
    "    For individual results for a given tournament we use lichess API\n",
    "    \"\"\"\n",
    "    api_url = f\"https://lichess.org/api/tournament/{tournament_id}/results\"\n",
    "    api_response = urllib.request.urlopen(api_url)\n",
    "    player_results = api_response.read()\n",
    "    return pd.DataFrame([json.loads(pl) for pl in player_results.splitlines()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_team_results(tournament_url):\n",
    "    \"\"\"\n",
    "    Unfortunately the API does not provide team results, hence we need to go the pedestrian way and \n",
    "    scrape it by hand\n",
    "    \"\"\"\n",
    "    tournament_response = urllib.request.urlopen(tournament_url)\n",
    "    tournament_html = tournament_response.read()\n",
    "    tournament_soup = BeautifulSoup(tournament_html, 'html.parser')\n",
    "    # there is a string before the json that needs to be deleted.\n",
    "    # This seems a little clumsy and error prone but there is no other way when parsing manually\n",
    "    str_to_delete = \"lichess.tournament=\"\n",
    "    tournament_strings = [script.text for script in tournament_soup.find_all(\"script\") \n",
    "                          if str_to_delete in script.text]\n",
    "    tournament_strings.sort(key=lambda s: len(s))\n",
    "    tournament_string = tournament_strings[-1]\n",
    "    tournament_json = json.loads(tournament_string[tournament_string.find(str_to_delete)+len(str_to_delete):])\n",
    "    team_name_dict = tournament_json[\"data\"][\"teamBattle\"][\"teams\"]\n",
    "    relevant_keys = [\"rank\", \"id\", \"score\"]\n",
    "    team_results = [{k: v for k, v in team_dict.items() if k in relevant_keys} for team_dict in \n",
    "                    tournament_json[\"data\"][\"teamStanding\"]]\n",
    "    teams_df = pd.DataFrame(team_results)\n",
    "    teams_df[\"Team\"] = teams_df[\"id\"].map(team_name_dict)\n",
    "    return teams_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Downloading data of tournament 20. Quarantäne-Welt-Bundesliga on 23.04.\n",
      "Downloading data of tournament 19. Quarantäne-Welt-Bundesliga on 19.04.\n",
      "Downloading data of tournament 18. Quarantäne-Welt-Bundesliga on 16.04.\n",
      "Downloading data of tournament 17. Quarantäne-Bundesliga on 12.04.\n",
      "Downloading data of tournament 16. Quarantäne-Bundesliga on 09.04.\n",
      "Downloading data of tournament 15. Quarantäne-Bundesliga on 05.04.\n",
      "Downloading data of tournament 14. Quarantäne-Bundesliga on 02.04.\n",
      "Downloading data of tournament 13. Quarantäne-Bundesliga on 31.03.\n",
      "Downloading data of tournament 12. Quarantäne-Bundesliga on 29.03.\n",
      "Downloading data of tournament 11. Quarantäne-Bundesliga on 26.03.\n",
      "Downloading data of tournament 10. Quarantäne-Bundesliga on 25.03.\n",
      "Downloading data of tournament 9. Quarantäne-Bundesliga on 24.03.\n",
      "Downloading data of tournament 8. Quarantäne-Bundesliga on 23.03.\n",
      "Downloading data of tournament 7. Quarantäne-Bundesliga on 22.03.\n",
      "Downloading data of tournament 6. Quarantäne-Bundesliga on 21.03.\n",
      "Downloading data of tournament 5. Quarantäne-Liga Teams 1-10 on 19.03.\n",
      "Downloading data of tournament 4. DE Quarantäne Teams 1-10 on 18.03.\n",
      "Downloading data of tournament 3. DE-Quarantäne Teams 1-10 on 17.03.\n",
      "Downloading data of tournament 2. DE-Quarantäne Teams 1-10 on 16.03.\n",
      "Downloading data of tournament 1. DE-Quarantäne Team Battle on 15.03.\n"
     ]
    }
   ],
   "source": [
    "team_df = pd.DataFrame()\n",
    "individual_df = pd.DataFrame()\n",
    "\n",
    "buli_tournaments = get_bundesliga_tournaments()\n",
    "for tournament in buli_tournaments:\n",
    "    print(f\"Downloading data of tournament {tournament[1]} on {tournament[0]}\")\n",
    "    team_df = team_df.append(get_team_results(tournament[4]))\n",
    "    individual_df = individual_df.append(get_individual_results(tournament[4].split(\"/\")[-1]))\n",
    "    \n",
    "team_df[\"count\"] = 1\n",
    "team_df[\"champion\"] = (team_df[\"rank\"] == 1).astype(int)\n",
    "individual_df[\"count\"] = 1\n",
    "individual_df[\"champion\"] = (individual_df[\"rank\"] == 1).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_time_teams = team_df.groupby(\"Team\", as_index=False).agg(\n",
    "    {\"score\": \"sum\", \"count\": \"count\", \"champion\": \"sum\", \"rank\": \"mean\"}).sort_values(\"score\", ascending=False)\n",
    "all_time_teams.columns = [\"Team\", \"Gesamtpunkte\", \"Teilnahmen\", \"Meisterschaften\", \"Durchschnittsplatzierung\"]\n",
    "all_time_teams.index = range(1, len(all_time_teams) + 1)\n",
    "all_time_indiv = individual_df.groupby(\"username\", as_index=False).agg(\n",
    "    {\"score\": \"sum\", \"count\": \"count\", \"champion\": \"sum\", \"performance\": \"mean\", \"rank\": \"mean\", }).sort_values(\"score\", ascending=False)\n",
    "all_time_indiv.columns = [\"Name\", \"Gesamtpunkte\", \"Teilnahmen\", \"Turniersiege\", \"Durchschnittsperformance\", \"Durchschnittsplatzierung\"]\n",
    "all_time_indiv.index = range(1, len(all_time_indiv) + 1)\n",
    "all_time_indiv[\"Durchschnittsscore\"] = all_time_indiv[\"Gesamtpunkte\"] / all_time_indiv[\"Teilnahmen\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Rang</th>\n",
       "      <th>Team</th>\n",
       "      <th>Gesamtpunkte</th>\n",
       "      <th>Teilnahmen</th>\n",
       "      <th>Meisterschaften</th>\n",
       "      <th>Durchschnittsplatzierung</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bierstube MUC</td>\n",
       "      <td>5691</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>3.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>HSK Lister Turm</td>\n",
       "      <td>4767</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>4.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>KSK Dr. Lasker 1861 e.V.</td>\n",
       "      <td>3840</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>5.153846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Oxfordgang</td>\n",
       "      <td>3044</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Delfintherapie</td>\n",
       "      <td>2528</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>3.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Sfr. Heidesheim</td>\n",
       "      <td>2290</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>6.142857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>SK Landau 1908 e.V.</td>\n",
       "      <td>2254</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>5.875000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>TSV Schöniach</td>\n",
       "      <td>2226</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>5.222222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Die Nordlichter</td>\n",
       "      <td>1859</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>5.111111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Wiener Kandidaten und Fake Meister</td>\n",
       "      <td>1859</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>6.285714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>Kingdom</td>\n",
       "      <td>1639</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>4.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>Hessische Schachjugend</td>\n",
       "      <td>1616</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>6.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>Hamburger SK</td>\n",
       "      <td>1411</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>6.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>The House Discord Server</td>\n",
       "      <td>1289</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>SV Werder Bremen</td>\n",
       "      <td>1193</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>6.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>Die oagen</td>\n",
       "      <td>1174</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>SK Münster 1932</td>\n",
       "      <td>1141</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>6.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>Bochumer Schachverein 02</td>\n",
       "      <td>1099</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>Växjö SK</td>\n",
       "      <td>1047</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>OSC Rheinhausen Schach</td>\n",
       "      <td>1037</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>SF Brackel 30</td>\n",
       "      <td>1031</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>Ergoldinger Jugend</td>\n",
       "      <td>915</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>6.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>NDK Chess</td>\n",
       "      <td>821</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>6.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>Die Hirschnackenbrecher</td>\n",
       "      <td>750</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>5.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>pupilochess</td>\n",
       "      <td>730</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>1. FC Simon</td>\n",
       "      <td>656</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>7.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>Grunbach 1</td>\n",
       "      <td>624</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>28</td>\n",
       "      <td>Lörrach</td>\n",
       "      <td>596</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29</td>\n",
       "      <td>Deutschsprachige</td>\n",
       "      <td>412</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>7.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>30</td>\n",
       "      <td>The Nigerian Princes</td>\n",
       "      <td>390</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>31</td>\n",
       "      <td>SC Kastellaun &amp; friends</td>\n",
       "      <td>338</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>6.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>32</td>\n",
       "      <td>DSSP - Die Schulschachprofis</td>\n",
       "      <td>280</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>33</td>\n",
       "      <td>SC Garching 1980 e.V.</td>\n",
       "      <td>273</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>34</td>\n",
       "      <td>SC Vaterstetten-Grasbrunn</td>\n",
       "      <td>252</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>6.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>35</td>\n",
       "      <td>Schachklub Kelheim</td>\n",
       "      <td>250</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>36</td>\n",
       "      <td>Rochade Europa - Schachzeitung</td>\n",
       "      <td>229</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>37</td>\n",
       "      <td>Amateur Chess Organization</td>\n",
       "      <td>224</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>6.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>38</td>\n",
       "      <td>Tajamar-Ajedrez</td>\n",
       "      <td>167</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>39</td>\n",
       "      <td>SB Borken</td>\n",
       "      <td>147</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>40</td>\n",
       "      <td>SF Neuberg</td>\n",
       "      <td>137</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>41</td>\n",
       "      <td>German Chess Team</td>\n",
       "      <td>118</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>42</td>\n",
       "      <td>Tiroler Schachkader</td>\n",
       "      <td>116</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>9.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>43</td>\n",
       "      <td>SV Erkenschwick</td>\n",
       "      <td>103</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>44</td>\n",
       "      <td>Austrian Lichess Players</td>\n",
       "      <td>47</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Rang                                Team  Gesamtpunkte  Teilnahmen  \\\n",
       "0      1                       Bierstube MUC          5691          20   \n",
       "1      2                     HSK Lister Turm          4767          16   \n",
       "2      3            KSK Dr. Lasker 1861 e.V.          3840          13   \n",
       "3      4                          Oxfordgang          3044          13   \n",
       "4      5                      Delfintherapie          2528           7   \n",
       "5      6                     Sfr. Heidesheim          2290           7   \n",
       "6      7                 SK Landau 1908 e.V.          2254           8   \n",
       "7      8                       TSV Schöniach          2226           9   \n",
       "8      9                     Die Nordlichter          1859           9   \n",
       "9     10  Wiener Kandidaten und Fake Meister          1859           7   \n",
       "10    11                             Kingdom          1639           5   \n",
       "11    12              Hessische Schachjugend          1616           6   \n",
       "12    13                        Hamburger SK          1411           6   \n",
       "13    14            The House Discord Server          1289           3   \n",
       "14    15                    SV Werder Bremen          1193           6   \n",
       "15    16                           Die oagen          1174           4   \n",
       "16    17                     SK Münster 1932          1141           6   \n",
       "17    18            Bochumer Schachverein 02          1099           3   \n",
       "18    19                            Växjö SK          1047           3   \n",
       "19    20              OSC Rheinhausen Schach          1037           3   \n",
       "20    21                       SF Brackel 30          1031           4   \n",
       "21    22                  Ergoldinger Jugend           915           3   \n",
       "22    23                           NDK Chess           821           3   \n",
       "23    24             Die Hirschnackenbrecher           750           4   \n",
       "24    25                         pupilochess           730           2   \n",
       "25    26                         1. FC Simon           656           2   \n",
       "26    27                          Grunbach 1           624           2   \n",
       "27    28                             Lörrach           596           3   \n",
       "28    29                    Deutschsprachige           412           3   \n",
       "29    30                The Nigerian Princes           390           1   \n",
       "30    31             SC Kastellaun & friends           338           2   \n",
       "31    32        DSSP - Die Schulschachprofis           280           1   \n",
       "32    33               SC Garching 1980 e.V.           273           1   \n",
       "33    34           SC Vaterstetten-Grasbrunn           252           2   \n",
       "34    35                  Schachklub Kelheim           250           2   \n",
       "35    36      Rochade Europa - Schachzeitung           229           2   \n",
       "36    37          Amateur Chess Organization           224           2   \n",
       "37    38                     Tajamar-Ajedrez           167           1   \n",
       "38    39                           SB Borken           147           1   \n",
       "39    40                          SF Neuberg           137           1   \n",
       "40    41                   German Chess Team           118           1   \n",
       "41    42                 Tiroler Schachkader           116           1   \n",
       "42    43                     SV Erkenschwick           103           1   \n",
       "43    44            Austrian Lichess Players            47           1   \n",
       "\n",
       "    Meisterschaften  Durchschnittsplatzierung  \n",
       "0                 3                  3.300000  \n",
       "1                 4                  4.125000  \n",
       "2                 1                  5.153846  \n",
       "3                 0                  6.000000  \n",
       "4                 3                  3.857143  \n",
       "5                 1                  6.142857  \n",
       "6                 0                  5.875000  \n",
       "7                 0                  5.222222  \n",
       "8                 3                  5.111111  \n",
       "9                 0                  6.285714  \n",
       "10                0                  4.600000  \n",
       "11                1                  6.333333  \n",
       "12                0                  6.500000  \n",
       "13                2                  1.333333  \n",
       "14                0                  6.500000  \n",
       "15                0                  7.000000  \n",
       "16                0                  6.166667  \n",
       "17                0                  6.000000  \n",
       "18                0                  5.000000  \n",
       "19                0                  4.000000  \n",
       "20                1                  6.000000  \n",
       "21                0                  6.666667  \n",
       "22                0                  6.333333  \n",
       "23                0                  5.500000  \n",
       "24                1                  5.000000  \n",
       "25                0                  7.500000  \n",
       "26                0                  4.500000  \n",
       "27                0                  7.000000  \n",
       "28                0                  7.333333  \n",
       "29                0                  3.000000  \n",
       "30                0                  6.500000  \n",
       "31                0                 10.000000  \n",
       "32                0                  8.000000  \n",
       "33                0                  6.500000  \n",
       "34                0                  8.000000  \n",
       "35                0                  7.000000  \n",
       "36                0                  6.500000  \n",
       "37                0                 10.000000  \n",
       "38                0                 10.000000  \n",
       "39                0                 10.000000  \n",
       "40                0                  7.000000  \n",
       "41                0                  9.000000  \n",
       "42                0                  8.000000  \n",
       "43                0                 10.000000  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_time_teams.reset_index().rename(columns={\"index\": \"Rang\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": false
   },
   "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>Name</th>\n",
       "      <th>Gesamtpunkte</th>\n",
       "      <th>Teilnahmen</th>\n",
       "      <th>Turniersiege</th>\n",
       "      <th>Durchschnittsperformance</th>\n",
       "      <th>Durchschnittsplatzierung</th>\n",
       "      <th>Durchschnittsscore</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SomeMightSay</td>\n",
       "      <td>1132</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>2596.1</td>\n",
       "      <td>4.6</td>\n",
       "      <td>56.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>psammenitos</td>\n",
       "      <td>714</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>2586.8</td>\n",
       "      <td>15.1</td>\n",
       "      <td>51.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kabeljaukrieger</td>\n",
       "      <td>668</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>2394.9</td>\n",
       "      <td>33.3</td>\n",
       "      <td>35.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Satscho</td>\n",
       "      <td>648</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>2348.8</td>\n",
       "      <td>42.3</td>\n",
       "      <td>32.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Eldrail</td>\n",
       "      <td>610</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>2478.8</td>\n",
       "      <td>29.9</td>\n",
       "      <td>38.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>P1W4</td>\n",
       "      <td>568</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>2170.9</td>\n",
       "      <td>62.0</td>\n",
       "      <td>28.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>fluffy_cactus</td>\n",
       "      <td>536</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>2222.8</td>\n",
       "      <td>67.8</td>\n",
       "      <td>26.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Donnerhall</td>\n",
       "      <td>524</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>2521.4</td>\n",
       "      <td>27.7</td>\n",
       "      <td>40.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Chillkroete77</td>\n",
       "      <td>519</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>2370.1</td>\n",
       "      <td>16.1</td>\n",
       "      <td>39.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Unkreativ3</td>\n",
       "      <td>511</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>2452.6</td>\n",
       "      <td>40.5</td>\n",
       "      <td>34.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ChevCheIios</td>\n",
       "      <td>458</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>2379.3</td>\n",
       "      <td>49.6</td>\n",
       "      <td>30.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Potzbl1tz</td>\n",
       "      <td>457</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>2223.4</td>\n",
       "      <td>79.6</td>\n",
       "      <td>22.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>PeterLaFleur</td>\n",
       "      <td>453</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>2508.3</td>\n",
       "      <td>18.5</td>\n",
       "      <td>41.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>karasso</td>\n",
       "      <td>445</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>2241.3</td>\n",
       "      <td>58.6</td>\n",
       "      <td>29.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Eloist</td>\n",
       "      <td>430</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>2330.8</td>\n",
       "      <td>45.5</td>\n",
       "      <td>33.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Veez</td>\n",
       "      <td>430</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>2341.1</td>\n",
       "      <td>46.5</td>\n",
       "      <td>30.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>bachess76</td>\n",
       "      <td>406</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>2407.1</td>\n",
       "      <td>44.7</td>\n",
       "      <td>33.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>wisibada</td>\n",
       "      <td>395</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>2315.7</td>\n",
       "      <td>54.7</td>\n",
       "      <td>30.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Blend99</td>\n",
       "      <td>388</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>2506.6</td>\n",
       "      <td>8.0</td>\n",
       "      <td>48.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Chessbard1972</td>\n",
       "      <td>372</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>2296.4</td>\n",
       "      <td>60.9</td>\n",
       "      <td>28.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>TobiasTSV</td>\n",
       "      <td>365</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>2113.8</td>\n",
       "      <td>79.5</td>\n",
       "      <td>22.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>habasila</td>\n",
       "      <td>361</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>2623.1</td>\n",
       "      <td>8.7</td>\n",
       "      <td>51.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>SirFaSunnyBoy</td>\n",
       "      <td>356</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>2056.4</td>\n",
       "      <td>89.3</td>\n",
       "      <td>23.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>tobi0709</td>\n",
       "      <td>319</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2488.3</td>\n",
       "      <td>13.7</td>\n",
       "      <td>45.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>bayrischerFuchs</td>\n",
       "      <td>318</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>2141.5</td>\n",
       "      <td>93.6</td>\n",
       "      <td>19.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Landau96</td>\n",
       "      <td>317</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>2454.6</td>\n",
       "      <td>21.1</td>\n",
       "      <td>39.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>vandyne</td>\n",
       "      <td>314</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>2108.2</td>\n",
       "      <td>86.4</td>\n",
       "      <td>24.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>ChessJake</td>\n",
       "      <td>305</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2411.6</td>\n",
       "      <td>21.7</td>\n",
       "      <td>43.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>LordFinzenz</td>\n",
       "      <td>304</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>2179.1</td>\n",
       "      <td>82.2</td>\n",
       "      <td>21.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Ketchup</td>\n",
       "      <td>296</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>2421.3</td>\n",
       "      <td>40.8</td>\n",
       "      <td>29.6</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1299</th>\n",
       "      <td>MortezaH13</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>157.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1300</th>\n",
       "      <td>Bill_Chiper_a4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1631.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1301</th>\n",
       "      <td>avi1599</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>242.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1302</th>\n",
       "      <td>easychess18</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>158.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1303</th>\n",
       "      <td>Bog_8</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>234.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1304</th>\n",
       "      <td>Bibernator</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>226.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1305</th>\n",
       "      <td>BekzodKhusanov1998</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>133.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1306</th>\n",
       "      <td>sk321</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>128.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1307</th>\n",
       "      <td>Muravei-gosha228</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1308</th>\n",
       "      <td>Bauel</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>235.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1309</th>\n",
       "      <td>NAR14</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1724.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1310</th>\n",
       "      <td>Smodoo</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1645.0</td>\n",
       "      <td>103.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1311</th>\n",
       "      <td>Boming</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>248.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1312</th>\n",
       "      <td>Sweet92</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1313</th>\n",
       "      <td>Mete91</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1443.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1314</th>\n",
       "      <td>alejo-dye</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>219.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1315</th>\n",
       "      <td>Drpiratestein</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>231.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1316</th>\n",
       "      <td>Meshkov_Maksim_2011</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1248.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1317</th>\n",
       "      <td>CHAMERKIT</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1829.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1318</th>\n",
       "      <td>anacak35</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>151.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1319</th>\n",
       "      <td>anarammar</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1056.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1320</th>\n",
       "      <td>Jerozapata</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1803.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1321</th>\n",
       "      <td>aramggedon2093</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2539.0</td>\n",
       "      <td>191.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1322</th>\n",
       "      <td>EinfachSim0n</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1574.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1323</th>\n",
       "      <td>rickyrich</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>231.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1324</th>\n",
       "      <td>ElMosho7u7</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1325</th>\n",
       "      <td>StalematingAttack</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>232.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1326</th>\n",
       "      <td>kayrac</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>119.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1327</th>\n",
       "      <td>Minkaski</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>129.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1328</th>\n",
       "      <td>Gmajor</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1330.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1328 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Name  Gesamtpunkte  Teilnahmen  Turniersiege  \\\n",
       "1            SomeMightSay          1132          20             5   \n",
       "2             psammenitos           714          14             4   \n",
       "3         Kabeljaukrieger           668          19             0   \n",
       "4                 Satscho           648          20             0   \n",
       "5                 Eldrail           610          16             0   \n",
       "6                    P1W4           568          20             0   \n",
       "7           fluffy_cactus           536          20             0   \n",
       "8              Donnerhall           524          13             0   \n",
       "9           Chillkroete77           519          13             1   \n",
       "10             Unkreativ3           511          15             0   \n",
       "11            ChevCheIios           458          15             0   \n",
       "12              Potzbl1tz           457          20             0   \n",
       "13           PeterLaFleur           453          11             0   \n",
       "14                karasso           445          15             0   \n",
       "15                 Eloist           430          13             0   \n",
       "16                   Veez           430          14             0   \n",
       "17              bachess76           406          12             0   \n",
       "18               wisibada           395          13             0   \n",
       "19                Blend99           388           8             0   \n",
       "20          Chessbard1972           372          13             0   \n",
       "21              TobiasTSV           365          16             0   \n",
       "22               habasila           361           7             2   \n",
       "23          SirFaSunnyBoy           356          15             0   \n",
       "24               tobi0709           319           7             0   \n",
       "25        bayrischerFuchs           318          16             0   \n",
       "26               Landau96           317           8             0   \n",
       "27                vandyne           314          13             0   \n",
       "28              ChessJake           305           7             0   \n",
       "29            LordFinzenz           304          14             0   \n",
       "30                Ketchup           296          10             0   \n",
       "...                   ...           ...         ...           ...   \n",
       "1299           MortezaH13             0           1             0   \n",
       "1300       Bill_Chiper_a4             0           1             0   \n",
       "1301              avi1599             0           1             0   \n",
       "1302          easychess18             0           1             0   \n",
       "1303                Bog_8             0           1             0   \n",
       "1304           Bibernator             0           1             0   \n",
       "1305   BekzodKhusanov1998             0           1             0   \n",
       "1306                sk321             0           1             0   \n",
       "1307     Muravei-gosha228             0           1             0   \n",
       "1308                Bauel             0           1             0   \n",
       "1309                NAR14             0           1             0   \n",
       "1310               Smodoo             0           1             0   \n",
       "1311               Boming             0           1             0   \n",
       "1312              Sweet92             0           1             0   \n",
       "1313               Mete91             0           1             0   \n",
       "1314            alejo-dye             0           1             0   \n",
       "1315        Drpiratestein             0           1             0   \n",
       "1316  Meshkov_Maksim_2011             0           1             0   \n",
       "1317            CHAMERKIT             0           1             0   \n",
       "1318             anacak35             0           1             0   \n",
       "1319            anarammar             0           1             0   \n",
       "1320           Jerozapata             0           1             0   \n",
       "1321       aramggedon2093             0           1             0   \n",
       "1322         EinfachSim0n             0           1             0   \n",
       "1323            rickyrich             0           1             0   \n",
       "1324           ElMosho7u7             0           1             0   \n",
       "1325    StalematingAttack             0           1             0   \n",
       "1326               kayrac             0           1             0   \n",
       "1327             Minkaski             0           1             0   \n",
       "1328               Gmajor             0           1             0   \n",
       "\n",
       "      Durchschnittsperformance  Durchschnittsplatzierung  Durchschnittsscore  \n",
       "1                       2596.1                       4.6                56.6  \n",
       "2                       2586.8                      15.1                51.0  \n",
       "3                       2394.9                      33.3                35.2  \n",
       "4                       2348.8                      42.3                32.4  \n",
       "5                       2478.8                      29.9                38.1  \n",
       "6                       2170.9                      62.0                28.4  \n",
       "7                       2222.8                      67.8                26.8  \n",
       "8                       2521.4                      27.7                40.3  \n",
       "9                       2370.1                      16.1                39.9  \n",
       "10                      2452.6                      40.5                34.1  \n",
       "11                      2379.3                      49.6                30.5  \n",
       "12                      2223.4                      79.6                22.8  \n",
       "13                      2508.3                      18.5                41.2  \n",
       "14                      2241.3                      58.6                29.7  \n",
       "15                      2330.8                      45.5                33.1  \n",
       "16                      2341.1                      46.5                30.7  \n",
       "17                      2407.1                      44.7                33.8  \n",
       "18                      2315.7                      54.7                30.4  \n",
       "19                      2506.6                       8.0                48.5  \n",
       "20                      2296.4                      60.9                28.6  \n",
       "21                      2113.8                      79.5                22.8  \n",
       "22                      2623.1                       8.7                51.6  \n",
       "23                      2056.4                      89.3                23.7  \n",
       "24                      2488.3                      13.7                45.6  \n",
       "25                      2141.5                      93.6                19.9  \n",
       "26                      2454.6                      21.1                39.6  \n",
       "27                      2108.2                      86.4                24.2  \n",
       "28                      2411.6                      21.7                43.6  \n",
       "29                      2179.1                      82.2                21.7  \n",
       "30                      2421.3                      40.8                29.6  \n",
       "...                        ...                       ...                 ...  \n",
       "1299                       NaN                     157.0                 0.0  \n",
       "1300                    1631.0                     152.0                 0.0  \n",
       "1301                       NaN                     242.0                 0.0  \n",
       "1302                       NaN                     158.0                 0.0  \n",
       "1303                       NaN                     234.0                 0.0  \n",
       "1304                       NaN                     226.0                 0.0  \n",
       "1305                       NaN                     133.0                 0.0  \n",
       "1306                    2000.0                     128.0                 0.0  \n",
       "1307                       NaN                     201.0                 0.0  \n",
       "1308                       NaN                     235.0                 0.0  \n",
       "1309                    1724.0                     130.0                 0.0  \n",
       "1310                    1645.0                     103.0                 0.0  \n",
       "1311                       NaN                     248.0                 0.0  \n",
       "1312                       NaN                     105.0                 0.0  \n",
       "1313                    1443.0                     240.0                 0.0  \n",
       "1314                       NaN                     219.0                 0.0  \n",
       "1315                       NaN                     231.0                 0.0  \n",
       "1316                    1248.0                     159.0                 0.0  \n",
       "1317                    1829.0                     156.0                 0.0  \n",
       "1318                       NaN                     151.0                 0.0  \n",
       "1319                    1056.0                     155.0                 0.0  \n",
       "1320                    1803.0                     143.0                 0.0  \n",
       "1321                    2539.0                     191.0                 0.0  \n",
       "1322                    1574.0                     158.0                 0.0  \n",
       "1323                       NaN                     231.0                 0.0  \n",
       "1324                       NaN                     229.0                 0.0  \n",
       "1325                       NaN                     232.0                 0.0  \n",
       "1326                       NaN                     119.0                 0.0  \n",
       "1327                       NaN                     129.0                 0.0  \n",
       "1328                    1330.0                     210.0                 0.0  \n",
       "\n",
       "[1328 rows x 7 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_time_indiv.round({\"Durchschnittsplatzierung\": 1, \"Durchschnittsperformance\": 1, \"Durchschnittsscore\": 1})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gspread\n",
    "from oauth2client.service_account import ServiceAccountCredentials\n",
    "\n",
    "def connect_to_spreadsheet():\n",
    "    scope = ['https://spreadsheets.google.com/feeds',\n",
    "             'https://www.googleapis.com/auth/drive']\n",
    "\n",
    "    credentials = ServiceAccountCredentials.from_json_keyfile_name('/home/paul/Downloads/dummyproject-273213-6c2b9c180973.json', scope)\n",
    "\n",
    "    gc = gspread.authorize(credentials)\n",
    "    return gc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gspread_dataframe as gd\n",
    "\n",
    "def write_to_spreadsheet(df, sheet_connection):\n",
    "    ws = sheet_connection.open(\"Ewige Quarantäne-Bundesligatabelle\").worksheet(\"Sheet1\")\n",
    "    gd.set_with_dataframe(ws, df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_to_write = all_time_teams.reset_index().rename(columns={\"index\": \"Rang\"}).round({\"Durchschnittsplatzierung\": 1})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = connect_to_spreadsheet()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "write_to_spreadsheet(df_to_write, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}