DMW_Project_ScrapingAndCleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T09:27:22.658941Z",
     "start_time": "2019-07-21T09:27:21.294990Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "from selenium import webdriver\n",
    "import sqlite3\n",
    "from time import sleep\n",
    "import re\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting player ids of verified players from Dotabuff"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-19T09:46:37.576496Z",
     "start_time": "2019-07-19T09:46:33.582660Z"
    }
   },
   "source": [
    "```python\n",
    "headers = {\n",
    "    'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/'\n",
    "    '537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36'}\n",
    "source = requests.get('https://www.dotabuff.com/players', headers=headers)\n",
    "soup = BeautifulSoup(source.text, 'lxml')\n",
    "pids = [i['href'].split(\"/\")[2] for i in soup.select('td a.link-type-player')]\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pickup where we left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-19T17:20:21.612268Z",
     "start_time": "2019-07-19T17:20:21.603749Z"
    }
   },
   "outputs": [],
   "source": [
    "def pickup_impact(pid_list):\n",
    "    try:\n",
    "        pids_impact = pd.read_sql('''SELECT DISTINCT PID FROM Impact''', \n",
    "                                  conn)['PID'].unique()\n",
    "        new_impact = [i for i in pids if i not in pids_impact]   \n",
    "    except:\n",
    "        new_impact = pid_list  \n",
    "    return new_impact"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-19T06:18:45.776401Z",
     "start_time": "2019-07-19T06:18:45.768254Z"
    }
   },
   "outputs": [],
   "source": [
    "def pickup_economy(pid_list):\n",
    "    try:\n",
    "        pids_economy = pd.read_sql('''SELECT DISTINCT PID FROM Economy''', \n",
    "                                   conn)['PID'].unique()\n",
    "        new_economy = [i for i in pids if i not in pids_economy]   \n",
    "    except:\n",
    "        new_economy = pid_list  \n",
    "    return new_economy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-19T06:18:45.975671Z",
     "start_time": "2019-07-19T06:18:45.968175Z"
    }
   },
   "outputs": [],
   "source": [
    "def pickup_played(pid_list):\n",
    "    try:\n",
    "        pids_played = pd.read_sql('''SELECT DISTINCT PID FROM Played''', \n",
    "                                  conn)['PID'].unique()\n",
    "        new_played = [i for i in pids if i not in pids_played]   \n",
    "    except:\n",
    "        new_played = pid_list  \n",
    "    return new_played"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-19T09:25:23.142798Z",
     "start_time": "2019-07-19T09:25:23.135855Z"
    }
   },
   "outputs": [],
   "source": [
    "def pickup_itemeco(pid_list):\n",
    "    try:\n",
    "        pids_itemeco = pd.read_sql('''SELECT DISTINCT PID FROM ItemEco''', \n",
    "                                   conn)['PID'].unique()\n",
    "        new_itemeco = [i for i in pids if i not in pids_itemeco]   \n",
    "    except:\n",
    "        new_itemeco = pid_list  \n",
    "    return new_itemeco"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-19T17:19:29.970556Z",
     "start_time": "2019-07-19T17:19:29.959924Z"
    }
   },
   "outputs": [],
   "source": [
    "def pickup_itemmain(pid_list):\n",
    "    try:\n",
    "        pids_itemmain = pd.read_sql('''SELECT DISTINCT PID FROM ItemMain''', \n",
    "                                    conn)['PID'].unique()\n",
    "        new_itemmain = [i for i in pids if i not in pids_itemmain]   \n",
    "    except:\n",
    "        new_itemmain = pid_list  \n",
    "    return new_itemmain"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scraping using Selenium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T09:27:28.852399Z",
     "start_time": "2019-07-21T09:27:28.848944Z"
    }
   },
   "outputs": [],
   "source": [
    "chrome_path = r'C:\\Users\\jedda\\Downloads\\chromedriver.exe'\n",
    "conn = sqlite3.connect('dota.db')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Impact"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-19T08:00:20.676227Z",
     "start_time": "2019-07-19T07:59:49.481424Z"
    }
   },
   "source": [
    "```python\n",
    "pids_impact = pickup_impact(pids)\n",
    "driver = webdriver.Chrome(chrome_path)\n",
    "for p in pids_impact:\n",
    "    driver.get('https://www.dotabuff.com/players/'+p+'/heroes?metric=impact')\n",
    "    query = driver.find_elements_by_xpath(\n",
    "        '/html/body/div[1]/div[8]/div[3]/section/article/table/tbody/*')\n",
    "    rows = [tuple(i.text.split('\\n')) for i in query]\n",
    "    header = ['Hero', 'Date', 'KDA Ratio', 'Kills', 'Death', 'Assists']\n",
    "    impactdb = pd.DataFrame(rows, columns=header)\n",
    "    impactdb.drop(columns='Date', inplace=True)\n",
    "    impactdb['PID'] = p\n",
    "    impactdb.to_sql('Impact', conn, if_exists='append')\n",
    "    sleep(2)\n",
    "driver.close()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Economy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-19T08:01:41.218377Z",
     "start_time": "2019-07-19T08:01:17.042471Z"
    }
   },
   "source": [
    "```python\n",
    "pids_economy = pickup_economy(pids)\n",
    "driver = webdriver.Chrome(chrome_path)\n",
    "for p in pids_economy:\n",
    "    driver.get('https://www.dotabuff.com/players/'+p+'/heroes?metric=economy')\n",
    "    query = driver.find_elements_by_xpath(\n",
    "        '/html/body/div[1]/div[8]/div[3]/section/article/table/tbody/*')\n",
    "    rows = [tuple(i.text.split('\\n')) for i in query]\n",
    "    header = ['Hero', 'Date', 'GPM', 'XPM']\n",
    "    economydb = pd.DataFrame(rows, columns=header)\n",
    "    economydb.drop(columns='Date', inplace=True)\n",
    "    economydb['PID'] = p\n",
    "    economydb.to_sql('Economy', conn, if_exists='append')\n",
    "    sleep(2)\n",
    "driver.close()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Played"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-19T08:04:51.333992Z",
     "start_time": "2019-07-19T08:04:48.243475Z"
    }
   },
   "source": [
    "```python\n",
    "pids_played = pickup_played(pids)\n",
    "driver = webdriver.Chrome(chrome_path)\n",
    "for p in pids_played:\n",
    "    driver.get('https://www.dotabuff.com/players/'+p+'/heroes?metric=played')\n",
    "    query = driver.find_elements_by_xpath(\n",
    "        '/html/body/div[1]/div[8]/div[3]/section/article/table/tbody/*')\n",
    "    rows = [tuple(i.text.split('\\n')) for i in query]\n",
    "    header = ['Hero', 'Date', 'Matches',\n",
    "              'Win Rate', 'KDA Ratio', 'Core', 'Safe']\n",
    "    playeddb = pd.DataFrame(rows, columns=header)\n",
    "    playeddb.drop(columns=['Date', 'Core', 'Safe'], inplace=True)\n",
    "    playeddb['PID'] = p\n",
    "    playeddb.to_sql('Played', conn, if_exists='append')\n",
    "    sleep(2)\n",
    "driver.close()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Item Economy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-19T19:23:30.043524Z",
     "start_time": "2019-07-19T19:23:30.031372Z"
    }
   },
   "source": [
    "```python\n",
    "pids_itemeco = pickup_itemeco(pids)\n",
    "driver = webdriver.Chrome(chrome_path)\n",
    "for p in pids_itemeco:\n",
    "    driver.get('https://www.dotabuff.com/players/'+p+'/items?metric=economy')\n",
    "    query = driver.find_elements_by_xpath(\n",
    "        '/html/body/div[1]/div[8]/div[3]/section/article/table/tbody/*')\n",
    "    raw = [i.text.split('\\n') for i in query]\n",
    "    rows = [[(i, j, r[1]) for i, j in re.findall(\n",
    "        r'(.*)\\s(.*?\\d+)$', r[0])][0] for r in raw]\n",
    "    header = ['Item', 'GPM', 'XPM']\n",
    "    itemecodb = pd.DataFrame(rows, columns=header)\n",
    "    itemecodb['PID'] = p\n",
    "    itemecodb.to_sql('ItemEco', conn, if_exists='append')\n",
    "    sleep(2)\n",
    "driver.close()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Item Main"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-19T17:17:39.148355Z",
     "start_time": "2019-07-19T17:15:15.993556Z"
    }
   },
   "source": [
    "```python\n",
    "pids_itemmain = pickup_itemmain(pids)\n",
    "driver = webdriver.Chrome(chrome_path)\n",
    "for p in pids_itemmain:\n",
    "    driver.get('https://www.dotabuff.com/players/'+p+'/items?metric=used')\n",
    "    query = driver.find_elements_by_xpath(\n",
    "        '/html/body/div[1]/div[8]/div[3]/section/article/table/tbody/*')\n",
    "    raw = [i.text.split('\\n')[:3] for i in query]\n",
    "    rows = [[(re.sub(r'\\s\\(level \\d+\\)$', '', i), j, r[1], r[2])\n",
    "            for i, j in re.findall(r'(.*)\\s(.*?\\d+)$', r[0])][0] for r in raw]\n",
    "    header = ['Item', 'Matches', 'WinRate', 'KDA_Ratio']\n",
    "    itemmaindb = pd.DataFrame(rows, columns=header)\n",
    "    itemmaindb['PID'] = p\n",
    "    itemmaindb.to_sql('ItemMain', conn, if_exists='append')\n",
    "    sleep(2)\n",
    "driver.close()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning up data (removing commas and % and removed heroes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-19T17:31:06.295383Z",
     "start_time": "2019-07-19T17:31:05.453423Z"
    }
   },
   "outputs": [],
   "source": [
    "conn.executescript('''\n",
    "            UPDATE Impact\n",
    "            SET Kills = REPLACE(Kills, ',', ''),\n",
    "                Death = REPLACE(Death, ',', ''),\n",
    "                Assists = REPLACE(Assists, ',', '');\n",
    "                \n",
    "            DELETE FROM Impact\n",
    "            WHERE Hero LIKE \"Removed%\";\n",
    "                \n",
    "            UPDATE Economy\n",
    "            SET GPM = REPLACE(GPM, ',', ''),\n",
    "                XPM = REPLACE(XPM, ',', '');\n",
    "                \n",
    "            DELETE FROM Economy\n",
    "            WHERE Hero LIKE \"Removed%\";\n",
    "                \n",
    "            UPDATE Played\n",
    "            SET Matches = REPLACE(Matches, ',', ''),\n",
    "                \"Win Rate\" = REPLACE(\"Win Rate\", '%', '');\n",
    "                \n",
    "            DELETE FROM Played\n",
    "            WHERE Hero LIKE \"Removed%\";\n",
    "                \n",
    "            UPDATE ItemEco\n",
    "            SET GPM = REPLACE(GPM, ',', ''),\n",
    "                XPM = REPLACE(XPM, ',', '');\n",
    "                \n",
    "            DELETE FROM ItemEco\n",
    "            WHERE Item LIKE \"Removed%\" OR Item LIKE \"Recipe%\"\n",
    "            OR Item LIKE \"Refresher Shard%\" OR Item LIKE \"River Vial%\";\n",
    "            \n",
    "            UPDATE ItemMain\n",
    "            SET Matches = REPLACE(Matches, ',', ''),\n",
    "                WinRate = REPLACE(\"Win Rate\", '%', '');\n",
    "                \n",
    "            DELETE FROM ItemMain\n",
    "            WHERE Item LIKE \"Removed%\" OR Item LIKE \"Recipe%\"\n",
    "            OR Item LIKE \"Refresher Shard%\" OR Item LIKE \"River Vial%\";\n",
    "            ''')\n",
    "conn.commit();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ITAY Eto po yung tables to copy:\n",
    "## Pacheck na lang po conn names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T10:12:37.335496Z",
     "start_time": "2019-07-21T10:12:37.333334Z"
    },
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# pd.read_sql('''SELECT * FROM Impact''', conn).to_sql('impact', conn2)\n",
    "# pd.read_sql('''SELECT * FROM Economy''', conn).to_sql('economy', conn2)\n",
    "# pd.read_sql('''SELECT * FROM Played''', conn).to_sql('played', conn2)\n",
    "# pd.read_sql('''SELECT * FROM ItemEco''', conn).to_sql('itemeco', conn2)\n",
    "# pd.read_sql('''SELECT * FROM ItemMain''', conn).to_sql('itemmain', conn2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# TRY TO FIX DATAFRAMES PLS.\n",
    "\n",
    "## players"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T10:15:27.744741Z",
     "start_time": "2019-07-21T10:14:58.982289Z"
    }
   },
   "source": [
    "```python\n",
    "\n",
    "# load dataframes from database\n",
    "econ = pd.read_sql('''SELECT * FROM Economy''', conn)\n",
    "impact = pd.read_sql('''SELECT * FROM Impact''', conn)\n",
    "played = pd.read_sql('''SELECT * FROM Played''', conn)\n",
    "\n",
    "# convert dtypes\n",
    "impact['Hero'] = impact['Hero'].astype(str)\n",
    "impact['PID'] = impact['PID'].astype(int)\n",
    "econ['Hero'] = econ['Hero'].astype(str)\n",
    "econ['PID'] = econ['PID'].astype(int)\n",
    "played['Hero'] = played['Hero'].astype(str)\n",
    "played['PID'] = played['PID'].astype(int)\n",
    "\n",
    "# remove KDA Ratio\n",
    "impact = impact[[i for i in impact.columns if i != 'KDA Ratio']]\n",
    "\n",
    "# merge played and impact dataframes\n",
    "df_played = played.merge(impact, left_on=['PID', 'Hero'], right_on=['PID', 'Hero'])\n",
    "\n",
    "# clean up and merge econ with played dataframe\n",
    "econ = econ[['Hero', 'GPM', 'XPM', 'PID']]\n",
    "econ.drop_duplicates(inplace=True)\n",
    "\n",
    "df_played = df_played[['Hero', 'Matches', 'Win Rate', 'KDA Ratio', 'PID',\n",
    "       'Kills', 'Death', 'Assists']]\n",
    "\n",
    "df_total = df_played.merge(econ, left_on=['PID', 'Hero'], right_on=['PID', 'Hero'])\n",
    "\n",
    "# define function to create match probabilities\n",
    "def match_prob(row):\n",
    "    total = df_total[df_total['PID'] == row['PID']]['Matches'].sum()\n",
    "    return row['Matches'] / total\n",
    "\n",
    "# run match probability for df_total\n",
    "df_total['match_prob'] = df_total.apply(match_prob, axis=1)\n",
    "\n",
    "# multiply player stats by match probabilities\n",
    "convert_cols = ['Win Rate', 'KDA Ratio', 'Kills', 'Death', 'Assists', 'GPM', 'XPM']\n",
    "for i in convert_cols:\n",
    "    df_total[i] = df_total[i] * df_total['match_prob']\n",
    "\n",
    "# final dataframe\n",
    "df_final = df_total.groupby('PID')[['Win Rate', 'KDA Ratio', \n",
    "                        'Kills', 'Death', 'Assists', 'GPM', 'XPM']].sum().reset_index()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T10:15:27.767043Z",
     "start_time": "2019-07-21T10:15:27.746705Z"
    }
   },
   "outputs": [],
   "source": [
    "df_played = df_played.groupby('PID')[['Matches', 'Kills', 'Death', 'Assists']].sum().reset_index()\n",
    "df_played['ave_kills'] = df_played['Kills'] / df_played['Matches']\n",
    "df_played['ave_death'] = df_played['Death'] / df_played['Matches']\n",
    "df_played['ave_assist'] = df_played['Assists'] / df_played['Matches']\n",
    "df_played = df_played.merge(df_final[['PID', 'Win Rate', 'KDA Ratio', \n",
    "                                'GPM', 'XPM']], left_on='PID', right_on='PID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T10:15:27.788740Z",
     "start_time": "2019-07-21T10:15:27.768849Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_played = df_played.merge(pd.read_pickle('player_ranks.pkl'), \n",
    "                left_on='PID', right_on='players').drop('players', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T10:27:09.020931Z",
     "start_time": "2019-07-21T10:27:08.975941Z"
    }
   },
   "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>PID</th>\n",
       "      <th>Matches</th>\n",
       "      <th>Kills</th>\n",
       "      <th>Death</th>\n",
       "      <th>Assists</th>\n",
       "      <th>ave_kills</th>\n",
       "      <th>ave_death</th>\n",
       "      <th>ave_assist</th>\n",
       "      <th>Win Rate</th>\n",
       "      <th>KDA Ratio</th>\n",
       "      <th>GPM</th>\n",
       "      <th>XPM</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>145875</td>\n",
       "      <td>1371.0</td>\n",
       "      <td>12503.0</td>\n",
       "      <td>7464.0</td>\n",
       "      <td>13464.0</td>\n",
       "      <td>9.119621</td>\n",
       "      <td>5.444201</td>\n",
       "      <td>9.820569</td>\n",
       "      <td>52.078818</td>\n",
       "      <td>3.606054</td>\n",
       "      <td>413.835157</td>\n",
       "      <td>505.447848</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>226583</td>\n",
       "      <td>7408.0</td>\n",
       "      <td>79220.0</td>\n",
       "      <td>54016.0</td>\n",
       "      <td>92000.0</td>\n",
       "      <td>10.693844</td>\n",
       "      <td>7.291577</td>\n",
       "      <td>12.419006</td>\n",
       "      <td>52.897262</td>\n",
       "      <td>3.262638</td>\n",
       "      <td>494.109967</td>\n",
       "      <td>550.276544</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>407336</td>\n",
       "      <td>4611.0</td>\n",
       "      <td>43681.0</td>\n",
       "      <td>34540.0</td>\n",
       "      <td>54302.0</td>\n",
       "      <td>9.473216</td>\n",
       "      <td>7.490783</td>\n",
       "      <td>11.776621</td>\n",
       "      <td>50.672159</td>\n",
       "      <td>2.907051</td>\n",
       "      <td>489.833550</td>\n",
       "      <td>539.688151</td>\n",
       "      <td>236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>690740</td>\n",
       "      <td>4067.0</td>\n",
       "      <td>40505.0</td>\n",
       "      <td>27462.0</td>\n",
       "      <td>53424.0</td>\n",
       "      <td>9.959430</td>\n",
       "      <td>6.752397</td>\n",
       "      <td>13.135972</td>\n",
       "      <td>49.766988</td>\n",
       "      <td>3.532818</td>\n",
       "      <td>435.938038</td>\n",
       "      <td>497.392427</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3916428</td>\n",
       "      <td>3761.0</td>\n",
       "      <td>35483.0</td>\n",
       "      <td>28229.0</td>\n",
       "      <td>46245.0</td>\n",
       "      <td>9.434459</td>\n",
       "      <td>7.505717</td>\n",
       "      <td>12.295932</td>\n",
       "      <td>60.796590</td>\n",
       "      <td>2.956741</td>\n",
       "      <td>425.391772</td>\n",
       "      <td>519.358430</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       PID  Matches    Kills    Death  Assists  ave_kills  ave_death  \\\n",
       "0   145875   1371.0  12503.0   7464.0  13464.0   9.119621   5.444201   \n",
       "1   226583   7408.0  79220.0  54016.0  92000.0  10.693844   7.291577   \n",
       "2   407336   4611.0  43681.0  34540.0  54302.0   9.473216   7.490783   \n",
       "3   690740   4067.0  40505.0  27462.0  53424.0   9.959430   6.752397   \n",
       "4  3916428   3761.0  35483.0  28229.0  46245.0   9.434459   7.505717   \n",
       "\n",
       "   ave_assist   Win Rate  KDA Ratio         GPM         XPM  rank  \n",
       "0    9.820569  52.078818   3.606054  413.835157  505.447848   265  \n",
       "1   12.419006  52.897262   3.262638  494.109967  550.276544   290  \n",
       "2   11.776621  50.672159   2.907051  489.833550  539.688151   236  \n",
       "3   13.135972  49.766988   3.532818  435.938038  497.392427   233  \n",
       "4   12.295932  60.796590   2.956741  425.391772  519.358430    17  "
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_played.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T10:46:32.413508Z",
     "start_time": "2019-07-21T10:46:32.402481Z"
    }
   },
   "outputs": [],
   "source": [
    "# df_played.to_sql('player_summary', conn2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## heroes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T10:28:06.773032Z",
     "start_time": "2019-07-21T10:28:06.762406Z"
    }
   },
   "outputs": [],
   "source": [
    "conn2 = sqlite3.connect('final_dota.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T10:28:07.103030Z",
     "start_time": "2019-07-21T10:28:07.098260Z"
    }
   },
   "outputs": [],
   "source": [
    "def hero_matchprob(row):\n",
    "    total = heroes[heroes['PID'] == row['PID']]['Matches'].sum()\n",
    "    return row['Matches'] / total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T10:28:41.062380Z",
     "start_time": "2019-07-21T10:28:07.487512Z"
    }
   },
   "outputs": [],
   "source": [
    "hero_cats = pd.read_sql('''SELECT * FROM hero_cat''', conn2)\n",
    "heroes = played.merge(hero_cats, left_on='Hero', right_on='hero').drop(['index_x', \n",
    "                                                        'index_y'], axis=1)\n",
    "\n",
    "heroes['match_prob'] = heroes.apply(hero_matchprob, axis=1)\n",
    "\n",
    "for i in heroes.columns[6:-1]:\n",
    "    heroes[i] = heroes[i] * heroes['match_prob']\n",
    "    \n",
    "player_hero = heroes.pivot_table(index='PID', columns='Hero', values='match_prob').reset_index().fillna(0)\n",
    "player_herocats = heroes.groupby('PID')[heroes.columns[6:-1]].sum().reset_index().fillna(0)\n",
    "\n",
    "hero = player_hero.merge(player_herocats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T10:28:41.095610Z",
     "start_time": "2019-07-21T10:28:41.063971Z"
    }
   },
   "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>PID</th>\n",
       "      <th>Abaddon</th>\n",
       "      <th>Alchemist</th>\n",
       "      <th>Ancient Apparition</th>\n",
       "      <th>Anti-Mage</th>\n",
       "      <th>Arc Warden</th>\n",
       "      <th>Axe</th>\n",
       "      <th>Bane</th>\n",
       "      <th>Batrider</th>\n",
       "      <th>Beastmaster</th>\n",
       "      <th>...</th>\n",
       "      <th>Nukers-Pushers</th>\n",
       "      <th>Nukers-Ranged_heroes</th>\n",
       "      <th>Nukers-Strength_heroes</th>\n",
       "      <th>Nukers-Supports</th>\n",
       "      <th>Pushers-Ranged_heroes</th>\n",
       "      <th>Pushers-Strength_heroes</th>\n",
       "      <th>Pushers-Supports</th>\n",
       "      <th>Ranged_heroes-Strength_heroes</th>\n",
       "      <th>Ranged_heroes-Supports</th>\n",
       "      <th>Strength_heroes-Supports</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>145875</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.002188</td>\n",
       "      <td>0.002918</td>\n",
       "      <td>0.037199</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.007294</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.006565</td>\n",
       "      <td>0.004376</td>\n",
       "      <td>...</td>\n",
       "      <td>0.196207</td>\n",
       "      <td>0.450036</td>\n",
       "      <td>0.189643</td>\n",
       "      <td>0.209336</td>\n",
       "      <td>0.196937</td>\n",
       "      <td>0.049599</td>\n",
       "      <td>0.030635</td>\n",
       "      <td>0.013129</td>\n",
       "      <td>0.167761</td>\n",
       "      <td>0.059810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>226583</td>\n",
       "      <td>0.002438</td>\n",
       "      <td>0.009480</td>\n",
       "      <td>0.005688</td>\n",
       "      <td>0.013814</td>\n",
       "      <td>0.000271</td>\n",
       "      <td>0.009480</td>\n",
       "      <td>0.001761</td>\n",
       "      <td>0.005146</td>\n",
       "      <td>0.005688</td>\n",
       "      <td>...</td>\n",
       "      <td>0.269908</td>\n",
       "      <td>0.481853</td>\n",
       "      <td>0.194745</td>\n",
       "      <td>0.255282</td>\n",
       "      <td>0.262324</td>\n",
       "      <td>0.030200</td>\n",
       "      <td>0.068933</td>\n",
       "      <td>0.016928</td>\n",
       "      <td>0.212216</td>\n",
       "      <td>0.079496</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>407336</td>\n",
       "      <td>0.000868</td>\n",
       "      <td>0.015625</td>\n",
       "      <td>0.004557</td>\n",
       "      <td>0.006076</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.005208</td>\n",
       "      <td>0.002821</td>\n",
       "      <td>0.004991</td>\n",
       "      <td>0.004123</td>\n",
       "      <td>...</td>\n",
       "      <td>0.241319</td>\n",
       "      <td>0.560113</td>\n",
       "      <td>0.206597</td>\n",
       "      <td>0.334418</td>\n",
       "      <td>0.214627</td>\n",
       "      <td>0.052951</td>\n",
       "      <td>0.083116</td>\n",
       "      <td>0.003472</td>\n",
       "      <td>0.276259</td>\n",
       "      <td>0.070530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>690740</td>\n",
       "      <td>0.011065</td>\n",
       "      <td>0.012786</td>\n",
       "      <td>0.002705</td>\n",
       "      <td>0.012294</td>\n",
       "      <td>0.000492</td>\n",
       "      <td>0.029260</td>\n",
       "      <td>0.001721</td>\n",
       "      <td>0.003442</td>\n",
       "      <td>0.001967</td>\n",
       "      <td>...</td>\n",
       "      <td>0.103024</td>\n",
       "      <td>0.209737</td>\n",
       "      <td>0.366609</td>\n",
       "      <td>0.167691</td>\n",
       "      <td>0.043767</td>\n",
       "      <td>0.069830</td>\n",
       "      <td>0.009589</td>\n",
       "      <td>0.019179</td>\n",
       "      <td>0.095648</td>\n",
       "      <td>0.116548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3916428</td>\n",
       "      <td>0.004840</td>\n",
       "      <td>0.008067</td>\n",
       "      <td>0.008336</td>\n",
       "      <td>0.011293</td>\n",
       "      <td>0.000538</td>\n",
       "      <td>0.009680</td>\n",
       "      <td>0.005378</td>\n",
       "      <td>0.011024</td>\n",
       "      <td>0.009680</td>\n",
       "      <td>...</td>\n",
       "      <td>0.177467</td>\n",
       "      <td>0.434794</td>\n",
       "      <td>0.251681</td>\n",
       "      <td>0.321592</td>\n",
       "      <td>0.208927</td>\n",
       "      <td>0.031460</td>\n",
       "      <td>0.085776</td>\n",
       "      <td>0.041678</td>\n",
       "      <td>0.285292</td>\n",
       "      <td>0.123958</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 222 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       PID   Abaddon  Alchemist  Ancient Apparition  Anti-Mage  Arc Warden  \\\n",
       "0   145875  0.000000   0.002188            0.002918   0.037199    0.000000   \n",
       "1   226583  0.002438   0.009480            0.005688   0.013814    0.000271   \n",
       "2   407336  0.000868   0.015625            0.004557   0.006076    0.000000   \n",
       "3   690740  0.011065   0.012786            0.002705   0.012294    0.000492   \n",
       "4  3916428  0.004840   0.008067            0.008336   0.011293    0.000538   \n",
       "\n",
       "        Axe      Bane  Batrider  Beastmaster  ...  Nukers-Pushers  \\\n",
       "0  0.007294  0.000000  0.006565     0.004376  ...        0.196207   \n",
       "1  0.009480  0.001761  0.005146     0.005688  ...        0.269908   \n",
       "2  0.005208  0.002821  0.004991     0.004123  ...        0.241319   \n",
       "3  0.029260  0.001721  0.003442     0.001967  ...        0.103024   \n",
       "4  0.009680  0.005378  0.011024     0.009680  ...        0.177467   \n",
       "\n",
       "   Nukers-Ranged_heroes  Nukers-Strength_heroes  Nukers-Supports  \\\n",
       "0              0.450036                0.189643         0.209336   \n",
       "1              0.481853                0.194745         0.255282   \n",
       "2              0.560113                0.206597         0.334418   \n",
       "3              0.209737                0.366609         0.167691   \n",
       "4              0.434794                0.251681         0.321592   \n",
       "\n",
       "   Pushers-Ranged_heroes  Pushers-Strength_heroes  Pushers-Supports  \\\n",
       "0               0.196937                 0.049599          0.030635   \n",
       "1               0.262324                 0.030200          0.068933   \n",
       "2               0.214627                 0.052951          0.083116   \n",
       "3               0.043767                 0.069830          0.009589   \n",
       "4               0.208927                 0.031460          0.085776   \n",
       "\n",
       "   Ranged_heroes-Strength_heroes  Ranged_heroes-Supports  \\\n",
       "0                       0.013129                0.167761   \n",
       "1                       0.016928                0.212216   \n",
       "2                       0.003472                0.276259   \n",
       "3                       0.019179                0.095648   \n",
       "4                       0.041678                0.285292   \n",
       "\n",
       "   Strength_heroes-Supports  \n",
       "0                  0.059810  \n",
       "1                  0.079496  \n",
       "2                  0.070530  \n",
       "3                  0.116548  \n",
       "4                  0.123958  \n",
       "\n",
       "[5 rows x 222 columns]"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hero.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T10:46:16.565497Z",
     "start_time": "2019-07-21T10:46:16.479288Z"
    }
   },
   "outputs": [],
   "source": [
    "# hero.to_sql('hero_summary', conn2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "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": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}