CSGO_Teams-Part02_extracting and cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Set UP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "code_folding": [
     11
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:80% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import os, os.path\n",
    "from datetime import date\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "\n",
    "import re\n",
    "\n",
    "from pandas.plotting import register_matplotlib_converters\n",
    "import seaborn as sns\n",
    "sns.set(\n",
    "    font_scale=1.5,\n",
    "    style=\"darkgrid\",\n",
    "    rc={'figure.figsize':(20,7)})\n",
    "\n",
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:80% !important; }</style>\"))\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "\n",
    "pd.set_option('display.float_format', lambda x: '%.3f' % x)\n",
    "pd.set_option('display.max_rows', 300)\n",
    "pd.set_option('display.max_columns', 100)\n",
    "pd.set_option('display.max_colwidth', 100)\n",
    "pd.set_option('display.width', 1000)\n",
    "pd.set_option('display.precision',3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preparing the datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatening the csv extracted from Liquipedia"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are several ways to get data from liquipedia.net/counterstrike's API. You can send requests from a Python script ([see Part 01 of this series](https://github.com/ThCord/CSGO_Team-analysis/blob/master/CSGO_Teams-Part01_fetching%20data%20through%20the%20API.ipynb)) or use their \"search engine\" that does the same queries. Though you can only download one 500 rows max CSV at a time.\n",
    "\n",
    "In our case, that means we will have to concatenate 3 of them since liquipedia.net counts 1013 CS:GO teams.\n",
    "\n",
    "Depending on when you get these data, the overall figures might evolve drastically - it's logical, since liquipedia.net updates its informations as soon as they happen.\n",
    "\n",
    "To directly download the 3 csv I use you can follow this [link](https://liquipedia.net/counterstrike/index.php?title=Special:Ask&limit=500&offset=0&q=%5B%5BCategory%3ACSGO+Teams%5D%5D%0A&p=format%3Dbroadtable%2Flink%3Dall%2Fheaders%3Dshow%2Fsearchlabel%3D...-20further-20results%2Fclass%3Dsortable-20wikitable-20smwtable&po=%3FHas+name%0A%3FIs+active%0A%3FWas+created%0A%3FWas+disbanded%0A%3FHas+earnings%0A%3FHas+location%0A%3FHas+region%0A%3FHas+sponsor%0A%3FHas+earnings+in+2012%0A%3FHas+earnings+in+2013%0A%3FHas+earnings+in+2014%0A%3FHas+earnings+in+2015%0A%3FHas+earnings+in+2016%0A%3FHas+earnings+in+2017%0A%3FHas+earnings+in+2018%0A%3FHas+earnings+in+2019%0A%3FHas+site%0A%3FHas+twitter%0A%3FHas+twitch+stream%0A%3FHas+instagram%0A%3FHas+facebook%0A%3FHas+youtube+channel%0A%3FHas+faceit+profile%0A%3FHas+vk%0A%3FHas+esea+id%0A%3FHas+steam+profile%0A&sort=&order=asc&eq=yes#search), it will lead you to the dedicated Liquipedia Counter Strike page.\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "code_folding": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Concatening over:\n",
      "\tNo duplicated rows detected.\n",
      "\tNbr of rows in the dataframe : 1013\n",
      "\tNbr columns in the dataframe : 26\n"
     ]
    }
   ],
   "source": [
    "# if you did not place your datasets in the same directory as your ipynb or py file modify the next line to match their actual location\n",
    "path = os.getcwd() + r\"\\Data\"\n",
    "\n",
    "dfs = []\n",
    "for file in os.listdir(path):\n",
    "    directories = path + \"\\\\\" + file #change the \"\\\\\" according to you system if you're not on Windows\n",
    "    df = pd.read_csv(directories) \n",
    "    dfs.append(df)\n",
    "    \n",
    "CSGO_Teams = pd.concat(dfs, ignore_index=True)\n",
    "duplicateRowsDF = CSGO_Teams[CSGO_Teams.duplicated(keep='first')]\n",
    "nbrDuplicates = len(duplicateRowsDF)\n",
    "CSGO_Teams = CSGO_Teams.drop_duplicates()\n",
    "print(\"\\nConcatening over:\")\n",
    "\n",
    "#sanity check\n",
    "if nbrDuplicates != 0:\n",
    "    print(\"There were\",nbrDuplicates, \"duplicates that were deleted from the dataframe.\")\n",
    "else:\n",
    "    print(\"\\tNo duplicated rows detected.\")\n",
    "print(\"\\tNbr of rows in the dataframe :\",CSGO_Teams.shape[0])\n",
    "print(\"\\tNbr columns in the dataframe :\",CSGO_Teams.shape[1])\n",
    "\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Unnamed: 0</th>\n",
       "      <th>Has name</th>\n",
       "      <th>Is active</th>\n",
       "      <th>Was created</th>\n",
       "      <th>Was disbanded</th>\n",
       "      <th>Has earnings</th>\n",
       "      <th>Has location</th>\n",
       "      <th>Has region</th>\n",
       "      <th>Has sponsor</th>\n",
       "      <th>Has earnings in 2012</th>\n",
       "      <th>Has earnings in 2013</th>\n",
       "      <th>Has earnings in 2014</th>\n",
       "      <th>Has earnings in 2015</th>\n",
       "      <th>Has earnings in 2016</th>\n",
       "      <th>Has earnings in 2017</th>\n",
       "      <th>Has earnings in 2018</th>\n",
       "      <th>Has earnings in 2019</th>\n",
       "      <th>Has site</th>\n",
       "      <th>Has twitter</th>\n",
       "      <th>Has twitch stream</th>\n",
       "      <th>Has instagram</th>\n",
       "      <th>Has facebook</th>\n",
       "      <th>Has youtube channel</th>\n",
       "      <th>Has faceit profile</th>\n",
       "      <th>Has vk</th>\n",
       "      <th>Has esea id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>796</th>\n",
       "      <td>Team Turkey</td>\n",
       "      <td>Team Turkey</td>\n",
       "      <td>True</td>\n",
       "      <td>2004</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50254</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>912</th>\n",
       "      <td>XPC Gaming Ladies</td>\n",
       "      <td>XPC Gaming Ladies</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-10-01</td>\n",
       "      <td>2016</td>\n",
       "      <td>0</td>\n",
       "      <td>Romania</td>\n",
       "      <td>Europe</td>\n",
       "      <td>CShub.net, HeflaTV, GamersApparel</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://xpc-gaming.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.twitch.tv/xpcgaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/XPC.eSports</td>\n",
       "      <td>https://www.youtube.com/channel/UCYLWf2PETD7xGw2LSFhpKJw</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>532</th>\n",
       "      <td>Property Female</td>\n",
       "      <td>Property Female</td>\n",
       "      <td>False</td>\n",
       "      <td>2010</td>\n",
       "      <td>7 February 2017</td>\n",
       "      <td>150</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>Europe</td>\n",
       "      <td>[http://comviq.se/student COMVIQ]&lt;br /&gt;[http://www.steelseries.com/ SteelSeries]&lt;br /&gt;[http://ww...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://www.teamproperty.net/</td>\n",
       "      <td>https://twitter.com/Team_Property</td>\n",
       "      <td>https://www.twitch.tv/Team_Property</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/TeamprOprty</td>\n",
       "      <td>https://www.youtube.com/TeamprOperty</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Unnamed: 0           Has name  Is active Was created    Was disbanded  Has earnings Has location Has region                                                                                          Has sponsor  Has earnings in 2012  Has earnings in 2013  Has earnings in 2014  Has earnings in 2015  Has earnings in 2016  Has earnings in 2017  Has earnings in 2018  Has earnings in 2019                      Has site                        Has twitter                    Has twitch stream Has instagram                      Has facebook                                       Has youtube channel  Has faceit profile Has vk  Has esea id\n",
       "796        Team Turkey        Team Turkey       True        2004              NaN         50254       Turkey     Europe                                                                                                  NaN                     0                     0                     0                     0                 50000                     0                     0                     0                           NaN                                NaN                                  NaN           NaN                               NaN                                                       NaN                 nan    NaN          nan\n",
       "912  XPC Gaming Ladies  XPC Gaming Ladies      False  2015-10-01             2016             0      Romania     Europe                                                                    CShub.net, HeflaTV, GamersApparel                     0                     0                     0                     0                     0                     0                     0                     0         http://xpc-gaming.com                                NaN      https://www.twitch.tv/xpcgaming           NaN  https://facebook.com/XPC.eSports  https://www.youtube.com/channel/UCYLWf2PETD7xGw2LSFhpKJw                 nan    NaN          nan\n",
       "532    Property Female    Property Female      False        2010  7 February 2017           150       Sweden     Europe  [http://comviq.se/student COMVIQ]<br />[http://www.steelseries.com/ SteelSeries]<br />[http://ww...                     0                     0                     0                   150                     0                     0                     0                     0  http://www.teamproperty.net/  https://twitter.com/Team_Property  https://www.twitch.tv/Team_Property           NaN  https://facebook.com/TeamprOprty                      https://www.youtube.com/TeamprOperty                 nan    NaN          nan"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Teams.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1013 entries, 0 to 1012\n",
      "Data columns (total 26 columns):\n",
      "Unnamed: 0              1013 non-null object\n",
      "Has name                1013 non-null object\n",
      "Is active               1013 non-null bool\n",
      "Was created             779 non-null object\n",
      "Was disbanded           703 non-null object\n",
      "Has earnings            1013 non-null int64\n",
      "Has location            1001 non-null object\n",
      "Has region              1009 non-null object\n",
      "Has sponsor             525 non-null object\n",
      "Has earnings in 2012    1013 non-null int64\n",
      "Has earnings in 2013    1013 non-null int64\n",
      "Has earnings in 2014    1013 non-null int64\n",
      "Has earnings in 2015    1013 non-null int64\n",
      "Has earnings in 2016    1013 non-null int64\n",
      "Has earnings in 2017    1013 non-null int64\n",
      "Has earnings in 2018    1013 non-null int64\n",
      "Has earnings in 2019    1013 non-null int64\n",
      "Has site                578 non-null object\n",
      "Has twitter             638 non-null object\n",
      "Has twitch stream       222 non-null object\n",
      "Has instagram           185 non-null object\n",
      "Has facebook            607 non-null object\n",
      "Has youtube channel     352 non-null object\n",
      "Has faceit profile      0 non-null float64\n",
      "Has vk                  73 non-null object\n",
      "Has esea id             0 non-null float64\n",
      "dtypes: bool(1), float64(2), int64(9), object(14)\n",
      "memory usage: 206.8+ KB\n"
     ]
    }
   ],
   "source": [
    "CSGO_Teams.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding other statistical informations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll add some other informations about the medals each team has won. Since this didn't show up with the API we will use a Pandas feature that is great for parsing html tables : pd.read_html()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "Stats = pd.read_html(\"https://liquipedia.net/counterstrike/Statistics/Total\")\n",
    "dfs_stats = []\n",
    "for i in range(len(Stats)):\n",
    "    df = Stats[i]\n",
    "    dfs_stats.append(df)\n",
    "\n",
    "df_medals = pd.concat(dfs_stats, ignore_index=True)\n",
    "df_medals.drop_duplicates(inplace=True) \n",
    "df_medals.drop(\"Earnings\",axis=1,inplace=True)\n",
    "df_medals.rename(columns={'Organization':'Has name',\n",
    "                                     'Unnamed: 1':'Gold_medals',\n",
    "                                     'Unnamed: 2':'Silver_medals',\n",
    "                                     'Unnamed: 3':'Bronze_medals'},\n",
    "                                     inplace=True)       \n",
    "\n",
    "df_medals[\"Total_Medals\"] = df_medals.Gold_medals + df_medals.Silver_medals + df_medals.Bronze_medals\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(600, 5)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_medals.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Has name</th>\n",
       "      <th>Gold_medals</th>\n",
       "      <th>Silver_medals</th>\n",
       "      <th>Bronze_medals</th>\n",
       "      <th>Total_Medals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fnatic</td>\n",
       "      <td>85</td>\n",
       "      <td>63</td>\n",
       "      <td>25</td>\n",
       "      <td>173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Virtus.pro</td>\n",
       "      <td>60</td>\n",
       "      <td>37</td>\n",
       "      <td>22</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SK Gaming</td>\n",
       "      <td>58</td>\n",
       "      <td>36</td>\n",
       "      <td>16</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>mousesports</td>\n",
       "      <td>60</td>\n",
       "      <td>28</td>\n",
       "      <td>20</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Ninjas in Pyjamas</td>\n",
       "      <td>62</td>\n",
       "      <td>30</td>\n",
       "      <td>12</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Has name  Gold_medals  Silver_medals  Bronze_medals  Total_Medals\n",
       "1             Fnatic           85             63             25           173\n",
       "6         Virtus.pro           60             37             22           119\n",
       "3          SK Gaming           58             36             16           110\n",
       "7        mousesports           60             28             20           108\n",
       "8  Ninjas in Pyjamas           62             30             12           104"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_medals.sort_values(\"Total_Medals\",ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking for potential multiple entries"
   ]
  },
  {
   "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>Unnamed: 0</th>\n",
       "      <th>Has name</th>\n",
       "      <th>Is active</th>\n",
       "      <th>Was created</th>\n",
       "      <th>Was disbanded</th>\n",
       "      <th>Has earnings</th>\n",
       "      <th>Has location</th>\n",
       "      <th>Has region</th>\n",
       "      <th>Has sponsor</th>\n",
       "      <th>Has earnings in 2012</th>\n",
       "      <th>Has earnings in 2013</th>\n",
       "      <th>Has earnings in 2014</th>\n",
       "      <th>Has earnings in 2015</th>\n",
       "      <th>Has earnings in 2016</th>\n",
       "      <th>Has earnings in 2017</th>\n",
       "      <th>Has earnings in 2018</th>\n",
       "      <th>Has earnings in 2019</th>\n",
       "      <th>Has site</th>\n",
       "      <th>Has twitter</th>\n",
       "      <th>Has twitch stream</th>\n",
       "      <th>Has instagram</th>\n",
       "      <th>Has facebook</th>\n",
       "      <th>Has youtube channel</th>\n",
       "      <th>Has faceit profile</th>\n",
       "      <th>Has vk</th>\n",
       "      <th>Has esea id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>812</th>\n",
       "      <td>Team X (Danish team)</td>\n",
       "      <td>Team X</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-06-30</td>\n",
       "      <td>26 August 2016</td>\n",
       "      <td>50000</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Unnamed: 0 Has name  Is active Was created   Was disbanded  Has earnings Has location Has region Has sponsor  Has earnings in 2012  Has earnings in 2013  Has earnings in 2014  Has earnings in 2015  Has earnings in 2016  Has earnings in 2017  Has earnings in 2018  Has earnings in 2019 Has site Has twitter Has twitch stream Has instagram Has facebook Has youtube channel  Has faceit profile Has vk  Has esea id\n",
       "812  Team X (Danish team)   Team X      False  2016-06-30  26 August 2016         50000      Denmark     Europe         NaN                     0                     0                     0                     0                 50000                     0                     0                     0      NaN         NaN               NaN           NaN          NaN                 NaN                 nan    NaN          nan"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>Has name</th>\n",
       "      <th>Gold_medals</th>\n",
       "      <th>Silver_medals</th>\n",
       "      <th>Bronze_medals</th>\n",
       "      <th>Total_Medals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>MIBR</td>\n",
       "      <td>27</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>Counter Logic Gaming</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>OverGaming</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Has name  Gold_medals  Silver_medals  Bronze_medals  Total_Medals\n",
       "55                   MIBR           27             10              6            43\n",
       "67   Counter Logic Gaming            6              5              0            11\n",
       "498            OverGaming            1              4              0             5"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Teams[CSGO_Teams[\"Has name\"].duplicated()]\n",
    "df_medals[df_medals[\"Has name\"].duplicated()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Only three entries are duplicated in these datasets : \n",
    "    - 1 in CSGO_Teams\n",
    "    - 3 in df_medals\n",
    "    \n",
    "This needs to be investigated since it will biais our analysis after our incoming merge.\n",
    "\n",
    "Before droping these rows by their indexes let's take a look at them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Unnamed: 0</th>\n",
       "      <th>Has name</th>\n",
       "      <th>Is active</th>\n",
       "      <th>Was created</th>\n",
       "      <th>Was disbanded</th>\n",
       "      <th>Has earnings</th>\n",
       "      <th>Has location</th>\n",
       "      <th>Has region</th>\n",
       "      <th>Has sponsor</th>\n",
       "      <th>Has earnings in 2012</th>\n",
       "      <th>Has earnings in 2013</th>\n",
       "      <th>Has earnings in 2014</th>\n",
       "      <th>Has earnings in 2015</th>\n",
       "      <th>Has earnings in 2016</th>\n",
       "      <th>Has earnings in 2017</th>\n",
       "      <th>Has earnings in 2018</th>\n",
       "      <th>Has earnings in 2019</th>\n",
       "      <th>Has site</th>\n",
       "      <th>Has twitter</th>\n",
       "      <th>Has twitch stream</th>\n",
       "      <th>Has instagram</th>\n",
       "      <th>Has facebook</th>\n",
       "      <th>Has youtube channel</th>\n",
       "      <th>Has faceit profile</th>\n",
       "      <th>Has vk</th>\n",
       "      <th>Has esea id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>811</th>\n",
       "      <td>Team X (Swedish team)</td>\n",
       "      <td>Team X</td>\n",
       "      <td>False</td>\n",
       "      <td>2011</td>\n",
       "      <td>13 June 2013</td>\n",
       "      <td>0</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>812</th>\n",
       "      <td>Team X (Danish team)</td>\n",
       "      <td>Team X</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-06-30</td>\n",
       "      <td>26 August 2016</td>\n",
       "      <td>50000</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>813</th>\n",
       "      <td>Team XENEX</td>\n",
       "      <td>Team XENEX</td>\n",
       "      <td>False</td>\n",
       "      <td>[[File:Csgo icon.png|link=Counter-Strike: Global Offensive]] 2014-08-31</td>\n",
       "      <td>3 September 2018</td>\n",
       "      <td>9969</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>Europe</td>\n",
       "      <td>[http://www.gamersapparel.co.uk Gamers Apparel]&lt;br&gt;[http://www.creative.com Creative]</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6141</td>\n",
       "      <td>188</td>\n",
       "      <td>1173</td>\n",
       "      <td>2468</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://twitter.com/TeamXENEX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/TeamXENEX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Unnamed: 0    Has name  Is active                                                              Was created     Was disbanded  Has earnings    Has location Has region                                                                            Has sponsor  Has earnings in 2012  Has earnings in 2013  Has earnings in 2014  Has earnings in 2015  Has earnings in 2016  Has earnings in 2017  Has earnings in 2018  Has earnings in 2019 Has site                    Has twitter Has twitch stream Has instagram                    Has facebook Has youtube channel  Has faceit profile Has vk  Has esea id\n",
       "811  Team X (Swedish team)      Team X      False                                                                     2011      13 June 2013             0          Sweden     Europe                                                                                    NaN                     0                     0                     0                     0                     0                     0                     0                     0      NaN                            NaN               NaN           NaN                             NaN                 NaN                 nan    NaN          nan\n",
       "812   Team X (Danish team)      Team X      False                                                               2016-06-30    26 August 2016         50000         Denmark     Europe                                                                                    NaN                     0                     0                     0                     0                 50000                     0                     0                     0      NaN                            NaN               NaN           NaN                             NaN                 NaN                 nan    NaN          nan\n",
       "813             Team XENEX  Team XENEX      False  [[File:Csgo icon.png|link=Counter-Strike: Global Offensive]] 2014-08-31  3 September 2018          9969  United Kingdom     Europe  [http://www.gamersapparel.co.uk Gamers Apparel]<br>[http://www.creative.com Creative]                     0                     0                     0                  6141                   188                  1173                  2468                     0      NaN  https://twitter.com/TeamXENEX               NaN           NaN  https://facebook.com/TeamXENEX                 NaN                 nan    NaN          nan"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Teams[CSGO_Teams[\"Has name\"].str.contains(\"Team X\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Actually there were two different teams that used the same name \"Team X\", one was from Sweeden and the other one from Danemark. We'll need to specify that in the cells."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "CSGO_Teams.loc[811, 'Has name'] = \"Team X (Swedish team)\"\n",
    "CSGO_Teams.loc[812, 'Has name'] = \"Team X (Danish team)\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's take a look at the duplicate entries in df_medals :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Has name</th>\n",
       "      <th>Gold_medals</th>\n",
       "      <th>Silver_medals</th>\n",
       "      <th>Bronze_medals</th>\n",
       "      <th>Total_Medals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Counter Logic Gaming</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>Counter Logic Gaming</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Has name  Gold_medals  Silver_medals  Bronze_medals  Total_Medals\n",
       "40  Counter Logic Gaming            8              3              3            14\n",
       "67  Counter Logic Gaming            6              5              0            11"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>Has name</th>\n",
       "      <th>Gold_medals</th>\n",
       "      <th>Silver_medals</th>\n",
       "      <th>Bronze_medals</th>\n",
       "      <th>Total_Medals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>MIBR</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>MIBR</td>\n",
       "      <td>27</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Has name  Gold_medals  Silver_medals  Bronze_medals  Total_Medals\n",
       "18     MIBR            5              2              3            10\n",
       "55     MIBR           27             10              6            43"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>Has name</th>\n",
       "      <th>Gold_medals</th>\n",
       "      <th>Silver_medals</th>\n",
       "      <th>Bronze_medals</th>\n",
       "      <th>Total_Medals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>314</th>\n",
       "      <td>OverGaming</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>OverGaming</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Has name  Gold_medals  Silver_medals  Bronze_medals  Total_Medals\n",
       "314  OverGaming            0              0              0             0\n",
       "498  OverGaming            1              4              0             5"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_medals[df_medals[\"Has name\"].str.contains(\"Counter Logic Gaming\")]\n",
    "\n",
    "df_medals[df_medals[\"Has name\"] == \"MIBR\"]\n",
    "\n",
    "df_medals[df_medals[\"Has name\"] == \"OverGaming\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The double entries in df_medals are a bit more challenging to handle. According to Liquiepedia there are also two teams that share the name \"Counter Logic Gaming\". They are from the same organization, but the one still active has a \"Red\" addendum to its name that is missing in the dataset - hence the double entry...\n",
    "\n",
    "Therefore, according to Liquiepedia, at index 40 is \"Counter Logic Gaming\" and the one at index 67 is \"Counter Logic Gaming Red\". "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_medals.loc[67, 'Has name'] = \"Counter Logic Gaming Red\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Regarding MIBR team, there are actually two different entries on Liquiepdia : one where all letters are capital wich is a CS:GO team and the other where just first letter is in capital, who was focused in Counter-Strike and Counter-Strike Source. \n",
    "\n",
    "Since our study is about CS:GO and because the \"Mibr\" entry is, therefore, not in our CSGO_Teams dataset we will get rid of this one in the df_medals dataset (index 54). \n",
    "\n"
   ]
  },
  {
   "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>Has name</th>\n",
       "      <th>Gold_medals</th>\n",
       "      <th>Silver_medals</th>\n",
       "      <th>Bronze_medals</th>\n",
       "      <th>Total_Medals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>MIBR</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Has name  Gold_medals  Silver_medals  Bronze_medals  Total_Medals\n",
       "18     MIBR            5              2              3            10"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_medals.drop([55],axis=0,inplace=True)\n",
    "\n",
    "df_medals[df_medals[\"Has name\"] == \"MIBR\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For OverGaming, since the first row has no medal, we will simple delete it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>Has name</th>\n",
       "      <th>Gold_medals</th>\n",
       "      <th>Silver_medals</th>\n",
       "      <th>Bronze_medals</th>\n",
       "      <th>Total_Medals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>OverGaming</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Has name  Gold_medals  Silver_medals  Bronze_medals  Total_Medals\n",
       "498  OverGaming            1              4              0             5"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_medals.drop([314],axis=0,inplace=True)\n",
    "\n",
    "df_medals[df_medals[\"Has name\"].str.contains(\"OverGaming\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging the two dataframes "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now it's time to combine these two dataframes with a left join and our dataset will be ready for some cleaning."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Unnamed: 0</th>\n",
       "      <th>Has name</th>\n",
       "      <th>Is active</th>\n",
       "      <th>Was created</th>\n",
       "      <th>Was disbanded</th>\n",
       "      <th>Has earnings</th>\n",
       "      <th>Has location</th>\n",
       "      <th>Has region</th>\n",
       "      <th>Has sponsor</th>\n",
       "      <th>Has earnings in 2012</th>\n",
       "      <th>Has earnings in 2013</th>\n",
       "      <th>Has earnings in 2014</th>\n",
       "      <th>Has earnings in 2015</th>\n",
       "      <th>Has earnings in 2016</th>\n",
       "      <th>Has earnings in 2017</th>\n",
       "      <th>Has earnings in 2018</th>\n",
       "      <th>Has earnings in 2019</th>\n",
       "      <th>Has site</th>\n",
       "      <th>Has twitter</th>\n",
       "      <th>Has twitch stream</th>\n",
       "      <th>Has instagram</th>\n",
       "      <th>Has facebook</th>\n",
       "      <th>Has youtube channel</th>\n",
       "      <th>Has faceit profile</th>\n",
       "      <th>Has vk</th>\n",
       "      <th>Has esea id</th>\n",
       "      <th>Gold_medals</th>\n",
       "      <th>Silver_medals</th>\n",
       "      <th>Bronze_medals</th>\n",
       "      <th>Total_Medals</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/10/</td>\n",
       "      <td>/10/</td>\n",
       "      <td>False</td>\n",
       "      <td>2013</td>\n",
       "      <td>27 April 2014</td>\n",
       "      <td>3900</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>3900</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/team10cs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>True</td>\n",
       "      <td>2017-11-20 &lt;br&gt;[[File:Csgo icon.png|link=Counter-Strike: Global Offensive]]: 2017-12-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50000</td>\n",
       "      <td>United States</td>\n",
       "      <td>North America</td>\n",
       "      <td>[https://www.rocketmortgage.com/ Rocket mortgage]&lt;br /&gt;[https://www.redbull.com/us-en/ Red Bull]...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1337</td>\n",
       "      <td>1337</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15 July 2015</td>\n",
       "      <td>6650</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "      <td>6500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>False</td>\n",
       "      <td>[[File:Csgo icon.png|link=Counter-Strike: Global Offensive]] : 2013-05-17</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>South America</td>\n",
       "      <td>[http://www.kappabrasil.com.br Kappa]&lt;br /&gt;[http://arsenalxfire.com.br Arsenal XFire]&lt;br /&gt;[http...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31337 eSPORTS</td>\n",
       "      <td>31337 eSPORTS</td>\n",
       "      <td>False</td>\n",
       "      <td>2001-02-05&lt;br&gt;\\n[[File:Csgo icon.png|link=Counter-Strike: Global Offensive]] 2012-01-01</td>\n",
       "      <td>1 February 2018</td>\n",
       "      <td>1052</td>\n",
       "      <td>Poland</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>792</td>\n",
       "      <td>260</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://eleet-esports.pl/</td>\n",
       "      <td>https://twitter.com/31337GAMING</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/31337esportspl</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0       Has name  Is active                                                                              Was created    Was disbanded  Has earnings   Has location     Has region                                                                                          Has sponsor  Has earnings in 2012  Has earnings in 2013  Has earnings in 2014  Has earnings in 2015  Has earnings in 2016  Has earnings in 2017  Has earnings in 2018  Has earnings in 2019                     Has site                      Has twitter Has twitch stream                          Has instagram                         Has facebook                          Has youtube channel  Has faceit profile Has vk  Has esea id  Gold_medals  Silver_medals  Bronze_medals  Total_Medals     _merge\n",
       "0           /10/           /10/      False                                                                                     2013    27 April 2014          3900        Germany         Europe                                                                                                  NaN                     0                  3900                     0                     0                     0                     0                     0                     0                          NaN                              NaN               NaN                                    NaN        https://facebook.com/team10cs                                          NaN                 nan    NaN          nan          nan            nan            nan           nan  left_only\n",
       "1    100 Thieves    100 Thieves       True  2017-11-20 <br>[[File:Csgo icon.png|link=Counter-Strike: Global Offensive]]: 2017-12-12              NaN         50000  United States  North America  [https://www.rocketmortgage.com/ Rocket mortgage]<br />[https://www.redbull.com/us-en/ Red Bull]...                     0                     0                     0                     0                     0                     0                     0                 50000  https://www.100thieves.com/   https://twitter.com/100Thieves               NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves                 nan    NaN          nan        0.000          1.000          0.000         1.000       both\n",
       "2           1337           1337      False                                                                                      NaN     15 July 2015          6650         Sweden         Europe                                                                                                  NaN                     0                     0                   150                  6500                     0                     0                     0                     0                          NaN                              NaN               NaN                                    NaN                                  NaN                                          NaN                 nan    NaN          nan        0.000          2.000          0.000         2.000       both\n",
       "3   2Kill Gaming   2Kill Gaming      False                [[File:Csgo icon.png|link=Counter-Strike: Global Offensive]] : 2013-05-17             2018          6497         Brazil  South America  [http://www.kappabrasil.com.br Kappa]<br />[http://arsenalxfire.com.br Arsenal XFire]<br />[http...                     0                     0                     0                     0                  3255                  3242                     0                     0  https://www.2killgaming.com  https://twitter.com/2KillGaming               NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming                 nan    NaN          nan        3.000          3.000          1.000         7.000       both\n",
       "4  31337 eSPORTS  31337 eSPORTS      False  2001-02-05<br>\\n[[File:Csgo icon.png|link=Counter-Strike: Global Offensive]] 2012-01-01  1 February 2018          1052         Poland         Europe                                                                                                  NaN                     0                     0                     0                   792                   260                     0                     0                     0     http://eleet-esports.pl/  https://twitter.com/31337GAMING               NaN                                    NaN  https://facebook.com/31337esportspl                                          NaN                 nan    NaN          nan          nan            nan            nan           nan  left_only"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Merged = pd.merge(CSGO_Teams,df_medals,how=\"left\",on=\"Has name\",indicator=True)\n",
    "\n",
    "CSGO_Merged.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To do :\n",
    "\n",
    "- Changing column names\n",
    "- Remove the \"unnamed: 0\" column\n",
    "- Changing \"Is active\" results from booleans to Active/Inactive\n",
    "- Cleaning the \"Was created\" and \"Was disbanded\" to only keep the year\n",
    "- Extracting the sponsors names in the \"Has sponsor\" column \n",
    "- Rearanging columns order"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing columns name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'Has name', 'Is active', 'Was created', 'Was disbanded', 'Has earnings', 'Has location', 'Has region', 'Has sponsor', 'Has earnings in 2012', 'Has earnings in 2013', 'Has earnings in 2014', 'Has earnings in 2015', 'Has earnings in 2016', 'Has earnings in 2017', 'Has earnings in 2018', 'Has earnings in 2019', 'Has site', 'Has twitter', 'Has twitch stream', 'Has instagram', 'Has facebook', 'Has youtube channel', 'Has faceit profile', 'Has vk', 'Has esea id', 'Gold_medals', 'Silver_medals', 'Bronze_medals', 'Total_Medals', '_merge'], dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Merged.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "CSGO_Merged.rename(columns={'Has name': \"team\", \n",
    "                            'Is active':\"status\",\n",
    "                            'Was created':\"creation_date\",\n",
    "                            'Was disbanded':\"disbanding_date\",\n",
    "                            'Has earnings':\"total_gains\",\n",
    "                            'Has location': \"country\",\n",
    "                            'Has region': \"region\",\n",
    "                            'Has sponsor':\"sponsors\",\n",
    "                            'Has earnings in 2012': \"2012_gains\",\n",
    "                            'Has earnings in 2013': \"2013_gains\",\n",
    "                            'Has earnings in 2014': \"2014_gains\",\n",
    "                            'Has earnings in 2015': \"2015_gains\",\n",
    "                            'Has earnings in 2016': \"2016_gains\",\n",
    "                            'Has earnings in 2017': \"2017_gains\",\n",
    "                            'Has earnings in 2018': \"2018_gains\",\n",
    "                            'Has earnings in 2019': \"2019_gains\",\n",
    "                            'Has site' : \"website\",\n",
    "                            'Has twitter': \"twitter_account\",\n",
    "                            'Has twitch stream': \"twitch_account\",\n",
    "                            'Has instagram': \"instagram_account\",\n",
    "                            'Has facebook' : \"facebook_account\",\n",
    "                            'Has youtube channel': \"youtube_channel\",\n",
    "                            'Has faceit profile': \"faceit_profile\",\n",
    "                            'Has vk': \"VKontakte_account\",\n",
    "                            'Has esea id': \"esea_id\",\n",
    "                            'Gold_medals':\"gold_medals\",\n",
    "                            'Silver_medals': \"silver_medals\",\n",
    "                            'Bronze_medals': \"bronze_medals\",\n",
    "                            'Total_Medals':\"total_medals\"},\n",
    "                                     inplace=True)  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Droping unnecessary columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "CSGO_Merged.drop(\"Unnamed: 0\",axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>team</th>\n",
       "      <th>status</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>disbanding_date</th>\n",
       "      <th>total_gains</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>sponsors</th>\n",
       "      <th>2012_gains</th>\n",
       "      <th>2013_gains</th>\n",
       "      <th>2014_gains</th>\n",
       "      <th>2015_gains</th>\n",
       "      <th>2016_gains</th>\n",
       "      <th>2017_gains</th>\n",
       "      <th>2018_gains</th>\n",
       "      <th>2019_gains</th>\n",
       "      <th>website</th>\n",
       "      <th>twitter_account</th>\n",
       "      <th>twitch_account</th>\n",
       "      <th>instagram_account</th>\n",
       "      <th>facebook_account</th>\n",
       "      <th>youtube_channel</th>\n",
       "      <th>faceit_profile</th>\n",
       "      <th>VKontakte_account</th>\n",
       "      <th>esea_id</th>\n",
       "      <th>gold_medals</th>\n",
       "      <th>silver_medals</th>\n",
       "      <th>bronze_medals</th>\n",
       "      <th>total_medals</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>Evil Geniuses</td>\n",
       "      <td>True</td>\n",
       "      <td>1999-??-??</td>\n",
       "      <td>NaN</td>\n",
       "      <td>555587</td>\n",
       "      <td>United States</td>\n",
       "      <td>North America</td>\n",
       "      <td>[http://www.monsterenergy.com/ Monster Energy]&lt;br /&gt;[http://www.xfinity.com/gaming xfinity]&lt;br /...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>390500</td>\n",
       "      <td>http://www.evilgeniuses.gg/</td>\n",
       "      <td>https://twitter.com/EvilGeniuses</td>\n",
       "      <td>https://www.twitch.tv/team/eg</td>\n",
       "      <td>https://www.instagram.com/evilgeniuses</td>\n",
       "      <td>https://facebook.com/EvilGeniuses</td>\n",
       "      <td>https://www.youtube.com/myEGnet</td>\n",
       "      <td>nan</td>\n",
       "      <td>https://vk.com/evilgeniuses</td>\n",
       "      <td>nan</td>\n",
       "      <td>14.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>8.000</td>\n",
       "      <td>26.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              team  status creation_date disbanding_date  total_gains        country         region                                                                                             sponsors  2012_gains  2013_gains  2014_gains  2015_gains  2016_gains  2017_gains  2018_gains  2019_gains                      website                   twitter_account                 twitch_account                       instagram_account                   facebook_account                  youtube_channel  faceit_profile            VKontakte_account  esea_id  gold_medals  silver_medals  bronze_medals  total_medals _merge\n",
       "235  Evil Geniuses    True    1999-??-??             NaN       555587  United States  North America  [http://www.monsterenergy.com/ Monster Energy]<br />[http://www.xfinity.com/gaming xfinity]<br /...           0           0           0           0           0           0           0      390500  http://www.evilgeniuses.gg/  https://twitter.com/EvilGeniuses  https://www.twitch.tv/team/eg  https://www.instagram.com/evilgeniuses  https://facebook.com/EvilGeniuses  https://www.youtube.com/myEGnet             nan  https://vk.com/evilgeniuses      nan       14.000          4.000          8.000        26.000   both"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Merged.sample()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing \"Is active\" results from booleans to Active/Inactive"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The former \"Is active\" column is filled with boolean objects. We want to transform them into \"Active\" and \"Inactive\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    703\n",
       "True     310\n",
       "Name: status, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Merged.status.value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>team</th>\n",
       "      <th>status</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>disbanding_date</th>\n",
       "      <th>total_gains</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>sponsors</th>\n",
       "      <th>2012_gains</th>\n",
       "      <th>2013_gains</th>\n",
       "      <th>2014_gains</th>\n",
       "      <th>2015_gains</th>\n",
       "      <th>2016_gains</th>\n",
       "      <th>2017_gains</th>\n",
       "      <th>2018_gains</th>\n",
       "      <th>2019_gains</th>\n",
       "      <th>website</th>\n",
       "      <th>twitter_account</th>\n",
       "      <th>twitch_account</th>\n",
       "      <th>instagram_account</th>\n",
       "      <th>facebook_account</th>\n",
       "      <th>youtube_channel</th>\n",
       "      <th>faceit_profile</th>\n",
       "      <th>VKontakte_account</th>\n",
       "      <th>esea_id</th>\n",
       "      <th>gold_medals</th>\n",
       "      <th>silver_medals</th>\n",
       "      <th>bronze_medals</th>\n",
       "      <th>total_medals</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>Japaleno</td>\n",
       "      <td>Active</td>\n",
       "      <td>2016-08-??</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21203</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>720</td>\n",
       "      <td>14983</td>\n",
       "      <td>3000</td>\n",
       "      <td>2500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>14.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>18.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>761</th>\n",
       "      <td>Team Plantronics</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>5102</td>\n",
       "      <td>USA</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3402</td>\n",
       "      <td>1700</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.plantronics.com/us/gaming/</td>\n",
       "      <td>https://twitter.com/PLTgaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/PlantronicsGamingNOR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 team    status creation_date disbanding_date  total_gains country  region sponsors  2012_gains  2013_gains  2014_gains  2015_gains  2016_gains  2017_gains  2018_gains  2019_gains                                 website                twitter_account twitch_account instagram_account                           facebook_account youtube_channel  faceit_profile VKontakte_account  esea_id  gold_medals  silver_medals  bronze_medals  total_medals     _merge\n",
       "358          Japaleno    Active    2016-08-??             NaN        21203  Sweden  Europe      NaN           0           0           0           0         720       14983        3000        2500                                     NaN                            NaN            NaN               NaN                                        NaN             NaN             nan               NaN      nan       14.000          4.000          0.000        18.000       both\n",
       "761  Team Plantronics  Inactive           NaN            2017         5102     USA  Europe      NaN           0           0           0           0        3402        1700           0           0  https://www.plantronics.com/us/gaming/  https://twitter.com/PLTgaming            NaN               NaN  https://facebook.com/PlantronicsGamingNOR             NaN             nan               NaN      nan          nan            nan            nan           nan  left_only"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Merged.status = CSGO_Merged.status.map({False: 'Inactive', True: 'Active'})\n",
    "CSGO_Merged.sample(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Cleaning the \"Was created\" and \"Was disbanded\" to only keep the year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's find out how many empty values are in these columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "234"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "23.099703849950643"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Merged.creation_date.isnull().sum()\n",
    "CSGO_Merged.creation_date.isnull().mean()*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "310"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "30.60217176702863"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Merged.disbanding_date.isnull().sum()\n",
    "CSGO_Merged.disbanding_date.isnull().mean()*100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the 310 null values in the \"disbanding_date\" columns correspond to the 310 teams that are still active, the 234 null values in the \"creation_date\"column are simply missing : we have no data regarding them. That's 23% of the total dataframe.\n",
    "\n",
    "In addition, there are three rows (index 466, 605 and 720) not considered \"null\" where the creation dates is unclear with patterns like \"20??-??\" or \"????-??-??\". They will be turned into NaN once we will use regular expressions to extract our points of interest :\n",
    "    - the year of creation of the team\n",
    "    - the year the team started playing or switched to CS:GO \n",
    "    - the year of their disbanding - if any\n",
    "    \n",
    "Then all these columns will be converted into datetime objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "Switch2CSGO = CSGO_Merged.creation_date.str.extract(\".*(\\d{4}).*$\")\n",
    "CSGO_Merged.creation_date = CSGO_Merged.creation_date.str.extract(\"(\\d{4})\")\n",
    "CSGO_Merged.disbanding_date = CSGO_Merged.disbanding_date.str.extract(\"(\\d{4})\")\n",
    "\n",
    "CSGO_Merged.insert(3,\"starts_playing_CSGO\", Switch2CSGO)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "CSGO_Merged.creation_date =  pd.to_datetime(CSGO_Merged.creation_date)\n",
    "CSGO_Merged.creation_date = CSGO_Merged.creation_date.dt.to_period('Y')\n",
    "\n",
    "CSGO_Merged.disbanding_date =  pd.to_datetime(CSGO_Merged.disbanding_date)\n",
    "CSGO_Merged.disbanding_date = CSGO_Merged.disbanding_date.dt.to_period('Y')\n",
    "\n",
    "CSGO_Merged.starts_playing_CSGO =  pd.to_datetime(CSGO_Merged.starts_playing_CSGO,errors=\"coerce\")\n",
    "CSGO_Merged.starts_playing_CSGO = CSGO_Merged.starts_playing_CSGO.dt.to_period('Y')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>team</th>\n",
       "      <th>status</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>starts_playing_CSGO</th>\n",
       "      <th>disbanding_date</th>\n",
       "      <th>total_gains</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>sponsors</th>\n",
       "      <th>2012_gains</th>\n",
       "      <th>2013_gains</th>\n",
       "      <th>2014_gains</th>\n",
       "      <th>2015_gains</th>\n",
       "      <th>2016_gains</th>\n",
       "      <th>2017_gains</th>\n",
       "      <th>2018_gains</th>\n",
       "      <th>2019_gains</th>\n",
       "      <th>website</th>\n",
       "      <th>twitter_account</th>\n",
       "      <th>twitch_account</th>\n",
       "      <th>instagram_account</th>\n",
       "      <th>facebook_account</th>\n",
       "      <th>youtube_channel</th>\n",
       "      <th>faceit_profile</th>\n",
       "      <th>VKontakte_account</th>\n",
       "      <th>esea_id</th>\n",
       "      <th>gold_medals</th>\n",
       "      <th>silver_medals</th>\n",
       "      <th>bronze_medals</th>\n",
       "      <th>total_medals</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/10/</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>3900</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>3900</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/team10cs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>United States</td>\n",
       "      <td>North America</td>\n",
       "      <td>[https://www.rocketmortgage.com/ Rocket mortgage]&lt;br /&gt;[https://www.redbull.com/us-en/ Red Bull]...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1337</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015</td>\n",
       "      <td>6650</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "      <td>6500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>South America</td>\n",
       "      <td>[http://www.kappabrasil.com.br Kappa]&lt;br /&gt;[http://arsenalxfire.com.br Arsenal XFire]&lt;br /&gt;[http...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31337 eSPORTS</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2001</td>\n",
       "      <td>2012</td>\n",
       "      <td>2018</td>\n",
       "      <td>1052</td>\n",
       "      <td>Poland</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>792</td>\n",
       "      <td>260</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://eleet-esports.pl/</td>\n",
       "      <td>https://twitter.com/31337GAMING</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/31337esportspl</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            team    status creation_date starts_playing_CSGO disbanding_date  total_gains        country         region                                                                                             sponsors  2012_gains  2013_gains  2014_gains  2015_gains  2016_gains  2017_gains  2018_gains  2019_gains                      website                  twitter_account twitch_account                      instagram_account                     facebook_account                              youtube_channel  faceit_profile VKontakte_account  esea_id  gold_medals  silver_medals  bronze_medals  total_medals     _merge\n",
       "0           /10/  Inactive          2013                2013            2014         3900        Germany         Europe                                                                                                  NaN           0        3900           0           0           0           0           0           0                          NaN                              NaN            NaN                                    NaN        https://facebook.com/team10cs                                          NaN             nan               NaN      nan          nan            nan            nan           nan  left_only\n",
       "1    100 Thieves    Active          2017                2017             NaT        50000  United States  North America  [https://www.rocketmortgage.com/ Rocket mortgage]<br />[https://www.redbull.com/us-en/ Red Bull]...           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves            NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both\n",
       "2           1337  Inactive           NaT                 NaT            2015         6650         Sweden         Europe                                                                                                  NaN           0           0         150        6500           0           0           0           0                          NaN                              NaN            NaN                                    NaN                                  NaN                                          NaN             nan               NaN      nan        0.000          2.000          0.000         2.000       both\n",
       "3   2Kill Gaming  Inactive          2013                2013            2018         6497         Brazil  South America  [http://www.kappabrasil.com.br Kappa]<br />[http://arsenalxfire.com.br Arsenal XFire]<br />[http...           0           0           0           0        3255        3242           0           0  https://www.2killgaming.com  https://twitter.com/2KillGaming            NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming             nan               NaN      nan        3.000          3.000          1.000         7.000       both\n",
       "4  31337 eSPORTS  Inactive          2001                2012            2018         1052         Poland         Europe                                                                                                  NaN           0           0           0         792         260           0           0           0     http://eleet-esports.pl/  https://twitter.com/31337GAMING            NaN                                    NaN  https://facebook.com/31337esportspl                                          NaN             nan               NaN      nan          nan            nan            nan           nan  left_only"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Merged.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extracting the sponsors names in the \"Has sponsor\" column "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data in sponsor column is a far more complex challenge than what we had so far. Not only there are several brands per rows, they are \"encrypted\" in html like format which make a basic cleaning or extraction with str.split(expand=True) impossible if we want to be exhaustive.\n",
    "\n",
    "Hopefully there is nothing regex and pd.str.extractall() can solve.\n",
    "\n",
    "First let's examine some rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>sponsors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[https://www.rocketmortgage.com/ Rocket mortgage]&lt;br /&gt;[https://www.redbull.com/us-en/ Red Bull]...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[http://www.kappabrasil.com.br Kappa]&lt;br /&gt;[http://arsenalxfire.com.br Arsenal XFire]&lt;br /&gt;[http...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>[http://www.newhomepc.net/ NewHomePc]&lt;br /&gt;[http://www.agroponiente.es/ Agroponiente]&lt;br /&gt;[http...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>[https://twitter.com/MuchaProLeague Mucha Pro League]&lt;br&gt;[https://www.ihosts3.com/ iHosts3]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>720</th>\n",
       "      <td>[https://steelseries.com/ SteelSeries]&lt;br&gt;[http://www.tees4gamers.com/ TEES4GAMERS]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                sponsors\n",
       "1    [https://www.rocketmortgage.com/ Rocket mortgage]<br />[https://www.redbull.com/us-en/ Red Bull]...\n",
       "3    [http://www.kappabrasil.com.br Kappa]<br />[http://arsenalxfire.com.br Arsenal XFire]<br />[http...\n",
       "5    [http://www.newhomepc.net/ NewHomePc]<br />[http://www.agroponiente.es/ Agroponiente]<br />[http...\n",
       "22                                                                                                   NaN\n",
       "85           [https://twitter.com/MuchaProLeague Mucha Pro League]<br>[https://www.ihosts3.com/ iHosts3]\n",
       "720                  [https://steelseries.com/ SteelSeries]<br>[http://www.tees4gamers.com/ TEES4GAMERS]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Merged.sponsors.loc[[1,3,5,22,85,720]].to_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For each sponsor's name there's an url which is an interesting piece of information that we want to keep. But if a team has more than one sponsor they are all in one row, which is problematic. \n",
    "\n",
    "Moreover, if the main pattern is [URL1 brand1] br tag [URL2 brand2] and so on and so forth up to 18 times for a japonses team, with a lot of noticeable variations.\n",
    "\n",
    "The presence of NaNs - where teams have no sponsors - will also hinder our sponsors extraction since pd.str.extractall(), unlike pd.str.extract(), doesn't return them. \n",
    "\n",
    "To get rid of that we need to transform our CSGO_Merged into a multi-index dataframe in order to be able to merge to what pd.str.extractall() will return once we'll pass some regex patterns as arguments.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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></th>\n",
       "      <th>status</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>starts_playing_CSGO</th>\n",
       "      <th>disbanding_date</th>\n",
       "      <th>total_gains</th>\n",
       "      <th>region</th>\n",
       "      <th>sponsors</th>\n",
       "      <th>2012_gains</th>\n",
       "      <th>2013_gains</th>\n",
       "      <th>2014_gains</th>\n",
       "      <th>2015_gains</th>\n",
       "      <th>2016_gains</th>\n",
       "      <th>2017_gains</th>\n",
       "      <th>2018_gains</th>\n",
       "      <th>2019_gains</th>\n",
       "      <th>website</th>\n",
       "      <th>twitter_account</th>\n",
       "      <th>twitch_account</th>\n",
       "      <th>instagram_account</th>\n",
       "      <th>facebook_account</th>\n",
       "      <th>youtube_channel</th>\n",
       "      <th>faceit_profile</th>\n",
       "      <th>VKontakte_account</th>\n",
       "      <th>esea_id</th>\n",
       "      <th>gold_medals</th>\n",
       "      <th>silver_medals</th>\n",
       "      <th>bronze_medals</th>\n",
       "      <th>total_medals</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>/10/</th>\n",
       "      <th>Germany</th>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>3900</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>3900</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/team10cs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100 Thieves</th>\n",
       "      <th>United States</th>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>North America</td>\n",
       "      <td>[https://www.rocketmortgage.com/ Rocket mortgage]&lt;br /&gt;[https://www.redbull.com/us-en/ Red Bull]...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1337</th>\n",
       "      <th>Sweden</th>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015</td>\n",
       "      <td>6650</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "      <td>6500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2Kill Gaming</th>\n",
       "      <th>Brazil</th>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>South America</td>\n",
       "      <td>[http://www.kappabrasil.com.br Kappa]&lt;br /&gt;[http://arsenalxfire.com.br Arsenal XFire]&lt;br /&gt;[http...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31337 eSPORTS</th>\n",
       "      <th>Poland</th>\n",
       "      <td>Inactive</td>\n",
       "      <td>2001</td>\n",
       "      <td>2012</td>\n",
       "      <td>2018</td>\n",
       "      <td>1052</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>792</td>\n",
       "      <td>260</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://eleet-esports.pl/</td>\n",
       "      <td>https://twitter.com/31337GAMING</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/31337esportspl</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               status creation_date starts_playing_CSGO disbanding_date  total_gains         region                                                                                             sponsors  2012_gains  2013_gains  2014_gains  2015_gains  2016_gains  2017_gains  2018_gains  2019_gains                      website                  twitter_account twitch_account                      instagram_account                     facebook_account                              youtube_channel  faceit_profile VKontakte_account  esea_id  gold_medals  silver_medals  bronze_medals  total_medals     _merge\n",
       "team          country                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        \n",
       "/10/          Germany        Inactive          2013                2013            2014         3900         Europe                                                                                                  NaN           0        3900           0           0           0           0           0           0                          NaN                              NaN            NaN                                    NaN        https://facebook.com/team10cs                                          NaN             nan               NaN      nan          nan            nan            nan           nan  left_only\n",
       "100 Thieves   United States    Active          2017                2017             NaT        50000  North America  [https://www.rocketmortgage.com/ Rocket mortgage]<br />[https://www.redbull.com/us-en/ Red Bull]...           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves            NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both\n",
       "1337          Sweden         Inactive           NaT                 NaT            2015         6650         Europe                                                                                                  NaN           0           0         150        6500           0           0           0           0                          NaN                              NaN            NaN                                    NaN                                  NaN                                          NaN             nan               NaN      nan        0.000          2.000          0.000         2.000       both\n",
       "2Kill Gaming  Brazil         Inactive          2013                2013            2018         6497  South America  [http://www.kappabrasil.com.br Kappa]<br />[http://arsenalxfire.com.br Arsenal XFire]<br />[http...           0           0           0           0        3255        3242           0           0  https://www.2killgaming.com  https://twitter.com/2KillGaming            NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming             nan               NaN      nan        3.000          3.000          1.000         7.000       both\n",
       "31337 eSPORTS Poland         Inactive          2001                2012            2018         1052         Europe                                                                                                  NaN           0           0           0         792         260           0           0           0     http://eleet-esports.pl/  https://twitter.com/31337GAMING            NaN                                    NaN  https://facebook.com/31337esportspl                                          NaN             nan               NaN      nan          nan            nan            nan           nan  left_only"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_MI = CSGO_Merged.copy()\n",
    "\n",
    "CSGO_MI.set_index(['team', 'country'], inplace=True)\n",
    "\n",
    "CSGO_MI.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Giving names to your regex capture groups allows pd.str.extractall() to use them as names for the columns it returns.\n",
    "To name a capture group put some word inside (?P<...>).\n",
    "    \n",
    "The pattern here is long, but not as complicated as it might look.     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "MultiIndex: 2211 entries, (100 Thieves, United States, 0) to (x6tence White, Spain, 5)\n",
      "Data columns (total 4 columns):\n",
      "url           2211 non-null object\n",
      "ToBdroped1    2211 non-null object\n",
      "brand         2211 non-null object\n",
      "ToBdroped2    442 non-null object\n",
      "dtypes: object(4)\n",
      "memory usage: 82.5+ KB\n"
     ]
    }
   ],
   "source": [
    "pattern = r\"(?P<url>[\\w\\./-]+)(?P<ToBdroped1>/?\\s)(?P<brand>\\b.*?)\\](?P<ToBdroped2><br\\s/>\\[)?\"\n",
    "\n",
    "Extract_df = CSGO_MI[\"sponsors\"].str.extractall(pattern)\n",
    "Extract_df.info()\n",
    "# Extract_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From just this extract we can already assume that 2211 sponsor partnerships have been made between CS:GO teams and brands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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></th>\n",
       "      <th></th>\n",
       "      <th>url</th>\n",
       "      <th>ToBdroped1</th>\n",
       "      <th>brand</th>\n",
       "      <th>ToBdroped2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th>country</th>\n",
       "      <th>match</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">100 Thieves</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">United States</th>\n",
       "      <th>0</th>\n",
       "      <td>//www.rocketmortgage.com/</td>\n",
       "      <td></td>\n",
       "      <td>Rocket mortgage</td>\n",
       "      <td>&lt;br /&gt;[</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>//www.redbull.com/us-en/</td>\n",
       "      <td></td>\n",
       "      <td>Red Bull</td>\n",
       "      <td>&lt;br /&gt;[</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>//stockx.com/</td>\n",
       "      <td></td>\n",
       "      <td>stockX</td>\n",
       "      <td>&lt;br /&gt;[</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>//www.totinos.com/</td>\n",
       "      <td></td>\n",
       "      <td>Totinos</td>\n",
       "      <td>&lt;br /&gt;[</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>//cash.app/</td>\n",
       "      <td></td>\n",
       "      <td>cashapp</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">2Kill Gaming</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">Brazil</th>\n",
       "      <th>0</th>\n",
       "      <td>//www.kappabrasil.com.br</td>\n",
       "      <td></td>\n",
       "      <td>Kappa</td>\n",
       "      <td>&lt;br /&gt;[</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>//arsenalxfire.com.br</td>\n",
       "      <td></td>\n",
       "      <td>Arsenal XFire</td>\n",
       "      <td>&lt;br /&gt;[</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>//www.asrock.com/</td>\n",
       "      <td></td>\n",
       "      <td>ASRock</td>\n",
       "      <td>&lt;br /&gt;[</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>//www.seagate.com/br/pt/</td>\n",
       "      <td></td>\n",
       "      <td>Seagate</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34united Club</th>\n",
       "      <th>Spain</th>\n",
       "      <th>0</th>\n",
       "      <td>//www.newhomepc.net/</td>\n",
       "      <td></td>\n",
       "      <td>NewHomePc</td>\n",
       "      <td>&lt;br /&gt;[</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                         url ToBdroped1            brand ToBdroped2\n",
       "team          country       match                                                                  \n",
       "100 Thieves   United States 0      //www.rocketmortgage.com/             Rocket mortgage    <br />[\n",
       "                            1       //www.redbull.com/us-en/                    Red Bull    <br />[\n",
       "                            2                  //stockx.com/                      stockX    <br />[\n",
       "                            3             //www.totinos.com/                     Totinos    <br />[\n",
       "                            4                    //cash.app/                     cashapp        NaN\n",
       "2Kill Gaming  Brazil        0       //www.kappabrasil.com.br                       Kappa    <br />[\n",
       "                            1          //arsenalxfire.com.br               Arsenal XFire    <br />[\n",
       "                            2              //www.asrock.com/                      ASRock    <br />[\n",
       "                            3       //www.seagate.com/br/pt/                     Seagate        NaN\n",
       "34united Club Spain         0           //www.newhomepc.net/                   NewHomePc    <br />["
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Extract_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we go back to a single index in order to merge later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>team</th>\n",
       "      <th>country</th>\n",
       "      <th>url</th>\n",
       "      <th>brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>//www.rocketmortgage.com/</td>\n",
       "      <td>Rocket mortgage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>//www.redbull.com/us-en/</td>\n",
       "      <td>Red Bull</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>//stockx.com/</td>\n",
       "      <td>stockX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>//www.totinos.com/</td>\n",
       "      <td>Totinos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>//cash.app/</td>\n",
       "      <td>cashapp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>//www.kappabrasil.com.br</td>\n",
       "      <td>Kappa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>//arsenalxfire.com.br</td>\n",
       "      <td>Arsenal XFire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>//www.asrock.com/</td>\n",
       "      <td>ASRock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>//www.seagate.com/br/pt/</td>\n",
       "      <td>Seagate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>34united Club</td>\n",
       "      <td>Spain</td>\n",
       "      <td>//www.newhomepc.net/</td>\n",
       "      <td>NewHomePc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>34united Club</td>\n",
       "      <td>Spain</td>\n",
       "      <td>//www.agroponiente.es/</td>\n",
       "      <td>Agroponiente</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>34united Club</td>\n",
       "      <td>Spain</td>\n",
       "      <td>//pracc.eu/es/</td>\n",
       "      <td>PraccEU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>34united Club</td>\n",
       "      <td>Spain</td>\n",
       "      <td>//www.full-size.es/</td>\n",
       "      <td>Full-Size</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3DMAX</td>\n",
       "      <td>France</td>\n",
       "      <td>//gamersapparel.co.uk/</td>\n",
       "      <td>Gamers Apparel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3DMAX</td>\n",
       "      <td>France</td>\n",
       "      <td>//www.avast.com/</td>\n",
       "      <td>Avast</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             team        country                        url            brand\n",
       "0     100 Thieves  United States  //www.rocketmortgage.com/  Rocket mortgage\n",
       "1     100 Thieves  United States   //www.redbull.com/us-en/         Red Bull\n",
       "2     100 Thieves  United States              //stockx.com/           stockX\n",
       "3     100 Thieves  United States         //www.totinos.com/          Totinos\n",
       "4     100 Thieves  United States                //cash.app/          cashapp\n",
       "5    2Kill Gaming         Brazil   //www.kappabrasil.com.br            Kappa\n",
       "6    2Kill Gaming         Brazil      //arsenalxfire.com.br    Arsenal XFire\n",
       "7    2Kill Gaming         Brazil          //www.asrock.com/           ASRock\n",
       "8    2Kill Gaming         Brazil   //www.seagate.com/br/pt/          Seagate\n",
       "9   34united Club          Spain       //www.newhomepc.net/        NewHomePc\n",
       "10  34united Club          Spain     //www.agroponiente.es/     Agroponiente\n",
       "11  34united Club          Spain             //pracc.eu/es/          PraccEU\n",
       "12  34united Club          Spain        //www.full-size.es/        Full-Size\n",
       "13          3DMAX         France     //gamersapparel.co.uk/   Gamers Apparel\n",
       "14          3DMAX         France           //www.avast.com/            Avast"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Extract_df.reset_index(inplace=True)\n",
    "Extract_df.drop([\"match\",\"ToBdroped1\",\"ToBdroped2\"],axis=1,inplace=True)\n",
    "Extract_df.head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>team</th>\n",
       "      <th>country</th>\n",
       "      <th>status</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>starts_playing_CSGO</th>\n",
       "      <th>disbanding_date</th>\n",
       "      <th>total_gains</th>\n",
       "      <th>region</th>\n",
       "      <th>2012_gains</th>\n",
       "      <th>2013_gains</th>\n",
       "      <th>2014_gains</th>\n",
       "      <th>2015_gains</th>\n",
       "      <th>2016_gains</th>\n",
       "      <th>2017_gains</th>\n",
       "      <th>2018_gains</th>\n",
       "      <th>2019_gains</th>\n",
       "      <th>website</th>\n",
       "      <th>twitter_account</th>\n",
       "      <th>twitch_account</th>\n",
       "      <th>instagram_account</th>\n",
       "      <th>facebook_account</th>\n",
       "      <th>youtube_channel</th>\n",
       "      <th>faceit_profile</th>\n",
       "      <th>VKontakte_account</th>\n",
       "      <th>esea_id</th>\n",
       "      <th>gold_medals</th>\n",
       "      <th>silver_medals</th>\n",
       "      <th>bronze_medals</th>\n",
       "      <th>total_medals</th>\n",
       "      <th>_merge</th>\n",
       "      <th>url</th>\n",
       "      <th>brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/10/</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>3900</td>\n",
       "      <td>Europe</td>\n",
       "      <td>0</td>\n",
       "      <td>3900</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/team10cs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//www.rocketmortgage.com/</td>\n",
       "      <td>Rocket mortgage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//www.redbull.com/us-en/</td>\n",
       "      <td>Red Bull</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//stockx.com/</td>\n",
       "      <td>stockX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//www.totinos.com/</td>\n",
       "      <td>Totinos</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          team        country    status creation_date starts_playing_CSGO disbanding_date  total_gains         region  2012_gains  2013_gains  2014_gains  2015_gains  2016_gains  2017_gains  2018_gains  2019_gains                      website                 twitter_account twitch_account                     instagram_account                 facebook_account                     youtube_channel  faceit_profile VKontakte_account  esea_id  gold_medals  silver_medals  bronze_medals  total_medals     _merge                        url            brand\n",
       "0         /10/        Germany  Inactive          2013                2013            2014         3900         Europe           0        3900           0           0           0           0           0           0                          NaN                             NaN            NaN                                   NaN    https://facebook.com/team10cs                                 NaN             nan               NaN      nan          nan            nan            nan           nan  left_only                        NaN              NaN\n",
       "1  100 Thieves  United States    Active          2017                2017             NaT        50000  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/  https://twitter.com/100Thieves            NaN  https://www.instagram.com/100thieves  https://facebook.com/100Thieves  https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both  //www.rocketmortgage.com/  Rocket mortgage\n",
       "2  100 Thieves  United States    Active          2017                2017             NaT        50000  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/  https://twitter.com/100Thieves            NaN  https://www.instagram.com/100thieves  https://facebook.com/100Thieves  https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both   //www.redbull.com/us-en/         Red Bull\n",
       "3  100 Thieves  United States    Active          2017                2017             NaT        50000  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/  https://twitter.com/100Thieves            NaN  https://www.instagram.com/100thieves  https://facebook.com/100Thieves  https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both              //stockx.com/           stockX\n",
       "4  100 Thieves  United States    Active          2017                2017             NaT        50000  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/  https://twitter.com/100Thieves            NaN  https://www.instagram.com/100thieves  https://facebook.com/100Thieves  https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both         //www.totinos.com/          Totinos"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_MI = pd.merge(CSGO_MI,Extract_df,how=\"left\",on=[\"team\",\"country\"]).drop(\"sponsors\",axis=1)\n",
    "CSGO_MI.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can find how much sponsors each team have."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>team</th>\n",
       "      <th>nbr_sponsors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>Lemondogs</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>Team AGG</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>Furious Gaming</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Berzerk</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>FlipSid3 Tactics</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>443</th>\n",
       "      <td>VeryGames</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>Giants Gaming</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 team  nbr_sponsors\n",
       "214         Lemondogs             4\n",
       "358          Team AGG             6\n",
       "156    Furious Gaming             3\n",
       "50            Berzerk             3\n",
       "149  FlipSid3 Tactics             4\n",
       "443         VeryGames             2\n",
       "174     Giants Gaming            10"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Nbr_Sponsor = pd.crosstab(CSGO_MI[\"team\"],CSGO_MI[\"brand\"],margins=True).iloc[:,-1].to_frame()\n",
    "\n",
    "# Nbr_Sponsor = Nbr_Sponsor.rename({\"All\":\"nbr_sponsors\"},axis=1)\n",
    "Nbr_Sponsor = Nbr_Sponsor.rename({\"All\":\"nbr_sponsors\"},axis=1).reset_index()\n",
    "\n",
    "# Nbr_Sponsor = Nbr_Sponsor.reset_index()\n",
    "Nbr_Sponsor.sample(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>team</th>\n",
       "      <th>country</th>\n",
       "      <th>status</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>starts_playing_CSGO</th>\n",
       "      <th>disbanding_date</th>\n",
       "      <th>total_gains</th>\n",
       "      <th>region</th>\n",
       "      <th>2012_gains</th>\n",
       "      <th>2013_gains</th>\n",
       "      <th>2014_gains</th>\n",
       "      <th>2015_gains</th>\n",
       "      <th>2016_gains</th>\n",
       "      <th>2017_gains</th>\n",
       "      <th>2018_gains</th>\n",
       "      <th>2019_gains</th>\n",
       "      <th>website</th>\n",
       "      <th>twitter_account</th>\n",
       "      <th>twitch_account</th>\n",
       "      <th>instagram_account</th>\n",
       "      <th>facebook_account</th>\n",
       "      <th>youtube_channel</th>\n",
       "      <th>faceit_profile</th>\n",
       "      <th>VKontakte_account</th>\n",
       "      <th>esea_id</th>\n",
       "      <th>gold_medals</th>\n",
       "      <th>silver_medals</th>\n",
       "      <th>bronze_medals</th>\n",
       "      <th>total_medals</th>\n",
       "      <th>_merge</th>\n",
       "      <th>url</th>\n",
       "      <th>brand</th>\n",
       "      <th>nbr_sponsors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/10/</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>3900</td>\n",
       "      <td>Europe</td>\n",
       "      <td>0</td>\n",
       "      <td>3900</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/team10cs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//www.rocketmortgage.com/</td>\n",
       "      <td>Rocket mortgage</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//www.redbull.com/us-en/</td>\n",
       "      <td>Red Bull</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//stockx.com/</td>\n",
       "      <td>stockX</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//www.totinos.com/</td>\n",
       "      <td>Totinos</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//cash.app/</td>\n",
       "      <td>cashapp</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1337</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015</td>\n",
       "      <td>6650</td>\n",
       "      <td>Europe</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "      <td>6500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>both</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>South America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//www.kappabrasil.com.br</td>\n",
       "      <td>Kappa</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>South America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//arsenalxfire.com.br</td>\n",
       "      <td>Arsenal XFire</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>South America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//www.asrock.com/</td>\n",
       "      <td>ASRock</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>South America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>both</td>\n",
       "      <td>//www.seagate.com/br/pt/</td>\n",
       "      <td>Seagate</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>31337 eSPORTS</td>\n",
       "      <td>Poland</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2001</td>\n",
       "      <td>2012</td>\n",
       "      <td>2018</td>\n",
       "      <td>1052</td>\n",
       "      <td>Europe</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>792</td>\n",
       "      <td>260</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://eleet-esports.pl/</td>\n",
       "      <td>https://twitter.com/31337GAMING</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/31337esportspl</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>34united Club</td>\n",
       "      <td>Spain</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>1827</td>\n",
       "      <td>Europe</td>\n",
       "      <td>1076</td>\n",
       "      <td>260</td>\n",
       "      <td>491</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://www.34united.es/</td>\n",
       "      <td>https://twitter.com/34united</td>\n",
       "      <td>https://www.twitch.tv/34unitedTV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/34united</td>\n",
       "      <td>https://www.youtube.com/34unitedesportsclub</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "      <td>//www.newhomepc.net/</td>\n",
       "      <td>NewHomePc</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>34united Club</td>\n",
       "      <td>Spain</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>1827</td>\n",
       "      <td>Europe</td>\n",
       "      <td>1076</td>\n",
       "      <td>260</td>\n",
       "      <td>491</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://www.34united.es/</td>\n",
       "      <td>https://twitter.com/34united</td>\n",
       "      <td>https://www.twitch.tv/34unitedTV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/34united</td>\n",
       "      <td>https://www.youtube.com/34unitedesportsclub</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "      <td>//www.agroponiente.es/</td>\n",
       "      <td>Agroponiente</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>34united Club</td>\n",
       "      <td>Spain</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>1827</td>\n",
       "      <td>Europe</td>\n",
       "      <td>1076</td>\n",
       "      <td>260</td>\n",
       "      <td>491</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://www.34united.es/</td>\n",
       "      <td>https://twitter.com/34united</td>\n",
       "      <td>https://www.twitch.tv/34unitedTV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/34united</td>\n",
       "      <td>https://www.youtube.com/34unitedesportsclub</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "      <td>//pracc.eu/es/</td>\n",
       "      <td>PraccEU</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             team        country    status creation_date starts_playing_CSGO disbanding_date  total_gains         region  2012_gains  2013_gains  2014_gains  2015_gains  2016_gains  2017_gains  2018_gains  2019_gains                      website                  twitter_account                    twitch_account                      instagram_account                     facebook_account                              youtube_channel  faceit_profile VKontakte_account  esea_id  gold_medals  silver_medals  bronze_medals  total_medals     _merge                        url            brand  nbr_sponsors\n",
       "0            /10/        Germany  Inactive          2013                2013            2014         3900         Europe           0        3900           0           0           0           0           0           0                          NaN                              NaN                               NaN                                    NaN        https://facebook.com/team10cs                                          NaN             nan               NaN      nan          nan            nan            nan           nan  left_only                        NaN              NaN             0\n",
       "1     100 Thieves  United States    Active          2017                2017             NaT        50000  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves                               NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both  //www.rocketmortgage.com/  Rocket mortgage             5\n",
       "2     100 Thieves  United States    Active          2017                2017             NaT        50000  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves                               NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both   //www.redbull.com/us-en/         Red Bull             5\n",
       "3     100 Thieves  United States    Active          2017                2017             NaT        50000  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves                               NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both              //stockx.com/           stockX             5\n",
       "4     100 Thieves  United States    Active          2017                2017             NaT        50000  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves                               NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both         //www.totinos.com/          Totinos             5\n",
       "5     100 Thieves  United States    Active          2017                2017             NaT        50000  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves                               NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both                //cash.app/          cashapp             5\n",
       "6            1337         Sweden  Inactive           NaT                 NaT            2015         6650         Europe           0           0         150        6500           0           0           0           0                          NaN                              NaN                               NaN                                    NaN                                  NaN                                          NaN             nan               NaN      nan        0.000          2.000          0.000         2.000       both                        NaN              NaN             0\n",
       "7    2Kill Gaming         Brazil  Inactive          2013                2013            2018         6497  South America           0           0           0           0        3255        3242           0           0  https://www.2killgaming.com  https://twitter.com/2KillGaming                               NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming             nan               NaN      nan        3.000          3.000          1.000         7.000       both   //www.kappabrasil.com.br            Kappa             4\n",
       "8    2Kill Gaming         Brazil  Inactive          2013                2013            2018         6497  South America           0           0           0           0        3255        3242           0           0  https://www.2killgaming.com  https://twitter.com/2KillGaming                               NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming             nan               NaN      nan        3.000          3.000          1.000         7.000       both      //arsenalxfire.com.br    Arsenal XFire             4\n",
       "9    2Kill Gaming         Brazil  Inactive          2013                2013            2018         6497  South America           0           0           0           0        3255        3242           0           0  https://www.2killgaming.com  https://twitter.com/2KillGaming                               NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming             nan               NaN      nan        3.000          3.000          1.000         7.000       both          //www.asrock.com/           ASRock             4\n",
       "10   2Kill Gaming         Brazil  Inactive          2013                2013            2018         6497  South America           0           0           0           0        3255        3242           0           0  https://www.2killgaming.com  https://twitter.com/2KillGaming                               NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming             nan               NaN      nan        3.000          3.000          1.000         7.000       both   //www.seagate.com/br/pt/          Seagate             4\n",
       "11  31337 eSPORTS         Poland  Inactive          2001                2012            2018         1052         Europe           0           0           0         792         260           0           0           0     http://eleet-esports.pl/  https://twitter.com/31337GAMING                               NaN                                    NaN  https://facebook.com/31337esportspl                                          NaN             nan               NaN      nan          nan            nan            nan           nan  left_only                        NaN              NaN             0\n",
       "12  34united Club          Spain  Inactive           NaT                 NaT            2014         1827         Europe        1076         260         491           0           0           0           0           0      http://www.34united.es/     https://twitter.com/34united  https://www.twitch.tv/34unitedTV                                    NaN        https://facebook.com/34united  https://www.youtube.com/34unitedesportsclub             nan               NaN      nan          nan            nan            nan           nan  left_only       //www.newhomepc.net/        NewHomePc             4\n",
       "13  34united Club          Spain  Inactive           NaT                 NaT            2014         1827         Europe        1076         260         491           0           0           0           0           0      http://www.34united.es/     https://twitter.com/34united  https://www.twitch.tv/34unitedTV                                    NaN        https://facebook.com/34united  https://www.youtube.com/34unitedesportsclub             nan               NaN      nan          nan            nan            nan           nan  left_only     //www.agroponiente.es/     Agroponiente             4\n",
       "14  34united Club          Spain  Inactive           NaT                 NaT            2014         1827         Europe        1076         260         491           0           0           0           0           0      http://www.34united.es/     https://twitter.com/34united  https://www.twitch.tv/34unitedTV                                    NaN        https://facebook.com/34united  https://www.youtube.com/34unitedesportsclub             nan               NaN      nan          nan            nan            nan           nan  left_only             //pracc.eu/es/          PraccEU             4"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_MI = pd.merge(CSGO_MI,Nbr_Sponsor,how=\"left\",on=\"team\")\n",
    "CSGO_MI.nbr_sponsors = CSGO_MI.nbr_sponsors.fillna(0).astype('int64') \n",
    "CSGO_MI.head(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the CSGO_MI dataframe is a bit big with all that brand/sponsors information - that goes as up as 18 sponsors for one japonse team - we'll add the Nbr_Sponsor dataframe to the CSGO_Merged dataframe.\n",
    "\n",
    "So that we will have the option : whether we want only the number of sponsors for each team, or that + the name of all the sponsors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>team</th>\n",
       "      <th>status</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>starts_playing_CSGO</th>\n",
       "      <th>disbanding_date</th>\n",
       "      <th>total_gains</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>sponsors</th>\n",
       "      <th>2012_gains</th>\n",
       "      <th>2013_gains</th>\n",
       "      <th>2014_gains</th>\n",
       "      <th>2015_gains</th>\n",
       "      <th>2016_gains</th>\n",
       "      <th>2017_gains</th>\n",
       "      <th>2018_gains</th>\n",
       "      <th>2019_gains</th>\n",
       "      <th>website</th>\n",
       "      <th>twitter_account</th>\n",
       "      <th>twitch_account</th>\n",
       "      <th>instagram_account</th>\n",
       "      <th>facebook_account</th>\n",
       "      <th>youtube_channel</th>\n",
       "      <th>faceit_profile</th>\n",
       "      <th>VKontakte_account</th>\n",
       "      <th>esea_id</th>\n",
       "      <th>gold_medals</th>\n",
       "      <th>silver_medals</th>\n",
       "      <th>bronze_medals</th>\n",
       "      <th>total_medals</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/10/</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>3900</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>3900</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/team10cs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>United States</td>\n",
       "      <td>North America</td>\n",
       "      <td>[https://www.rocketmortgage.com/ Rocket mortgage]&lt;br /&gt;[https://www.redbull.com/us-en/ Red Bull]...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1337</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015</td>\n",
       "      <td>6650</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "      <td>6500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>South America</td>\n",
       "      <td>[http://www.kappabrasil.com.br Kappa]&lt;br /&gt;[http://arsenalxfire.com.br Arsenal XFire]&lt;br /&gt;[http...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31337 eSPORTS</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2001</td>\n",
       "      <td>2012</td>\n",
       "      <td>2018</td>\n",
       "      <td>1052</td>\n",
       "      <td>Poland</td>\n",
       "      <td>Europe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>792</td>\n",
       "      <td>260</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://eleet-esports.pl/</td>\n",
       "      <td>https://twitter.com/31337GAMING</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/31337esportspl</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            team    status creation_date starts_playing_CSGO disbanding_date  total_gains        country         region                                                                                             sponsors  2012_gains  2013_gains  2014_gains  2015_gains  2016_gains  2017_gains  2018_gains  2019_gains                      website                  twitter_account twitch_account                      instagram_account                     facebook_account                              youtube_channel  faceit_profile VKontakte_account  esea_id  gold_medals  silver_medals  bronze_medals  total_medals     _merge\n",
       "0           /10/  Inactive          2013                2013            2014         3900        Germany         Europe                                                                                                  NaN           0        3900           0           0           0           0           0           0                          NaN                              NaN            NaN                                    NaN        https://facebook.com/team10cs                                          NaN             nan               NaN      nan          nan            nan            nan           nan  left_only\n",
       "1    100 Thieves    Active          2017                2017             NaT        50000  United States  North America  [https://www.rocketmortgage.com/ Rocket mortgage]<br />[https://www.redbull.com/us-en/ Red Bull]...           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves            NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both\n",
       "2           1337  Inactive           NaT                 NaT            2015         6650         Sweden         Europe                                                                                                  NaN           0           0         150        6500           0           0           0           0                          NaN                              NaN            NaN                                    NaN                                  NaN                                          NaN             nan               NaN      nan        0.000          2.000          0.000         2.000       both\n",
       "3   2Kill Gaming  Inactive          2013                2013            2018         6497         Brazil  South America  [http://www.kappabrasil.com.br Kappa]<br />[http://arsenalxfire.com.br Arsenal XFire]<br />[http...           0           0           0           0        3255        3242           0           0  https://www.2killgaming.com  https://twitter.com/2KillGaming            NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming             nan               NaN      nan        3.000          3.000          1.000         7.000       both\n",
       "4  31337 eSPORTS  Inactive          2001                2012            2018         1052         Poland         Europe                                                                                                  NaN           0           0           0         792         260           0           0           0     http://eleet-esports.pl/  https://twitter.com/31337GAMING            NaN                                    NaN  https://facebook.com/31337esportspl                                          NaN             nan               NaN      nan          nan            nan            nan           nan  left_only"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "CSGO_Merged = pd.merge(CSGO_Merged,Nbr_Sponsor,how=\"left\",on=\"team\")\n",
    "CSGO_Merged.nbr_sponsors = CSGO_Merged.nbr_sponsors.fillna(0).astype('int64')\n",
    "\n",
    "Col_List = [\"nbr_sponsors\",\"country\",\"total_medals\"]    \n",
    "Other_cols = [i for i in CSGO_Merged if i not in Col_List]\n",
    "Start = Other_cols[0:7]\n",
    "End = Other_cols[7:]\n",
    "CSGO_Merged = CSGO_Merged[Start + Col_List + End]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>team</th>\n",
       "      <th>status</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>starts_playing_CSGO</th>\n",
       "      <th>disbanding_date</th>\n",
       "      <th>total_gains</th>\n",
       "      <th>region</th>\n",
       "      <th>nbr_sponsors</th>\n",
       "      <th>country</th>\n",
       "      <th>total_medals</th>\n",
       "      <th>sponsors</th>\n",
       "      <th>2012_gains</th>\n",
       "      <th>2013_gains</th>\n",
       "      <th>2014_gains</th>\n",
       "      <th>2015_gains</th>\n",
       "      <th>2016_gains</th>\n",
       "      <th>2017_gains</th>\n",
       "      <th>2018_gains</th>\n",
       "      <th>2019_gains</th>\n",
       "      <th>website</th>\n",
       "      <th>twitter_account</th>\n",
       "      <th>twitch_account</th>\n",
       "      <th>instagram_account</th>\n",
       "      <th>facebook_account</th>\n",
       "      <th>youtube_channel</th>\n",
       "      <th>faceit_profile</th>\n",
       "      <th>VKontakte_account</th>\n",
       "      <th>esea_id</th>\n",
       "      <th>gold_medals</th>\n",
       "      <th>silver_medals</th>\n",
       "      <th>bronze_medals</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/10/</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>3900</td>\n",
       "      <td>Europe</td>\n",
       "      <td>0</td>\n",
       "      <td>Germany</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>3900</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/team10cs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>North America</td>\n",
       "      <td>5</td>\n",
       "      <td>United States</td>\n",
       "      <td>1.000</td>\n",
       "      <td>[https://www.rocketmortgage.com/ Rocket mortgage]&lt;br /&gt;[https://www.redbull.com/us-en/ Red Bull]...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1337</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015</td>\n",
       "      <td>6650</td>\n",
       "      <td>Europe</td>\n",
       "      <td>0</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>2.000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "      <td>6500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>South America</td>\n",
       "      <td>4</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>7.000</td>\n",
       "      <td>[http://www.kappabrasil.com.br Kappa]&lt;br /&gt;[http://arsenalxfire.com.br Arsenal XFire]&lt;br /&gt;[http...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31337 eSPORTS</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2001</td>\n",
       "      <td>2012</td>\n",
       "      <td>2018</td>\n",
       "      <td>1052</td>\n",
       "      <td>Europe</td>\n",
       "      <td>0</td>\n",
       "      <td>Poland</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>792</td>\n",
       "      <td>260</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://eleet-esports.pl/</td>\n",
       "      <td>https://twitter.com/31337GAMING</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/31337esportspl</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>34united Club</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>1827</td>\n",
       "      <td>Europe</td>\n",
       "      <td>4</td>\n",
       "      <td>Spain</td>\n",
       "      <td>nan</td>\n",
       "      <td>[http://www.newhomepc.net/ NewHomePc]&lt;br /&gt;[http://www.agroponiente.es/ Agroponiente]&lt;br /&gt;[http...</td>\n",
       "      <td>1076</td>\n",
       "      <td>260</td>\n",
       "      <td>491</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://www.34united.es/</td>\n",
       "      <td>https://twitter.com/34united</td>\n",
       "      <td>https://www.twitch.tv/34unitedTV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/34united</td>\n",
       "      <td>https://www.youtube.com/34unitedesportsclub</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3DMAX</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2009</td>\n",
       "      <td>2009</td>\n",
       "      <td>2019</td>\n",
       "      <td>86562</td>\n",
       "      <td>Europe</td>\n",
       "      <td>3</td>\n",
       "      <td>France</td>\n",
       "      <td>15.000</td>\n",
       "      <td>[https://gamersapparel.co.uk/ Gamers Apparel]&lt;br&gt;[https://www.avast.com/ Avast]&lt;br&gt;[https://fr.a...</td>\n",
       "      <td>2125</td>\n",
       "      <td>2969</td>\n",
       "      <td>7952</td>\n",
       "      <td>6538</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45576</td>\n",
       "      <td>15375</td>\n",
       "      <td>https://www.3dmax.team/</td>\n",
       "      <td>https://twitter.com/3DMAXGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/3DMAXGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>9.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            team    status creation_date starts_playing_CSGO disbanding_date  total_gains         region  nbr_sponsors        country  total_medals                                                                                             sponsors  2012_gains  2013_gains  2014_gains  2015_gains  2016_gains  2017_gains  2018_gains  2019_gains                      website                  twitter_account                    twitch_account                      instagram_account                     facebook_account                              youtube_channel  faceit_profile VKontakte_account  esea_id  gold_medals  silver_medals  bronze_medals     _merge\n",
       "0           /10/  Inactive          2013                2013            2014         3900         Europe             0        Germany           nan                                                                                                  NaN           0        3900           0           0           0           0           0           0                          NaN                              NaN                               NaN                                    NaN        https://facebook.com/team10cs                                          NaN             nan               NaN      nan          nan            nan            nan  left_only\n",
       "1    100 Thieves    Active          2017                2017             NaT        50000  North America             5  United States         1.000  [https://www.rocketmortgage.com/ Rocket mortgage]<br />[https://www.redbull.com/us-en/ Red Bull]...           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves                               NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000       both\n",
       "2           1337  Inactive           NaT                 NaT            2015         6650         Europe             0         Sweden         2.000                                                                                                  NaN           0           0         150        6500           0           0           0           0                          NaN                              NaN                               NaN                                    NaN                                  NaN                                          NaN             nan               NaN      nan        0.000          2.000          0.000       both\n",
       "3   2Kill Gaming  Inactive          2013                2013            2018         6497  South America             4         Brazil         7.000  [http://www.kappabrasil.com.br Kappa]<br />[http://arsenalxfire.com.br Arsenal XFire]<br />[http...           0           0           0           0        3255        3242           0           0  https://www.2killgaming.com  https://twitter.com/2KillGaming                               NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming             nan               NaN      nan        3.000          3.000          1.000       both\n",
       "4  31337 eSPORTS  Inactive          2001                2012            2018         1052         Europe             0         Poland           nan                                                                                                  NaN           0           0           0         792         260           0           0           0     http://eleet-esports.pl/  https://twitter.com/31337GAMING                               NaN                                    NaN  https://facebook.com/31337esportspl                                          NaN             nan               NaN      nan          nan            nan            nan  left_only\n",
       "5  34united Club  Inactive           NaT                 NaT            2014         1827         Europe             4          Spain           nan  [http://www.newhomepc.net/ NewHomePc]<br />[http://www.agroponiente.es/ Agroponiente]<br />[http...        1076         260         491           0           0           0           0           0      http://www.34united.es/     https://twitter.com/34united  https://www.twitch.tv/34unitedTV                                    NaN        https://facebook.com/34united  https://www.youtube.com/34unitedesportsclub             nan               NaN      nan          nan            nan            nan  left_only\n",
       "6          3DMAX  Inactive          2009                2009            2019        86562         Europe             3         France        15.000  [https://gamersapparel.co.uk/ Gamers Apparel]<br>[https://www.avast.com/ Avast]<br>[https://fr.a...        2125        2969        7952        6538           0           0       45576       15375      https://www.3dmax.team/  https://twitter.com/3DMAXGaming                               NaN                                    NaN     https://facebook.com/3DMAXGaming                                          NaN             nan               NaN      nan        9.000          4.000          2.000       both"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Merged.head(7)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rearanging columns order"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we'll create a list of the column(s) we want to move.\n",
    "Then we'll create another list with the rest of the columns.\n",
    "And we'll specify where we want to place or columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "Col_List = [\"nbr_sponsors\",\"brand\",\"url\"]    \n",
    "Other_cols = [i for i in CSGO_MI if i not in Col_List]\n",
    "Start = Other_cols[0:7]\n",
    "End = Other_cols[7:]\n",
    "CSGO_MI = CSGO_MI[Start + Col_List + End]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>team</th>\n",
       "      <th>country</th>\n",
       "      <th>status</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>starts_playing_CSGO</th>\n",
       "      <th>disbanding_date</th>\n",
       "      <th>total_gains</th>\n",
       "      <th>nbr_sponsors</th>\n",
       "      <th>brand</th>\n",
       "      <th>url</th>\n",
       "      <th>region</th>\n",
       "      <th>2012_gains</th>\n",
       "      <th>2013_gains</th>\n",
       "      <th>2014_gains</th>\n",
       "      <th>2015_gains</th>\n",
       "      <th>2016_gains</th>\n",
       "      <th>2017_gains</th>\n",
       "      <th>2018_gains</th>\n",
       "      <th>2019_gains</th>\n",
       "      <th>website</th>\n",
       "      <th>twitter_account</th>\n",
       "      <th>twitch_account</th>\n",
       "      <th>instagram_account</th>\n",
       "      <th>facebook_account</th>\n",
       "      <th>youtube_channel</th>\n",
       "      <th>faceit_profile</th>\n",
       "      <th>VKontakte_account</th>\n",
       "      <th>esea_id</th>\n",
       "      <th>gold_medals</th>\n",
       "      <th>silver_medals</th>\n",
       "      <th>bronze_medals</th>\n",
       "      <th>total_medals</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/10/</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2014</td>\n",
       "      <td>3900</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Europe</td>\n",
       "      <td>0</td>\n",
       "      <td>3900</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/team10cs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>5</td>\n",
       "      <td>Rocket mortgage</td>\n",
       "      <td>//www.rocketmortgage.com/</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>5</td>\n",
       "      <td>Red Bull</td>\n",
       "      <td>//www.redbull.com/us-en/</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>5</td>\n",
       "      <td>stockX</td>\n",
       "      <td>//stockx.com/</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>5</td>\n",
       "      <td>Totinos</td>\n",
       "      <td>//www.totinos.com/</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100 Thieves</td>\n",
       "      <td>United States</td>\n",
       "      <td>Active</td>\n",
       "      <td>2017</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaT</td>\n",
       "      <td>50000</td>\n",
       "      <td>5</td>\n",
       "      <td>cashapp</td>\n",
       "      <td>//cash.app/</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>https://www.100thieves.com/</td>\n",
       "      <td>https://twitter.com/100Thieves</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/100thieves</td>\n",
       "      <td>https://facebook.com/100Thieves</td>\n",
       "      <td>https://www.youtube.com/100thieves</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1337</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2015</td>\n",
       "      <td>6650</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Europe</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "      <td>6500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>4</td>\n",
       "      <td>Kappa</td>\n",
       "      <td>//www.kappabrasil.com.br</td>\n",
       "      <td>South America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>4</td>\n",
       "      <td>Arsenal XFire</td>\n",
       "      <td>//arsenalxfire.com.br</td>\n",
       "      <td>South America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>4</td>\n",
       "      <td>ASRock</td>\n",
       "      <td>//www.asrock.com/</td>\n",
       "      <td>South America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2Kill Gaming</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013</td>\n",
       "      <td>2018</td>\n",
       "      <td>6497</td>\n",
       "      <td>4</td>\n",
       "      <td>Seagate</td>\n",
       "      <td>//www.seagate.com/br/pt/</td>\n",
       "      <td>South America</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3255</td>\n",
       "      <td>3242</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://www.2killgaming.com</td>\n",
       "      <td>https://twitter.com/2KillGaming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.instagram.com/2killgaming</td>\n",
       "      <td>https://facebook.com/2killgaming</td>\n",
       "      <td>https://www.youtube.com/channel/2KillGaming</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>7.000</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>31337 eSPORTS</td>\n",
       "      <td>Poland</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2001</td>\n",
       "      <td>2012</td>\n",
       "      <td>2018</td>\n",
       "      <td>1052</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Europe</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>792</td>\n",
       "      <td>260</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://eleet-esports.pl/</td>\n",
       "      <td>https://twitter.com/31337GAMING</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/31337esportspl</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>34united Club</td>\n",
       "      <td>Spain</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>1827</td>\n",
       "      <td>4</td>\n",
       "      <td>NewHomePc</td>\n",
       "      <td>//www.newhomepc.net/</td>\n",
       "      <td>Europe</td>\n",
       "      <td>1076</td>\n",
       "      <td>260</td>\n",
       "      <td>491</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://www.34united.es/</td>\n",
       "      <td>https://twitter.com/34united</td>\n",
       "      <td>https://www.twitch.tv/34unitedTV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/34united</td>\n",
       "      <td>https://www.youtube.com/34unitedesportsclub</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>34united Club</td>\n",
       "      <td>Spain</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>1827</td>\n",
       "      <td>4</td>\n",
       "      <td>Agroponiente</td>\n",
       "      <td>//www.agroponiente.es/</td>\n",
       "      <td>Europe</td>\n",
       "      <td>1076</td>\n",
       "      <td>260</td>\n",
       "      <td>491</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://www.34united.es/</td>\n",
       "      <td>https://twitter.com/34united</td>\n",
       "      <td>https://www.twitch.tv/34unitedTV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/34united</td>\n",
       "      <td>https://www.youtube.com/34unitedesportsclub</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>34united Club</td>\n",
       "      <td>Spain</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>1827</td>\n",
       "      <td>4</td>\n",
       "      <td>PraccEU</td>\n",
       "      <td>//pracc.eu/es/</td>\n",
       "      <td>Europe</td>\n",
       "      <td>1076</td>\n",
       "      <td>260</td>\n",
       "      <td>491</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>http://www.34united.es/</td>\n",
       "      <td>https://twitter.com/34united</td>\n",
       "      <td>https://www.twitch.tv/34unitedTV</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://facebook.com/34united</td>\n",
       "      <td>https://www.youtube.com/34unitedesportsclub</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             team        country    status creation_date starts_playing_CSGO disbanding_date  total_gains  nbr_sponsors            brand                        url         region  2012_gains  2013_gains  2014_gains  2015_gains  2016_gains  2017_gains  2018_gains  2019_gains                      website                  twitter_account                    twitch_account                      instagram_account                     facebook_account                              youtube_channel  faceit_profile VKontakte_account  esea_id  gold_medals  silver_medals  bronze_medals  total_medals     _merge\n",
       "0            /10/        Germany  Inactive          2013                2013            2014         3900             0              NaN                        NaN         Europe           0        3900           0           0           0           0           0           0                          NaN                              NaN                               NaN                                    NaN        https://facebook.com/team10cs                                          NaN             nan               NaN      nan          nan            nan            nan           nan  left_only\n",
       "1     100 Thieves  United States    Active          2017                2017             NaT        50000             5  Rocket mortgage  //www.rocketmortgage.com/  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves                               NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both\n",
       "2     100 Thieves  United States    Active          2017                2017             NaT        50000             5         Red Bull   //www.redbull.com/us-en/  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves                               NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both\n",
       "3     100 Thieves  United States    Active          2017                2017             NaT        50000             5           stockX              //stockx.com/  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves                               NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both\n",
       "4     100 Thieves  United States    Active          2017                2017             NaT        50000             5          Totinos         //www.totinos.com/  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves                               NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both\n",
       "5     100 Thieves  United States    Active          2017                2017             NaT        50000             5          cashapp                //cash.app/  North America           0           0           0           0           0           0           0       50000  https://www.100thieves.com/   https://twitter.com/100Thieves                               NaN   https://www.instagram.com/100thieves      https://facebook.com/100Thieves           https://www.youtube.com/100thieves             nan               NaN      nan        0.000          1.000          0.000         1.000       both\n",
       "6            1337         Sweden  Inactive           NaT                 NaT            2015         6650             0              NaN                        NaN         Europe           0           0         150        6500           0           0           0           0                          NaN                              NaN                               NaN                                    NaN                                  NaN                                          NaN             nan               NaN      nan        0.000          2.000          0.000         2.000       both\n",
       "7    2Kill Gaming         Brazil  Inactive          2013                2013            2018         6497             4            Kappa   //www.kappabrasil.com.br  South America           0           0           0           0        3255        3242           0           0  https://www.2killgaming.com  https://twitter.com/2KillGaming                               NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming             nan               NaN      nan        3.000          3.000          1.000         7.000       both\n",
       "8    2Kill Gaming         Brazil  Inactive          2013                2013            2018         6497             4    Arsenal XFire      //arsenalxfire.com.br  South America           0           0           0           0        3255        3242           0           0  https://www.2killgaming.com  https://twitter.com/2KillGaming                               NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming             nan               NaN      nan        3.000          3.000          1.000         7.000       both\n",
       "9    2Kill Gaming         Brazil  Inactive          2013                2013            2018         6497             4           ASRock          //www.asrock.com/  South America           0           0           0           0        3255        3242           0           0  https://www.2killgaming.com  https://twitter.com/2KillGaming                               NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming             nan               NaN      nan        3.000          3.000          1.000         7.000       both\n",
       "10   2Kill Gaming         Brazil  Inactive          2013                2013            2018         6497             4          Seagate   //www.seagate.com/br/pt/  South America           0           0           0           0        3255        3242           0           0  https://www.2killgaming.com  https://twitter.com/2KillGaming                               NaN  https://www.instagram.com/2killgaming     https://facebook.com/2killgaming  https://www.youtube.com/channel/2KillGaming             nan               NaN      nan        3.000          3.000          1.000         7.000       both\n",
       "11  31337 eSPORTS         Poland  Inactive          2001                2012            2018         1052             0              NaN                        NaN         Europe           0           0           0         792         260           0           0           0     http://eleet-esports.pl/  https://twitter.com/31337GAMING                               NaN                                    NaN  https://facebook.com/31337esportspl                                          NaN             nan               NaN      nan          nan            nan            nan           nan  left_only\n",
       "12  34united Club          Spain  Inactive           NaT                 NaT            2014         1827             4        NewHomePc       //www.newhomepc.net/         Europe        1076         260         491           0           0           0           0           0      http://www.34united.es/     https://twitter.com/34united  https://www.twitch.tv/34unitedTV                                    NaN        https://facebook.com/34united  https://www.youtube.com/34unitedesportsclub             nan               NaN      nan          nan            nan            nan           nan  left_only\n",
       "13  34united Club          Spain  Inactive           NaT                 NaT            2014         1827             4     Agroponiente     //www.agroponiente.es/         Europe        1076         260         491           0           0           0           0           0      http://www.34united.es/     https://twitter.com/34united  https://www.twitch.tv/34unitedTV                                    NaN        https://facebook.com/34united  https://www.youtube.com/34unitedesportsclub             nan               NaN      nan          nan            nan            nan           nan  left_only\n",
       "14  34united Club          Spain  Inactive           NaT                 NaT            2014         1827             4          PraccEU             //pracc.eu/es/         Europe        1076         260         491           0           0           0           0           0      http://www.34united.es/     https://twitter.com/34united  https://www.twitch.tv/34unitedTV                                    NaN        https://facebook.com/34united  https://www.youtube.com/34unitedesportsclub             nan               NaN      nan          nan            nan            nan           nan  left_only"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_MI.head(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the next part we'll start to analyse this dataset. But for now, as an appetizer, let's find out how the top 10 CS:GO teams has performed in terms of gains, and how much medals and sponsorships they have :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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></th>\n",
       "      <th>total_gains</th>\n",
       "      <th>total_medals</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th>nbr_sponsors</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Astralis</th>\n",
       "      <th>7</th>\n",
       "      <td>7452634</td>\n",
       "      <td>43.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fnatic</th>\n",
       "      <th>4</th>\n",
       "      <td>4934614</td>\n",
       "      <td>173.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Team Liquid</th>\n",
       "      <th>9</th>\n",
       "      <td>4160812</td>\n",
       "      <td>57.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SK Gaming</th>\n",
       "      <th>6</th>\n",
       "      <td>3651018</td>\n",
       "      <td>110.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Natus Vincere</th>\n",
       "      <th>6</th>\n",
       "      <td>3505092</td>\n",
       "      <td>91.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FaZe Clan</th>\n",
       "      <th>6</th>\n",
       "      <td>3357347</td>\n",
       "      <td>28.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Virtus.pro</th>\n",
       "      <th>6</th>\n",
       "      <td>3286234</td>\n",
       "      <td>119.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ninjas in Pyjamas</th>\n",
       "      <th>7</th>\n",
       "      <td>2830282</td>\n",
       "      <td>104.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mousesports</th>\n",
       "      <th>5</th>\n",
       "      <td>2448417</td>\n",
       "      <td>108.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cloud9</th>\n",
       "      <th>8</th>\n",
       "      <td>2341726</td>\n",
       "      <td>57.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                total_gains  total_medals\n",
       "team              nbr_sponsors                           \n",
       "Astralis          7                 7452634        43.000\n",
       "Fnatic            4                 4934614       173.000\n",
       "Team Liquid       9                 4160812        57.000\n",
       "SK Gaming         6                 3651018       110.000\n",
       "Natus Vincere     6                 3505092        91.000\n",
       "FaZe Clan         6                 3357347        28.000\n",
       "Virtus.pro        6                 3286234       119.000\n",
       "Ninjas in Pyjamas 7                 2830282       104.000\n",
       "mousesports       5                 2448417       108.000\n",
       "Cloud9            8                 2341726        57.000"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_Merged.groupby([\"team\",\"nbr_sponsors\"]).agg({\"total_gains\":sum,'total_medals':(sum)}).sort_values([\"total_gains\",\"total_medals\"],ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And here are the 10 brands that has sponsored the most CS:GO teams :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>team</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>brand</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Twitch</th>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HyperX</th>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DXRacer</th>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Razer</th>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SteelSeries</th>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sennheiser</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMD</th>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MSI</th>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Intel</th>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scuf Gaming</th>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             team\n",
       "brand            \n",
       "Twitch         64\n",
       "HyperX         51\n",
       "DXRacer        40\n",
       "Razer          34\n",
       "SteelSeries    31\n",
       "Sennheiser     21\n",
       "AMD            20\n",
       "MSI            19\n",
       "Intel          19\n",
       "Scuf Gaming    18"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CSGO_MI.groupby('brand').agg({'team':(np.size)}).sort_values(\"team\",ascending=False).nlargest(10,\"team\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting the two dataframes to csv :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "CSGO_Merged.to_csv(os.getcwd() + \"\\%s.csv\" % \"CSGO_Merged\",index=False)\n",
    "CSGO_MI.to_csv(os.getcwd() + \"\\%s.csv\" % \"CSGO_MI\",index=False)"
   ]
  }
 ],
 "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"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "327.8px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}