mylichessgames.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>game_id</th>\n",
       "      <th>game_order</th>\n",
       "      <th>event</th>\n",
       "      <th>site</th>\n",
       "      <th>date_played</th>\n",
       "      <th>round</th>\n",
       "      <th>white</th>\n",
       "      <th>black</th>\n",
       "      <th>result</th>\n",
       "      <th>white_elo</th>\n",
       "      <th>...</th>\n",
       "      <th>winner_loser_elo_diff</th>\n",
       "      <th>eco</th>\n",
       "      <th>termination</th>\n",
       "      <th>time_control</th>\n",
       "      <th>utc_date</th>\n",
       "      <th>utc_time</th>\n",
       "      <th>variant</th>\n",
       "      <th>ply_count</th>\n",
       "      <th>date_created</th>\n",
       "      <th>file_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b1de229e-95a6-45f6-a26c-7fc2d1ac1506</td>\n",
       "      <td>1</td>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>https://lichess.org/dnSCO0Cr</td>\n",
       "      <td>2023.02.07</td>\n",
       "      <td>?</td>\n",
       "      <td>Andrea_Tutone</td>\n",
       "      <td>Jay_1999</td>\n",
       "      <td>1-0</td>\n",
       "      <td>1987</td>\n",
       "      <td>...</td>\n",
       "      <td>-56</td>\n",
       "      <td>B01</td>\n",
       "      <td>Normal</td>\n",
       "      <td>180+2</td>\n",
       "      <td>2023.02.07</td>\n",
       "      <td>19:39:01</td>\n",
       "      <td>Standard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-02-10T20:50:53+0000</td>\n",
       "      <td>lichess_Jay_1999_2023-02-10.pgn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4b846301-9dc7-4a7a-9816-8afab9145665</td>\n",
       "      <td>2</td>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>https://lichess.org/t2z2WkNp</td>\n",
       "      <td>2023.02.07</td>\n",
       "      <td>?</td>\n",
       "      <td>Jay_1999</td>\n",
       "      <td>PotatoMatey</td>\n",
       "      <td>0-1</td>\n",
       "      <td>2050</td>\n",
       "      <td>...</td>\n",
       "      <td>-59</td>\n",
       "      <td>A01</td>\n",
       "      <td>Normal</td>\n",
       "      <td>180+2</td>\n",
       "      <td>2023.02.07</td>\n",
       "      <td>04:54:30</td>\n",
       "      <td>Standard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-02-10T20:50:53+0000</td>\n",
       "      <td>lichess_Jay_1999_2023-02-10.pgn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3d1f3dfc-0bfb-43b9-afc1-02269d3753d9</td>\n",
       "      <td>3</td>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>https://lichess.org/vxqs7HvO</td>\n",
       "      <td>2023.02.06</td>\n",
       "      <td>?</td>\n",
       "      <td>Jay_1999</td>\n",
       "      <td>Alrrash</td>\n",
       "      <td>0-1</td>\n",
       "      <td>2055</td>\n",
       "      <td>...</td>\n",
       "      <td>71</td>\n",
       "      <td>A01</td>\n",
       "      <td>Normal</td>\n",
       "      <td>180+2</td>\n",
       "      <td>2023.02.06</td>\n",
       "      <td>04:30:26</td>\n",
       "      <td>Standard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-02-10T20:50:53+0000</td>\n",
       "      <td>lichess_Jay_1999_2023-02-10.pgn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29fd5ad8-47a7-4b12-a9d3-5548539c0d88</td>\n",
       "      <td>4</td>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>https://lichess.org/MlIGVPO8</td>\n",
       "      <td>2023.02.05</td>\n",
       "      <td>?</td>\n",
       "      <td>Crocodingo</td>\n",
       "      <td>Jay_1999</td>\n",
       "      <td>1-0</td>\n",
       "      <td>2115</td>\n",
       "      <td>...</td>\n",
       "      <td>55</td>\n",
       "      <td>E90</td>\n",
       "      <td>Normal</td>\n",
       "      <td>180+2</td>\n",
       "      <td>2023.02.05</td>\n",
       "      <td>23:38:06</td>\n",
       "      <td>Standard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-02-10T20:50:53+0000</td>\n",
       "      <td>lichess_Jay_1999_2023-02-10.pgn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1f9ecf78-8bee-44b6-8e02-ebd1e5d349c9</td>\n",
       "      <td>5</td>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>https://lichess.org/Vot0ijSw</td>\n",
       "      <td>2023.02.05</td>\n",
       "      <td>?</td>\n",
       "      <td>pijuan</td>\n",
       "      <td>Jay_1999</td>\n",
       "      <td>0-1</td>\n",
       "      <td>2054</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>B01</td>\n",
       "      <td>Normal</td>\n",
       "      <td>180+2</td>\n",
       "      <td>2023.02.05</td>\n",
       "      <td>22:09:54</td>\n",
       "      <td>Standard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-02-10T20:50:53+0000</td>\n",
       "      <td>lichess_Jay_1999_2023-02-10.pgn</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                game_id  game_order             event  \\\n",
       "0  b1de229e-95a6-45f6-a26c-7fc2d1ac1506           1  Rated Blitz game   \n",
       "1  4b846301-9dc7-4a7a-9816-8afab9145665           2  Rated Blitz game   \n",
       "2  3d1f3dfc-0bfb-43b9-afc1-02269d3753d9           3  Rated Blitz game   \n",
       "3  29fd5ad8-47a7-4b12-a9d3-5548539c0d88           4  Rated Blitz game   \n",
       "4  1f9ecf78-8bee-44b6-8e02-ebd1e5d349c9           5  Rated Blitz game   \n",
       "\n",
       "                           site date_played round          white        black  \\\n",
       "0  https://lichess.org/dnSCO0Cr  2023.02.07     ?  Andrea_Tutone     Jay_1999   \n",
       "1  https://lichess.org/t2z2WkNp  2023.02.07     ?       Jay_1999  PotatoMatey   \n",
       "2  https://lichess.org/vxqs7HvO  2023.02.06     ?       Jay_1999      Alrrash   \n",
       "3  https://lichess.org/MlIGVPO8  2023.02.05     ?     Crocodingo     Jay_1999   \n",
       "4  https://lichess.org/Vot0ijSw  2023.02.05     ?         pijuan     Jay_1999   \n",
       "\n",
       "  result white_elo  ...  winner_loser_elo_diff  eco  termination time_control  \\\n",
       "0    1-0      1987  ...                    -56  B01       Normal        180+2   \n",
       "1    0-1      2050  ...                    -59  A01       Normal        180+2   \n",
       "2    0-1      2055  ...                     71  A01       Normal        180+2   \n",
       "3    1-0      2115  ...                     55  E90       Normal        180+2   \n",
       "4    0-1      2054  ...                      0  B01       Normal        180+2   \n",
       "\n",
       "     utc_date  utc_time   variant ply_count              date_created  \\\n",
       "0  2023.02.07  19:39:01  Standard       NaN  2023-02-10T20:50:53+0000   \n",
       "1  2023.02.07  04:54:30  Standard       NaN  2023-02-10T20:50:53+0000   \n",
       "2  2023.02.06  04:30:26  Standard       NaN  2023-02-10T20:50:53+0000   \n",
       "3  2023.02.05  23:38:06  Standard       NaN  2023-02-10T20:50:53+0000   \n",
       "4  2023.02.05  22:09:54  Standard       NaN  2023-02-10T20:50:53+0000   \n",
       "\n",
       "                         file_name  \n",
       "0  lichess_Jay_1999_2023-02-10.pgn  \n",
       "1  lichess_Jay_1999_2023-02-10.pgn  \n",
       "2  lichess_Jay_1999_2023-02-10.pgn  \n",
       "3  lichess_Jay_1999_2023-02-10.pgn  \n",
       "4  lichess_Jay_1999_2023-02-10.pgn  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('jay_1999_game_info.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['game_id', 'game_order', 'event', 'site', 'date_played', 'round',\n",
       "       'white', 'black', 'result', 'white_elo', 'white_rating_diff',\n",
       "       'black_elo', 'black_rating_diff', 'white_title', 'black_title',\n",
       "       'winner', 'winner_elo', 'loser', 'loser_elo', 'winner_loser_elo_diff',\n",
       "       'eco', 'termination', 'time_control', 'utc_date', 'utc_time', 'variant',\n",
       "       'ply_count', 'date_created', 'file_name'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(columns=['game_id', 'game_order', 'site', 'round', 'white_title', 'black_title', 'ply_count', 'date_created', 'file_name'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2911 entries, 0 to 2910\n",
      "Data columns (total 20 columns):\n",
      " #   Column                 Non-Null Count  Dtype  \n",
      "---  ------                 --------------  -----  \n",
      " 0   event                  2911 non-null   object \n",
      " 1   date_played            2911 non-null   object \n",
      " 2   white                  2911 non-null   object \n",
      " 3   black                  2911 non-null   object \n",
      " 4   result                 2911 non-null   object \n",
      " 5   white_elo              2911 non-null   object \n",
      " 6   white_rating_diff      2658 non-null   float64\n",
      " 7   black_elo              2911 non-null   object \n",
      " 8   black_rating_diff      2658 non-null   float64\n",
      " 9   winner                 2911 non-null   object \n",
      " 10  winner_elo             2795 non-null   object \n",
      " 11  loser                  2911 non-null   object \n",
      " 12  loser_elo              2795 non-null   object \n",
      " 13  winner_loser_elo_diff  2911 non-null   int64  \n",
      " 14  eco                    2911 non-null   object \n",
      " 15  termination            2911 non-null   object \n",
      " 16  time_control           2911 non-null   object \n",
      " 17  utc_date               2911 non-null   object \n",
      " 18  utc_time               2911 non-null   object \n",
      " 19  variant                2911 non-null   object \n",
      "dtypes: float64(2), int64(1), object(17)\n",
      "memory usage: 455.0+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['white_rating_diff'].fillna('0', inplace=True)\n",
    "df['black_rating_diff'].fillna('0', inplace=True)\n",
    "df['winner_elo'].fillna('0', inplace=True)\n",
    "df['loser_elo'].fillna('0', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def classify_time_control(tc):\n",
    "    tc_minutes = int(tc.split(\"+\")[0])\n",
    "    if tc_minutes < 180:\n",
    "        return \"Bullet\"\n",
    "    elif tc_minutes >= 180 and tc_minutes < 310:\n",
    "        return \"Blitz\"\n",
    "    elif tc_minutes >= 310 and tc_minutes <= 900:\n",
    "        return \"Rapid\"\n",
    "    else:\n",
    "        return \"Classical\"\n",
    "\n",
    "df['time_control'] = df['time_control'].apply(classify_time_control)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "blitz        2239\n",
       "rapid         394\n",
       "bullet        246\n",
       "classical      32\n",
       "Name: time_control, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['time_control'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['result'] = np.where((df['white'] == 'Jay_1999') & (df['result'] == '1-0'), 'Won', \n",
    "                np.where((df['black'] == 'Jay_1999') & (df['result'] == '0-1'), 'Won', \n",
    "                np.where(df['result'] == '1/2-1/2', 'Draw', 'Lost')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Won     1596\n",
       "Lost    1199\n",
       "Draw     116\n",
       "Name: result, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['result'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def opening_name(eco_code):\n",
    "    if eco_code in ['A01', 'A06']:\n",
    "        return 'Nimzovich-Larsen Attack'\n",
    "    elif eco_code in ['A00']:\n",
    "        return 'Benko Opening'\n",
    "    elif eco_code in ['A02', 'A03']:\n",
    "        return \"Bird's Opening\"\n",
    "    elif eco_code in ['A04', 'A05', 'A07', 'A08', 'A09']:\n",
    "        return 'Reti Opening'\n",
    "    elif eco_code in ['A10', 'A15', 'A17', 'A13', 'A19', 'A20', 'A21', 'A22', 'A23', 'A24', 'A25', 'A26', 'A27', 'A28']:\n",
    "        return 'English Opening'\n",
    "    elif eco_code in ['A40', 'A41', 'A45', 'A46', 'A50', 'D00', 'D04', 'D05', 'E00', 'E10']:\n",
    "        return \"Queen's Pawn' Opening\"\n",
    "    elif eco_code in ['A43', 'A44']:\n",
    "        return 'Old Benoni Defense'\n",
    "    elif eco_code in ['A47']:\n",
    "        return \"Queen's Indian Defense\"\n",
    "    \n",
    "    elif eco_code in ['A48', 'A49']:\n",
    "        return \"King's Indian, East Indian Defense\" \n",
    "    elif eco_code in ['A51', 'A52']:\n",
    "        return \"Budapest Defense\"\n",
    "    elif eco_code in ['A53', 'A54', 'A55']:\n",
    "        return \"Old Indian Defense\"  \n",
    "    elif eco_code in ['A57', 'A58', 'A59']:\n",
    "        return \"Benko Gambit\" \n",
    "    elif eco_code in ['A56', 'A57','A58','A59','A60',\n",
    "                        'A61','A62','A63','A64','A65','A66','A68','A67','A69','A70',\n",
    "                        'A71','A72','A73','A74','A75','A77','A76','A78','A79']:\n",
    "        return \"Benoni Defense\"  \n",
    "    elif eco_code in ['A80', 'A81', 'A82', 'A83', 'A84', 'A85', 'A86', 'A87', 'A88', 'A89', 'A90', \n",
    "                        'A91', 'A92', 'A93', 'A94', 'A95', 'A96', 'A97', 'A98', 'A99']:\n",
    "        return \"Benko Gambit\" \n",
    "    elif eco_code in ['B01', 'B02']:\n",
    "        return 'Scandinavian Defense'\n",
    "    elif eco_code in ['B10', 'B11', 'B12', 'B13', 'B14', 'B15', 'B16', 'B17', 'B18', 'B19']:\n",
    "        return 'Caro-Kann Defense'    \n",
    "    elif eco_code in ['B20', 'B21', 'B22', 'B23', 'B24', 'B25', 'B26', 'B27', 'B28', 'B29', \n",
    "                    'B30', 'B31', 'B32', 'B33', 'B34', 'B35', 'B36', 'B37', 'B38', 'B39', \n",
    "                    'B40', 'B41', 'B42', 'B43', 'B44', 'B45', 'B46', 'B47', 'B48', 'B49', \n",
    "                    'B50', 'B51', 'B52', 'B53', 'B54', 'B55', 'B56', 'B57', 'B58', 'B59', \n",
    "                    'B60', 'B61', 'B62', 'B63', 'B64', 'B65', 'B66', 'B67', 'B68', 'B69', \n",
    "                    'B70', 'B71', 'B72', 'B73', 'B74', 'B75', 'B76', 'B77', 'B78', 'B79', \n",
    "                    'B80', 'B81', 'B82', 'B83', 'B84', 'B85', 'B86', 'B87', 'B88', 'B89', \n",
    "                    'B90', 'B91', 'B92', 'B93', 'B94', 'B95', 'B96', 'B97', 'B98', 'B99'\n",
    "                    ]:\n",
    "        return 'Sicillian Defense'\n",
    "  \n",
    "    elif eco_code in ['C00', 'C01', 'C02', 'C03', 'C04', 'C05', 'C06', 'C07', 'C08', 'C09', \n",
    "                        'C10', 'C11', 'C12', 'C13', 'C14', 'C15', 'C16', 'C17', 'C18', 'C19']:\n",
    "        return 'French Defense'\n",
    "        \n",
    "    elif eco_code in [\"C20\", \"C21\", \"C22\", \"C23\", \"C24\", \"C25\", \"C26\", \"C27\", \"C28\", \"C29\"]:\n",
    "        return \"King's Pawn Opening\" \n",
    "    elif eco_code in [\"C30\", \"C31\", \"C32\", \"C33\", \"C34\", \"C35\", \"C36\", \"C37\", \"C38\", \"C39\"]:\n",
    "        return \"King's Gambit\"\n",
    "    elif eco_code in ['C40']:\n",
    "        return \"King's Knight Opening\"\n",
    "    elif eco_code in ['C41']:\n",
    "        return \"Philidor's Defense\"\n",
    "    elif eco_code in ['C42', 'C43']:\n",
    "        return \"Petrov's Defense\"\n",
    "    elif eco_code in ['C44']:\n",
    "        return \"King's Pawn Opening\"\n",
    "    elif eco_code in ['C45']:\n",
    "        return \"Scotch Opening\"\n",
    "    elif eco_code in ['C46']:\n",
    "        return \"Three Knights Game\"\n",
    "    elif eco_code in ['C47', 'C48', 'C49']:\n",
    "        return \"Four Knights, Scotch Variation\"\n",
    "    elif eco_code in ['C50']:\n",
    "        return \"King's Pawn Game\"\n",
    "    elif eco_code in ['C51', 'C52']:\n",
    "        return \"Evans Gambit\"\n",
    "    elif eco_code in ['C53', 'C54']:\n",
    "        return \"Giuoco Piano\"\n",
    "    elif eco_code in ['C55', 'C56', 'C57', 'C58', 'C59']:\n",
    "        return \"Two Knights Defense\"\n",
    "    elif eco_code in ['C60', 'C61', 'C62', 'C63', 'C64', 'C65', 'C66', 'C67', 'C68', 'C69', \n",
    "                    'C70', 'C71', 'C72', 'C73', 'C74', 'C75', 'C76', 'C77', 'C78', 'C79', \n",
    "                    'C80', 'C81', 'C82', 'C83', 'C84', 'C85', 'C86', 'C87', 'C88', 'C89', \n",
    "                    'C90', 'C91', 'C92', 'C93', 'C94', 'C95', 'C96', 'C97', 'C98', 'C99']:\n",
    "        return \"Ruy Lopez (Spanish Opening)\"\n",
    "    elif eco_code in ['D02']:\n",
    "        return 'London System'\n",
    "    elif eco_code in ['D16', 'D17', 'D18', 'D19', 'D30', 'D31', 'D32', 'D33', 'D34', 'D35', 'D36', 'D37', 'D38', 'D39', 'D40', 'D41', 'D42']:\n",
    "        return \"Queen's Gambit Declined\"\n",
    "    elif eco_code in ['D20', 'D21', 'D22', 'D23', 'D24', 'D25', 'D26', 'D27', 'D28', 'D29']:\n",
    "        return \"Queen's Gambit Accepted\"\n",
    "    elif eco_code in [\"D80\", \"D81\", \"D82\", \"D83\", \"D84\", \"D85\", \"D86\", \"D87\", \"D88\", \"D89\",\n",
    "                    \"D90\", \"D91\", \"D92\", \"D93\", \"D94\", \"D95\", \"D96\", \"D97\", \"D98\", \"D99\"]:\n",
    "        return \"Gruenfeld Defense\"\n",
    "    elif eco_code in  ['E20', 'E21', 'E22', 'E23', 'E24', 'E25', 'E26', 'E27', 'E28', 'E29',     \n",
    "                    'E30', 'E31', 'E32', 'E33', 'E34', 'E35', 'E36', 'E37', 'E38', 'E39',     \n",
    "                    'E40', 'E41', 'E42', 'E43', 'E44', 'E45', 'E46', 'E47', 'E48', 'E49',\n",
    "                    'E50', 'E51', 'E52', 'E53', 'E54', 'E55', 'E56', 'E57', 'E58', 'E59']:\n",
    "        return \"Nimzo-Indian Defense\"\n",
    "    elif eco_code in ['E60', 'E61', 'E62', 'E63', 'E64', 'E65', 'E66', 'E67', 'E68', 'E69',  \n",
    "                    'E70', 'E71', 'E72', 'E73', 'E74', 'E75', 'E76', 'E77', 'E78', 'E79',  \n",
    "                    'E80', 'E81', 'E82', 'E83', 'E84', 'E85', 'E86', 'E87', 'E88', 'E89',  \n",
    "                    'E90', 'E91', 'E92', 'E93', 'E94', 'E95', 'E96', 'E97', 'E98', 'E99']:\n",
    "        return \"King's Indian Defense\"\n",
    "    elif eco_code in ['?']:\n",
    "        return 'Unknown'\n",
    "    else:\n",
    "        return 'Mixed of opening I usually not played'\n",
    "        \n",
    "\n",
    "\n",
    "df['Opening'] = df['eco'].apply(opening_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Caro-Kann Defense                        465\n",
       "Sicillian Defense                        426\n",
       "Queen's Pawn' Opening                    297\n",
       "Mixed of opening I usually not played    241\n",
       "Nimzovich-Larsen Attack                  226\n",
       "Scandinavian Defense                     186\n",
       "French Defense                           159\n",
       "London System                            139\n",
       "Ruy Lopez (Spanish Opening)              114\n",
       "Queen's Gambit Declined                   67\n",
       "Reti Opening                              67\n",
       "Bird's Opening                            58\n",
       "English Opening                           57\n",
       "King's Indian Defense                     52\n",
       "Benko Opening                             49\n",
       "Unknown                                   39\n",
       "King's Pawn Opening                       37\n",
       "King's Indian, East Indian Defense        33\n",
       "Philidor's Defense                        25\n",
       "Benko Gambit                              21\n",
       "Petrov's Defense                          20\n",
       "King's Gambit                             17\n",
       "Benoni Defense                            15\n",
       "Old Benoni Defense                        15\n",
       "King's Knight Opening                     13\n",
       "King's Pawn Game                          13\n",
       "Budapest Defense                          13\n",
       "Gruenfeld Defense                          6\n",
       "Nimzo-Indian Defense                       6\n",
       "Four Knights, Scotch Variation             5\n",
       "Three Knights Game                         5\n",
       "Two Knights Defense                        5\n",
       "Queen's Gambit Accepted                    5\n",
       "Scotch Opening                             5\n",
       "Old Indian Defense                         4\n",
       "Giuoco Piano                               4\n",
       "Queen's Indian Defense                     1\n",
       "Evans Gambit                               1\n",
       "Name: Opening, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Opening'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['myrating'] = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['white_elo'].replace('?', np.nan, inplace=True)\n",
    "df['black_elo'].replace('?', np.nan, inplace=True)\n",
    "\n",
    "df['white_elo'] = pd.to_numeric(df['white_elo'], errors='coerce')\n",
    "df['black_elo'] = pd.to_numeric(df['black_elo'], errors='coerce')\n",
    "\n",
    "df['white_rating_diff'] = pd.to_numeric(df['white_rating_diff'], errors='coerce')\n",
    "df['black_rating_diff'] = pd.to_numeric(df['black_rating_diff'], errors='coerce')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, row in df.iterrows():\n",
    "    if row['event'] not in ['Casual Blitz game', 'Casual Rapid game', 'Casual Classical game', 'Casual Bullet game']:\n",
    "        if row['white'] == 'Jay_1999' and row['result'] == 'Won':\n",
    "            df.at[i, 'myrating'] = row['white_elo'] + row['white_rating_diff']\n",
    "        elif row['black'] == 'Jay_1999' and row['result'] == 'Won':\n",
    "            df.at[i, 'myrating'] = row['black_elo'] + row['black_rating_diff']\n",
    "        elif row['white'] == 'Jay_1999' and row['result'] == 'Lost':\n",
    "            df.at[i, 'myrating'] = row['white_elo']\n",
    "        elif row['black'] == 'Jay_1999' and row['result'] == 'Lost':\n",
    "            df.at[i, 'myrating'] = row['black_elo']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['utc_time'] = pd.to_datetime(df['utc_time'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['hour'] = df['utc_time'].dt.hour\n",
    "df['time_of_day'] = np.where((df['hour'] >= 6) & (df['hour'] < 12), 'Morning',\n",
    "                   np.where((df['hour'] >= 12) & (df['hour'] < 18), 'Afternoon',\n",
    "                            np.where((df['hour'] >= 18) & (df['hour'] < 22), 'Evening', 'Night')))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['termination'].replace(\"Normal\", \"Resignation/Checkmate\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Resignation/Checkmate    2326\n",
       "Time forfeit              571\n",
       "Abandoned                  14\n",
       "Name: termination, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['termination'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "morning      1257\n",
       "afternoon    1245\n",
       "evening       225\n",
       "night         184\n",
       "Name: time_of_day, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['time_of_day'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1794\n"
     ]
    }
   ],
   "source": [
    "print(max(df['winner_loser_elo_diff']) - min(df['winner_loser_elo_diff']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1123"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "max(df['winner_loser_elo_diff'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-671"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "min(df['winner_loser_elo_diff'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['type of game'] = np.where((df['hour'] >= 6) & (df['hour'] < 12), 'morning',\n",
    "                   np.where((df['hour'] >= 12) & (df['hour'] < 18), 'afternoon',\n",
    "                            np.where((df['hour'] >= 18) & (df['hour'] < 22), 'evening', 'night')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, row in df.iterrows():\n",
    "    if row['white'] == 'Jay_1999' and row['result'] == 'Won':\n",
    "        if row['winner_loser_elo_diff'] >= 100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Won against very high rated player'\n",
    "        elif row['winner_loser_elo_diff'] >= 50 and row['winner_loser_elo_diff'] < 100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Won agaist high rated player'\n",
    "        elif row['winner_loser_elo_diff'] > 0 and row['winner_loser_elo_diff'] < 50:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Won against Similar Rating'\n",
    "\n",
    "        elif row['winner_loser_elo_diff'] <= -100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Won against very high rated player'\n",
    "        elif row['winner_loser_elo_diff'] <= -50 and row['winner_loser_elo_diff'] > -100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Won agaist high rated player'\n",
    "        elif row['winner_loser_elo_diff'] < 0 and row['winner_loser_elo_diff'] > -50:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Won against Similar Rating'\n",
    "            \n",
    "\n",
    "    elif row['white'] == 'Jay_1999' and row['result'] == 'Lost':\n",
    "        if row['winner_loser_elo_diff'] >= 100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Lost against very high rated player'\n",
    "        elif row['winner_loser_elo_diff'] >= 50 and row['winner_loser_elo_diff'] < 100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Lost agaist high rated player'\n",
    "        elif row['winner_loser_elo_diff'] >= 0 and row['winner_loser_elo_diff'] < 50:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Lost against Similar Rating'\n",
    "        if row['winner_loser_elo_diff'] <= -100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Lost against very high rated player'\n",
    "        elif row['winner_loser_elo_diff'] <= -50 and row['winner_loser_elo_diff'] > -100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Lost agaist high rated player'\n",
    "        elif row['winner_loser_elo_diff'] < 0 and row['winner_loser_elo_diff'] > -50:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Lost against Similar Rating'\n",
    "        # if row['winner_loser_elo_diff'] >= 70:\n",
    "        #     df.at[i, 'winner_loser_elo_diff'] = 'Lost against very high rated player'\n",
    "        # elif row['winner_loser_elo_diff'] >= 30 and row['winner_loser_elo_diff'] <= 70:\n",
    "        #     df.at[i, 'winner_loser_elo_diff'] = 'Lost agaist high rated player'\n",
    "        # elif row['winner_loser_elo_diff'] >= 0 and row['winner_loser_elo_diff'] <= 30:\n",
    "        #     df.at[i, 'winner_loser_elo_diff'] = 'Lost against Similar Rating'\n",
    "\n",
    "    elif row['black'] == 'Jay_1999' and row['result'] == 'Won':\n",
    "        # if row['winner_loser_elo_diff'] <= -100:\n",
    "        #     df.at[i, 'winner_loser_elo_diff'] = 'Won against very high rated player (Great Victory)'\n",
    "        # elif row['winner_loser_elo_diff'] <= -50 and row['winner_loser_elo_diff'] > -100:\n",
    "        #     df.at[i, 'winner_loser_elo_diff'] = 'Won agaist high rated player (Excellent Win)'\n",
    "        # elif row['winner_loser_elo_diff'] <= 0 and row['winner_loser_elo_diff'] > -50:\n",
    "        #     df.at[i, 'winner_loser_elo_diff'] = 'Won against Similar Rating (Good Win)'\n",
    "        if row['winner_loser_elo_diff'] >= 100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Won against very high rated player'\n",
    "        elif row['winner_loser_elo_diff'] >= 50 and row['winner_loser_elo_diff'] < 100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Won agaist high rated player'\n",
    "        elif row['winner_loser_elo_diff'] >= 0 and row['winner_loser_elo_diff'] < 50:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Won against Similar Rating'\n",
    "        elif row['winner_loser_elo_diff'] <= -100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Won against very high rated player'\n",
    "        elif row['winner_loser_elo_diff'] <= -50 and row['winner_loser_elo_diff'] > -100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Won agaist high rated player'\n",
    "        elif row['winner_loser_elo_diff'] < 0 and row['winner_loser_elo_diff'] > -50:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Won against Similar Rating'\n",
    "\n",
    "\n",
    "    \n",
    "    elif row['black'] == 'Jay_1999' and row['result'] == 'Lost':\n",
    "        if row['winner_loser_elo_diff'] <= -100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Lost against very high rated player'\n",
    "        elif row['winner_loser_elo_diff'] <= -50 and row['winner_loser_elo_diff'] > -100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Lost agaist high rated player'\n",
    "        elif row['winner_loser_elo_diff'] <= 0 and row['winner_loser_elo_diff'] > -50:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Lost against Similar Rating'\n",
    "        elif row['winner_loser_elo_diff'] >= 100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Lost against very high rated player'\n",
    "        elif row['winner_loser_elo_diff'] >= 50 and row['winner_loser_elo_diff'] < 100:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Lost agaist high rated player'\n",
    "        elif row['winner_loser_elo_diff'] > 0 and row['winner_loser_elo_diff'] < 50:\n",
    "            df.at[i, 'winner_loser_elo_diff'] = 'Lost against Similar Rating'\n",
    "    else:\n",
    "        df.at[i, 'winner_loser_elo_diff'] = \"Draw Game\"\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Won against Similar Rating             675\n",
       "Lost against Similar Rating            654\n",
       "Won against very high rated player     632\n",
       "Lost against very high rated player    300\n",
       "Won agaist high rated player           282\n",
       "Lost agaist high rated player          245\n",
       "Draw Game                              116\n",
       "0                                        7\n",
       "Name: winner_loser_elo_diff, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['winner_loser_elo_diff'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Standard         2880\n",
       "From Position      31\n",
       "Name: variant, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['variant'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['event', 'date_played', 'white', 'black', 'result', 'white_elo',\n",
       "       'white_rating_diff', 'black_elo', 'black_rating_diff', 'winner',\n",
       "       'winner_elo', 'loser', 'loser_elo', 'winner_loser_elo_diff', 'eco',\n",
       "       'termination', 'time_control', 'utc_date', 'utc_time', 'variant',\n",
       "       'Opening', 'myrating', 'hour', 'time_of_day', 'type of game'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.to_csv('Jay_Games.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "jaypython",
   "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.10.8"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "8e29d5ce6dc1ab1f0cefd3558958db33f63fd950bd93fe778caeb759c423b34f"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}