data_wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Setup Environment"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "from pyspark.sql import SparkSession\n",
    "import pyspark.sql.functions as F\n",
    "\n",
    "os.environ['PYSPARK_PYTHON'] = sys.executable\n",
    "os.environ['PYSPARK_DRIVER_PYTHON'] = sys.executable"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [],
   "source": [
    "def init_spark():\n",
    "    return SparkSession \\\n",
    "        .builder \\\n",
    "        .master(\"local[4]\") \\\n",
    "        .appName(\"Chess Preprocess\") \\\n",
    "        .config(\"spark.executor.instances\", 4) \\\n",
    "        .config(\"spark.executor.cores\", 4) \\\n",
    "        .config(\"spark.some.config.option\", \"some-value\") \\\n",
    "        .getOrCreate()\n",
    "\n",
    "spark = init_spark()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [],
   "source": [
    "seed = 42"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Load Dataset"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [],
   "source": [
    "df = spark.read.csv('data/games.csv', header=True, inferSchema=True)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Data Inspection"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "data": {
      "text/plain": "         id  rated    created_at  last_move_at  turns victory_status winner  \\\n0  TZJHLljE  False  1.504210e+12  1.504210e+12     13      outoftime  white   \n1  l1NXvwaE   True  1.504130e+12  1.504130e+12     16         resign  black   \n2  mIICvQHh   True  1.504130e+12  1.504130e+12     61           mate  white   \n3  kWKvrqYL   True  1.504110e+12  1.504110e+12     61           mate  white   \n4  9tXo1AUZ   True  1.504030e+12  1.504030e+12     95           mate  white   \n\n  increment_code       white_id  white_rating      black_id  black_rating  \\\n0           15+2       bourgris          1500          a-00          1191   \n1           5+10           a-00          1322     skinnerua          1261   \n2           5+10         ischia          1496          a-00          1500   \n3           20+0  daniamurashov          1439  adivanov2009          1454   \n4           30+3      nik221107          1523  adivanov2009          1469   \n\n                                               moves opening_eco  \\\n0  d4 d5 c4 c6 cxd5 e6 dxe6 fxe6 Nf3 Bb4+ Nc3 Ba5...         D10   \n1  d4 Nc6 e4 e5 f4 f6 dxe5 fxe5 fxe5 Nxe5 Qd4 Nc6...         B00   \n2  e4 e5 d3 d6 Be3 c6 Be2 b5 Nd2 a5 a4 c5 axb5 Nc...         C20   \n3  d4 d5 Nf3 Bf5 Nc3 Nf6 Bf4 Ng4 e3 Nc6 Be2 Qd7 O...         D02   \n4  e4 e5 Nf3 d6 d4 Nc6 d5 Nb4 a3 Na6 Nc3 Be7 b4 N...         C41   \n\n                             opening_name  opening_ply  \n0        Slav Defense: Exchange Variation            5  \n1  Nimzowitsch Defense: Kennedy Variation            4  \n2   King's Pawn Game: Leonardis Variation            3  \n3  Queen's Pawn Game: Zukertort Variation            3  \n4                        Philidor Defense            5  ",
      "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>id</th>\n      <th>rated</th>\n      <th>created_at</th>\n      <th>last_move_at</th>\n      <th>turns</th>\n      <th>victory_status</th>\n      <th>winner</th>\n      <th>increment_code</th>\n      <th>white_id</th>\n      <th>white_rating</th>\n      <th>black_id</th>\n      <th>black_rating</th>\n      <th>moves</th>\n      <th>opening_eco</th>\n      <th>opening_name</th>\n      <th>opening_ply</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>TZJHLljE</td>\n      <td>False</td>\n      <td>1.504210e+12</td>\n      <td>1.504210e+12</td>\n      <td>13</td>\n      <td>outoftime</td>\n      <td>white</td>\n      <td>15+2</td>\n      <td>bourgris</td>\n      <td>1500</td>\n      <td>a-00</td>\n      <td>1191</td>\n      <td>d4 d5 c4 c6 cxd5 e6 dxe6 fxe6 Nf3 Bb4+ Nc3 Ba5...</td>\n      <td>D10</td>\n      <td>Slav Defense: Exchange Variation</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>l1NXvwaE</td>\n      <td>True</td>\n      <td>1.504130e+12</td>\n      <td>1.504130e+12</td>\n      <td>16</td>\n      <td>resign</td>\n      <td>black</td>\n      <td>5+10</td>\n      <td>a-00</td>\n      <td>1322</td>\n      <td>skinnerua</td>\n      <td>1261</td>\n      <td>d4 Nc6 e4 e5 f4 f6 dxe5 fxe5 fxe5 Nxe5 Qd4 Nc6...</td>\n      <td>B00</td>\n      <td>Nimzowitsch Defense: Kennedy Variation</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>mIICvQHh</td>\n      <td>True</td>\n      <td>1.504130e+12</td>\n      <td>1.504130e+12</td>\n      <td>61</td>\n      <td>mate</td>\n      <td>white</td>\n      <td>5+10</td>\n      <td>ischia</td>\n      <td>1496</td>\n      <td>a-00</td>\n      <td>1500</td>\n      <td>e4 e5 d3 d6 Be3 c6 Be2 b5 Nd2 a5 a4 c5 axb5 Nc...</td>\n      <td>C20</td>\n      <td>King's Pawn Game: Leonardis Variation</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>kWKvrqYL</td>\n      <td>True</td>\n      <td>1.504110e+12</td>\n      <td>1.504110e+12</td>\n      <td>61</td>\n      <td>mate</td>\n      <td>white</td>\n      <td>20+0</td>\n      <td>daniamurashov</td>\n      <td>1439</td>\n      <td>adivanov2009</td>\n      <td>1454</td>\n      <td>d4 d5 Nf3 Bf5 Nc3 Nf6 Bf4 Ng4 e3 Nc6 Be2 Qd7 O...</td>\n      <td>D02</td>\n      <td>Queen's Pawn Game: Zukertort Variation</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>9tXo1AUZ</td>\n      <td>True</td>\n      <td>1.504030e+12</td>\n      <td>1.504030e+12</td>\n      <td>95</td>\n      <td>mate</td>\n      <td>white</td>\n      <td>30+3</td>\n      <td>nik221107</td>\n      <td>1523</td>\n      <td>adivanov2009</td>\n      <td>1469</td>\n      <td>e4 e5 Nf3 d6 d4 Nc6 d5 Nb4 a3 Na6 Nc3 Be7 b4 N...</td>\n      <td>C41</td>\n      <td>Philidor Defense</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.toPandas().head()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Inspect Datatypes"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "[('id', 'string'),\n ('rated', 'boolean'),\n ('created_at', 'double'),\n ('last_move_at', 'double'),\n ('turns', 'int'),\n ('victory_status', 'string'),\n ('winner', 'string'),\n ('increment_code', 'string'),\n ('white_id', 'string'),\n ('white_rating', 'int'),\n ('black_id', 'string'),\n ('black_rating', 'int'),\n ('moves', 'string'),\n ('opening_eco', 'string'),\n ('opening_name', 'string'),\n ('opening_ply', 'int')]"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [],
   "source": [
    "categorical = [feature for feature, dtype in df.dtypes if dtype in {'string', 'boolean'}]\n",
    "numerical = [feature for feature, dtype in df.dtypes if dtype in {'double', 'int'}]"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Numerical Features"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "created_at          :13151 unique values\n",
      "last_move_at        :13186 unique values\n",
      "turns               :211 unique values\n",
      "white_rating        :1516 unique values\n",
      "black_rating        :1521 unique values\n",
      "opening_ply         :[28, 12, 1, 13, 6, 16, 3, 20, 5, 19, 15, 9, 17, 4, 8, 7, 10, 11, 14, 2, 18, 22, 24]\n"
     ]
    }
   ],
   "source": [
    "for col in numerical:\n",
    "    unique_values = df.select(col).distinct()\n",
    "    n_unique = unique_values.count()\n",
    "    if n_unique < 50:\n",
    "        print(f'{col:20s}:{[row[col] for row in unique_values.collect()]}')\n",
    "    else:\n",
    "        print(f'{col:20s}:{n_unique} unique values')"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "- `created_at`: Timestamp in UTC\n",
    "- `last_move_at`: Timestamp in UTC\n",
    "- `turns`: Number of turns in the match\n",
    "- `white_rating`: white player rating\n",
    "- `black_rating`: black player rating\n",
    "- `opening_ply`: Number of plies used to set up opening"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Categorical Features"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id                  :19113 unique values\n",
      "rated               :[True, False]\n",
      "victory_status      :['resign', 'outoftime', 'mate', 'draw']\n",
      "winner              :['white', 'black', 'draw']\n",
      "increment_code      :400 unique values\n",
      "white_id            :9438 unique values\n",
      "black_id            :9331 unique values\n",
      "moves               :18920 unique values\n",
      "opening_eco         :365 unique values\n",
      "opening_name        :1477 unique values\n"
     ]
    }
   ],
   "source": [
    "for col in categorical:\n",
    "    unique_values = df.select(col).distinct()\n",
    "    n_unique = unique_values.count()\n",
    "    if n_unique < 50:\n",
    "        print(f'{col:20s}:{[row[col] for row in unique_values.collect()]}')\n",
    "    else:\n",
    "        print(f'{col:20s}:{n_unique} unique values')"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "- `id`: Game ID, uniquely identifies a match record\n",
    "- `rated`: If rated, the game result affects player ratings\n",
    "- `victory_status`: How the game ended\n",
    "- `winner`: Match winner\n",
    "- `increment_code`: Game time setting\n",
    "- `white_id`: white player id\n",
    "- `black_id`: black player id\n",
    "- `moves`: Sequence of moves recorded during the match\n",
    "- `opening_eco`: ECO classification code for the chess openings moves\n",
    "- `opening_name`: Name of opening moves"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Overview of the Target Columns"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-----+\n",
      "|winner|count|\n",
      "+------+-----+\n",
      "| white|10001|\n",
      "| black| 9107|\n",
      "|  draw|  950|\n",
      "+------+-----+\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": "<AxesSubplot:xlabel='winner'>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": "<Figure size 432x288 with 1 Axes>",
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYQAAAEYCAYAAABcGYHrAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/YYfK9AAAACXBIWXMAAAsTAAALEwEAmpwYAAAWjklEQVR4nO3df5RfdX3n8efLhBLkRwUSacywTbQpSlwQGVNWbQXTs8RT27BH2I0/ICrHrCxK3XaPC/UPz56z7LbrLtq0yi5VJChbmlJXaFdW3RAXxax0IlYMkZKVX7OwkEJro5Yfgff+8f0Ev52MyUwmzP3OzPNxzvd87/3c+7nf9zd3Jq+5v1NVSJL0gq4LkCQNBgNBkgQYCJKkxkCQJAEGgiSpMRAkSQDM77qAg7Vw4cJaunRp12VI0oyybdu2v6qqReNNm7GBsHTpUkZGRrouQ5JmlCT3/6Rp7jKSJAEGgiSpMRAkScAMPoYgSQfy9NNPMzo6yhNPPNF1KdNuwYIFDA0Ncdhhh024j4EgadYaHR3l6KOPZunSpSTpupxpU1U89thjjI6OsmzZsgn3O+AuoyRXJ3k0yXf62o5L8uUk97T3Y/umXZZkZ5K7k5zd1356kjvbtA1payfJ4Un+qLV/I8nSCVcvSfvxxBNPcPzxx8+pMABIwvHHHz/pLaOJHEO4Blg9pu1SYHNVLQc2t3GSnAysBVa0Pp9IMq/1uRJYDyxvr73LvBD466r6OeCjwO9M6htI0n7MtTDY62C+9wEDoapuBR4f07wG2NiGNwLn9LVfX1VPVtW9wE5gZZLFwDFVtbV6D2C4dkyfvcu6AViVuboGJWkSPvaxj/GjH/3okC3vYM8yOqGqHgZo7y9u7UuAB/vmG21tS9rw2Pa/16eq9gDfB44f70OTrE8ykmRk165dB1n6wUkyq1/SXDDbfm8GJRB+kvH+hWo/7fvrs29j1VVVNVxVw4sWjXvltSQNlGuvvZZTTjmFU089lfPPP5/777+fVatWccopp7Bq1SoeeOABAN75zndyww03PNfvqKOOAuArX/kKZ555Jueeey4vf/nLefvb305VsWHDBh566CHOOusszjrrrENS68GeZfRIksVV9XDbHfRoax8FTuybbwh4qLUPjdPe32c0yXzgp9l3F5UkzTjbt2/n8ssv57bbbmPhwoU8/vjjrFu3jgsuuIB169Zx9dVXc8kll/D5z39+v8u544472L59Oy95yUt43etex2233cYll1zCFVdcwZYtW1i4cOEhqfdgtxBuAta14XXAjX3ta9uZQ8voHTy+ve1W2p3kjHZ84IIxffYu61zglvJBz5JmgVtuuYVzzz33uf+wjzvuOLZu3crb3vY2AM4//3y+9rWvHXA5K1euZGhoiBe84AW86lWv4r777nte6j3gFkKSPwTOBBYmGQU+DPw2sCnJhcADwHkAVbU9ySbgLmAPcHFVPdMWdRG9M5aOAG5uL4BPAZ9JspPelsHaQ/LNJKljVXXAYw17p8+fP59nn332uX5PPfXUc/Mcfvjhzw3PmzePPXv2PA/VTuwso7dW1eKqOqyqhqrqU1X1WFWtqqrl7f3xvvkvr6qXVdVJVXVzX/tIVb2yTXvf3q2Aqnqiqs6rqp+rqpVV9b3n5ZtK0jRbtWoVmzZt4rHHHgPg8ccf57WvfS3XX389ANdddx2vf/3rgd4dnLdt2wbAjTfeyNNPP33A5R999NHs3r37kNXrlcqS9DxZsWIFH/rQh3jDG97AvHnzOO2009iwYQPvfve7+chHPsKiRYv49Kc/DcB73vMe1qxZw8qVK1m1ahVHHnnkAZe/fv163vSmN7F48WK2bNky5XozU3fXDw8P13Q+D2EQTjF7Ps3UnwNpf3bs2MErXvGKrsvozHjfP8m2qhoeb37vdipJAgwESVJjIEiSAANB0iw3V4+PHcz3NhAkzVoLFizgsccem3OhsPd5CAsWLJhUP087lTRrDQ0NMTo6ynTfDHMQ7H1i2mQYCJJmrcMOO2xSTwyb69xlJEkCDARJUmMgSJIAA0GS1HhQWXPCbL4X1Vw7pVLPH7cQJEmAgSBJagwESRJgIEiSGgNBkgQYCJKkxkCQJAEGgiSpMRAkSYCBIElqDARJEmAgSJIaA0GSBBgIkqTGQJAkAQaCJKkxECRJgIEgSWqmFAhJ/mWS7Um+k+QPkyxIclySLye5p70f2zf/ZUl2Jrk7ydl97acnubNN25DZ/LxDSRpQBx0ISZYAlwDDVfVKYB6wFrgU2FxVy4HNbZwkJ7fpK4DVwCeSzGuLuxJYDyxvr9UHW5ck6eBMdZfRfOCIJPOBFwIPAWuAjW36RuCcNrwGuL6qnqyqe4GdwMoki4Fjqmpr9Z4Wfm1fH0nSNDnoQKiq/wv8R+AB4GHg+1X1JeCEqnq4zfMw8OLWZQnwYN8iRlvbkjY8tl2SNI2mssvoWHp/9S8DXgIcmeQd++syTlvtp328z1yfZCTJyK5duyZbsiRpP6ayy+iXgXuraldVPQ18Dngt8EjbDUR7f7TNPwqc2Nd/iN4uptE2PLZ9H1V1VVUNV9XwokWLplC6JGmsqQTCA8AZSV7YzgpaBewAbgLWtXnWATe24ZuAtUkOT7KM3sHj29tupd1JzmjLuaCvjyRpmsw/2I5V9Y0kNwDfBPYAdwBXAUcBm5JcSC80zmvzb0+yCbirzX9xVT3TFncRcA1wBHBze0mSplF6J/bMPMPDwzUyMjJtnzfbL42YqT8HEzWb199sX3c6tJJsq6rh8aZ5pbIkCTAQJEmNgSBJAgwESVJjIEiSAANBktQYCJIkwECQJDUGgiQJMBAkSY2BIEkCDARJUmMgSJIAA0GS1BgIkiTAQJAkNQaCJAkwECRJjYEgSQIMBElSYyBIkgADQZLUGAiSJMBAkCQ1BoIkCTAQJEmNgSBJAgwESVJjIEiSAANBktQYCJIkwECQJDVTCoQkL0pyQ5LvJtmR5B8lOS7Jl5Pc096P7Zv/siQ7k9yd5Oy+9tOT3NmmbUiSqdQlSZq8qW4h/C7wP6rq5cCpwA7gUmBzVS0HNrdxkpwMrAVWAKuBTySZ15ZzJbAeWN5eq6dYlyRpkg46EJIcA/wS8CmAqnqqqv4GWANsbLNtBM5pw2uA66vqyaq6F9gJrEyyGDimqrZWVQHX9vWRJE2TqWwhvBTYBXw6yR1JPpnkSOCEqnoYoL2/uM2/BHiwr/9oa1vShse2S5Km0VQCYT7wauDKqjoN+CFt99BPMN5xgdpP+74LSNYnGUkysmvXrsnWK0naj6kEwigwWlXfaOM30AuIR9puINr7o33zn9jXfwh4qLUPjdO+j6q6qqqGq2p40aJFUyhdkjTWQQdCVf0/4MEkJ7WmVcBdwE3Auta2DrixDd8ErE1yeJJl9A4e3952K+1OckY7u+iCvj6SpGkyf4r93w9cl+SngO8B76IXMpuSXAg8AJwHUFXbk2yiFxp7gIur6pm2nIuAa4AjgJvbS5I0jdI7sWfmGR4erpGRkWn7vNl+acRM/TmYqNm8/mb7utOhlWRbVQ2PN80rlSVJgIEgSWoMBEkSYCBIkhoDQZIEGAiSpMZAkCQBBoIkqTEQJEmAgSBJagwESRJgIEiSGgNBkgQYCJKkxkCQJAEGgiSpMRAkSYCBIElqDARJEmAgSJIaA0GSBBgIkqTGQJAkAQaCJKkxECRJgIEgSWoMBEkSYCBIkhoDQZIEGAiSpMZAkCQBBoIkqTEQJEnAIQiEJPOS3JHkz9r4cUm+nOSe9n5s37yXJdmZ5O4kZ/e1n57kzjZtQ5JMtS5J0uQcii2EXwd29I1fCmyuquXA5jZOkpOBtcAKYDXwiSTzWp8rgfXA8vZafQjqkiRNwpQCIckQ8CvAJ/ua1wAb2/BG4Jy+9uur6smquhfYCaxMshg4pqq2VlUB1/b1kSRNk6luIXwM+CDwbF/bCVX1MEB7f3FrXwI82DffaGtb0obHtkuSptFBB0KSNwOPVtW2iXYZp6320z7eZ65PMpJkZNeuXRP8WEnSRExlC+F1wK8luQ+4Hnhjks8Cj7TdQLT3R9v8o8CJff2HgIda+9A47fuoqquqariqhhctWjSF0iVJYx10IFTVZVU1VFVL6R0svqWq3gHcBKxrs60DbmzDNwFrkxyeZBm9g8e3t91Ku5Oc0c4uuqCvjyRpmsx/Hpb528CmJBcCDwDnAVTV9iSbgLuAPcDFVfVM63MRcA1wBHBze0mSplF6J/bMPMPDwzUyMjJtnzfbL42YqT8HEzWb199sX3c6tJJsq6rh8aZ5pbIkCTAQJEmNgSBJAgwESVJjIEiSAANBktQYCJIkwECQJDUGgiQJMBAkSY2BIEkCDARJUmMgSJIAA0GS1BgIkiTAQJAkNQaCJAkwECRJjYEgSQIMBElSYyBIkgADQZLUGAiSJMBAkCQ1BoIkCTAQJEmNgSBJAgwESVJjIEiSAANBktQYCJIkwECQJDUHHQhJTkyyJcmOJNuT/HprPy7Jl5Pc096P7etzWZKdSe5OcnZf++lJ7mzTNiTJ1L6WJGmyprKFsAf4zap6BXAGcHGSk4FLgc1VtRzY3MZp09YCK4DVwCeSzGvLuhJYDyxvr9VTqEuSdBAOOhCq6uGq+mYb3g3sAJYAa4CNbbaNwDlteA1wfVU9WVX3AjuBlUkWA8dU1daqKuDavj6SpGlySI4hJFkKnAZ8Azihqh6GXmgAL26zLQEe7Os22tqWtOGx7ZKkaTTlQEhyFPAnwAeq6m/3N+s4bbWf9vE+a32SkSQju3btmnyxkqSfaEqBkOQwemFwXVV9rjU/0nYD0d4fbe2jwIl93YeAh1r70Djt+6iqq6pquKqGFy1aNJXSJUljTOUsowCfAnZU1RV9k24C1rXhdcCNfe1rkxyeZBm9g8e3t91Ku5Oc0ZZ5QV8fSdI0mT+Fvq8DzgfuTPKt1vZbwG8Dm5JcCDwAnAdQVduTbALuoneG0sVV9UzrdxFwDXAEcHN7SZKmUXon9sw8w8PDNTIyMm2fN9svjZipPwcTNZvX32xfdzq0kmyrquHxpnmlsiQJMBAkSY2BIEkCDARJUmMgSJIAA0GS1BgIkiTAQJAkNQaCJAkwECRJjYEgSQIMBElSYyBIkgADQZLUGAiSJMBAkCQ1BoIkCZjaIzQl6Xk3m592B4P1xDu3ECRJgIEgSWoMBEkSYCBIkhoDQZIEGAiSpMZAkCQBBoIkqTEQJEmAgSBJagwESRJgIEiSGgNBkgQYCJKkxkCQJAEDFAhJVie5O8nOJJd2XY8kzTUDEQhJ5gEfB94EnAy8NcnJ3VYlSXPLQAQCsBLYWVXfq6qngOuBNR3XJElzyqA8QnMJ8GDf+CjwC2NnSrIeWN9Gf5Dk7mmorSsLgb+arg+b7Y8pnGauu5lttq+/n/1JEwYlEMb7F9nnQaNVdRVw1fNfTveSjFTVcNd1aPJcdzPbXF5/g7LLaBQ4sW98CHioo1okaU4alED4c2B5kmVJfgpYC9zUcU2SNKcMxC6jqtqT5H3AF4F5wNVVtb3jsro2J3aNzVKuu5ltzq6/VO2zq16SNAcNyi4jSVLHDARJEmAgSJIaA0E6BJIcN07bsi5q0eQleWOSF3ZdR9cMhAGR5IQkn0pycxs/OcmFXdelCfvTJMfsHWn34vrTDuvR5LwT+FaSrUn+Q5JfTXJs10VNNwNhcFxD77Tbl7TxvwQ+0FUxmrR/Ry8UjkpyOvDHwDs6rkkTVFUXVNXPA2+hd6Hsx4Fd3VY1/QbiOgQBsLCqNiW5DJ67NuOZrovSxFTVf09yGPAl4GjgnKq6p+OyNEFJ3gH8IvAP6d3H6PeBr3ZaVAcMhMHxwyTH0+7hlOQM4PvdlqQDSfJ7/P37bh0DfA94fxKq6pJuKtMkfQz4P8B/BrZU1X2dVtMRA2Fw/Aa923W8LMltwCLgvG5L0gSMjBnf1kkVmpKqWphkBfBLwOVJlgN3V9X5HZc2rQyEwbEdeANwEr27v96Nx3gGXlVtBEhyJPBEVT3TxucBh3dZmyaunRDwD+jdGnop8NPAs13W1AVvXTEgknyzql59oDYNpiT/G/jlqvpBGz8K+FJVvbbbyjQRSb4NfK29bq2q0Y5L6oRbCB1L8jP0HhB0RJLT+PGzIY4B5vx50TPIgr1hAFBVP/C89pmjqk7puoZBYCB072x650APAVf0te8GfquLgnRQfpjk1VX1TYB26unfdVyTJijJIuCDwApgwd72qnpjZ0V1wF1GAyLJW6rqT7quQwcnyWvoPQt874OdFgP/rKo8yDwDJPkS8EfAvwLeC6wDdlXVv+60sGlmIHQsyTuq6rNJfpPxHxt6xTjdNIDadQh7Twr4blU93XFJmqAk26rq9CTf3rv7KMn/qqo3dF3bdHKXUfeObO9HdVqFDoWTgJPp7XI4rV2HcG3HNWli9ob3w0l+hd6W3lCH9XTCLQTpEEjyYeBMeoHwBeBNwNeq6twu69LEJHkzvSuTTwR+j95JHf+mqubUo3zdQhgQ7aDWe+idA/3ceqmqd3dVkyblXOBU4I6qeleSE4BPdlyTJqBdM7K8qv6M3t0Bzuq4pM4YCIPjRnp/ofxPwHsYzTx/V1XPJtnTLnJ6FHhp10XpwKrqmSS/Bny061q6ZiAMjhfOtTMaZpmRJC8C/oDe7St+ANzeaUWajK8n+X16Zxr9cG/j3tOI5wqPIQyIJP8W+HpVfaHrWjQ1SZYCx1TVt7uuRROTZEsb3PsfYoDyOgRNqyS7+fEP4VHAk8CeNl5Vdcy4HTUQkuz31iJz7S/MmSbJb+wdpPd7mL7JNddO+3aXUceq6miAJJ+hdwzhq1W1o9uqNAn/qW+4/6+rvf/BzKm/MGego9v7ScBr6B3LC/CrwK1dFdUVtxAGRJI3Aq+n95COlwJ30AuH3+20ME1IkiOAf0FvHRa9cL+yqp7otDBNSLtS+S1VtbuNHw38cVWt7ray6WUgDJB2+ttr6J329l56Z668vNuqNBFJNgF/C1zXmt4KvKiq/ml3VWmiknwXOLWqnmzjhwN/Mdd+/9xlNCCSbKZ31fJWen9dvqaqHu22Kk3CSVV1at/4liR/0Vk1mqzPALcn+W/0tvD+CbCx25Kmnw9gGRzfBp4CXgmcAryy7YbQzHBHe+wpAEl+Abitw3o0CVV1OfAu4K+BvwHeVVX/vtOiOuAuowHTHqzyLnp3XfyZqvKpWwMsyZ30/qLce2O7B9r4zwJ3VdUrOyxPmhR3GQ2IJO+jd0D5dOB+4Gp6u4402N7cdQHSoWIgDI4j6D0gZ1tV7TnQzBoMVXV/1zVIh4q7jCRJgAeVJUmNgSBJAgwEadKSfKHd2VSaVTyGIA2YJPOqymdiaNq5hSCNkeSDSS5pwx9NcksbXpXks0nuS7IwydIkO5L8QZLtSb6092LCJF9J8jtJbk/yl0l+sbXPS/KRJH+e5NtJ/nlrPzPJliT/Fbizo6+uOc5AkPZ1K71rQgCGgaOSHEbvxnVjrw1ZDny8qlbQu8L1LX3T5lfVSuADwIdb24XA96vqNfTuW/WeJMvatJXAh6rq5EP7daSJMRCkfW0DTm93vHyS3v2lhumFxNhAuLeqvtXXb2nftM+N0/6PgQuSfAv4BnA8vVABuL2q7j1UX0KaLC9Mk8aoqqeT3EfvFiJfp3efqbOAlwFjn1XxZN/wM/QuMBw77Rl+/LsW4P1V9cX+hSQ5k75HN0pdcAtBGt+t9O4ndSu9rYL3At+qqZ+F8UXgorYLiiQ/n+TIKS5TOiQMBGl8XwUWA1ur6hHgCQ7NvaU+CdwFfDPJd4D/glvqGhCedipJAtxCkCQ1BoIkCTAQJEmNgSBJAgwESVJjIEiSAANBktQYCJIkAP4/EINp4vEaw88AAAAASUVORK5CYII=\n"
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df.groupBy('winner').count().show()\n",
    "df.groupBy('winner').count().toPandas().plot(kind='bar', x='winner', cmap='gray')"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [
    {
     "data": {
      "text/plain": "  summary        black_rating        white_rating         rating_diff\n0   count               20058               20058               20058\n1    mean  1588.8319872370128  1596.6318675840064  -7.799880346993718\n2  stddev   291.0361259603342  291.25337573701825  249.03666666007965\n3     min                 789                 784               -1499\n4     max                2723                2700                1605",
      "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>summary</th>\n      <th>black_rating</th>\n      <th>white_rating</th>\n      <th>rating_diff</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>count</td>\n      <td>20058</td>\n      <td>20058</td>\n      <td>20058</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>mean</td>\n      <td>1588.8319872370128</td>\n      <td>1596.6318675840064</td>\n      <td>-7.799880346993718</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>stddev</td>\n      <td>291.0361259603342</td>\n      <td>291.25337573701825</td>\n      <td>249.03666666007965</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>min</td>\n      <td>789</td>\n      <td>784</td>\n      <td>-1499</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>max</td>\n      <td>2723</td>\n      <td>2700</td>\n      <td>1605</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.withColumn('rating_diff', df.black_rating - df.white_rating).describe('black_rating', 'white_rating', 'rating_diff').toPandas()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Look for Anomalies"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "unique samples / total samples: 19629 / 20058 \n"
     ]
    }
   ],
   "source": [
    "print( f'unique samples / total samples: {df.distinct().count()} / {df.count()} ' )"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Count Null Values in each Column\n"
     ]
    },
    {
     "data": {
      "text/plain": "   id  rated  created_at  last_move_at  turns  victory_status  winner  \\\n0   0      0           0             0      0               0       0   \n\n   increment_code  white_id  white_rating  black_id  black_rating  moves  \\\n0               0         0             0         0             0      0   \n\n   opening_eco  opening_name  opening_ply  \n0            0             0            0  ",
      "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>id</th>\n      <th>rated</th>\n      <th>created_at</th>\n      <th>last_move_at</th>\n      <th>turns</th>\n      <th>victory_status</th>\n      <th>winner</th>\n      <th>increment_code</th>\n      <th>white_id</th>\n      <th>white_rating</th>\n      <th>black_id</th>\n      <th>black_rating</th>\n      <th>moves</th>\n      <th>opening_eco</th>\n      <th>opening_name</th>\n      <th>opening_ply</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\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>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    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('Count Null Values in each Column')\n",
    "df.select([F.count(F.when(F.isnull(col), col)).alias(col) for col in df.columns]).toPandas()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "- There are duplicate rows, which should be dropped from the dataset.\n",
    "- Luckily, this dataset does not contain missing values."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Data Preparation (Preprocessing)\n",
    "Scikit-Learn offers a range of useful methods for preprocessing and data splits. With the approval from the course instructor, we will transform the datasets into *Pandas DataFrames* in this part."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [],
   "source": [
    "# from sklearn.preprocessing import OneHotEncoder\n",
    "from pyspark.ml import Pipeline\n",
    "from pyspark.ml.feature import StringIndexer, OneHotEncoder, VectorAssembler"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Fix Anomalies"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [],
   "source": [
    "# Drop Duplicates\n",
    "df = df.distinct()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Label `winner`\n"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+------------+\n",
      "|winner|winner_label|\n",
      "+------+------------+\n",
      "|  draw|         2.0|\n",
      "| white|         0.0|\n",
      "| black|         1.0|\n",
      "+------+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "label_indexer = StringIndexer(inputCol='winner', outputCol='winner_label')\n",
    "label_indexer_model = label_indexer.fit(df)\n",
    "df = label_indexer_model.transform(df)\n",
    "df.select('winner', 'winner_label').distinct().show()\n",
    "df = df.drop('winner').withColumnRenamed('winner_label', 'winner')"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Feature `increment_code`\n",
    "As per inspection, the feature contains a string with two numbers separated by `+`. After research, we found that the first number refers to initial total clock time per player in *minutes*; the second number refers to the number of *seconds* added to the total clock time after the player makes a move. We decided to extract these two numbers as two separate features `clock` and `increment` replacing `increment_code`."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [
    {
     "data": {
      "text/plain": "[Row(increment_code='30+25'),\n Row(increment_code='10+0'),\n Row(increment_code='10+3')]"
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.select('increment_code').head(3)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [
    {
     "data": {
      "text/plain": "[Row(clock=5, increment=60),\n Row(clock=8, increment=0),\n Row(clock=3, increment=10)]"
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "splits = F.split(df.increment_code, '[+]')\n",
    "df = df.withColumn('clock', splits.getItem(0).cast('Integer')).withColumn('increment', splits.getItem(1).cast('Integer'))\n",
    "df.select('clock', 'increment').tail(3)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Feature `opening_eco`\n",
    "As per inspection, each row of this feature is a concatenation of a letter that denotes an [opening moves category](https://www.365chess.com/eco.php). Although there are variations within each category, we assume that the opening moves in each category to be similar enough that we can ignore the differences within each opening move category. Therefore, we extract the first letter from `opening_eco` to a new feature `open_cat` and ignore `opening_eco` during training."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+\n",
      "|opening_eco|\n",
      "+-----------+\n",
      "|        B11|\n",
      "|        B45|\n",
      "|        C60|\n",
      "+-----------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select('opening_eco').show(3)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+\n",
      "|open_category|\n",
      "+-------------+\n",
      "|            E|\n",
      "|            B|\n",
      "|            D|\n",
      "|            C|\n",
      "|            A|\n",
      "+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df = df.withColumn('open_category', df.opening_eco.substr(0, 1))\n",
    "df.select('open_category').distinct().show()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+--------+\n",
      "|open_category|open_cat|\n",
      "+-------------+--------+\n",
      "|            D|     3.0|\n",
      "|            B|     1.0|\n",
      "|            E|     4.0|\n",
      "|            C|     0.0|\n",
      "|            A|     2.0|\n",
      "+-------------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "open_cat_indexer = StringIndexer(inputCol='open_category', outputCol='open_cat')\n",
    "open_cat_model = open_cat_indexer.fit(df)\n",
    "df = open_cat_model.transform(df)\n",
    "df.select('open_category', 'open_cat').distinct().show()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Feature `rated`\n",
    "Contains boolean values only and does not require preprocessing. Rated games affect player ratings and may affect performance of the player. We may choose to separate rated games from unrated games for this fact, here we did not."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Split into Train/Test Sets\n",
    "1. Encode player ids into numerical values.\n",
    "    - Ensure that each player is assigned one and only one numerical id.\n",
    "2. Use encoded player ids to split dataset based on groups.\n",
    "    - Each group contains the matches played by one player. Make sure that each group are sampled evenly in the training set."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sklearn.model_selection import GroupShuffleSplit"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Transform Spark DF to __Pandas DF__\n",
    "Scikit-Learn Split methods cannot handle distributed datasets. Because we want to leverage the `GroupSplit` method in Scikit-Learn, we are turning the Spark DF into Pandas DF."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "outputs": [],
   "source": [
    "df_pd = df.toPandas()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Encode categorical player `white_id`s `black_id`s to split based on individual players"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "outputs": [
    {
     "data": {
      "text/plain": "                  white_id  white_idx        black_id  black_idx\n0                 konst767          0    ducksandcats          1\n1      everybodylovesjesus          2          ahmd11          3\n2                 jeff1983          4       bekzodjon          5\n3                 mellowg7          6        jesteroz          7\n4                 omnivoid          8  hasan_al-banna          9\n...                    ...        ...             ...        ...\n19624     demontechristo66       9815           eie24       3698\n19625         r-mohamadi55      15633           eie24       3698\n19626           kastorcito       5322            ed84       4132\n19627                 ssf7       3469            casr      15634\n19628                vares       4045        seciyeli       4932\n\n[19629 rows x 4 columns]",
      "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>white_id</th>\n      <th>white_idx</th>\n      <th>black_id</th>\n      <th>black_idx</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>konst767</td>\n      <td>0</td>\n      <td>ducksandcats</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>everybodylovesjesus</td>\n      <td>2</td>\n      <td>ahmd11</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>jeff1983</td>\n      <td>4</td>\n      <td>bekzodjon</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>mellowg7</td>\n      <td>6</td>\n      <td>jesteroz</td>\n      <td>7</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>omnivoid</td>\n      <td>8</td>\n      <td>hasan_al-banna</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>19624</th>\n      <td>demontechristo66</td>\n      <td>9815</td>\n      <td>eie24</td>\n      <td>3698</td>\n    </tr>\n    <tr>\n      <th>19625</th>\n      <td>r-mohamadi55</td>\n      <td>15633</td>\n      <td>eie24</td>\n      <td>3698</td>\n    </tr>\n    <tr>\n      <th>19626</th>\n      <td>kastorcito</td>\n      <td>5322</td>\n      <td>ed84</td>\n      <td>4132</td>\n    </tr>\n    <tr>\n      <th>19627</th>\n      <td>ssf7</td>\n      <td>3469</td>\n      <td>casr</td>\n      <td>15634</td>\n    </tr>\n    <tr>\n      <th>19628</th>\n      <td>vares</td>\n      <td>4045</td>\n      <td>seciyeli</td>\n      <td>4932</td>\n    </tr>\n  </tbody>\n</table>\n<p>19629 rows × 4 columns</p>\n</div>"
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_ids = pd.DataFrame(df_pd[['white_id', 'black_id']])\n",
    "player_ids = player_ids.stack().pipe(lambda s: pd.Series(pd.factorize(s.values)[0], s.index)).unstack()\n",
    "\n",
    "df_pd['white_idx'] = player_ids['white_id']\n",
    "df_pd['black_idx'] = player_ids['black_id']\n",
    "\n",
    "df_pd[['white_id', 'white_idx', 'black_id', 'black_idx']]"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Data Split"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "outputs": [],
   "source": [
    "gss = GroupShuffleSplit(n_splits=1, test_size=0.2, random_state=seed)\n",
    "train_idx, test_idx = next(gss.split(df_pd, df_pd['winner'], groups=df_pd['white_idx']))\n",
    "\n",
    "df_train = df_pd.iloc[train_idx]\n",
    "\n",
    "df_test = df_pd.iloc[test_idx]"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "outputs": [],
   "source": [
    "# Proof that none of the IDs from the training set are present in the test set.\n",
    "df_train_values = set(df_train['white_id'].values)\n",
    "df_test_values = set(df_test['white_id'].values)\n",
    "\n",
    "assert len(df_train_values.intersection(df_test_values)) == 0, 'A group is present in both training and test sets'"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Transform Pandas DF Train/Test Set to __Spark DF__"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "outputs": [],
   "source": [
    "df_train = spark.createDataFrame(df_train)\n",
    "\n",
    "df_test = spark.createDataFrame(df_test)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Feature `victory_status`\n",
    "From inspection, we observe 4 unique values for this categorical feature: `'resign', 'outoftime', 'mate', 'draw'`. We perform one-hot encoding before feeding this into the model. For Spark, the result is a `DenseVector` that can be fed into the Spark ML models. "
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "outputs": [],
   "source": [
    "si = StringIndexer(inputCol=\"victory_status\", outputCol=\"victory_status_code\")\n",
    "ohe = OneHotEncoder(inputCol=si.getOutputCol(), outputCol=\"match_status\", handleInvalid='keep')\n",
    "statusEncoderPipeline = Pipeline(stages=[si, ohe])\n",
    "statusEncoderModel = statusEncoderPipeline.fit(df_train)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "outputs": [],
   "source": [
    "df_train = statusEncoderModel.transform(df_train)\n",
    "df_test = statusEncoderModel.transform(df_test)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "outputs": [
    {
     "data": {
      "text/plain": "Row(victory_status='resign', victory_status_code=0.0, match_status=SparseVector(4, {0: 1.0}))"
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train.select('victory_status', 'victory_status_code', 'match_status').head()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Feature `moves`\n",
    "As per inspection, this feature records the list/sequence of moves during the match. Because that the sequence, if parsed correctly, actually indicates the winning player, we need to either drop this feature or obfuscate this information. We find that Spark supports Word2Vec feature transformation, which will turn the sequence into a vector. Such vector representation is independent of the order of the moves, and this can obfuscate part of the information lied within this feature."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "outputs": [],
   "source": [
    "from pyspark.ml.feature import Word2Vec"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "outputs": [
    {
     "data": {
      "text/plain": "'e4 c6 Nc3 d5 Nf3 Bg4 h3 Bh5 exd5 cxd5 Bb5+ Nc6 g4 Bg6 Ne5 Qb6 Nxg6 hxg6 Nxd5 Qc5 Qf3 Rc8 Nc3 e6 d3 Qe5+ Qe4 Bd6 Be3 Ne7 Bd4 Qxe4+ Nxe4 Bb8 Bxg7 Rh4 Bf6 Rh6'"
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train.select('moves').head().moves"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "outputs": [],
   "source": [
    "vectorSize = 100\n",
    "word2Vec = Word2Vec(vectorSize=vectorSize, seed=seed, inputCol=\"moves_list\", outputCol=\"moves_vec\")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Parameters for `Word2Vec`\n",
    "- `vectorSize`: size of the output vector, the choice of 100 is arbitrary here\n",
    "- `minCount`: Ignores all words with total frequency lower than this.\n",
    "- `inputCol`: `moves_list` is the input feature, a list of `moves` split by space\n",
    "- `outputCol`: `moves_vec` is the output feature, the transformed vector\n",
    "\n",
    "The Word2Vec model will be trained with the corpus gathered from the `moves` in the training set. After which, it will transform both training and testing datasets' `moves` feature."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "outputs": [
    {
     "data": {
      "text/plain": "DataFrame[moves_list: array<string>]"
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# split moves from string into list of strings, moves -> [move, move, ...]\n",
    "train_moves_corpus = df_train.select(F.split(df_train.moves, '\\s', -1).alias('moves_list'))\n",
    "train_moves_corpus"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Trained Word2Vec Model\n",
      "+-----+--------------------+\n",
      "| word|              vector|\n",
      "+-----+--------------------+\n",
      "| Bxd2|[0.08646679669618...|\n",
      "| Nxf6|[-0.3127570748329...|\n",
      "|  a2+|[0.02550127729773...|\n",
      "|Bxd5+|[0.14410454034805...|\n",
      "|  Kg8|[-0.1851859688758...|\n",
      "+-----+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\tools\\miniconda3\\envs\\bigdata\\lib\\site-packages\\pyspark\\sql\\context.py:125: FutureWarning: Deprecated in 3.0.0. Use SparkSession.builder.getOrCreate() instead.\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "fitted_word2Vec = word2Vec.fit(train_moves_corpus)\n",
    "print('Trained Word2Vec Model')\n",
    "fitted_word2Vec.getVectors().show(5)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "outputs": [
    {
     "data": {
      "text/plain": "('Transformed Moves for the 1st Match in Training Set: ',\n DenseVector([0.1149, 0.0761, 0.019, 0.0671, -0.1265, -0.0495, -0.0001, 0.0269, -0.0027, 0.0543, -0.0921, -0.043, 0.0581, 0.0162, -0.0572, -0.0128, 0.0236, -0.0254, 0.0416, 0.104, -0.0107, -0.0327, -0.0123, -0.0158, -0.0854, -0.0706, -0.1003, -0.0493, 0.0998, -0.0557, 0.025, 0.0252, 0.0767, -0.0238, 0.1444, 0.1162, -0.0034, 0.0435, -0.0162, -0.1023, -0.0363, 0.0349, -0.0397, -0.0861, -0.1683, -0.1017, -0.0548, 0.0306, -0.0268, 0.0998, -0.0735, 0.0093, 0.0021, -0.0197, -0.0052, 0.0371, 0.033, 0.0168, -0.0196, 0.0726, -0.0009, 0.0104, -0.0574, 0.0021, -0.0818, -0.0809, 0.0882, 0.0114, -0.0434, -0.0145, 0.0182, -0.0209, -0.0122, 0.1118, 0.0584, -0.0295, -0.0278, -0.0114, 0.115, 0.1062, -0.0852, -0.0015, 0.0958, 0.0385, -0.0011, 0.0064, 0.0006, -0.0648, 0.0568, -0.0183, -0.0443, -0.1293, 0.0597, 0.0492, -0.0374, -0.133, 0.0243, -0.056, 0.0682, 0.0676]))"
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'Transformed Moves for the 1st Match in Training Set: ', fitted_word2Vec.transform(train_moves_corpus).head().moves_vec"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "outputs": [],
   "source": [
    "df_train = df_train.withColumn('moves_list', F.split(df_train.moves, '\\s', -1))\n",
    "df_train = fitted_word2Vec.transform(df_train)\n",
    "\n",
    "df_test = df_test.withColumn('moves_list', F.split(df_test.moves, '\\s', -1))\n",
    "df_test = fitted_word2Vec.transform(df_test)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Save Datasets"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "outputs": [],
   "source": [
    "df_train.write.mode('ignore').parquet('data/train.parquet')\n",
    "df_test.write.mode('ignore').parquet('data/test.parquet')"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.13"
  },
  "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": 1
}