queries_notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AOE2\n",
    "The following kernel process a subset of Age of Empires DE games"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5",
    "execution": {
     "iopub.execute_input": "2021-05-20T21:37:10.700706Z",
     "iopub.status.busy": "2021-05-20T21:37:10.700023Z",
     "iopub.status.idle": "2021-05-20T21:37:10.720182Z",
     "shell.execute_reply": "2021-05-20T21:37:10.719511Z",
     "shell.execute_reply.started": "2021-05-20T21:37:10.700569Z"
    }
   },
   "outputs": [],
   "source": [
    "# Basic setup stuff\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import os\n",
    "for dirname, _, filenames in os.walk('/kaggle/input'):\n",
    "    for filename in filenames:\n",
    "        display(os.path.join(dirname, filename))\n",
    "\n",
    "plt.rcParams['figure.figsize'] = (12.0, 8.0)  # default figure size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read both the matches and match_players data to show the type of data that the files store.\n",
    "* **matches.csv**: It contains information about the matches play, one by row. The match is identified by the _token_ field\n",
    "* **match_players.csv**: It contains information about the players that where involved in every match. The _token_ identifies the player and the _match_ field is the foreign key of _matches.csv_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-20T21:37:18.582994Z",
     "iopub.status.busy": "2021-05-20T21:37:18.582434Z",
     "iopub.status.idle": "2021-05-20T21:37:26.482365Z",
     "shell.execute_reply": "2021-05-20T21:37:26.481458Z",
     "shell.execute_reply.started": "2021-05-20T21:37:18.582947Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3153767 entries, 0 to 3153766\n",
      "Data columns (total 11 columns):\n",
      " #   Column          Dtype  \n",
      "---  ------          -----  \n",
      " 0   token           object \n",
      " 1   winning_team    int64  \n",
      " 2   mirror          bool   \n",
      " 3   ladder          object \n",
      " 4   patch           int64  \n",
      " 5   average_rating  float64\n",
      " 6   map             object \n",
      " 7   map_size        object \n",
      " 8   num_players     int64  \n",
      " 9   server          object \n",
      " 10  duration        object \n",
      "dtypes: bool(1), float64(1), int64(3), object(6)\n",
      "memory usage: 243.6+ MB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "None"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>token</th>\n",
       "      <th>winning_team</th>\n",
       "      <th>mirror</th>\n",
       "      <th>ladder</th>\n",
       "      <th>patch</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>map</th>\n",
       "      <th>map_size</th>\n",
       "      <th>num_players</th>\n",
       "      <th>server</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>OFb9Dkk3adCN5GLV</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>RM_1v1</td>\n",
       "      <td>40874</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>arabia</td>\n",
       "      <td>tiny</td>\n",
       "      <td>2</td>\n",
       "      <td>ukwest</td>\n",
       "      <td>0:32:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hKNeRYrz0ecdBMFO</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>RM_1v1</td>\n",
       "      <td>37906</td>\n",
       "      <td>NaN</td>\n",
       "      <td>arabia</td>\n",
       "      <td>tiny</td>\n",
       "      <td>2</td>\n",
       "      <td>ukwest</td>\n",
       "      <td>0:41:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>90ZCh6wfUVc3ujLo</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>RM_1v1</td>\n",
       "      <td>41855</td>\n",
       "      <td>1127.0</td>\n",
       "      <td>arabia</td>\n",
       "      <td>tiny</td>\n",
       "      <td>2</td>\n",
       "      <td>ukwest</td>\n",
       "      <td>0:16:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>47e37f92a0551b4d</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>RM_1v1</td>\n",
       "      <td>36906</td>\n",
       "      <td>1203.0</td>\n",
       "      <td>arabia</td>\n",
       "      <td>tiny</td>\n",
       "      <td>2</td>\n",
       "      <td>ukwest</td>\n",
       "      <td>0:30:17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>zq3ILuaPrhdhgGUM</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>RM_1v1</td>\n",
       "      <td>40220</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>valley</td>\n",
       "      <td>tiny</td>\n",
       "      <td>2</td>\n",
       "      <td>westus2</td>\n",
       "      <td>0:14:44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              token  winning_team  mirror  ladder  patch  average_rating  \\\n",
       "0  OFb9Dkk3adCN5GLV             2   False  RM_1v1  40874          1100.0   \n",
       "1  hKNeRYrz0ecdBMFO             1   False  RM_1v1  37906             NaN   \n",
       "2  90ZCh6wfUVc3ujLo             2   False  RM_1v1  41855          1127.0   \n",
       "3  47e37f92a0551b4d             1   False  RM_1v1  36906          1203.0   \n",
       "4  zq3ILuaPrhdhgGUM             1   False  RM_1v1  40220          1059.0   \n",
       "\n",
       "      map map_size  num_players   server duration  \n",
       "0  arabia     tiny            2   ukwest  0:32:00  \n",
       "1  arabia     tiny            2   ukwest  0:41:45  \n",
       "2  arabia     tiny            2   ukwest  0:16:59  \n",
       "3  arabia     tiny            2   ukwest  0:30:17  \n",
       "4  valley     tiny            2  westus2  0:14:44  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matches = pd.read_csv(\"matches.csv\")\n",
    "display(matches.info())\n",
    "matches.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-20T21:37:31.778499Z",
     "iopub.status.busy": "2021-05-20T21:37:31.777912Z",
     "iopub.status.idle": "2021-05-20T21:37:59.304864Z",
     "shell.execute_reply": "2021-05-20T21:37:59.304131Z",
     "shell.execute_reply.started": "2021-05-20T21:37:31.778454Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 9732500 entries, 0 to 9732499\n",
      "Data columns (total 7 columns):\n",
      " #   Column  Dtype  \n",
      "---  ------  -----  \n",
      " 0   token   object \n",
      " 1   match   object \n",
      " 2   rating  float64\n",
      " 3   color   object \n",
      " 4   civ     object \n",
      " 5   team    int64  \n",
      " 6   winner  bool   \n",
      "dtypes: bool(1), float64(1), int64(1), object(4)\n",
      "memory usage: 454.8+ MB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "None"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>token</th>\n",
       "      <th>match</th>\n",
       "      <th>rating</th>\n",
       "      <th>color</th>\n",
       "      <th>civ</th>\n",
       "      <th>team</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BsnDkR5pb9DVCbIt</td>\n",
       "      <td>9YDIBsStGjrIuGbW</td>\n",
       "      <td>952.0</td>\n",
       "      <td>Blue</td>\n",
       "      <td>Tatars</td>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>88224b59212f259b</td>\n",
       "      <td>3930e585b343d9b2</td>\n",
       "      <td>1395.0</td>\n",
       "      <td>Green</td>\n",
       "      <td>Aztecs</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2RvyZPW1f9y4jP7M</td>\n",
       "      <td>6zAtwTFqh8OIJJuX</td>\n",
       "      <td>1330.0</td>\n",
       "      <td>Blue</td>\n",
       "      <td>Khmer</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ck4C5MvBqlXJLSqx</td>\n",
       "      <td>xZTyZZokUTv5Cg94</td>\n",
       "      <td>1728.0</td>\n",
       "      <td>Red</td>\n",
       "      <td>Franks</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>97991ac787bb23e3</td>\n",
       "      <td>b638b4ef08b2df96</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Blue</td>\n",
       "      <td>Persians</td>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              token             match  rating  color       civ  team  winner\n",
       "0  BsnDkR5pb9DVCbIt  9YDIBsStGjrIuGbW   952.0   Blue    Tatars     2    True\n",
       "1  88224b59212f259b  3930e585b343d9b2  1395.0  Green    Aztecs     2   False\n",
       "2  2RvyZPW1f9y4jP7M  6zAtwTFqh8OIJJuX  1330.0   Blue     Khmer     2   False\n",
       "3  Ck4C5MvBqlXJLSqx  xZTyZZokUTv5Cg94  1728.0    Red    Franks     1    True\n",
       "4  97991ac787bb23e3  b638b4ef08b2df96     NaN   Blue  Persians     2    True"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "players = pd.read_csv(\"match_players.csv\")\n",
    "display(players.info())\n",
    "players.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Point 1: Games that took more than 2 hour per server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-20T21:38:14.281573Z",
     "iopub.status.busy": "2021-05-20T21:38:14.281204Z",
     "iopub.status.idle": "2021-05-20T21:38:18.468115Z",
     "shell.execute_reply": "2021-05-20T21:38:18.466812Z",
     "shell.execute_reply.started": "2021-05-20T21:38:14.281543Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               server  counts\n",
      "0  australiasoutheast       8\n",
      "1         brazilsouth     330\n",
      "2              eastus     101\n",
      "3        koreacentral       9\n",
      "4       southeastasia      49\n",
      "5              ukwest     246\n",
      "6          westeurope       7\n",
      "7           westindia      42\n",
      "8             westus2      23\n"
     ]
    }
   ],
   "source": [
    "matches[\"duration\"] = pd.to_timedelta(matches[\"duration\"])\n",
    "huge_duration_matches = matches[(matches[\"average_rating\"] > 2000) & (matches[\"duration\"] > pd.to_timedelta(\"02:00:00\"))]\n",
    "long_games = huge_duration_matches.groupby([\"server\"])[\"token\"].count().reset_index().rename(columns={\"token\": \"counts\"})\n",
    "print(long_games[[\"server\", \"counts\"]])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Point 2: Games where the player with less rating (30% difference) won the game in 1v1 games (rating > 1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-20T21:39:38.696822Z",
     "iopub.status.busy": "2021-05-20T21:39:38.696476Z",
     "iopub.status.idle": "2021-05-20T22:06:51.856182Z",
     "shell.execute_reply": "2021-05-20T22:06:51.854777Z",
     "shell.execute_reply.started": "2021-05-20T21:39:38.696792Z"
    }
   },
   "outputs": [],
   "source": [
    "result = 0\n",
    "for idx,group in players.groupby(\"match\"):\n",
    "    # Filter games with more than two players\n",
    "    if len(group) == 2:\n",
    "        # Check if the player with lower rating won\n",
    "        conditionMet = False\n",
    "        rating_diff = 0\n",
    "        if group.iloc[0][\"rating\"] < group.iloc[1][\"rating\"] and group.iloc[0][\"winner\"] == True and group.iloc[0][\"rating\"] > 1000:\n",
    "            rating_diff = (group.iloc[1][\"rating\"] - group.iloc[0][\"rating\"]) / group.iloc[0][\"rating\"] * 100\n",
    "            conditionMet = True\n",
    "        elif group.iloc[0][\"rating\"] < group.iloc[1][\"rating\"] and group.iloc[0][\"winner\"] == True and group.iloc[1][\"rating\"] > 1000: \n",
    "            conditionMet = True\n",
    "            rating_diff = (group.iloc[0][\"rating\"] - group.iloc[1][\"rating\"]) / group.iloc[1][\"rating\"] * 100\n",
    "            \n",
    "        # Compare the rating, and only show those ones where the rating pct is above 30%\n",
    "        if conditionMet and rating_diff > 30:\n",
    "            result += 1\n",
    "\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Join both datasets to be able to do complete points 3 and 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-20T21:38:23.034214Z",
     "iopub.status.busy": "2021-05-20T21:38:23.033823Z",
     "iopub.status.idle": "2021-05-20T21:38:49.827921Z",
     "shell.execute_reply": "2021-05-20T21:38:49.826854Z",
     "shell.execute_reply.started": "2021-05-20T21:38:23.034185Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 9732500 entries, 0 to 9732499\n",
      "Data columns (total 11 columns):\n",
      " #   Column    Dtype  \n",
      "---  ------    -----  \n",
      " 0   match     object \n",
      " 1   rating    float64\n",
      " 2   civ       object \n",
      " 3   team      int64  \n",
      " 4   winner    bool   \n",
      " 5   token     object \n",
      " 6   mirror    bool   \n",
      " 7   ladder    object \n",
      " 8   map       object \n",
      " 9   server    object \n",
      " 10  duration  object \n",
      "dtypes: bool(2), float64(1), int64(1), object(7)\n",
      "memory usage: 761.1+ MB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "None"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              match  rating      civ  team  winner             token  mirror  \\\n",
      "0  9YDIBsStGjrIuGbW   952.0   Tatars     2    True  9YDIBsStGjrIuGbW   False   \n",
      "1  9YDIBsStGjrIuGbW   947.0  Teutons     1   False  9YDIBsStGjrIuGbW   False   \n",
      "2  3930e585b343d9b2  1395.0   Aztecs     2   False  3930e585b343d9b2   False   \n",
      "3  3930e585b343d9b2  1203.0  Mongols     2   False  3930e585b343d9b2   False   \n",
      "4  3930e585b343d9b2  1273.0    Khmer     1    True  3930e585b343d9b2   False   \n",
      "\n",
      "    ladder        map       server duration  \n",
      "0   RM_1v1     arabia       ukwest  0:30:57  \n",
      "1   RM_1v1     arabia       ukwest  0:30:57  \n",
      "2  RM_TEAM  lombardia  brazilsouth  0:34:26  \n",
      "3  RM_TEAM  lombardia  brazilsouth  0:34:26  \n",
      "4  RM_TEAM  lombardia  brazilsouth  0:34:26  \n"
     ]
    }
   ],
   "source": [
    "# Drop unused fields to reduce the memory footprint of the simulation\n",
    "matches.pop(\"map_size\")\n",
    "matches.pop(\"num_players\")\n",
    "matches.pop(\"average_rating\")\n",
    "matches.pop(\"winning_team\")\n",
    "matches.pop(\"patch\")\n",
    "players.pop(\"token\")\n",
    "players.pop(\"color\")\n",
    "\n",
    "join_matches = pd.merge(players, matches, left_on=\"match\", right_on=\"token\")\n",
    "display(join_matches.info())\n",
    "print(join_matches.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9732500\n"
     ]
    }
   ],
   "source": [
    "print(len(join_matches))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Point 3: Civ win rate in 1v1 games in map arena"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-20T21:39:19.158471Z",
     "iopub.status.busy": "2021-05-20T21:39:19.158093Z",
     "iopub.status.idle": "2021-05-20T21:39:25.042651Z",
     "shell.execute_reply": "2021-05-20T21:39:25.041491Z",
     "shell.execute_reply.started": "2021-05-20T21:39:19.158420Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Len previo a mergear: 240815\n",
      "Len en el join: 481630\n",
      "Join:                    match  rating         civ  team  winner             token  \\\n",
      "0       d1fe9df08a72f39c   828.0       Turks     1    True  d1fe9df08a72f39c   \n",
      "1       d1fe9df08a72f39c     NaN    Japanese     2   False  d1fe9df08a72f39c   \n",
      "2       320de965154e05ac  1120.0    Italians     2    True  320de965154e05ac   \n",
      "3       320de965154e05ac  1075.0    Japanese     1   False  320de965154e05ac   \n",
      "4       FcINRAJzqAnParaQ   946.0      Cumans     1    True  FcINRAJzqAnParaQ   \n",
      "...                  ...     ...         ...   ...     ...               ...   \n",
      "481625  BYyk4ToasDHKhSvy   713.0      Aztecs     2   False  BYyk4ToasDHKhSvy   \n",
      "481626  ImBjryDyMoWFwC1W   995.0  Vietnamese     1    True  ImBjryDyMoWFwC1W   \n",
      "481627  ImBjryDyMoWFwC1W   860.0      Franks     2   False  ImBjryDyMoWFwC1W   \n",
      "481628  dgxRINZIh0WFlZRl  1438.0     Britons     1    True  dgxRINZIh0WFlZRl   \n",
      "481629  dgxRINZIh0WFlZRl  1452.0  Bulgarians     2   False  dgxRINZIh0WFlZRl   \n",
      "\n",
      "        mirror  ladder    map         server duration  \n",
      "0        False  RM_1v1  arena         ukwest  0:44:11  \n",
      "1        False  RM_1v1  arena         ukwest  0:44:11  \n",
      "2        False  RM_1v1  arena  southeastasia  0:31:35  \n",
      "3        False  RM_1v1  arena  southeastasia  0:31:35  \n",
      "4        False  RM_1v1  arena         ukwest  0:31:15  \n",
      "...        ...     ...    ...            ...      ...  \n",
      "481625   False  RM_1v1  arena         eastus  0:54:11  \n",
      "481626   False  RM_1v1  arena         ukwest  0:27:30  \n",
      "481627   False  RM_1v1  arena         ukwest  0:27:30  \n",
      "481628   False  RM_1v1  arena      westindia  0:32:01  \n",
      "481629   False  RM_1v1  arena      westindia  0:32:01  \n",
      "\n",
      "[481630 rows x 11 columns]\n",
      "            civ   win_rate\n",
      "4       Burmese  55.122614\n",
      "32        Turks  54.511006\n",
      "11        Goths  53.842403\n",
      "0        Aztecs  52.983345\n",
      "13        Incas  52.739633\n",
      "6         Celts  52.589175\n",
      "17        Khmer  52.520497\n",
      "31      Teutons  52.510556\n",
      "28        Slavs  51.955694\n",
      "10       Franks  51.555889\n",
      "34      Vikings  51.347882\n",
      "29      Spanish  50.912328\n",
      "3    Bulgarians  50.069735\n",
      "2       Britons  49.445923\n",
      "19  Lithuanians  49.317336\n",
      "8        Cumans  48.813220\n",
      "21        Malay  48.364577\n",
      "23       Mayans  48.330778\n",
      "15     Italians  47.730769\n",
      "24      Mongols  47.707066\n",
      "9    Ethiopians  47.557252\n",
      "22      Malians  47.153343\n",
      "7       Chinese  47.150207\n",
      "5    Byzantines  46.961084\n",
      "12         Huns  46.680799\n",
      "33   Vietnamese  46.502237\n",
      "14      Indians  46.233010\n",
      "16     Japanese  45.907028\n",
      "26   Portuguese  45.665428\n",
      "25     Persians  45.062265\n",
      "18      Koreans  44.799005\n",
      "1       Berbers  43.218730\n",
      "27     Saracens  43.131961\n",
      "30       Tatars  42.050168\n",
      "20      Magyars  42.015358\n"
     ]
    }
   ],
   "source": [
    "matches_short = matches\n",
    "players_short = players\n",
    "\n",
    "matches_1v1_no_mirror = matches_short[(matches_short[\"ladder\"] == \"RM_1v1\") & (matches_short[\"mirror\"] == False) & (matches_short[\"map\"] == \"arena\")]\n",
    "print(f\"Len previo a mergear: {len(matches_1v1_no_mirror)}\")\n",
    "\n",
    "join_matches_1v1_no_mirror = pd.merge(players_short, matches_1v1_no_mirror, left_on=\"match\", right_on=\"token\")\n",
    "print(f\"Len en el join: {len(join_matches_1v1_no_mirror)}\")\n",
    "print(f\"Join: {join_matches_1v1_no_mirror}\")\n",
    "\n",
    "win_per_civ = join_matches_1v1_no_mirror.groupby(\"civ\")[\"winner\"].mean().reset_index().rename(columns={\"winner\": \"win_rate\"}).sort_values(\"win_rate\", ascending=False)\n",
    "win_per_civ[\"win_rate\"] = win_per_civ[\"win_rate\"] * 100\n",
    "print(win_per_civ[[\"civ\", \"win_rate\"]])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Point 4: Usage rate of every civ used by pro players (rating > 2000) in team matches in map islands"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-20T21:39:28.976354Z",
     "iopub.status.busy": "2021-05-20T21:39:28.975979Z",
     "iopub.status.idle": "2021-05-20T21:39:31.475795Z",
     "shell.execute_reply": "2021-05-20T21:39:31.474823Z",
     "shell.execute_reply.started": "2021-05-20T21:39:28.976323Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1675\n",
      "2419\n",
      "            civ  counts\n",
      "34      Vikings     627\n",
      "15     Italians     415\n",
      "26   Portuguese     350\n",
      "16     Japanese     143\n",
      "27     Saracens     142\n",
      "21        Malay     141\n",
      "5    Byzantines     131\n",
      "25     Persians      65\n",
      "18      Koreans      57\n",
      "29      Spanish      29\n",
      "1       Berbers      27\n",
      "19  Lithuanians      26\n",
      "2       Britons      24\n",
      "24      Mongols      18\n",
      "6         Celts      16\n",
      "22      Malians      14\n",
      "3    Bulgarians      14\n",
      "32        Turks      14\n",
      "9    Ethiopians      14\n",
      "20      Magyars      13\n",
      "13        Incas      13\n",
      "12         Huns      12\n",
      "8        Cumans      12\n",
      "11        Goths      11\n",
      "10       Franks      11\n",
      "28        Slavs      11\n",
      "31      Teutons      10\n",
      "17        Khmer      10\n",
      "14      Indians      10\n",
      "7       Chinese      10\n",
      "33   Vietnamese       8\n",
      "0        Aztecs       8\n",
      "23       Mayans       6\n",
      "30       Tatars       4\n",
      "4       Burmese       3\n"
     ]
    }
   ],
   "source": [
    "matches_1v1_pros = matches[(matches[\"ladder\"] == \"RM_TEAM\") & (matches[\"map\"] == \"islands\")]\n",
    "print(len(matches_1v1_pros))\n",
    "\n",
    "players_1v1_pros = players[(players[\"rating\"] > 2000)]\n",
    "\n",
    "join_matches_1v1_pros = pd.merge(players_1v1_pros, matches_1v1_pros, left_on=\"match\", right_on=\"token\")\n",
    "print(len(join_matches_1v1_pros))\n",
    "\n",
    "top5_civs = join_matches_1v1_pros.groupby(\"civ\")[\"token\"].count().reset_index().rename(columns={\"token\": \"counts\"})\n",
    "top5_civs = top5_civs[[\"civ\", \"counts\"]].sort_values(by='counts', ascending=False)\n",
    "print(top5_civs[[\"civ\", \"counts\"]])"
   ]
  },
  {
   "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.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}