cricket-world-cup2019-[fall-of-wicket-prediction--regression-pyspark-MLlib].ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c2558236",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy  as np\n",
    "import findspark\n",
    "findspark.init()\n",
    "import pyspark\n",
    "from pyspark import SparkContext\n",
    "from pyspark.sql import SQLContext\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import desc\n",
    "from pyspark.sql import functions as F\n",
    "from pyspark.ml.feature import VectorAssembler #feature transformer that merges multiple columns into a vector column\n",
    "from pyspark.ml.classification import LogisticRegression\n",
    "from sklearn.metrics import accuracy_score,precision_score, recall_score, roc_auc_score, f1_score\n",
    "#from pyspark.ml.evaluation import BinaryClassificationEvaluator #evaluate accuracy \n",
    "#from pyspark.context import SparkContext\n",
    "#from pyspark.sql.session import SparkSession\n",
    "sc= SparkContext(appName=\"my_app1\", master= \"local[1]\") \n",
    "sqlContext = SQLContext(sc) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a95fe2fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nSteps - \\n\\n1.Load the scrapped data of matches and player\\n2.Prepare semi finals data set [from the scrapped matches data] - -46th Match in Cricket World Cup 2019\\n3.Prepare Player Bowling Styles Data [from the scrapped players data]\\n3.Generate Data of wicket falls[FoW] pattern for Team - India in Semi Finals\\n4.Create wicket falls data for team India in semifinals by merging bowling style data with semifinal data\\n5.Preprocess data -\\n  - Add new columns -\\n      -balls_faced\\n      -cumruns[total runs a team score on a particular ball]\\n      -cumrunsbyballsfaced[derived variable calculated from cummulative runs / balls faced]- \\n          this value will decrease with more and more unplayed balls during the match\\n      \\n6.Implement logistic regression using pyspark MLlib modules to predict wicket falls.\\n\\n'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\" UseCase2 - This is a demonstration of Spark ML Libraries capabilities \n",
    "for which I have tried to implement Logistic Regression Model to predict wicket fall in World Cup Semi Final\n",
    " (between India and New Zealand) held in Old Trafford, Manchester, England. \"\"\"\n",
    "\n",
    "'''\n",
    "Steps - \n",
    "\n",
    "1.Load the scrapped data of matches and player\n",
    "2.Prepare semi finals data set [from the scrapped matches data] - -46th Match in Cricket World Cup 2019\n",
    "3.Prepare Player Bowling Styles Data [from the scrapped players data]\n",
    "3.Generate Data of wicket falls[FoW] pattern for Team - India in Semi Finals\n",
    "4.Create wicket falls data for team India in semifinals by merging bowling style data with semifinal data\n",
    "5.Preprocess data -\n",
    "  - Add new columns -\n",
    "      -balls_faced\n",
    "      -cumruns[total runs a team score on a particular ball]\n",
    "      -cumrunsbyballsfaced[derived variable calculated from cummulative runs / balls faced]- \n",
    "          this value will decrease with more and more unplayed balls during the match\n",
    "      \n",
    "6.Implement logistic regression using pyspark MLlib modules to predict wicket falls.\n",
    "\n",
    "'''\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "df356835",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Matches data-\n"
     ]
    },
    {
     "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>Match_No</th>\n",
       "      <th>Match_date</th>\n",
       "      <th>Batting_team</th>\n",
       "      <th>Ball_no</th>\n",
       "      <th>Bowler</th>\n",
       "      <th>Batsmen</th>\n",
       "      <th>Runs</th>\n",
       "      <th>commentary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1st match</td>\n",
       "      <td>May 30 2019</td>\n",
       "      <td>ENG</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1st match</td>\n",
       "      <td>May 30 2019</td>\n",
       "      <td>ENG</td>\n",
       "      <td>49.6</td>\n",
       "      <td>Rabada</td>\n",
       "      <td>Plunkett</td>\n",
       "      <td>1</td>\n",
       "      <td>Rabada to Plunkett, 1 run, full again, can onl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1st match</td>\n",
       "      <td>May 30 2019</td>\n",
       "      <td>ENG</td>\n",
       "      <td>49.5</td>\n",
       "      <td>Rabada</td>\n",
       "      <td>Plunkett</td>\n",
       "      <td>2</td>\n",
       "      <td>Rabada to Plunkett, 2 runs, fired in towards t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1st match</td>\n",
       "      <td>May 30 2019</td>\n",
       "      <td>ENG</td>\n",
       "      <td>49.4</td>\n",
       "      <td>Rabada</td>\n",
       "      <td>Archer</td>\n",
       "      <td>1</td>\n",
       "      <td>Rabada to Archer, 1 run, pitched up and whippe...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1st match</td>\n",
       "      <td>May 30 2019</td>\n",
       "      <td>ENG</td>\n",
       "      <td>49.3</td>\n",
       "      <td>Rabada</td>\n",
       "      <td>Archer</td>\n",
       "      <td>2</td>\n",
       "      <td>Rabada to Archer, 2 runs, fullish, 86mph and f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28746</th>\n",
       "      <td>Final</td>\n",
       "      <td>Jul 14 2019</td>\n",
       "      <td>ENG</td>\n",
       "      <td>0.5</td>\n",
       "      <td>Boult</td>\n",
       "      <td>Bairstow</td>\n",
       "      <td>0</td>\n",
       "      <td>Boult to Bairstow, no run, looks to flick, bal...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28747</th>\n",
       "      <td>Final</td>\n",
       "      <td>Jul 14 2019</td>\n",
       "      <td>ENG</td>\n",
       "      <td>0.4</td>\n",
       "      <td>Boult</td>\n",
       "      <td>Bairstow</td>\n",
       "      <td>0</td>\n",
       "      <td>Boult to Bairstow, no run, back of a length, h...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28748</th>\n",
       "      <td>Final</td>\n",
       "      <td>Jul 14 2019</td>\n",
       "      <td>ENG</td>\n",
       "      <td>0.3</td>\n",
       "      <td>Boult</td>\n",
       "      <td>Bairstow</td>\n",
       "      <td>0</td>\n",
       "      <td>Boult to Bairstow, no run, fullish, fourth-stu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28749</th>\n",
       "      <td>Final</td>\n",
       "      <td>Jul 14 2019</td>\n",
       "      <td>ENG</td>\n",
       "      <td>0.2</td>\n",
       "      <td>Boult</td>\n",
       "      <td>Roy</td>\n",
       "      <td>1</td>\n",
       "      <td>Boult to Roy, 1 run, full again, more off-stum...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28750</th>\n",
       "      <td>Final</td>\n",
       "      <td>Jul 14 2019</td>\n",
       "      <td>ENG</td>\n",
       "      <td>0.1</td>\n",
       "      <td>Boult</td>\n",
       "      <td>Roy</td>\n",
       "      <td>0</td>\n",
       "      <td>Boult to Roy, no run, gone first ball? No? Mar...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28751 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Match_No    Match_date Batting_team  Ball_no  Bowler    Batsmen Runs  \\\n",
       "0      1st match   May 30 2019          ENG      NaN     NaN        NaN  NaN   \n",
       "1      1st match   May 30 2019          ENG     49.6  Rabada   Plunkett    1   \n",
       "2      1st match   May 30 2019          ENG     49.5  Rabada   Plunkett    2   \n",
       "3      1st match   May 30 2019          ENG     49.4  Rabada     Archer    1   \n",
       "4      1st match   May 30 2019          ENG     49.3  Rabada     Archer    2   \n",
       "...          ...           ...          ...      ...     ...        ...  ...   \n",
       "28746      Final   Jul 14 2019          ENG      0.5   Boult   Bairstow    0   \n",
       "28747      Final   Jul 14 2019          ENG      0.4   Boult   Bairstow    0   \n",
       "28748      Final   Jul 14 2019          ENG      0.3   Boult   Bairstow    0   \n",
       "28749      Final   Jul 14 2019          ENG      0.2   Boult        Roy    1   \n",
       "28750      Final   Jul 14 2019          ENG      0.1   Boult        Roy    0   \n",
       "\n",
       "                                              commentary  \n",
       "0                                                    NaN  \n",
       "1      Rabada to Plunkett, 1 run, full again, can onl...  \n",
       "2      Rabada to Plunkett, 2 runs, fired in towards t...  \n",
       "3      Rabada to Archer, 1 run, pitched up and whippe...  \n",
       "4      Rabada to Archer, 2 runs, fullish, 86mph and f...  \n",
       "...                                                  ...  \n",
       "28746  Boult to Bairstow, no run, looks to flick, bal...  \n",
       "28747  Boult to Bairstow, no run, back of a length, h...  \n",
       "28748  Boult to Bairstow, no run, fullish, fourth-stu...  \n",
       "28749  Boult to Roy, 1 run, full again, more off-stum...  \n",
       "28750  Boult to Roy, no run, gone first ball? No? Mar...  \n",
       "\n",
       "[28751 rows x 8 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "Players data-\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Team</th>\n",
       "      <th>Player_Name</th>\n",
       "      <th>Playera_Role</th>\n",
       "      <th>Age</th>\n",
       "      <th>Player_Role</th>\n",
       "      <th>Batting</th>\n",
       "      <th>Bowling</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan Squad / Players</td>\n",
       "      <td>Gulbadin Naib</td>\n",
       "      <td>Captain</td>\n",
       "      <td>Captain</td>\n",
       "      <td>Playing role: Bowler</td>\n",
       "      <td>Batting: Right-hand bat</td>\n",
       "      <td>Bowling: Right-arm medium-fast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan Squad / Players</td>\n",
       "      <td>Asghar Afghan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Age: 31 years 117 days</td>\n",
       "      <td>Playing role: Middle-order batsman</td>\n",
       "      <td>Batting: Right-hand bat</td>\n",
       "      <td>Bowling: Right-arm medium-fast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan Squad / Players</td>\n",
       "      <td>Dawlat Zadran</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Age: 31 years 34 days</td>\n",
       "      <td>Playing role: Bowler</td>\n",
       "      <td>Batting: Right-hand bat</td>\n",
       "      <td>Bowling: Right-arm fast-medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan Squad / Players</td>\n",
       "      <td>Hamid Hassan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Age: 31 years 325 days</td>\n",
       "      <td>Playing role: Bowler</td>\n",
       "      <td>Batting: Right-hand bat</td>\n",
       "      <td>Bowling: Right-arm fast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan Squad / Players</td>\n",
       "      <td>Hashmatullah Shahidi</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Age: 24 years 169 days</td>\n",
       "      <td>Playing role: Top-order batsman</td>\n",
       "      <td>Batting: Left-hand bat</td>\n",
       "      <td>Bowling: Right-arm offbreak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>World Cup 2019 Umpires list / Players</td>\n",
       "      <td>Sundaram Ravi</td>\n",
       "      <td>Umpire</td>\n",
       "      <td>Umpire</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>World Cup 2019 Umpires list / Players</td>\n",
       "      <td>Paul Reiffel</td>\n",
       "      <td>Umpire</td>\n",
       "      <td>Umpire</td>\n",
       "      <td>Playing role: Bowler</td>\n",
       "      <td>Batting: Right-hand bat</td>\n",
       "      <td>Bowling: Right-arm fast-medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>World Cup 2019 Umpires list / Players</td>\n",
       "      <td>Rod Tucker</td>\n",
       "      <td>Umpire</td>\n",
       "      <td>Umpire</td>\n",
       "      <td>Batting: Left-hand bat</td>\n",
       "      <td>Bowling: Right-arm medium</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>World Cup 2019 Umpires list / Players</td>\n",
       "      <td>Joel Wilson</td>\n",
       "      <td>Umpire</td>\n",
       "      <td>Umpire</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>World Cup 2019 Umpires list / Players</td>\n",
       "      <td>Paul Wilson</td>\n",
       "      <td>Umpire</td>\n",
       "      <td>Umpire</td>\n",
       "      <td>Batting: Right-hand bat</td>\n",
       "      <td>Bowling: Right-arm fast-medium</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>190 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Team           Player_Name Playera_Role  \\\n",
       "0              Afghanistan Squad / Players         Gulbadin Naib      Captain   \n",
       "1              Afghanistan Squad / Players         Asghar Afghan          NaN   \n",
       "2              Afghanistan Squad / Players         Dawlat Zadran          NaN   \n",
       "3              Afghanistan Squad / Players          Hamid Hassan          NaN   \n",
       "4              Afghanistan Squad / Players  Hashmatullah Shahidi          NaN   \n",
       "..                                     ...                   ...          ...   \n",
       "185  World Cup 2019 Umpires list / Players         Sundaram Ravi       Umpire   \n",
       "186  World Cup 2019 Umpires list / Players          Paul Reiffel       Umpire   \n",
       "187  World Cup 2019 Umpires list / Players            Rod Tucker       Umpire   \n",
       "188  World Cup 2019 Umpires list / Players           Joel Wilson       Umpire   \n",
       "189  World Cup 2019 Umpires list / Players           Paul Wilson       Umpire   \n",
       "\n",
       "                        Age                         Player_Role  \\\n",
       "0                   Captain                Playing role: Bowler   \n",
       "1    Age: 31 years 117 days  Playing role: Middle-order batsman   \n",
       "2     Age: 31 years 34 days                Playing role: Bowler   \n",
       "3    Age: 31 years 325 days                Playing role: Bowler   \n",
       "4    Age: 24 years 169 days     Playing role: Top-order batsman   \n",
       "..                      ...                                 ...   \n",
       "185                  Umpire                                 NaN   \n",
       "186                  Umpire                Playing role: Bowler   \n",
       "187                  Umpire              Batting: Left-hand bat   \n",
       "188                  Umpire                                 NaN   \n",
       "189                  Umpire             Batting: Right-hand bat   \n",
       "\n",
       "                            Batting                         Bowling  \n",
       "0           Batting: Right-hand bat  Bowling: Right-arm medium-fast  \n",
       "1           Batting: Right-hand bat  Bowling: Right-arm medium-fast  \n",
       "2           Batting: Right-hand bat  Bowling: Right-arm fast-medium  \n",
       "3           Batting: Right-hand bat         Bowling: Right-arm fast  \n",
       "4            Batting: Left-hand bat     Bowling: Right-arm offbreak  \n",
       "..                              ...                             ...  \n",
       "185                             NaN                             NaN  \n",
       "186         Batting: Right-hand bat  Bowling: Right-arm fast-medium  \n",
       "187       Bowling: Right-arm medium                             NaN  \n",
       "188                             NaN                             NaN  \n",
       "189  Bowling: Right-arm fast-medium                             NaN  \n",
       "\n",
       "[190 rows x 7 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "'UseCase2.Step1 - Load the scrapped datasets of matches and players'\n",
    "\n",
    "matches_data = pd.read_csv(\"C:\\\\...\\\\Cricket World Cup 2019 Analysis\\\\playersdata\\\\Matches_data.csv\")\n",
    "print(\"Matches data-\")\n",
    "display(matches_data)\n",
    "players_data = pd.read_csv(\"C:\\\\...\\\\Cricket World Cup 2019 Analysis\\\\playersdata\\\\Players_data.csv\")\n",
    "print(\"\\n\\nPlayers data-\")\n",
    "display(players_data)\n",
    "\n",
    "#unstructured players data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7b02d14a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Semi Finals Data of IN vs NZ:-\n"
     ]
    },
    {
     "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>Match_No</th>\n",
       "      <th>Match_date</th>\n",
       "      <th>Batting_team</th>\n",
       "      <th>Bowler</th>\n",
       "      <th>Batsmen</th>\n",
       "      <th>runs</th>\n",
       "      <th>extras_type</th>\n",
       "      <th>over</th>\n",
       "      <th>ball</th>\n",
       "      <th>wicket</th>\n",
       "      <th>datetime</th>\n",
       "      <th>month</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>27078</th>\n",
       "      <td>46</td>\n",
       "      <td>Jul 9-10 2019</td>\n",
       "      <td>NZ</td>\n",
       "      <td>Kumar</td>\n",
       "      <td>Guptill</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-09 20:19:00</td>\n",
       "      <td>Jul</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27079</th>\n",
       "      <td>46</td>\n",
       "      <td>Jul 9-10 2019</td>\n",
       "      <td>NZ</td>\n",
       "      <td>Kumar</td>\n",
       "      <td>Guptill</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-09 20:19:00</td>\n",
       "      <td>Jul</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27080</th>\n",
       "      <td>46</td>\n",
       "      <td>Jul 9-10 2019</td>\n",
       "      <td>NZ</td>\n",
       "      <td>Kumar</td>\n",
       "      <td>Guptill</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-09 20:19:00</td>\n",
       "      <td>Jul</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27081</th>\n",
       "      <td>46</td>\n",
       "      <td>Jul 9-10 2019</td>\n",
       "      <td>NZ</td>\n",
       "      <td>Kumar</td>\n",
       "      <td>Guptill</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-09 20:19:00</td>\n",
       "      <td>Jul</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27082</th>\n",
       "      <td>46</td>\n",
       "      <td>Jul 9-10 2019</td>\n",
       "      <td>NZ</td>\n",
       "      <td>Kumar</td>\n",
       "      <td>Guptill</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-09 20:19:00</td>\n",
       "      <td>Jul</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26730</th>\n",
       "      <td>46</td>\n",
       "      <td>Jul 9-10 2019</td>\n",
       "      <td>NZ</td>\n",
       "      <td>Bumrah</td>\n",
       "      <td>Boult</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-09 20:19:00</td>\n",
       "      <td>Jul</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26731</th>\n",
       "      <td>46</td>\n",
       "      <td>Jul 9-10 2019</td>\n",
       "      <td>NZ</td>\n",
       "      <td>Bumrah</td>\n",
       "      <td>Santner</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-09 20:19:00</td>\n",
       "      <td>Jul</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27084</th>\n",
       "      <td>46</td>\n",
       "      <td>Jul 9-10 2019</td>\n",
       "      <td>INDIA</td>\n",
       "      <td>Neesham</td>\n",
       "      <td>Chahal</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2010-07-09 20:19:00</td>\n",
       "      <td>Jul</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27085</th>\n",
       "      <td>46</td>\n",
       "      <td>Jul 9-10 2019</td>\n",
       "      <td>INDIA</td>\n",
       "      <td>Neesham</td>\n",
       "      <td>Chahal</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-09 20:19:00</td>\n",
       "      <td>Jul</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27086</th>\n",
       "      <td>46</td>\n",
       "      <td>Jul 9-10 2019</td>\n",
       "      <td>INDIA</td>\n",
       "      <td>Neesham</td>\n",
       "      <td>Chahal</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-09 20:19:00</td>\n",
       "      <td>Jul</td>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>619 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Match_No      Match_date Batting_team   Bowler   Batsmen  runs  \\\n",
       "27078        46   Jul 9-10 2019           NZ    Kumar   Guptill     0   \n",
       "27079        46   Jul 9-10 2019           NZ    Kumar   Guptill     0   \n",
       "27080        46   Jul 9-10 2019           NZ    Kumar   Guptill     0   \n",
       "27081        46   Jul 9-10 2019           NZ    Kumar   Guptill     0   \n",
       "27082        46   Jul 9-10 2019           NZ    Kumar   Guptill     0   \n",
       "...         ...             ...          ...      ...       ...   ...   \n",
       "26730        46   Jul 9-10 2019           NZ   Bumrah     Boult     0   \n",
       "26731        46   Jul 9-10 2019           NZ   Bumrah   Santner     1   \n",
       "27084        46   Jul 9-10 2019        INDIA  Neesham    Chahal     0   \n",
       "27085        46   Jul 9-10 2019        INDIA  Neesham    Chahal     0   \n",
       "27086        46   Jul 9-10 2019        INDIA  Neesham    Chahal     4   \n",
       "\n",
       "      extras_type  over  ball  wicket            datetime month date  \n",
       "27078         NaN     0     6       0 2010-07-09 20:19:00   Jul   09  \n",
       "27079         NaN     0     5       0 2010-07-09 20:19:00   Jul   09  \n",
       "27080         NaN     0     4       0 2010-07-09 20:19:00   Jul   09  \n",
       "27081         NaN     0     3       0 2010-07-09 20:19:00   Jul   09  \n",
       "27082         NaN     0     2       0 2010-07-09 20:19:00   Jul   09  \n",
       "...           ...   ...   ...     ...                 ...   ...  ...  \n",
       "26730         NaN    49     2       0 2010-07-09 20:19:00   Jul   09  \n",
       "26731         NaN    49     1       0 2010-07-09 20:19:00   Jul   09  \n",
       "27084         NaN    49     3       1 2010-07-09 20:19:00   Jul   09  \n",
       "27085         NaN    49     2       0 2010-07-09 20:19:00   Jul   09  \n",
       "27086         NaN    49     1       0 2010-07-09 20:19:00   Jul   09  \n",
       "\n",
       "[619 rows x 13 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "'UseCase2.Step2 - Prepare semi finals data set [from the scrapped matches data] - -46th Match in Cricket World Cup 2019'\n",
    " \n",
    "testdf1 = matches_data.drop(['commentary'], axis=1)\n",
    "testdf1 = testdf1.dropna()\n",
    "testdf1['runs'] = testdf1['Runs'].astype(str).str[0]\n",
    "testdf1['extras_type'] = testdf1['Runs'].astype(str).str[1:]\n",
    "testdf1 = testdf1.replace(r'^\\s*$', np.nan, regex=True)\n",
    "\n",
    "#Split by delimiter(.) for Over column and introduce two columns- over and ball  --> \n",
    "testdf1[['over','ball']] = testdf1['Ball_no'].astype(str).str.split('.', expand=True)\n",
    "testdf1['wicket'] = np.where(testdf1['runs'] == \"W\", 1, 0)\n",
    "matchdata = testdf1.drop(['Ball_no', 'Runs'], axis=1)\n",
    "matchdata['over'] = matchdata['over'].astype(np.int64)\n",
    "matchdata['ball'] = matchdata['ball'].astype(np.int64)\n",
    "matchdata['wicket'] = matchdata['wicket'].astype(np.int64)\n",
    "del testdf1\n",
    "\n",
    "matchdata.runs.replace(['W'], ['0'], inplace= True)\n",
    "matchdata[\"runs\"] = matchdata[\"runs\"].astype('int64')\n",
    "matchdata['Match_No'] = matchdata['Match_No'].str[0:5]\n",
    "matchdata['datetime'] = pd.to_datetime(matchdata['Match_date'], dayfirst = True)\n",
    "matchdata['month'] = matchdata['datetime'].apply(lambda x: x.strftime('%b')) \n",
    "matchdata['date'] = matchdata['datetime'].apply(lambda x: x.strftime('%d'))\n",
    "\n",
    "\n",
    "#there were total 48 matches and semi-finals listed on 46th number.\n",
    "matchnumber = {'1st m': 1, '2nd m': 2, '3rd m': 3,'4th m': 4, '5th m': 5, '6th m': 6, '7th m': 7, \n",
    "         '8th m': 8, '9th m': 9, '10th ': 10, '12th ': 12, '13th ': 13, '14th ': 14, '15th ': 15,\n",
    "         '17th ': 17, '19th ': 19, '20th ': 20, '21st ': 21, '22nd ': 22, '23rd ': 23, '24th ': 24,\n",
    "         '25th ': 25, '26th ': 26, '27th ': 27, '28th ': 28, '29th ': 29, '30th ': 30, '31st ': 31,\n",
    "         '32nd ': 32, '33rd ': 33, '34th ': 34, '35th ': 35, '36th ': 36, '37th ': 37, '38th ': 38,\n",
    "         '39th ': 39, '40th ': 40, '41st ': 41, '42nd ': 42, '43rd ': 43, '44th ': 44, '45th ': 45,\n",
    "         '1st S': 46, '2nd S': 47, 'Final': 48} \n",
    "matchdata.Match_No = [matchnumber[i] for i in matchdata.Match_No] \n",
    "\n",
    "semifinal = matchdata.sort_values(['Match_No', 'over'], ascending=[True, True])\n",
    "semifinal = semifinal[semifinal['Match_No'] == 46]\n",
    "print(\"Semi Finals Data of IN vs NZ:-\")\n",
    "display(semifinal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "efd42e46",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bowling style of players-\n"
     ]
    },
    {
     "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>Bowler</th>\n",
       "      <th>BowlingStyle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Kohli</td>\n",
       "      <td>Right-arm medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Sharma</td>\n",
       "      <td>Right-arm offbreak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>Bumrah</td>\n",
       "      <td>Right-arm fast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>Chahal</td>\n",
       "      <td>Legbreak googly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>Dhoni</td>\n",
       "      <td>Right-arm medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>Jadeja</td>\n",
       "      <td>Slow left-arm orthodox</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>Jadhav</td>\n",
       "      <td>Right-arm offbreak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Yadav</td>\n",
       "      <td>Slow left-arm chinama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Kumar</td>\n",
       "      <td>Right-arm medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>Shami</td>\n",
       "      <td>Right-arm fast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Pandya</td>\n",
       "      <td>Right-arm medium-fast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Dhawan</td>\n",
       "      <td>Right-arm offbreak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Shankar</td>\n",
       "      <td>Right-arm medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Williamson</td>\n",
       "      <td>Right-arm offbreak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Blundell</td>\n",
       "      <td>Right-arm offbreak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Boult</td>\n",
       "      <td>Left-arm fast-medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>de Grandhomme</td>\n",
       "      <td>Right-arm medium-fast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>Ferguson</td>\n",
       "      <td>Right-arm fast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Guptill</td>\n",
       "      <td>Right-arm offbreak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>Henry</td>\n",
       "      <td>Right-arm fast-medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Latham</td>\n",
       "      <td>Right-arm medium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>Munro</td>\n",
       "      <td>Right-arm medium-fast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Neesham</td>\n",
       "      <td>Right-arm medium-fast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Nicholls</td>\n",
       "      <td>Right-arm offbreak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Santner</td>\n",
       "      <td>Slow left-arm orthodox</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Sodhi</td>\n",
       "      <td>Legbreak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>Southee</td>\n",
       "      <td>Right-arm medium-fast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>Taylor</td>\n",
       "      <td>Right-arm offbreak</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Bowler            BowlingStyle\n",
       "71           Kohli        Right-arm medium\n",
       "72          Sharma      Right-arm offbreak\n",
       "74          Bumrah          Right-arm fast\n",
       "75          Chahal         Legbreak googly\n",
       "76           Dhoni        Right-arm medium\n",
       "77          Jadeja  Slow left-arm orthodox\n",
       "78          Jadhav      Right-arm offbreak\n",
       "80           Yadav   Slow left-arm chinama\n",
       "81           Kumar        Right-arm medium\n",
       "82           Shami          Right-arm fast\n",
       "83          Pandya   Right-arm medium-fast\n",
       "86          Dhawan      Right-arm offbreak\n",
       "87         Shankar        Right-arm medium\n",
       "88      Williamson      Right-arm offbreak\n",
       "89        Blundell      Right-arm offbreak\n",
       "90           Boult    Left-arm fast-medium\n",
       "91   de Grandhomme   Right-arm medium-fast\n",
       "92        Ferguson          Right-arm fast\n",
       "93         Guptill      Right-arm offbreak\n",
       "94           Henry   Right-arm fast-medium\n",
       "95          Latham        Right-arm medium\n",
       "96           Munro   Right-arm medium-fast\n",
       "97         Neesham   Right-arm medium-fast\n",
       "98        Nicholls      Right-arm offbreak\n",
       "99         Santner  Slow left-arm orthodox\n",
       "100          Sodhi                Legbreak\n",
       "101        Southee   Right-arm medium-fast\n",
       "102         Taylor      Right-arm offbreak"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "'UseCase2.Step3 - Prepare Player Bowling Styles Data [from the scrapped players data]'\n",
    "\n",
    "#extract New Zealand and India Semifinals data to get bowling style of players in these teams.\n",
    "\n",
    "fsem_pinfo =players_data[(players_data['Team'] == 'New Zealand Squad / Players') | (players_data['Team'] == 'India Squad / Players')]\n",
    "fsem_pinfo = fsem_pinfo[['Team', 'Player_Name', 'Bowling']]\n",
    "fsem_pinfo = fsem_pinfo.dropna()\n",
    "fsem_pinfo['Player_Name'] = fsem_pinfo.Player_Name.str.strip()\n",
    "semifinalteams = {'New Zealand Squad / Players': 'NZ', 'India Squad / Players': 'INDIA'} \n",
    "fsem_pinfo.Team = [semifinalteams[i] for i in fsem_pinfo.Team] \n",
    "\n",
    "# Remove rows with string value as \"Notes:\"  since this does not describe bowling type -\n",
    "fsem_pinfo = fsem_pinfo[~fsem_pinfo.Bowling.str.contains(\"Notes:\")]\n",
    "# Remove string \"Bowling:\"  since it is common across all rows -\n",
    "fsem_pinfo['Bowling'] = fsem_pinfo['Bowling'].str.strip('Bowling:')\n",
    "\n",
    "#Remove whitespaces from the column with object data type -\n",
    "fsem_pinfo['Bowling'] = fsem_pinfo['Bowling'].str.strip()\n",
    "fsem_pinfo['Player_Name'] = fsem_pinfo['Player_Name'].str.strip()\n",
    "fsem_pinfo['Team'] = fsem_pinfo['Team'].str.strip()\n",
    "fsem_pinfo.columns = ['Batting_team', 'Bowler1', 'BowlingStyle']\n",
    "fsem_pinfo[['bowler_firstname','bowler_lastname1','bowler_lastname2']] =fsem_pinfo['Bowler1'].astype(str).str.split(' ', expand=True)\n",
    "fsem_pinfo = fsem_pinfo.replace(np.nan, '', regex=True)\n",
    "fsem_pinfo['Bowler'] = fsem_pinfo['bowler_lastname1'].astype(str) + \" \" +  fsem_pinfo['bowler_lastname2']\n",
    "fsem_pinfo = fsem_pinfo[['Bowler', 'BowlingStyle']]\n",
    "fsem_pinfo['Bowler'] = fsem_pinfo.Bowler.str.strip()\n",
    "fsem_pinfo['BowlingStyle'] = fsem_pinfo.BowlingStyle.str.strip()\n",
    "print(\"Bowling style of players-\")\n",
    "display(fsem_pinfo)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "14686d34",
   "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>Bowler</th>\n",
       "      <th>BowlingStyle</th>\n",
       "      <th>over</th>\n",
       "      <th>ball</th>\n",
       "      <th>wicket</th>\n",
       "      <th>runs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Boult</td>\n",
       "      <td>Left-arm fast-medium</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Boult</td>\n",
       "      <td>Left-arm fast-medium</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Boult</td>\n",
       "      <td>Left-arm fast-medium</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Boult</td>\n",
       "      <td>Left-arm fast-medium</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Boult</td>\n",
       "      <td>Left-arm fast-medium</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Ferguson</td>\n",
       "      <td>Right-arm fast</td>\n",
       "      <td>48</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>Ferguson</td>\n",
       "      <td>Right-arm fast</td>\n",
       "      <td>48</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>Neesham</td>\n",
       "      <td>Right-arm medium-fast</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>Neesham</td>\n",
       "      <td>Right-arm medium-fast</td>\n",
       "      <td>49</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>Neesham</td>\n",
       "      <td>Right-arm medium-fast</td>\n",
       "      <td>49</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>310 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Bowler           BowlingStyle  over  ball  wicket  runs\n",
       "5       Boult   Left-arm fast-medium     0     1       0     1\n",
       "4       Boult   Left-arm fast-medium     0     2       0     1\n",
       "3       Boult   Left-arm fast-medium     0     3       0     0\n",
       "2       Boult   Left-arm fast-medium     0     4       0     0\n",
       "1       Boult   Left-arm fast-medium     0     5       0     0\n",
       "..        ...                    ...   ...   ...     ...   ...\n",
       "133  Ferguson         Right-arm fast    48     5       0     1\n",
       "132  Ferguson         Right-arm fast    48     6       1     0\n",
       "248   Neesham  Right-arm medium-fast    49     1       0     4\n",
       "247   Neesham  Right-arm medium-fast    49     2       0     0\n",
       "246   Neesham  Right-arm medium-fast    49     3       1     0\n",
       "\n",
       "[310 rows x 6 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fall of Wicket pattern data(top 20 obs.) - Team India-\n",
      "+------+---------------------+----+----+------+----+\n",
      "|Bowler|BowlingStyle         |over|ball|wicket|runs|\n",
      "+------+---------------------+----+----+------+----+\n",
      "|Boult |Left-arm fast-medium |0   |1   |0     |1   |\n",
      "|Boult |Left-arm fast-medium |0   |2   |0     |1   |\n",
      "|Boult |Left-arm fast-medium |0   |3   |0     |0   |\n",
      "|Boult |Left-arm fast-medium |0   |4   |0     |0   |\n",
      "|Boult |Left-arm fast-medium |0   |5   |0     |0   |\n",
      "|Boult |Left-arm fast-medium |0   |6   |0     |0   |\n",
      "|Henry |Right-arm fast-medium|1   |1   |0     |0   |\n",
      "|Henry |Right-arm fast-medium|1   |2   |0     |2   |\n",
      "|Henry |Right-arm fast-medium|1   |3   |1     |0   |\n",
      "|Henry |Right-arm fast-medium|1   |4   |0     |0   |\n",
      "|Henry |Right-arm fast-medium|1   |5   |0     |1   |\n",
      "|Henry |Right-arm fast-medium|1   |6   |0     |0   |\n",
      "|Boult |Left-arm fast-medium |2   |1   |0     |0   |\n",
      "|Boult |Left-arm fast-medium |2   |2   |0     |0   |\n",
      "|Boult |Left-arm fast-medium |2   |3   |0     |0   |\n",
      "|Boult |Left-arm fast-medium |2   |4   |1     |0   |\n",
      "|Boult |Left-arm fast-medium |2   |5   |0     |0   |\n",
      "|Boult |Left-arm fast-medium |2   |6   |0     |0   |\n",
      "|Henry |Right-arm fast-medium|3   |1   |1     |0   |\n",
      "|Henry |Right-arm fast-medium|3   |2   |0     |0   |\n",
      "+------+---------------------+----+----+------+----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "None"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Bowler: string (nullable = true)\n",
      " |-- BowlingStyle: string (nullable = true)\n",
      " |-- over: long (nullable = true)\n",
      " |-- ball: long (nullable = true)\n",
      " |-- wicket: long (nullable = true)\n",
      " |-- runs: long (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "\" UseCase2.Step4 - Create wicket falls data for team India in semifinals by merging bowling style data with semifinal data\"\n",
    "\n",
    "#Batting team - INDIA - wicket fall data \n",
    "semifinal_BT1 = semifinal[semifinal['Batting_team'] == 'INDIA']\n",
    "wicketfalldata = pd.merge(fsem_pinfo, semifinal_BT1[['Bowler', 'over', 'ball', 'wicket', 'runs']], on='Bowler')\n",
    "wicketfalldata_IND  = wicketfalldata.sort_values(['over', 'ball'], ascending=[True, True])\n",
    "display(wicketfalldata_IND) # this contains overwise details of bowler with their wickets taken and runs conceded.\n",
    "#wicketfalldata_IND.to_csv(\"wicketfalldata_IND.csv\")\n",
    "\n",
    "print(\"Fall of Wicket pattern data(top 20 obs.) - Team India-\")\n",
    "wktfall_data= sqlContext.createDataFrame(wicketfalldata_IND)\n",
    "wktfall_data.createOrReplaceTempView(\"wicketfall_IND\")\n",
    "display(wktfall_data.show(truncate=False))\n",
    "wktfall_data.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a5ef440d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------+----+----+------+----+----------------------+--------------+---------------------+---------------------+--------------------+\n",
      "|BowlingStyle         |over|ball|wicket|runs|Slow left-arm orthodox|Right-arm fast|Right-arm medium-fast|Right-arm fast-medium|Left-arm fast-medium|\n",
      "+---------------------+----+----+------+----+----------------------+--------------+---------------------+---------------------+--------------------+\n",
      "|Left-arm fast-medium |0   |1   |0     |1   |0                     |0             |0                    |0                    |1                   |\n",
      "|Left-arm fast-medium |0   |2   |0     |1   |0                     |0             |0                    |0                    |1                   |\n",
      "|Left-arm fast-medium |0   |3   |0     |0   |0                     |0             |0                    |0                    |1                   |\n",
      "|Left-arm fast-medium |0   |4   |0     |0   |0                     |0             |0                    |0                    |1                   |\n",
      "|Left-arm fast-medium |0   |5   |0     |0   |0                     |0             |0                    |0                    |1                   |\n",
      "|Left-arm fast-medium |0   |6   |0     |0   |0                     |0             |0                    |0                    |1                   |\n",
      "|Right-arm fast-medium|1   |1   |0     |0   |0                     |0             |0                    |1                    |0                   |\n",
      "|Right-arm fast-medium|1   |2   |0     |2   |0                     |0             |0                    |1                    |0                   |\n",
      "|Right-arm fast-medium|1   |3   |1     |0   |0                     |0             |0                    |1                    |0                   |\n",
      "|Right-arm fast-medium|1   |4   |0     |0   |0                     |0             |0                    |1                    |0                   |\n",
      "|Right-arm fast-medium|1   |5   |0     |1   |0                     |0             |0                    |1                    |0                   |\n",
      "|Right-arm fast-medium|1   |6   |0     |0   |0                     |0             |0                    |1                    |0                   |\n",
      "|Left-arm fast-medium |2   |1   |0     |0   |0                     |0             |0                    |0                    |1                   |\n",
      "|Left-arm fast-medium |2   |2   |0     |0   |0                     |0             |0                    |0                    |1                   |\n",
      "|Left-arm fast-medium |2   |3   |0     |0   |0                     |0             |0                    |0                    |1                   |\n",
      "|Left-arm fast-medium |2   |4   |1     |0   |0                     |0             |0                    |0                    |1                   |\n",
      "|Left-arm fast-medium |2   |5   |0     |0   |0                     |0             |0                    |0                    |1                   |\n",
      "|Left-arm fast-medium |2   |6   |0     |0   |0                     |0             |0                    |0                    |1                   |\n",
      "|Right-arm fast-medium|3   |1   |1     |0   |0                     |0             |0                    |1                    |0                   |\n",
      "|Right-arm fast-medium|3   |2   |0     |0   |0                     |0             |0                    |1                    |0                   |\n",
      "+---------------------+----+----+------+----+----------------------+--------------+---------------------+---------------------+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "None"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- BowlingStyle: string (nullable = true)\n",
      " |-- over: long (nullable = true)\n",
      " |-- ball: long (nullable = true)\n",
      " |-- wicket: long (nullable = true)\n",
      " |-- runs: long (nullable = true)\n",
      " |-- Slow left-arm orthodox: integer (nullable = false)\n",
      " |-- Right-arm fast: integer (nullable = false)\n",
      " |-- Right-arm medium-fast: integer (nullable = false)\n",
      " |-- Right-arm fast-medium: integer (nullable = false)\n",
      " |-- Left-arm fast-medium: integer (nullable = false)\n",
      "\n",
      "\n",
      "Preprocessed wicket falls data-\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+\n",
      "|cumrunsbyballsfaced|Slow left-arm orthodox|Right-arm fast|Right-arm medium-fast|Right-arm fast-medium|Left-arm fast-medium|wicket|\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+\n",
      "|1.0                |0                     |0             |0                    |0                    |1                   |0     |\n",
      "|1.0                |0                     |0             |0                    |0                    |1                   |0     |\n",
      "|0.6666666666666666 |0                     |0             |0                    |0                    |1                   |0     |\n",
      "|0.5                |0                     |0             |0                    |0                    |1                   |0     |\n",
      "|0.4                |0                     |0             |0                    |0                    |1                   |0     |\n",
      "|0.3333333333333333 |0                     |0             |0                    |0                    |1                   |0     |\n",
      "|0.2857142857142857 |0                     |0             |0                    |1                    |0                   |0     |\n",
      "|0.5                |0                     |0             |0                    |1                    |0                   |0     |\n",
      "|0.4444444444444444 |0                     |0             |0                    |1                    |0                   |1     |\n",
      "|0.4                |0                     |0             |0                    |1                    |0                   |0     |\n",
      "|0.45454545454545453|0                     |0             |0                    |1                    |0                   |0     |\n",
      "|0.4166666666666667 |0                     |0             |0                    |1                    |0                   |0     |\n",
      "|0.38461538461538464|0                     |0             |0                    |0                    |1                   |0     |\n",
      "|0.35714285714285715|0                     |0             |0                    |0                    |1                   |0     |\n",
      "|0.3333333333333333 |0                     |0             |0                    |0                    |1                   |0     |\n",
      "|0.3125             |0                     |0             |0                    |0                    |1                   |1     |\n",
      "|0.29411764705882354|0                     |0             |0                    |0                    |1                   |0     |\n",
      "|0.2777777777777778 |0                     |0             |0                    |0                    |1                   |0     |\n",
      "|0.2631578947368421 |0                     |0             |0                    |1                    |0                   |1     |\n",
      "|0.25               |0                     |0             |0                    |1                    |0                   |0     |\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+\n",
      "only showing top 20 rows\n",
      "\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+-----------------------------------+\n",
      "|cumrunsbyballsfaced|Slow left-arm orthodox|Right-arm fast|Right-arm medium-fast|Right-arm fast-medium|Left-arm fast-medium|wicket|features                           |\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+-----------------------------------+\n",
      "|1.0                |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[1.0,1.0])                |\n",
      "|1.0                |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[1.0,1.0])                |\n",
      "|0.6666666666666666 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.6666666666666666,1.0]) |\n",
      "|0.5                |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.5,1.0])                |\n",
      "|0.4                |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.4,1.0])                |\n",
      "|0.3333333333333333 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.3333333333333333,1.0]) |\n",
      "|0.2857142857142857 |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.2857142857142857,1.0]) |\n",
      "|0.5                |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.5,1.0])                |\n",
      "|0.4444444444444444 |0                     |0             |0                    |1                    |0                   |1     |(6,[0,4],[0.4444444444444444,1.0]) |\n",
      "|0.4                |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.4,1.0])                |\n",
      "|0.45454545454545453|0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.45454545454545453,1.0])|\n",
      "|0.4166666666666667 |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.4166666666666667,1.0]) |\n",
      "|0.38461538461538464|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.38461538461538464,1.0])|\n",
      "|0.35714285714285715|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.35714285714285715,1.0])|\n",
      "|0.3333333333333333 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.3333333333333333,1.0]) |\n",
      "|0.3125             |0                     |0             |0                    |0                    |1                   |1     |(6,[0,5],[0.3125,1.0])             |\n",
      "|0.29411764705882354|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.29411764705882354,1.0])|\n",
      "|0.2777777777777778 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.2777777777777778,1.0]) |\n",
      "|0.2631578947368421 |0                     |0             |0                    |1                    |0                   |1     |(6,[0,4],[0.2631578947368421,1.0]) |\n",
      "|0.25               |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.25,1.0])               |\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+-----------------------------------+\n",
      "only showing top 20 rows\n",
      "\n",
      "\n",
      "Observations from Train data set:\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+-----------------------------------+\n",
      "|cumrunsbyballsfaced|Slow left-arm orthodox|Right-arm fast|Right-arm medium-fast|Right-arm fast-medium|Left-arm fast-medium|wicket|features                           |\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+-----------------------------------+\n",
      "|0.17647058823529413|0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.17647058823529413,1.0])|\n",
      "|0.18181818181818182|0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.18181818181818182,1.0])|\n",
      "|0.1935483870967742 |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.1935483870967742,1.0]) |\n",
      "|0.2                |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.2,1.0])                |\n",
      "|0.20833333333333334|0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.20833333333333334,1.0])|\n",
      "|0.21739130434782608|0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.21739130434782608,1.0])|\n",
      "|0.22727272727272727|0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.22727272727272727,1.0])|\n",
      "|0.23255813953488372|0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.23255813953488372,1.0])|\n",
      "|0.23809523809523808|0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.23809523809523808,1.0])|\n",
      "|0.24390243902439024|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.24390243902439024,1.0])|\n",
      "|0.25               |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.25,1.0])               |\n",
      "|0.25               |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.25,1.0])               |\n",
      "|0.2631578947368421 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.2631578947368421,1.0]) |\n",
      "|0.2631578947368421 |0                     |0             |0                    |1                    |0                   |1     |(6,[0,4],[0.2631578947368421,1.0]) |\n",
      "|0.2653061224489796 |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.2653061224489796,1.0]) |\n",
      "|0.2708333333333333 |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.2708333333333333,1.0]) |\n",
      "|0.2765957446808511 |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.2765957446808511,1.0]) |\n",
      "|0.2777777777777778 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.2777777777777778,1.0]) |\n",
      "|0.2777777777777778 |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.2777777777777778,1.0]) |\n",
      "|0.2857142857142857 |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.2857142857142857,1.0]) |\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+-----------------------------------+\n",
      "only showing top 20 rows\n",
      "\n",
      "\n",
      "Observations from Test data set:\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+-----------------------------------+\n",
      "|cumrunsbyballsfaced|Slow left-arm orthodox|Right-arm fast|Right-arm medium-fast|Right-arm fast-medium|Left-arm fast-medium|wicket|features                           |\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+-----------------------------------+\n",
      "|0.1875             |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.1875,1.0])             |\n",
      "|0.20689655172413793|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.20689655172413793,1.0])|\n",
      "|0.21428571428571427|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.21428571428571427,1.0])|\n",
      "|0.2222222222222222 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.2222222222222222,1.0]) |\n",
      "|0.22727272727272727|0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.22727272727272727,1.0])|\n",
      "|0.23076923076923078|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.23076923076923078,1.0])|\n",
      "|0.23809523809523808|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.23809523809523808,1.0])|\n",
      "|0.24               |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.24,1.0])               |\n",
      "|0.24528301886792453|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.24528301886792453,1.0])|\n",
      "|0.25               |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.25,1.0])               |\n",
      "|0.2549019607843137 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.2549019607843137,1.0]) |\n",
      "|0.2564102564102564 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.2564102564102564,1.0]) |\n",
      "|0.26               |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.26,1.0])               |\n",
      "|0.26666666666666666|0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.26666666666666666,1.0])|\n",
      "|0.2702702702702703 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.2702702702702703,1.0]) |\n",
      "|0.2826086956521739 |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.2826086956521739,1.0]) |\n",
      "|0.2857142857142857 |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.2857142857142857,1.0]) |\n",
      "|0.29411764705882354|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.29411764705882354,1.0])|\n",
      "|0.34545454545454546|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.34545454545454546,1.0])|\n",
      "|0.3787878787878788 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.3787878787878788,1.0]) |\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+-----------------------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "'UseCase2.Step5-Preprocess data'\n",
    "\n",
    "#Create Dummies for Bowling Type -\n",
    "bowlingstyle_categories = wktfall_data.select(\"BowlingStyle\").distinct().rdd.flatMap(lambda x: x).collect()\n",
    "\n",
    "bowlingstyle_binary_dummies = [F.when(F.col(\"BowlingStyle\") == cat, 1).otherwise(0).alias(cat)\n",
    "         for cat in bowlingstyle_categories]\n",
    "\n",
    "wktfall_data1 = wktfall_data.select(\"BowlingStyle\", \"over\", \"ball\", \"wicket\", \"runs\", *bowlingstyle_binary_dummies)\n",
    "display(wktfall_data1.show(truncate=False))\n",
    "wktfall_data1.printSchema()\n",
    "\n",
    "#create three new columns- balls_faced, cumruns[total runs a team score on a particular ball], cumrunperball[derived variable calculated from cummulative runs / balls faced].\n",
    "\n",
    "#Convert to pandas dataframe for ease in computations\n",
    "wktfall_data2 = wktfall_data1.toPandas()\n",
    "#Create a new column of balls faced - \n",
    "wktfall_data2['balls_faced'] = range(1, wktfall_data2.shape[0]+1)\n",
    "#Create a new columns as cummruns i.e.total team score on a particular ball  -\n",
    "wktfall_data2['cumruns'] = wktfall_data2['runs'].cumsum(axis = 0)\n",
    "wktfall_data2['cumrunsbyballsfaced'] = wktfall_data2['cumruns']/wktfall_data2['balls_faced']\n",
    "#this will decrease with more and more unplayed balls during the match\n",
    "\n",
    "#subset the above data to obtain the needed information/features.\n",
    "wktfall_data3 = wktfall_data2[['cumrunsbyballsfaced', 'Slow left-arm orthodox', 'Right-arm fast', 'Right-arm medium-fast', 'Right-arm fast-medium', 'Left-arm fast-medium', 'wicket']]\n",
    "wktfall_data3.head()\n",
    "wktfall_data4 = sqlContext.createDataFrame(wktfall_data3)\n",
    "print(\"\\nPreprocessed wicket falls data-\")\n",
    "wktfall_data4.show(truncate=False)\n",
    "\n",
    "\n",
    "#Use Spark ML libraries to generate input features for logistic reg model\n",
    "in_features = wktfall_data4.columns[:-1]\n",
    "assembler = VectorAssembler(inputCols = in_features,outputCol=\"features\") \n",
    "##Vector Assembler is a feature transformer that merges multiple columns into a vector column\n",
    "wktfall_pred = assembler.transform(wktfall_data4)\n",
    "wktfall_pred.show(truncate=False)\n",
    "\n",
    "\n",
    "#Divide the wicket fall data in traindata and testdata for building model on traindata\n",
    "traindata, testdata = wktfall_pred.randomSplit([0.7, 0.3], seed = 7)\n",
    "print(\"\\nObservations from Train data set:\")\n",
    "traindata.show(truncate=False)\n",
    "print(\"\\nObservations from Test data set:\")\n",
    "testdata.show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "11b715ab",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Coefficients: [1.3974923141131208,-0.4768580328055,-0.3803401638162682,-1.0543037612417197,0.09289639017145575,-0.3770530370185253]\n",
      "Intercept: -3.349182963318684\n",
      "Fitted Model accuracy on Train Set: 0.9528301886792453\n",
      "\n",
      "Predictions on test data set-\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+-----------------------------------+----------------------------------------+-----------------------------------------+----------+\n",
      "|cumrunsbyballsfaced|Slow left-arm orthodox|Right-arm fast|Right-arm medium-fast|Right-arm fast-medium|Left-arm fast-medium|wicket|features                           |rawPrediction                           |probability                              |prediction|\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+-----------------------------------+----------------------------------------+-----------------------------------------+----------+\n",
      "|0.1875             |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.1875,1.0])             |[2.994256764251018,-2.994256764251018]  |[0.9523139911753348,0.04768600882466523] |0.0       |\n",
      "|0.20689655172413793|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.20689655172413793,1.0])|[3.4370996594862184,-3.4370996594862184]|[0.9688440873659794,0.03115591263402062] |0.0       |\n",
      "|0.21428571428571427|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.21428571428571427,1.0])|[3.4267733615986833,-3.4267733615986833]|[0.9685308721501352,0.031469127849864775]|0.0       |\n",
      "|0.2222222222222222 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.2222222222222222,1.0]) |[3.4156821527565158,-3.4156821527565158]|[0.968191062810988,0.03180893718901201]  |0.0       |\n",
      "|0.22727272727272727|0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.22727272727272727,1.0])|[2.9386746835760644,-2.9386746835760644]|[0.9497254846451268,0.05027451535487315] |0.0       |\n",
      "|0.23076923076923078|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.23076923076923078,1.0])|[3.403737774003412,-3.403737774003412]  |[0.9678211459712183,0.032178854028781734]|0.0       |\n",
      "|0.23809523809523808|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.23809523809523808,1.0])|[3.3934997350721803,-3.3934997350721803]|[0.9675007672031505,0.03249923279684952] |0.0       |\n",
      "|0.24               |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.24,1.0])               |[3.39083784495006,-3.39083784495006]    |[0.9674169650682078,0.032583034931792154]|0.0       |\n",
      "|0.24528301886792453|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.24528301886792453,1.0])|[3.383454866686821,-3.383454866686821]  |[0.9671834385765327,0.03281656142346734] |0.0       |\n",
      "|0.25               |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.25,1.0])               |[2.906913494618948,-2.906913494618948]  |[0.9481871393998539,0.051812860600146116]|0.0       |\n",
      "|0.2549019607843137 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.2549019607843137,1.0]) |[3.3700124692887665,-3.3700124692887665]|[0.9667540919335013,0.03324590806649874] |0.0       |\n",
      "|0.2564102564102564 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.2564102564102564,1.0]) |[3.3679046377441013,-3.3679046377441013]|[0.96668627823307,0.03331372176693004]   |0.0       |\n",
      "|0.26               |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.26,1.0])               |[3.3628879986677975,-3.3628879986677975]|[0.9665243440208628,0.03347565597913715] |0.0       |\n",
      "|0.26666666666666666|0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.26666666666666666,1.0])|[2.883621956050396,-2.883621956050396]  |[0.9470308477650241,0.05296915223497589] |0.0       |\n",
      "|0.2702702702702703 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.2702702702702703,1.0]) |[3.3485353749012305,-3.3485353749012305]|[0.9660568420590248,0.033943157940975244]|0.0       |\n",
      "|0.2826086956521739 |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.2826086956521739,1.0]) |[2.861343093071781,-2.861343093071781]  |[0.9459020685726551,0.05409793142734487] |0.0       |\n",
      "|0.2857142857142857 |0                     |0             |0                    |1                    |0                   |0     |(6,[0,4],[0.2857142857142857,1.0]) |[2.8570030548291934,-2.8570030548291934]|[0.945679552706821,0.054320447293178975] |0.0       |\n",
      "|0.29411764705882354|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.29411764705882354,1.0])|[3.3152088491274676,-3.3152088491274676]|[0.9649468942450254,0.035053105754974556]|0.0       |\n",
      "|0.34545454545454546|0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.34545454545454546,1.0])|[3.24346592818904,-3.24346592818904]    |[0.9624376088701165,0.037562391129883466]|0.0       |\n",
      "|0.3787878787878788 |0                     |0             |0                    |0                    |1                   |0     |(6,[0,5],[0.3787878787878788,1.0]) |[3.196882851051936,-3.196882851051936]  |[0.9607168047214071,0.03928319527859292] |0.0       |\n",
      "+-------------------+----------------------+--------------+---------------------+---------------------+--------------------+------+-----------------------------------+----------------------------------------+-----------------------------------------+----------+\n",
      "only showing top 20 rows\n",
      "\n",
      "Test Set metrics:\n",
      " accuracy: 1.0\n",
      " precision:0.0\n",
      " recall:0.0\n",
      " f1 score:0.0\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\ag89382\\appdata\\local\\programs\\python\\python37\\deepenv\\lib\\site-packages\\sklearn\\metrics\\_classification.py:1465: UndefinedMetricWarning: F-score is ill-defined and being set to 0.0 due to no true nor predicted samples. Use `zero_division` parameter to control this behavior.\n",
      "  average, \"true nor predicted\", 'F-score is', len(true_sum)\n",
      "c:\\users\\ag89382\\appdata\\local\\programs\\python\\python37\\deepenv\\lib\\site-packages\\sklearn\\metrics\\_classification.py:1221: UndefinedMetricWarning: Precision is ill-defined and being set to 0.0 due to no predicted samples. Use `zero_division` parameter to control this behavior.\n",
      "  _warn_prf(average, modifier, msg_start, len(result))\n",
      "c:\\users\\ag89382\\appdata\\local\\programs\\python\\python37\\deepenv\\lib\\site-packages\\sklearn\\metrics\\_classification.py:1221: UndefinedMetricWarning: Recall is ill-defined and being set to 0.0 due to no true samples. Use `zero_division` parameter to control this behavior.\n",
      "  _warn_prf(average, modifier, msg_start, len(result))\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "' Summary -\\n-The data ingested in our model is highly imbalanced.\\n-Recall and Precision are zero which implies that there are no true positives in test set. \\n-We need to first improve the imbalancing and tune model for improvements.\\n'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'UseCase2.Step6-Implement logistic regression using pyspark MLlib modules to predict wicket falls.'\n",
    "\n",
    "#Apply logistic reg function for implementing logistic reg model\n",
    "logmodel = LogisticRegression(featuresCol=\"features\", labelCol=\"wicket\")\n",
    "\n",
    "# Fit the logistic regression model - \n",
    "model=logmodel.fit(traindata)\n",
    "\n",
    "# Get the coefficients and intercept for logistic regression\n",
    "print(\"Coefficients: \" + str(model.coefficients))\n",
    "print(\"Intercept: \" + str(model.intercept))\n",
    "\n",
    "\n",
    "trainingSummary = model.summary \n",
    "accuracy = trainingSummary.accuracy\n",
    "print(\"Fitted Model accuracy on Train Set: \" + str(accuracy))\n",
    "\n",
    "\n",
    "\n",
    "#Predict on test data -\n",
    "print(\"\\nPredictions on test data set-\")\n",
    "predict_test=model.transform(testdata)\n",
    "predict_test.show(truncate=False)\n",
    "\n",
    "\n",
    "test_acc = predict_test.select(\"wicket\", \"prediction\")\n",
    "\n",
    "test_acc_arr = np.array((test_acc.collect()))\n",
    "acc = accuracy_score(test_acc_arr[:,0], test_acc_arr[:,1])\n",
    "f1 = f1_score(test_acc_arr[:,0], test_acc_arr[:,1])\n",
    "precision = precision_score(test_acc_arr[:,0], test_acc_arr[:,1])\n",
    "recall = recall_score(test_acc_arr[:,0], test_acc_arr[:,1])\n",
    "#auc = roc_auc_score(predictionAndTargetNumpy[:,0], predictionAndTargetNumpy[:,1])\n",
    "print(\"Test Set metrics:\\n accuracy: \" + str(acc) + \"\\n precision:\" + str(precision) \n",
    "     + \"\\n recall:\" + str(recall) + \"\\n f1 score:\" + str(f1))\n",
    "\n",
    "\"\"\" Summary -\n",
    "-The data ingested in our model is highly imbalanced.\n",
    "-Recall and Precision are zero which implies that there are no true positives in test set. \n",
    "-We need to first improve the imbalancing and tune model for improvements.\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a4ed6bb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#References:\n",
    "#https://spark.apache.org/docs/3.1.1/api/python/reference/api/pyspark.ml.feature.VectorAssembler.html\n",
    "#https://spark.apache.org/docs/latest/api/python/reference/api/pyspark.ml.evaluation.BinaryClassificationEvaluator.html\n",
    "#https://scikit-learn.org/stable/modules/generated/sklearn.metrics.confusion_matrix.html"
   ]
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}