{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A Worksheet for the Lichess Data Set\n",
    "\n",
    "The first code cell is provided by Kaggle and provides a list of what datasets are available.  What's available would be a function of what dataset was under inspection when New Notebook got pressed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5",
    "execution": {
     "iopub.execute_input": "2022-04-03T22:29:41.722009Z",
     "iopub.status.busy": "2022-04-03T22:29:41.721602Z",
     "iopub.status.idle": "2022-04-03T22:29:41.758366Z",
     "shell.execute_reply": "2022-04-03T22:29:41.757293Z",
     "shell.execute_reply.started": "2022-04-03T22:29:41.721904Z"
    }
   },
   "outputs": [],
   "source": [
    "# This Python 3 environment comes with many helpful analytics libraries installed\n",
    "# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n",
    "# For example, here's several helpful packages to load\n",
    "\n",
    "import numpy as np # linear algebra\n",
    "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n",
    "\n",
    "# Input data files are available in the read-only \"../input/\" directory\n",
    "# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n",
    "\n",
    "import os\n",
    "for dirname, _, filenames in os.walk('/kaggle/input'):\n",
    "    for filename in filenames:\n",
    "        print(os.path.join(dirname, filename))\n",
    "\n",
    "# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n",
    "# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A standard opening would be to read the csv file provided, into a pandas DataFrame.  `read_csv` comes with a huger number of options, set by named argument.  We consider ourselves lucky if the defaults do the job."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:29:41.855875Z",
     "iopub.status.busy": "2022-04-03T22:29:41.855524Z",
     "iopub.status.idle": "2022-04-03T22:29:42.131594Z",
     "shell.execute_reply": "2022-04-03T22:29:42.130519Z",
     "shell.execute_reply.started": "2022-04-03T22:29:41.855836Z"
    }
   },
   "outputs": [],
   "source": [
    "games = pd.read_csv('/kaggle/input/chess/games.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've likely already studied the columns, how full each one is (i.e. how riddled with nans) but it can't hurt to study them again, now that the data is loaded.  These are not all unique games according to the Kaggle gods or guides.\n",
    "\n",
    "What is of interest?  Good questions will not occur to you (or me) until we have some understanding of the columns available.  \n",
    "\n",
    "Since our two chess players are usually rated, with a boolean field flagging if not, and since we have the moves for most games, we can start envisioning an investigation:  what games between players of widely different rank nevertheless took a lot of moves to resolve?\n",
    "\n",
    "Another question:  how might we curate a DataFrame called upsets, in which the lower ranked player, by say 500 or more points, nevertheless checkmated the stronger player?  See below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:29:42.133795Z",
     "iopub.status.busy": "2022-04-03T22:29:42.133497Z",
     "iopub.status.idle": "2022-04-03T22:29:42.188413Z",
     "shell.execute_reply": "2022-04-03T22:29:42.187648Z",
     "shell.execute_reply.started": "2022-04-03T22:29:42.133759Z"
    }
   },
   "outputs": [],
   "source": [
    "games.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We won't need all the columns going forward.  Lets put a fork in the road with start down our path with filter_cols.  Later, we will reorder these columns, and compute new columns based on what these columns contain."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:31:02.227377Z",
     "iopub.status.busy": "2022-04-03T22:31:02.226991Z",
     "iopub.status.idle": "2022-04-03T22:31:02.249378Z",
     "shell.execute_reply": "2022-04-03T22:31:02.248592Z",
     "shell.execute_reply.started": "2022-04-03T22:31:02.227338Z"
    }
   },
   "outputs": [],
   "source": [
    "filter_cols = games.loc[:, ['id', 'white_rating', 'black_rating', 'moves', \"victory_status\", \"winner\"]]\n",
    "filter_cols.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The diff column will measure the difference in rank between the two players for each game.  The difference will always be positive i.e. will be the \"absolute value\" of the span."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:31:06.496420Z",
     "iopub.status.busy": "2022-04-03T22:31:06.495661Z",
     "iopub.status.idle": "2022-04-03T22:31:06.504108Z",
     "shell.execute_reply": "2022-04-03T22:31:06.502932Z",
     "shell.execute_reply.started": "2022-04-03T22:31:06.496370Z"
    }
   },
   "outputs": [],
   "source": [
    "filter_cols[\"diff\"] = abs(filter_cols.white_rating - filter_cols.black_rating)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:31:07.905111Z",
     "iopub.status.busy": "2022-04-03T22:31:07.904750Z",
     "iopub.status.idle": "2022-04-03T22:31:07.909789Z",
     "shell.execute_reply": "2022-04-03T22:31:07.908580Z",
     "shell.execute_reply.started": "2022-04-03T22:31:07.905071Z"
    }
   },
   "outputs": [],
   "source": [
    "def number_of_moves(the_moves):\n",
    "    \"\"\"\n",
    "    return the number of moves in a moves string\n",
    "    \"\"\"\n",
    "    return len(the_moves.split())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:29:42.674273Z",
     "iopub.status.busy": "2022-04-03T22:29:42.673909Z",
     "iopub.status.idle": "2022-04-03T22:29:42.688989Z",
     "shell.execute_reply": "2022-04-03T22:29:42.688303Z",
     "shell.execute_reply.started": "2022-04-03T22:29:42.674208Z"
    }
   },
   "outputs": [],
   "source": [
    "filter_cols.moves[0]  #  example moves string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:31:10.584868Z",
     "iopub.status.busy": "2022-04-03T22:31:10.583805Z",
     "iopub.status.idle": "2022-04-03T22:31:10.592505Z",
     "shell.execute_reply": "2022-04-03T22:31:10.591511Z",
     "shell.execute_reply.started": "2022-04-03T22:31:10.584810Z"
    }
   },
   "outputs": [],
   "source": [
    "number_of_moves(filter_cols.moves[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:31:12.270172Z",
     "iopub.status.busy": "2022-04-03T22:31:12.269205Z",
     "iopub.status.idle": "2022-04-03T22:31:12.357929Z",
     "shell.execute_reply": "2022-04-03T22:31:12.356844Z",
     "shell.execute_reply.started": "2022-04-03T22:31:12.270111Z"
    }
   },
   "outputs": [],
   "source": [
    "filter_cols[\"num_moves\"] = filter_cols.moves.apply(number_of_moves)  # apply is for whole Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:31:14.825895Z",
     "iopub.status.busy": "2022-04-03T22:31:14.825529Z",
     "iopub.status.idle": "2022-04-03T22:31:14.840902Z",
     "shell.execute_reply": "2022-04-03T22:31:14.839980Z",
     "shell.execute_reply.started": "2022-04-03T22:31:14.825850Z"
    }
   },
   "outputs": [],
   "source": [
    "filter_cols.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reorder the columns for `final`, the DataFrame on which future data analysis will be based."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:31:49.318825Z",
     "iopub.status.busy": "2022-04-03T22:31:49.318487Z",
     "iopub.status.idle": "2022-04-03T22:31:49.328597Z",
     "shell.execute_reply": "2022-04-03T22:31:49.327632Z",
     "shell.execute_reply.started": "2022-04-03T22:31:49.318794Z"
    }
   },
   "outputs": [],
   "source": [
    "final = filter_cols[[\"id\", \"white_rating\", \"black_rating\", \"diff\", \"winner\", \n",
    "                     \"victory_status\", \"num_moves\", \"moves\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:31:51.815766Z",
     "iopub.status.busy": "2022-04-03T22:31:51.814597Z",
     "iopub.status.idle": "2022-04-03T22:31:51.847416Z",
     "shell.execute_reply": "2022-04-03T22:31:51.846474Z",
     "shell.execute_reply.started": "2022-04-03T22:31:51.815689Z"
    }
   },
   "outputs": [],
   "source": [
    "final.sort_values([\"diff\", \"num_moves\"], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:31:55.285635Z",
     "iopub.status.busy": "2022-04-03T22:31:55.285266Z",
     "iopub.status.idle": "2022-04-03T22:31:55.315648Z",
     "shell.execute_reply": "2022-04-03T22:31:55.314741Z",
     "shell.execute_reply.started": "2022-04-03T22:31:55.285592Z"
    }
   },
   "outputs": [],
   "source": [
    "final.sort_values([\"diff\", \"num_moves\"], ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:31:58.433675Z",
     "iopub.status.busy": "2022-04-03T22:31:58.432675Z",
     "iopub.status.idle": "2022-04-03T22:31:58.464947Z",
     "shell.execute_reply": "2022-04-03T22:31:58.463895Z",
     "shell.execute_reply.started": "2022-04-03T22:31:58.433617Z"
    }
   },
   "outputs": [],
   "source": [
    "final.sort_values([\"num_moves\", \"diff\"], ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:32:01.224823Z",
     "iopub.status.busy": "2022-04-03T22:32:01.224498Z",
     "iopub.status.idle": "2022-04-03T22:32:01.239182Z",
     "shell.execute_reply": "2022-04-03T22:32:01.238277Z",
     "shell.execute_reply.started": "2022-04-03T22:32:01.224788Z"
    }
   },
   "outputs": [],
   "source": [
    "upset_for_black = final.query(\"white_rating - black_rating >= 500 and winner == 'black' and victory_status == 'mate'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:32:03.206042Z",
     "iopub.status.busy": "2022-04-03T22:32:03.205062Z",
     "iopub.status.idle": "2022-04-03T22:32:03.221434Z",
     "shell.execute_reply": "2022-04-03T22:32:03.220348Z",
     "shell.execute_reply.started": "2022-04-03T22:32:03.205980Z"
    }
   },
   "outputs": [],
   "source": [
    "upset_for_white = final.query(\"black_rating - white_rating >= 500 and winner == 'white' and victory_status == 'mate'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:38:18.042096Z",
     "iopub.status.busy": "2022-04-03T22:38:18.041674Z",
     "iopub.status.idle": "2022-04-03T22:38:18.067911Z",
     "shell.execute_reply": "2022-04-03T22:38:18.067119Z",
     "shell.execute_reply.started": "2022-04-03T22:38:18.042058Z"
    }
   },
   "outputs": [],
   "source": [
    "upsets = pd.concat([upset_for_black, upset_for_white], axis=0)\n",
    "upsets.reset_index(drop=True, inplace=True)\n",
    "upsets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets export this upsets DataFrame to a csv file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-04-03T22:42:59.187817Z",
     "iopub.status.busy": "2022-04-03T22:42:59.187010Z",
     "iopub.status.idle": "2022-04-03T22:42:59.195185Z",
     "shell.execute_reply": "2022-04-03T22:42:59.194459Z",
     "shell.execute_reply.started": "2022-04-03T22:42:59.187765Z"
    }
   },
   "outputs": [],
   "source": [
    "upsets.to_csv(\"upsets.csv\", header=True, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}