t20_data_preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5bc95a08",
   "metadata": {},
   "source": [
    "<h1 align=\"center\"> T20 World Cup Cricket Data Pre Processing </h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "4de3844a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import necessary libraries\n",
    "import pandas as pd\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c64f424a",
   "metadata": {},
   "source": [
    "<h4 style=\"color:blue\">(1) Process Match Results</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "cae9c35a",
   "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>team1</th>\n",
       "      <th>team2</th>\n",
       "      <th>winner</th>\n",
       "      <th>margin</th>\n",
       "      <th>ground</th>\n",
       "      <th>matchDate</th>\n",
       "      <th>scorecard</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Namibia</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>55 runs</td>\n",
       "      <td>Geelong</td>\n",
       "      <td>Oct 16, 2022</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>U.A.E.</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>3 wickets</td>\n",
       "      <td>Geelong</td>\n",
       "      <td>Oct 16, 2022</td>\n",
       "      <td>T20I # 1825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Scotland</td>\n",
       "      <td>West Indies</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>42 runs</td>\n",
       "      <td>Hobart</td>\n",
       "      <td>Oct 17, 2022</td>\n",
       "      <td>T20I # 1826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ireland</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>31 runs</td>\n",
       "      <td>Hobart</td>\n",
       "      <td>Oct 17, 2022</td>\n",
       "      <td>T20I # 1828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Namibia</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>5 wickets</td>\n",
       "      <td>Geelong</td>\n",
       "      <td>Oct 18, 2022</td>\n",
       "      <td>T20I # 1830</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         team1        team2       winner     margin   ground     matchDate  \\\n",
       "0      Namibia    Sri Lanka      Namibia    55 runs  Geelong  Oct 16, 2022   \n",
       "1  Netherlands       U.A.E.  Netherlands  3 wickets  Geelong  Oct 16, 2022   \n",
       "2     Scotland  West Indies     Scotland    42 runs   Hobart  Oct 17, 2022   \n",
       "3      Ireland     Zimbabwe     Zimbabwe    31 runs   Hobart  Oct 17, 2022   \n",
       "4      Namibia  Netherlands  Netherlands  5 wickets  Geelong  Oct 18, 2022   \n",
       "\n",
       "     scorecard  \n",
       "0  T20I # 1823  \n",
       "1  T20I # 1825  \n",
       "2  T20I # 1826  \n",
       "3  T20I # 1828  \n",
       "4  T20I # 1830  "
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('t20_json_files/t20_wc_match_results.json') as f:\n",
    "    data = json.load(f)\n",
    "\n",
    "df_match = pd.DataFrame(data[0]['matchSummary'])\n",
    "df_match.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "691f36ec",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45, 7)"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_match.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b865576",
   "metadata": {},
   "source": [
    "**Use scorecard as a match id to link with other tables**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "1f2c4e9e",
   "metadata": {
    "scrolled": true
   },
   "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>team1</th>\n",
       "      <th>team2</th>\n",
       "      <th>winner</th>\n",
       "      <th>margin</th>\n",
       "      <th>ground</th>\n",
       "      <th>matchDate</th>\n",
       "      <th>match_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Namibia</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>55 runs</td>\n",
       "      <td>Geelong</td>\n",
       "      <td>Oct 16, 2022</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>U.A.E.</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>3 wickets</td>\n",
       "      <td>Geelong</td>\n",
       "      <td>Oct 16, 2022</td>\n",
       "      <td>T20I # 1825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Scotland</td>\n",
       "      <td>West Indies</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>42 runs</td>\n",
       "      <td>Hobart</td>\n",
       "      <td>Oct 17, 2022</td>\n",
       "      <td>T20I # 1826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ireland</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>31 runs</td>\n",
       "      <td>Hobart</td>\n",
       "      <td>Oct 17, 2022</td>\n",
       "      <td>T20I # 1828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Namibia</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>5 wickets</td>\n",
       "      <td>Geelong</td>\n",
       "      <td>Oct 18, 2022</td>\n",
       "      <td>T20I # 1830</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         team1        team2       winner     margin   ground     matchDate  \\\n",
       "0      Namibia    Sri Lanka      Namibia    55 runs  Geelong  Oct 16, 2022   \n",
       "1  Netherlands       U.A.E.  Netherlands  3 wickets  Geelong  Oct 16, 2022   \n",
       "2     Scotland  West Indies     Scotland    42 runs   Hobart  Oct 17, 2022   \n",
       "3      Ireland     Zimbabwe     Zimbabwe    31 runs   Hobart  Oct 17, 2022   \n",
       "4      Namibia  Netherlands  Netherlands  5 wickets  Geelong  Oct 18, 2022   \n",
       "\n",
       "      match_id  \n",
       "0  T20I # 1823  \n",
       "1  T20I # 1825  \n",
       "2  T20I # 1826  \n",
       "3  T20I # 1828  \n",
       "4  T20I # 1830  "
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_match.rename({'scorecard': 'match_id'}, axis = 1, inplace = True)\n",
    "df_match.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2638d62",
   "metadata": {},
   "source": [
    "**Create a match ids dictionary that maps team names to a unique match id. This will be useful later on to link with other tables**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "fe3721af",
   "metadata": {},
   "outputs": [],
   "source": [
    "match_ids_dict = {}\n",
    "\n",
    "for index, row in df_match.iterrows():\n",
    "    key1 = row['team1'] + ' Vs ' + row['team2']\n",
    "    key2 = row['team2'] + ' Vs ' + row['team1']\n",
    "    match_ids_dict[key1] = row['match_id']\n",
    "    match_ids_dict[key2] = row['match_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "8916b21f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_match.to_csv('t20_csv_files/dim_match_summary.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c48642e6",
   "metadata": {},
   "source": [
    "<h4 style=\"color:blue\">(2) Process Batting Summary</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "5b815da6",
   "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>match</th>\n",
       "      <th>teamInnings</th>\n",
       "      <th>battingPos</th>\n",
       "      <th>batsmanName</th>\n",
       "      <th>dismissal</th>\n",
       "      <th>runs</th>\n",
       "      <th>balls</th>\n",
       "      <th>4s</th>\n",
       "      <th>6s</th>\n",
       "      <th>SR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>1</td>\n",
       "      <td>Michael van Lingen</td>\n",
       "      <td>c Pramod Madushan b Chameera</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>2</td>\n",
       "      <td>Divan la Cock</td>\n",
       "      <td>c Shanaka b Pramod Madushan</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>3</td>\n",
       "      <td>Jan Nicol Loftie-Eaton</td>\n",
       "      <td>c â€ Mendis b Karunaratne</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>166.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>4</td>\n",
       "      <td>Stephan Baard</td>\n",
       "      <td>c DM de Silva b Pramod Madushan</td>\n",
       "      <td>26</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>108.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>5</td>\n",
       "      <td>Gerhard Erasmus(c)</td>\n",
       "      <td>c Gunathilaka b PWH de Silva</td>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>83.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>6</td>\n",
       "      <td>Jan Frylinck</td>\n",
       "      <td>run out (Gunathilaka/â€ Mendis)</td>\n",
       "      <td>44</td>\n",
       "      <td>28</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>157.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>7</td>\n",
       "      <td>David Wiese</td>\n",
       "      <td>c â€ Mendis b Theekshana</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>8</td>\n",
       "      <td>JJ Smit</td>\n",
       "      <td></td>\n",
       "      <td>31</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>193.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>1</td>\n",
       "      <td>Pathum Nissanka</td>\n",
       "      <td>c Smit b Shikongo</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>90.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>2</td>\n",
       "      <td>Kusal Mendisâ€</td>\n",
       "      <td>c â€ Green b Wiese</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>3</td>\n",
       "      <td>Dhananjaya de Silva</td>\n",
       "      <td>c Shikongo b Frylinck</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>109.09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   match teamInnings  battingPos             batsmanName  \\\n",
       "0   Namibia Vs Sri Lanka     Namibia           1      Michael van Lingen   \n",
       "1   Namibia Vs Sri Lanka     Namibia           2           Divan la Cock   \n",
       "2   Namibia Vs Sri Lanka     Namibia           3  Jan Nicol Loftie-Eaton   \n",
       "3   Namibia Vs Sri Lanka     Namibia           4           Stephan Baard   \n",
       "4   Namibia Vs Sri Lanka     Namibia           5      Gerhard Erasmus(c)   \n",
       "5   Namibia Vs Sri Lanka     Namibia           6            Jan Frylinck   \n",
       "6   Namibia Vs Sri Lanka     Namibia           7             David Wiese   \n",
       "7   Namibia Vs Sri Lanka     Namibia           8                 JJ Smit   \n",
       "8   Namibia Vs Sri Lanka   Sri Lanka           1         Pathum Nissanka   \n",
       "9   Namibia Vs Sri Lanka   Sri Lanka           2         Kusal Mendisâ€    \n",
       "10  Namibia Vs Sri Lanka   Sri Lanka           3     Dhananjaya de Silva   \n",
       "\n",
       "                          dismissal runs balls 4s 6s      SR  \n",
       "0      c Pramod Madushan b Chameera    3     6  0  0   50.00  \n",
       "1       c Shanaka b Pramod Madushan    9     9  1  0  100.00  \n",
       "2         c â€ Mendis b Karunaratne   20    12  1  2  166.66  \n",
       "3   c DM de Silva b Pramod Madushan   26    24  2  0  108.33  \n",
       "4      c Gunathilaka b PWH de Silva   20    24  0  0   83.33  \n",
       "5   run out (Gunathilaka/â€ Mendis)   44    28  4  0  157.14  \n",
       "6          c â€ Mendis b Theekshana    0     1  0  0    0.00  \n",
       "7                                     31    16  2  2  193.75  \n",
       "8                 c Smit b Shikongo    9    10  1  0   90.00  \n",
       "9                c â€ Green b Wiese    6     6  0  0  100.00  \n",
       "10            c Shikongo b Frylinck   12    11  1  0  109.09  "
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('t20_json_files/t20_wc_batting_summary.json') as f:\n",
    "    data = json.load(f)\n",
    "    all_records = []\n",
    "    for rec in data:\n",
    "        all_records.extend(rec['battingSummary'])\n",
    "  \n",
    "df_batting = pd.DataFrame(all_records)\n",
    "df_batting.head(11)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "ff91aa85",
   "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>match</th>\n",
       "      <th>teamInnings</th>\n",
       "      <th>battingPos</th>\n",
       "      <th>batsmanName</th>\n",
       "      <th>dismissal</th>\n",
       "      <th>runs</th>\n",
       "      <th>balls</th>\n",
       "      <th>4s</th>\n",
       "      <th>6s</th>\n",
       "      <th>SR</th>\n",
       "      <th>out/not_out</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>1</td>\n",
       "      <td>Michael van Lingen</td>\n",
       "      <td>c Pramod Madushan b Chameera</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50.00</td>\n",
       "      <td>out</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>2</td>\n",
       "      <td>Divan la Cock</td>\n",
       "      <td>c Shanaka b Pramod Madushan</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>out</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>3</td>\n",
       "      <td>Jan Nicol Loftie-Eaton</td>\n",
       "      <td>c â€ Mendis b Karunaratne</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>166.66</td>\n",
       "      <td>out</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>4</td>\n",
       "      <td>Stephan Baard</td>\n",
       "      <td>c DM de Silva b Pramod Madushan</td>\n",
       "      <td>26</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>108.33</td>\n",
       "      <td>out</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>5</td>\n",
       "      <td>Gerhard Erasmus(c)</td>\n",
       "      <td>c Gunathilaka b PWH de Silva</td>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>83.33</td>\n",
       "      <td>out</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>6</td>\n",
       "      <td>Jan Frylinck</td>\n",
       "      <td>run out (Gunathilaka/â€ Mendis)</td>\n",
       "      <td>44</td>\n",
       "      <td>28</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>157.14</td>\n",
       "      <td>out</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>7</td>\n",
       "      <td>David Wiese</td>\n",
       "      <td>c â€ Mendis b Theekshana</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>out</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>8</td>\n",
       "      <td>JJ Smit</td>\n",
       "      <td></td>\n",
       "      <td>31</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>193.75</td>\n",
       "      <td>not_out</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>1</td>\n",
       "      <td>Pathum Nissanka</td>\n",
       "      <td>c Smit b Shikongo</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>90.00</td>\n",
       "      <td>out</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>2</td>\n",
       "      <td>Kusal Mendisâ€</td>\n",
       "      <td>c â€ Green b Wiese</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>out</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>3</td>\n",
       "      <td>Dhananjaya de Silva</td>\n",
       "      <td>c Shikongo b Frylinck</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>109.09</td>\n",
       "      <td>out</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   match teamInnings  battingPos             batsmanName  \\\n",
       "0   Namibia Vs Sri Lanka     Namibia           1      Michael van Lingen   \n",
       "1   Namibia Vs Sri Lanka     Namibia           2           Divan la Cock   \n",
       "2   Namibia Vs Sri Lanka     Namibia           3  Jan Nicol Loftie-Eaton   \n",
       "3   Namibia Vs Sri Lanka     Namibia           4           Stephan Baard   \n",
       "4   Namibia Vs Sri Lanka     Namibia           5      Gerhard Erasmus(c)   \n",
       "5   Namibia Vs Sri Lanka     Namibia           6            Jan Frylinck   \n",
       "6   Namibia Vs Sri Lanka     Namibia           7             David Wiese   \n",
       "7   Namibia Vs Sri Lanka     Namibia           8                 JJ Smit   \n",
       "8   Namibia Vs Sri Lanka   Sri Lanka           1         Pathum Nissanka   \n",
       "9   Namibia Vs Sri Lanka   Sri Lanka           2         Kusal Mendisâ€    \n",
       "10  Namibia Vs Sri Lanka   Sri Lanka           3     Dhananjaya de Silva   \n",
       "\n",
       "                          dismissal runs balls 4s 6s      SR out/not_out  \n",
       "0      c Pramod Madushan b Chameera    3     6  0  0   50.00         out  \n",
       "1       c Shanaka b Pramod Madushan    9     9  1  0  100.00         out  \n",
       "2         c â€ Mendis b Karunaratne   20    12  1  2  166.66         out  \n",
       "3   c DM de Silva b Pramod Madushan   26    24  2  0  108.33         out  \n",
       "4      c Gunathilaka b PWH de Silva   20    24  0  0   83.33         out  \n",
       "5   run out (Gunathilaka/â€ Mendis)   44    28  4  0  157.14         out  \n",
       "6          c â€ Mendis b Theekshana    0     1  0  0    0.00         out  \n",
       "7                                     31    16  2  2  193.75     not_out  \n",
       "8                 c Smit b Shikongo    9    10  1  0   90.00         out  \n",
       "9                c â€ Green b Wiese    6     6  0  0  100.00         out  \n",
       "10            c Shikongo b Frylinck   12    11  1  0  109.09         out  "
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_batting['out/not_out'] = df_batting.dismissal.apply(lambda x: \"out\" if len(x)>0 else \"not_out\")\n",
    "df_batting.head(11)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "f406128b",
   "metadata": {
    "scrolled": false
   },
   "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>match</th>\n",
       "      <th>teamInnings</th>\n",
       "      <th>battingPos</th>\n",
       "      <th>batsmanName</th>\n",
       "      <th>dismissal</th>\n",
       "      <th>runs</th>\n",
       "      <th>balls</th>\n",
       "      <th>4s</th>\n",
       "      <th>6s</th>\n",
       "      <th>SR</th>\n",
       "      <th>out/not_out</th>\n",
       "      <th>match_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>1</td>\n",
       "      <td>Michael van Lingen</td>\n",
       "      <td>c Pramod Madushan b Chameera</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50.00</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>2</td>\n",
       "      <td>Divan la Cock</td>\n",
       "      <td>c Shanaka b Pramod Madushan</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>3</td>\n",
       "      <td>Jan Nicol Loftie-Eaton</td>\n",
       "      <td>c â€ Mendis b Karunaratne</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>166.66</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>4</td>\n",
       "      <td>Stephan Baard</td>\n",
       "      <td>c DM de Silva b Pramod Madushan</td>\n",
       "      <td>26</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>108.33</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>5</td>\n",
       "      <td>Gerhard Erasmus(c)</td>\n",
       "      <td>c Gunathilaka b PWH de Silva</td>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>83.33</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  match teamInnings  battingPos             batsmanName  \\\n",
       "0  Namibia Vs Sri Lanka     Namibia           1      Michael van Lingen   \n",
       "1  Namibia Vs Sri Lanka     Namibia           2           Divan la Cock   \n",
       "2  Namibia Vs Sri Lanka     Namibia           3  Jan Nicol Loftie-Eaton   \n",
       "3  Namibia Vs Sri Lanka     Namibia           4           Stephan Baard   \n",
       "4  Namibia Vs Sri Lanka     Namibia           5      Gerhard Erasmus(c)   \n",
       "\n",
       "                         dismissal runs balls 4s 6s      SR out/not_out  \\\n",
       "0     c Pramod Madushan b Chameera    3     6  0  0   50.00         out   \n",
       "1      c Shanaka b Pramod Madushan    9     9  1  0  100.00         out   \n",
       "2        c â€ Mendis b Karunaratne   20    12  1  2  166.66         out   \n",
       "3  c DM de Silva b Pramod Madushan   26    24  2  0  108.33         out   \n",
       "4     c Gunathilaka b PWH de Silva   20    24  0  0   83.33         out   \n",
       "\n",
       "      match_id  \n",
       "0  T20I # 1823  \n",
       "1  T20I # 1823  \n",
       "2  T20I # 1823  \n",
       "3  T20I # 1823  \n",
       "4  T20I # 1823  "
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_batting['match_id'] = df_batting['match'].map(match_ids_dict)\n",
    "df_batting.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "f9e5fe21",
   "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>match</th>\n",
       "      <th>teamInnings</th>\n",
       "      <th>battingPos</th>\n",
       "      <th>batsmanName</th>\n",
       "      <th>runs</th>\n",
       "      <th>balls</th>\n",
       "      <th>4s</th>\n",
       "      <th>6s</th>\n",
       "      <th>SR</th>\n",
       "      <th>out/not_out</th>\n",
       "      <th>match_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>1</td>\n",
       "      <td>Michael van Lingen</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50.00</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>2</td>\n",
       "      <td>Divan la Cock</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>3</td>\n",
       "      <td>Jan Nicol Loftie-Eaton</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>166.66</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>4</td>\n",
       "      <td>Stephan Baard</td>\n",
       "      <td>26</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>108.33</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>5</td>\n",
       "      <td>Gerhard Erasmus(c)</td>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>83.33</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>6</td>\n",
       "      <td>Jan Frylinck</td>\n",
       "      <td>44</td>\n",
       "      <td>28</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>157.14</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>7</td>\n",
       "      <td>David Wiese</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>8</td>\n",
       "      <td>JJ Smit</td>\n",
       "      <td>31</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>193.75</td>\n",
       "      <td>not_out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>1</td>\n",
       "      <td>Pathum Nissanka</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>90.00</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>2</td>\n",
       "      <td>Kusal Mendisâ€</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  match teamInnings  battingPos             batsmanName runs  \\\n",
       "0  Namibia Vs Sri Lanka     Namibia           1      Michael van Lingen    3   \n",
       "1  Namibia Vs Sri Lanka     Namibia           2           Divan la Cock    9   \n",
       "2  Namibia Vs Sri Lanka     Namibia           3  Jan Nicol Loftie-Eaton   20   \n",
       "3  Namibia Vs Sri Lanka     Namibia           4           Stephan Baard   26   \n",
       "4  Namibia Vs Sri Lanka     Namibia           5      Gerhard Erasmus(c)   20   \n",
       "5  Namibia Vs Sri Lanka     Namibia           6            Jan Frylinck   44   \n",
       "6  Namibia Vs Sri Lanka     Namibia           7             David Wiese    0   \n",
       "7  Namibia Vs Sri Lanka     Namibia           8                 JJ Smit   31   \n",
       "8  Namibia Vs Sri Lanka   Sri Lanka           1         Pathum Nissanka    9   \n",
       "9  Namibia Vs Sri Lanka   Sri Lanka           2         Kusal Mendisâ€     6   \n",
       "\n",
       "  balls 4s 6s      SR out/not_out     match_id  \n",
       "0     6  0  0   50.00         out  T20I # 1823  \n",
       "1     9  1  0  100.00         out  T20I # 1823  \n",
       "2    12  1  2  166.66         out  T20I # 1823  \n",
       "3    24  2  0  108.33         out  T20I # 1823  \n",
       "4    24  0  0   83.33         out  T20I # 1823  \n",
       "5    28  4  0  157.14         out  T20I # 1823  \n",
       "6     1  0  0    0.00         out  T20I # 1823  \n",
       "7    16  2  2  193.75     not_out  T20I # 1823  \n",
       "8    10  1  0   90.00         out  T20I # 1823  \n",
       "9     6  0  0  100.00         out  T20I # 1823  "
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_batting.drop(columns=[\"dismissal\"], inplace=True)\n",
    "df_batting.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4145ae4",
   "metadata": {},
   "source": [
    "**Cleanup weird characters**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "779bb2f4",
   "metadata": {
    "scrolled": true
   },
   "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>match</th>\n",
       "      <th>teamInnings</th>\n",
       "      <th>battingPos</th>\n",
       "      <th>batsmanName</th>\n",
       "      <th>runs</th>\n",
       "      <th>balls</th>\n",
       "      <th>4s</th>\n",
       "      <th>6s</th>\n",
       "      <th>SR</th>\n",
       "      <th>out/not_out</th>\n",
       "      <th>match_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>1</td>\n",
       "      <td>Michael van Lingen</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50.00</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>2</td>\n",
       "      <td>Divan la Cock</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>3</td>\n",
       "      <td>Jan Nicol Loftie-Eaton</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>166.66</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>4</td>\n",
       "      <td>Stephan Baard</td>\n",
       "      <td>26</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>108.33</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>5</td>\n",
       "      <td>Gerhard Erasmus(c)</td>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>83.33</td>\n",
       "      <td>out</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  match teamInnings  battingPos             batsmanName runs  \\\n",
       "0  Namibia Vs Sri Lanka     Namibia           1      Michael van Lingen    3   \n",
       "1  Namibia Vs Sri Lanka     Namibia           2           Divan la Cock    9   \n",
       "2  Namibia Vs Sri Lanka     Namibia           3  Jan Nicol Loftie-Eaton   20   \n",
       "3  Namibia Vs Sri Lanka     Namibia           4           Stephan Baard   26   \n",
       "4  Namibia Vs Sri Lanka     Namibia           5      Gerhard Erasmus(c)   20   \n",
       "\n",
       "  balls 4s 6s      SR out/not_out     match_id  \n",
       "0     6  0  0   50.00         out  T20I # 1823  \n",
       "1     9  1  0  100.00         out  T20I # 1823  \n",
       "2    12  1  2  166.66         out  T20I # 1823  \n",
       "3    24  2  0  108.33         out  T20I # 1823  \n",
       "4    24  0  0   83.33         out  T20I # 1823  "
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_batting['batsmanName'] = df_batting['batsmanName'].apply(lambda x: x.replace('â€', ''))\n",
    "df_batting['batsmanName'] = df_batting['batsmanName'].apply(lambda x: x.replace('\\xa0', ''))\n",
    "df_batting.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "22a2fb62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(699, 11)"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_batting.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "191d4f5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_batting.to_csv('t20_csv_files/fact_bating_summary.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc509885",
   "metadata": {},
   "source": [
    "<h4 style=\"color:blue\">(3) Process Bowling Summary</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "81ed6da6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'match': 'Namibia Vs Sri Lanka',\n",
       "  'bowlingTeam': 'Sri Lanka',\n",
       "  'bowlerName': 'Maheesh Theekshana',\n",
       "  'overs': '4',\n",
       "  'maiden': '0',\n",
       "  'runs': '23',\n",
       "  'wickets': '1',\n",
       "  'economy': '5.75',\n",
       "  '0s': '7',\n",
       "  '4s': '0',\n",
       "  '6s': '0',\n",
       "  'wides': '2',\n",
       "  'noBalls': '0'},\n",
       " {'match': 'Namibia Vs Sri Lanka',\n",
       "  'bowlingTeam': 'Sri Lanka',\n",
       "  'bowlerName': 'Dushmantha Chameera',\n",
       "  'overs': '4',\n",
       "  'maiden': '0',\n",
       "  'runs': '39',\n",
       "  'wickets': '1',\n",
       "  'economy': '9.75',\n",
       "  '0s': '6',\n",
       "  '4s': '3',\n",
       "  '6s': '1',\n",
       "  'wides': '2',\n",
       "  'noBalls': '0'}]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('t20_json_files/t20_wc_bowling_summary.json') as f:\n",
    "    data = json.load(f)\n",
    "    all_records = []\n",
    "    for rec in data:\n",
    "        all_records.extend(rec['bowlingSummary'])\n",
    "all_records[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "adb9462f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(500, 13)\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</th>\n",
       "      <th>bowlingTeam</th>\n",
       "      <th>bowlerName</th>\n",
       "      <th>overs</th>\n",
       "      <th>maiden</th>\n",
       "      <th>runs</th>\n",
       "      <th>wickets</th>\n",
       "      <th>economy</th>\n",
       "      <th>0s</th>\n",
       "      <th>4s</th>\n",
       "      <th>6s</th>\n",
       "      <th>wides</th>\n",
       "      <th>noBalls</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Maheesh Theekshana</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>5.75</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Dushmantha Chameera</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>39</td>\n",
       "      <td>1</td>\n",
       "      <td>9.75</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Pramod Madushan</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>37</td>\n",
       "      <td>2</td>\n",
       "      <td>9.25</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Chamika Karunaratne</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>1</td>\n",
       "      <td>9.00</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Wanindu Hasaranga de Silva</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>6.75</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  match bowlingTeam                  bowlerName overs maiden  \\\n",
       "0  Namibia Vs Sri Lanka   Sri Lanka          Maheesh Theekshana     4      0   \n",
       "1  Namibia Vs Sri Lanka   Sri Lanka         Dushmantha Chameera     4      0   \n",
       "2  Namibia Vs Sri Lanka   Sri Lanka             Pramod Madushan     4      0   \n",
       "3  Namibia Vs Sri Lanka   Sri Lanka         Chamika Karunaratne     4      0   \n",
       "4  Namibia Vs Sri Lanka   Sri Lanka  Wanindu Hasaranga de Silva     4      0   \n",
       "\n",
       "  runs wickets economy 0s 4s 6s wides noBalls  \n",
       "0   23       1    5.75  7  0  0     2       0  \n",
       "1   39       1    9.75  6  3  1     2       0  \n",
       "2   37       2    9.25  6  3  1     0       0  \n",
       "3   36       1    9.00  7  3  1     1       0  \n",
       "4   27       1    6.75  8  1  1     0       0  "
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bowling = pd.DataFrame(all_records)\n",
    "print(df_bowling.shape)\n",
    "df_bowling.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "701f2c8c",
   "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>match</th>\n",
       "      <th>bowlingTeam</th>\n",
       "      <th>bowlerName</th>\n",
       "      <th>overs</th>\n",
       "      <th>maiden</th>\n",
       "      <th>runs</th>\n",
       "      <th>wickets</th>\n",
       "      <th>economy</th>\n",
       "      <th>0s</th>\n",
       "      <th>4s</th>\n",
       "      <th>6s</th>\n",
       "      <th>wides</th>\n",
       "      <th>noBalls</th>\n",
       "      <th>match_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Maheesh Theekshana</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>5.75</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Dushmantha Chameera</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>39</td>\n",
       "      <td>1</td>\n",
       "      <td>9.75</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Pramod Madushan</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>37</td>\n",
       "      <td>2</td>\n",
       "      <td>9.25</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Chamika Karunaratne</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>1</td>\n",
       "      <td>9.00</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Wanindu Hasaranga de Silva</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>6.75</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>T20I # 1823</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  match bowlingTeam                  bowlerName overs maiden  \\\n",
       "0  Namibia Vs Sri Lanka   Sri Lanka          Maheesh Theekshana     4      0   \n",
       "1  Namibia Vs Sri Lanka   Sri Lanka         Dushmantha Chameera     4      0   \n",
       "2  Namibia Vs Sri Lanka   Sri Lanka             Pramod Madushan     4      0   \n",
       "3  Namibia Vs Sri Lanka   Sri Lanka         Chamika Karunaratne     4      0   \n",
       "4  Namibia Vs Sri Lanka   Sri Lanka  Wanindu Hasaranga de Silva     4      0   \n",
       "\n",
       "  runs wickets economy 0s 4s 6s wides noBalls     match_id  \n",
       "0   23       1    5.75  7  0  0     2       0  T20I # 1823  \n",
       "1   39       1    9.75  6  3  1     2       0  T20I # 1823  \n",
       "2   37       2    9.25  6  3  1     0       0  T20I # 1823  \n",
       "3   36       1    9.00  7  3  1     1       0  T20I # 1823  \n",
       "4   27       1    6.75  8  1  1     0       0  T20I # 1823  "
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bowling['match_id'] = df_bowling['match'].map(match_ids_dict)\n",
    "df_bowling.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "a2efb73e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_bowling.to_csv('t20_csv_files/fact_bowling_summary.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44634ac7",
   "metadata": {},
   "source": [
    "<h4 style=\"color:blue\">(4) Process Players Information</h4>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "1fd8e6bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('t20_json_files/t20_wc_player_info.json') as f:\n",
    "    data = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "8c0cc07c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(219, 6)\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>name</th>\n",
       "      <th>team</th>\n",
       "      <th>battingStyle</th>\n",
       "      <th>bowlingStyle</th>\n",
       "      <th>playingRole</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Michael van Lingen</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Left hand Bat</td>\n",
       "      <td>Left arm Medium</td>\n",
       "      <td>Bowling Allrounder</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Divan la Cock</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Legbreak</td>\n",
       "      <td>Opening Batter</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jan Nicol Loftie-Eaton</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Left hand Bat</td>\n",
       "      <td>Right arm Medium, Legbreak</td>\n",
       "      <td>Batter</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Stephan Baard</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Medium fast</td>\n",
       "      <td>Batter</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gerhard Erasmus(c)</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Offbreak</td>\n",
       "      <td>Allrounder</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Jan Frylinck</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Left hand Bat</td>\n",
       "      <td>Left arm Fast medium</td>\n",
       "      <td>Allrounder</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>David Wiese</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Medium fast</td>\n",
       "      <td>Allrounder</td>\n",
       "      <td>David Wiese joined a marked outflow of South A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>JJ Smit</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Left arm Medium fast</td>\n",
       "      <td>Bowling Allrounder</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Pathum Nissanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td></td>\n",
       "      <td>Top order Batter</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Kusal Mendisâ€</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Legbreak</td>\n",
       "      <td>Wicketkeeper Batter</td>\n",
       "      <td>Blessed with a compact technique, an aggressiv...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name       team    battingStyle  \\\n",
       "0      Michael van Lingen    Namibia   Left hand Bat   \n",
       "1           Divan la Cock    Namibia  Right hand Bat   \n",
       "2  Jan Nicol Loftie-Eaton    Namibia   Left hand Bat   \n",
       "3           Stephan Baard    Namibia  Right hand Bat   \n",
       "4      Gerhard Erasmus(c)    Namibia  Right hand Bat   \n",
       "5            Jan Frylinck    Namibia   Left hand Bat   \n",
       "6             David Wiese    Namibia  Right hand Bat   \n",
       "7                 JJ Smit    Namibia  Right hand Bat   \n",
       "8         Pathum Nissanka  Sri Lanka  Right hand Bat   \n",
       "9         Kusal Mendisâ€   Sri Lanka  Right hand Bat   \n",
       "\n",
       "                 bowlingStyle          playingRole  \\\n",
       "0             Left arm Medium   Bowling Allrounder   \n",
       "1                    Legbreak       Opening Batter   \n",
       "2  Right arm Medium, Legbreak               Batter   \n",
       "3       Right arm Medium fast               Batter   \n",
       "4          Right arm Offbreak           Allrounder   \n",
       "5        Left arm Fast medium           Allrounder   \n",
       "6       Right arm Medium fast           Allrounder   \n",
       "7        Left arm Medium fast   Bowling Allrounder   \n",
       "8                                 Top order Batter   \n",
       "9                    Legbreak  Wicketkeeper Batter   \n",
       "\n",
       "                                         description  \n",
       "0                                                     \n",
       "1                                                     \n",
       "2                                                     \n",
       "3                                                     \n",
       "4                                                     \n",
       "5                                                     \n",
       "6  David Wiese joined a marked outflow of South A...  \n",
       "7                                                     \n",
       "8                                                     \n",
       "9  Blessed with a compact technique, an aggressiv...  "
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_players = pd.DataFrame(data)\n",
    "\n",
    "print(df_players.shape)\n",
    "df_players.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f53b0700",
   "metadata": {},
   "source": [
    "**Cleanup weird characters**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "3b9b11eb",
   "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>name</th>\n",
       "      <th>team</th>\n",
       "      <th>battingStyle</th>\n",
       "      <th>bowlingStyle</th>\n",
       "      <th>playingRole</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Michael van Lingen</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Left hand Bat</td>\n",
       "      <td>Left arm Medium</td>\n",
       "      <td>Bowling Allrounder</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Divan la Cock</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Legbreak</td>\n",
       "      <td>Opening Batter</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jan Nicol Loftie-Eaton</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Left hand Bat</td>\n",
       "      <td>Right arm Medium, Legbreak</td>\n",
       "      <td>Batter</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Stephan Baard</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Medium fast</td>\n",
       "      <td>Batter</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gerhard Erasmus(c)</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Offbreak</td>\n",
       "      <td>Allrounder</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Jan Frylinck</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Left hand Bat</td>\n",
       "      <td>Left arm Fast medium</td>\n",
       "      <td>Allrounder</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>David Wiese</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Medium fast</td>\n",
       "      <td>Allrounder</td>\n",
       "      <td>David Wiese joined a marked outflow of South A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>JJ Smit</td>\n",
       "      <td>Namibia</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Left arm Medium fast</td>\n",
       "      <td>Bowling Allrounder</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Pathum Nissanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td></td>\n",
       "      <td>Top order Batter</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Kusal Mendis</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Legbreak</td>\n",
       "      <td>Wicketkeeper Batter</td>\n",
       "      <td>Blessed with a compact technique, an aggressiv...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name       team    battingStyle  \\\n",
       "0      Michael van Lingen    Namibia   Left hand Bat   \n",
       "1           Divan la Cock    Namibia  Right hand Bat   \n",
       "2  Jan Nicol Loftie-Eaton    Namibia   Left hand Bat   \n",
       "3           Stephan Baard    Namibia  Right hand Bat   \n",
       "4      Gerhard Erasmus(c)    Namibia  Right hand Bat   \n",
       "5            Jan Frylinck    Namibia   Left hand Bat   \n",
       "6             David Wiese    Namibia  Right hand Bat   \n",
       "7                 JJ Smit    Namibia  Right hand Bat   \n",
       "8         Pathum Nissanka  Sri Lanka  Right hand Bat   \n",
       "9            Kusal Mendis  Sri Lanka  Right hand Bat   \n",
       "\n",
       "                 bowlingStyle          playingRole  \\\n",
       "0             Left arm Medium   Bowling Allrounder   \n",
       "1                    Legbreak       Opening Batter   \n",
       "2  Right arm Medium, Legbreak               Batter   \n",
       "3       Right arm Medium fast               Batter   \n",
       "4          Right arm Offbreak           Allrounder   \n",
       "5        Left arm Fast medium           Allrounder   \n",
       "6       Right arm Medium fast           Allrounder   \n",
       "7        Left arm Medium fast   Bowling Allrounder   \n",
       "8                                 Top order Batter   \n",
       "9                    Legbreak  Wicketkeeper Batter   \n",
       "\n",
       "                                         description  \n",
       "0                                                     \n",
       "1                                                     \n",
       "2                                                     \n",
       "3                                                     \n",
       "4                                                     \n",
       "5                                                     \n",
       "6  David Wiese joined a marked outflow of South A...  \n",
       "7                                                     \n",
       "8                                                     \n",
       "9  Blessed with a compact technique, an aggressiv...  "
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_players['name'] = df_players['name'].apply(lambda x: x.replace('â€', ''))\n",
    "df_players['name'] = df_players['name'].apply(lambda x: x.replace('†', ''))\n",
    "df_players['name'] = df_players['name'].apply(lambda x: x.replace('\\xa0', ''))\n",
    "df_players.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "6be96f94",
   "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>name</th>\n",
       "      <th>team</th>\n",
       "      <th>battingStyle</th>\n",
       "      <th>bowlingStyle</th>\n",
       "      <th>playingRole</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>KL Rahul</td>\n",
       "      <td>India</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td></td>\n",
       "      <td>Opening Batter</td>\n",
       "      <td>A tall, elegant right-hand batsman who can kee...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Rohit Sharma(c)</td>\n",
       "      <td>India</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Offbreak</td>\n",
       "      <td>Top order Batter</td>\n",
       "      <td>Languid and easy on the eye, Rohit Sharma owne...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>Virat Kohli</td>\n",
       "      <td>India</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Medium</td>\n",
       "      <td>Top order Batter</td>\n",
       "      <td>India has given to the world many a great cric...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>Suryakumar Yadav</td>\n",
       "      <td>India</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Medium, Right arm Offbreak</td>\n",
       "      <td>Batter</td>\n",
       "      <td>Hard-hitting 360-degree batter Suryakumar Yada...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>Axar Patel</td>\n",
       "      <td>India</td>\n",
       "      <td>Left hand Bat</td>\n",
       "      <td>Slow Left arm Orthodox</td>\n",
       "      <td>Bowling Allrounder</td>\n",
       "      <td>Left-arm spinner Axar Patel has been increasin...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>Hardik Pandya</td>\n",
       "      <td>India</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Medium fast</td>\n",
       "      <td>Allrounder</td>\n",
       "      <td>Hardik Pandya swears by living life king size ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Dinesh Karthik</td>\n",
       "      <td>India</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Offbreak</td>\n",
       "      <td>Wicketkeeper Batter</td>\n",
       "      <td>Not many would forget the sight of Dinesh Kart...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Ravichandran Ashwin</td>\n",
       "      <td>India</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Offbreak</td>\n",
       "      <td>Bowling Allrounder</td>\n",
       "      <td>R Ashwin took the tricks and skills he learned...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>Bhuvneshwar Kumar</td>\n",
       "      <td>India</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Medium</td>\n",
       "      <td>Bowler</td>\n",
       "      <td>At the time of his India debut in 2012, Bhuvne...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>Arshdeep Singh</td>\n",
       "      <td>India</td>\n",
       "      <td>Left hand Bat</td>\n",
       "      <td>Left arm Medium fast</td>\n",
       "      <td>Bowler</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>Mohammed Shami</td>\n",
       "      <td>India</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Fast</td>\n",
       "      <td>Bowler</td>\n",
       "      <td>Mohammed Shami was India's leading fast bowler...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>Deepak Hooda</td>\n",
       "      <td>India</td>\n",
       "      <td>Right hand Bat</td>\n",
       "      <td>Right arm Offbreak</td>\n",
       "      <td>Allrounder</td>\n",
       "      <td>An allrounder who can bat in any position, Dee...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>Rishabh Pant</td>\n",
       "      <td>India</td>\n",
       "      <td>Left hand Bat</td>\n",
       "      <td></td>\n",
       "      <td>Wicketkeeper Batter</td>\n",
       "      <td>A match-turning, swashbuckling batter-keeper i...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    name   team    battingStyle  \\\n",
       "127             KL Rahul  India  Right hand Bat   \n",
       "128      Rohit Sharma(c)  India  Right hand Bat   \n",
       "129          Virat Kohli  India  Right hand Bat   \n",
       "130     Suryakumar Yadav  India  Right hand Bat   \n",
       "131           Axar Patel  India   Left hand Bat   \n",
       "132        Hardik Pandya  India  Right hand Bat   \n",
       "133       Dinesh Karthik  India  Right hand Bat   \n",
       "134  Ravichandran Ashwin  India  Right hand Bat   \n",
       "135    Bhuvneshwar Kumar  India  Right hand Bat   \n",
       "136       Arshdeep Singh  India   Left hand Bat   \n",
       "137       Mohammed Shami  India  Right hand Bat   \n",
       "192         Deepak Hooda  India  Right hand Bat   \n",
       "211         Rishabh Pant  India   Left hand Bat   \n",
       "\n",
       "                             bowlingStyle          playingRole  \\\n",
       "127                                             Opening Batter   \n",
       "128                    Right arm Offbreak     Top order Batter   \n",
       "129                      Right arm Medium     Top order Batter   \n",
       "130  Right arm Medium, Right arm Offbreak               Batter   \n",
       "131                Slow Left arm Orthodox   Bowling Allrounder   \n",
       "132                 Right arm Medium fast           Allrounder   \n",
       "133                    Right arm Offbreak  Wicketkeeper Batter   \n",
       "134                    Right arm Offbreak   Bowling Allrounder   \n",
       "135                      Right arm Medium               Bowler   \n",
       "136                  Left arm Medium fast               Bowler   \n",
       "137                        Right arm Fast               Bowler   \n",
       "192                    Right arm Offbreak           Allrounder   \n",
       "211                                        Wicketkeeper Batter   \n",
       "\n",
       "                                           description  \n",
       "127  A tall, elegant right-hand batsman who can kee...  \n",
       "128  Languid and easy on the eye, Rohit Sharma owne...  \n",
       "129  India has given to the world many a great cric...  \n",
       "130  Hard-hitting 360-degree batter Suryakumar Yada...  \n",
       "131  Left-arm spinner Axar Patel has been increasin...  \n",
       "132  Hardik Pandya swears by living life king size ...  \n",
       "133  Not many would forget the sight of Dinesh Kart...  \n",
       "134  R Ashwin took the tricks and skills he learned...  \n",
       "135  At the time of his India debut in 2012, Bhuvne...  \n",
       "136                                                     \n",
       "137  Mohammed Shami was India's leading fast bowler...  \n",
       "192  An allrounder who can bat in any position, Dee...  \n",
       "211  A match-turning, swashbuckling batter-keeper i...  "
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_players[df_players['team'] == 'India']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "b59548bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_players.to_csv('t20_csv_files/dim_players_no_images.csv', index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}