transform.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'team1': 'Namibia',\n",
       "  'team2': 'Sri Lanka',\n",
       "  'winner': 'Namibia',\n",
       "  'margin': '55 runs',\n",
       "  'ground': 'Geelong',\n",
       "  'matchDate': 'Oct 16, 2022',\n",
       "  'scorecard': 'T20I # 1823'},\n",
       " {'team1': 'Netherlands',\n",
       "  'team2': 'U.A.E.',\n",
       "  'winner': 'Netherlands',\n",
       "  'margin': '3 wickets',\n",
       "  'ground': 'Geelong',\n",
       "  'matchDate': 'Oct 16, 2022',\n",
       "  'scorecard': 'T20I # 1825'},\n",
       " {'team1': 'Scotland',\n",
       "  'team2': 'West Indies',\n",
       "  'winner': 'Scotland',\n",
       "  'margin': '42 runs',\n",
       "  'ground': 'Hobart',\n",
       "  'matchDate': 'Oct 17, 2022',\n",
       "  'scorecard': 'T20I # 1826'},\n",
       " {'team1': 'Ireland',\n",
       "  'team2': 'Zimbabwe',\n",
       "  'winner': 'Zimbabwe',\n",
       "  'margin': '31 runs',\n",
       "  'ground': 'Hobart',\n",
       "  'matchDate': 'Oct 17, 2022',\n",
       "  'scorecard': 'T20I # 1828'},\n",
       " {'team1': 'Namibia',\n",
       "  'team2': 'Netherlands',\n",
       "  'winner': 'Netherlands',\n",
       "  'margin': '5 wickets',\n",
       "  'ground': 'Geelong',\n",
       "  'matchDate': 'Oct 18, 2022',\n",
       "  'scorecard': 'T20I # 1830'},\n",
       " {'team1': 'Sri Lanka',\n",
       "  'team2': 'U.A.E.',\n",
       "  'winner': 'Sri Lanka',\n",
       "  'margin': '79 runs',\n",
       "  'ground': 'Geelong',\n",
       "  'matchDate': 'Oct 18, 2022',\n",
       "  'scorecard': 'T20I # 1832'},\n",
       " {'team1': 'Ireland',\n",
       "  'team2': 'Scotland',\n",
       "  'winner': 'Ireland',\n",
       "  'margin': '6 wickets',\n",
       "  'ground': 'Hobart',\n",
       "  'matchDate': 'Oct 19, 2022',\n",
       "  'scorecard': 'T20I # 1833'},\n",
       " {'team1': 'West Indies',\n",
       "  'team2': 'Zimbabwe',\n",
       "  'winner': 'West Indies',\n",
       "  'margin': '31 runs',\n",
       "  'ground': 'Hobart',\n",
       "  'matchDate': 'Oct 19, 2022',\n",
       "  'scorecard': 'T20I # 1834'},\n",
       " {'team1': 'Netherlands',\n",
       "  'team2': 'Sri Lanka',\n",
       "  'winner': 'Sri Lanka',\n",
       "  'margin': '16 runs',\n",
       "  'ground': 'Geelong',\n",
       "  'matchDate': 'Oct 20, 2022',\n",
       "  'scorecard': 'T20I # 1835'},\n",
       " {'team1': 'Namibia',\n",
       "  'team2': 'U.A.E.',\n",
       "  'winner': 'U.A.E.',\n",
       "  'margin': '7 runs',\n",
       "  'ground': 'Geelong',\n",
       "  'matchDate': 'Oct 20, 2022',\n",
       "  'scorecard': 'T20I # 1836'},\n",
       " {'team1': 'Ireland',\n",
       "  'team2': 'West Indies',\n",
       "  'winner': 'Ireland',\n",
       "  'margin': '9 wickets',\n",
       "  'ground': 'Hobart',\n",
       "  'matchDate': 'Oct 21, 2022',\n",
       "  'scorecard': 'T20I # 1837'},\n",
       " {'team1': 'Scotland',\n",
       "  'team2': 'Zimbabwe',\n",
       "  'winner': 'Zimbabwe',\n",
       "  'margin': '5 wickets',\n",
       "  'ground': 'Hobart',\n",
       "  'matchDate': 'Oct 21, 2022',\n",
       "  'scorecard': 'T20I # 1838'},\n",
       " {'team1': 'Australia',\n",
       "  'team2': 'New Zealand',\n",
       "  'winner': 'New Zealand',\n",
       "  'margin': '89 runs',\n",
       "  'ground': 'Sydney',\n",
       "  'matchDate': 'Oct 22, 2022',\n",
       "  'scorecard': 'T20I # 1839'},\n",
       " {'team1': 'Afghanistan',\n",
       "  'team2': 'England',\n",
       "  'winner': 'England',\n",
       "  'margin': '5 wickets',\n",
       "  'ground': 'Perth',\n",
       "  'matchDate': 'Oct 22, 2022',\n",
       "  'scorecard': 'T20I # 1840'},\n",
       " {'team1': 'Ireland',\n",
       "  'team2': 'Sri Lanka',\n",
       "  'winner': 'Sri Lanka',\n",
       "  'margin': '9 wickets',\n",
       "  'ground': 'Hobart',\n",
       "  'matchDate': 'Oct 23, 2022',\n",
       "  'scorecard': 'T20I # 1841'},\n",
       " {'team1': 'India',\n",
       "  'team2': 'Pakistan',\n",
       "  'winner': 'India',\n",
       "  'margin': '4 wickets',\n",
       "  'ground': 'Melbourne',\n",
       "  'matchDate': 'Oct 23, 2022',\n",
       "  'scorecard': 'T20I # 1842'},\n",
       " {'team1': 'Bangladesh',\n",
       "  'team2': 'Netherlands',\n",
       "  'winner': 'Bangladesh',\n",
       "  'margin': '9 runs',\n",
       "  'ground': 'Hobart',\n",
       "  'matchDate': 'Oct 24, 2022',\n",
       "  'scorecard': 'T20I # 1843'},\n",
       " {'team1': 'South Africa',\n",
       "  'team2': 'Zimbabwe',\n",
       "  'winner': 'no result',\n",
       "  'margin': '',\n",
       "  'ground': 'Hobart',\n",
       "  'matchDate': 'Oct 24, 2022',\n",
       "  'scorecard': 'T20I # 1844'},\n",
       " {'team1': 'Australia',\n",
       "  'team2': 'Sri Lanka',\n",
       "  'winner': 'Australia',\n",
       "  'margin': '7 wickets',\n",
       "  'ground': 'Perth',\n",
       "  'matchDate': 'Oct 25, 2022',\n",
       "  'scorecard': 'T20I # 1845'},\n",
       " {'team1': 'England',\n",
       "  'team2': 'Ireland',\n",
       "  'winner': 'Ireland',\n",
       "  'margin': '5 runs',\n",
       "  'ground': 'Melbourne',\n",
       "  'matchDate': 'Oct 26, 2022',\n",
       "  'scorecard': 'T20I # 1846'},\n",
       " {'team1': 'Afghanistan',\n",
       "  'team2': 'New Zealand',\n",
       "  'winner': 'abandoned',\n",
       "  'margin': '',\n",
       "  'ground': 'Melbourne',\n",
       "  'matchDate': 'Oct 26, 2022',\n",
       "  'scorecard': 'T20I # 1846a'},\n",
       " {'team1': 'Bangladesh',\n",
       "  'team2': 'South Africa',\n",
       "  'winner': 'South Africa',\n",
       "  'margin': '104 runs',\n",
       "  'ground': 'Sydney',\n",
       "  'matchDate': 'Oct 27, 2022',\n",
       "  'scorecard': 'T20I # 1847'},\n",
       " {'team1': 'India',\n",
       "  'team2': 'Netherlands',\n",
       "  'winner': 'India',\n",
       "  'margin': '56 runs',\n",
       "  'ground': 'Sydney',\n",
       "  'matchDate': 'Oct 27, 2022',\n",
       "  'scorecard': 'T20I # 1848'},\n",
       " {'team1': 'Pakistan',\n",
       "  'team2': 'Zimbabwe',\n",
       "  'winner': 'Zimbabwe',\n",
       "  'margin': '1 run',\n",
       "  'ground': 'Perth',\n",
       "  'matchDate': 'Oct 27, 2022',\n",
       "  'scorecard': 'T20I # 1849'},\n",
       " {'team1': 'Afghanistan',\n",
       "  'team2': 'Ireland',\n",
       "  'winner': 'abandoned',\n",
       "  'margin': '',\n",
       "  'ground': 'Melbourne',\n",
       "  'matchDate': 'Oct 28, 2022',\n",
       "  'scorecard': 'T20I # 1849a'},\n",
       " {'team1': 'Australia',\n",
       "  'team2': 'England',\n",
       "  'winner': 'abandoned',\n",
       "  'margin': '',\n",
       "  'ground': 'Melbourne',\n",
       "  'matchDate': 'Oct 28, 2022',\n",
       "  'scorecard': 'T20I # 1849b'},\n",
       " {'team1': 'New Zealand',\n",
       "  'team2': 'Sri Lanka',\n",
       "  'winner': 'New Zealand',\n",
       "  'margin': '65 runs',\n",
       "  'ground': 'Sydney',\n",
       "  'matchDate': 'Oct 29, 2022',\n",
       "  'scorecard': 'T20I # 1850'},\n",
       " {'team1': 'Bangladesh',\n",
       "  'team2': 'Zimbabwe',\n",
       "  'winner': 'Bangladesh',\n",
       "  'margin': '3 runs',\n",
       "  'ground': 'Brisbane',\n",
       "  'matchDate': 'Oct 30, 2022',\n",
       "  'scorecard': 'T20I # 1851'},\n",
       " {'team1': 'Netherlands',\n",
       "  'team2': 'Pakistan',\n",
       "  'winner': 'Pakistan',\n",
       "  'margin': '6 wickets',\n",
       "  'ground': 'Perth',\n",
       "  'matchDate': 'Oct 30, 2022',\n",
       "  'scorecard': 'T20I # 1852'},\n",
       " {'team1': 'India',\n",
       "  'team2': 'South Africa',\n",
       "  'winner': 'South Africa',\n",
       "  'margin': '5 wickets',\n",
       "  'ground': 'Perth',\n",
       "  'matchDate': 'Oct 30, 2022',\n",
       "  'scorecard': 'T20I # 1853'},\n",
       " {'team1': 'Australia',\n",
       "  'team2': 'Ireland',\n",
       "  'winner': 'Australia',\n",
       "  'margin': '42 runs',\n",
       "  'ground': 'Brisbane',\n",
       "  'matchDate': 'Oct 31, 2022',\n",
       "  'scorecard': 'T20I # 1855'},\n",
       " {'team1': 'Afghanistan',\n",
       "  'team2': 'Sri Lanka',\n",
       "  'winner': 'Sri Lanka',\n",
       "  'margin': '6 wickets',\n",
       "  'ground': 'Brisbane',\n",
       "  'matchDate': 'Nov 1, 2022',\n",
       "  'scorecard': 'T20I # 1856'},\n",
       " {'team1': 'England',\n",
       "  'team2': 'New Zealand',\n",
       "  'winner': 'England',\n",
       "  'margin': '20 runs',\n",
       "  'ground': 'Brisbane',\n",
       "  'matchDate': 'Nov 1, 2022',\n",
       "  'scorecard': 'T20I # 1858'},\n",
       " {'team1': 'Netherlands',\n",
       "  'team2': 'Zimbabwe',\n",
       "  'winner': 'Netherlands',\n",
       "  'margin': '5 wickets',\n",
       "  'ground': 'Adelaide',\n",
       "  'matchDate': 'Nov 2, 2022',\n",
       "  'scorecard': 'T20I # 1859'},\n",
       " {'team1': 'Bangladesh',\n",
       "  'team2': 'India',\n",
       "  'winner': 'India',\n",
       "  'margin': '5 runs',\n",
       "  'ground': 'Adelaide',\n",
       "  'matchDate': 'Nov 2, 2022',\n",
       "  'scorecard': 'T20I # 1860'},\n",
       " {'team1': 'Pakistan',\n",
       "  'team2': 'South Africa',\n",
       "  'winner': 'Pakistan',\n",
       "  'margin': '33 runs',\n",
       "  'ground': 'Sydney',\n",
       "  'matchDate': 'Nov 3, 2022',\n",
       "  'scorecard': 'T20I # 1861'},\n",
       " {'team1': 'Ireland',\n",
       "  'team2': 'New Zealand',\n",
       "  'winner': 'New Zealand',\n",
       "  'margin': '35 runs',\n",
       "  'ground': 'Adelaide',\n",
       "  'matchDate': 'Nov 4, 2022',\n",
       "  'scorecard': 'T20I # 1862'},\n",
       " {'team1': 'Australia',\n",
       "  'team2': 'Afghanistan',\n",
       "  'winner': 'Australia',\n",
       "  'margin': '4 runs',\n",
       "  'ground': 'Adelaide',\n",
       "  'matchDate': 'Nov 4, 2022',\n",
       "  'scorecard': 'T20I # 1864'},\n",
       " {'team1': 'England',\n",
       "  'team2': 'Sri Lanka',\n",
       "  'winner': 'England',\n",
       "  'margin': '4 wickets',\n",
       "  'ground': 'Sydney',\n",
       "  'matchDate': 'Nov 5, 2022',\n",
       "  'scorecard': 'T20I # 1867'},\n",
       " {'team1': 'Netherlands',\n",
       "  'team2': 'South Africa',\n",
       "  'winner': 'Netherlands',\n",
       "  'margin': '13 runs',\n",
       "  'ground': 'Adelaide',\n",
       "  'matchDate': 'Nov 6, 2022',\n",
       "  'scorecard': 'T20I # 1871'},\n",
       " {'team1': 'Bangladesh',\n",
       "  'team2': 'Pakistan',\n",
       "  'winner': 'Pakistan',\n",
       "  'margin': '5 wickets',\n",
       "  'ground': 'Adelaide',\n",
       "  'matchDate': 'Nov 6, 2022',\n",
       "  'scorecard': 'T20I # 1872'},\n",
       " {'team1': 'India',\n",
       "  'team2': 'Zimbabwe',\n",
       "  'winner': 'India',\n",
       "  'margin': '71 runs',\n",
       "  'ground': 'Melbourne',\n",
       "  'matchDate': 'Nov 6, 2022',\n",
       "  'scorecard': 'T20I # 1873'},\n",
       " {'team1': 'New Zealand',\n",
       "  'team2': 'Pakistan',\n",
       "  'winner': 'Pakistan',\n",
       "  'margin': '7 wickets',\n",
       "  'ground': 'Sydney',\n",
       "  'matchDate': 'Nov 9, 2022',\n",
       "  'scorecard': 'T20I # 1877'},\n",
       " {'team1': 'England',\n",
       "  'team2': 'India',\n",
       "  'winner': 'England',\n",
       "  'margin': '10 wickets',\n",
       "  'ground': 'Adelaide',\n",
       "  'matchDate': 'Nov 10, 2022',\n",
       "  'scorecard': 'T20I # 1878'},\n",
       " {'team1': 'England',\n",
       "  'team2': 'Pakistan',\n",
       "  'winner': 'England',\n",
       "  'margin': '5 wickets',\n",
       "  'ground': 'Melbourne',\n",
       "  'matchDate': 'Nov 13, 2022',\n",
       "  'scorecard': 'T20I # 1879'}]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('t20_json_files/t20_wc_match_results.json') as f:\n",
    "    data = json.load(f)\n",
    "data[0]['matchSummary']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(45, 7)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_match = pd.DataFrame(data[0]['matchSummary'])\n",
    "df_match.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_match.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rename scorecard into match_id which is unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>match_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>5 wickets</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Nov 6, 2022</td>\n",
       "      <td>T20I # 1872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>India</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>India</td>\n",
       "      <td>71 runs</td>\n",
       "      <td>Melbourne</td>\n",
       "      <td>Nov 6, 2022</td>\n",
       "      <td>T20I # 1873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>7 wickets</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>Nov 9, 2022</td>\n",
       "      <td>T20I # 1877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>England</td>\n",
       "      <td>India</td>\n",
       "      <td>England</td>\n",
       "      <td>10 wickets</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Nov 10, 2022</td>\n",
       "      <td>T20I # 1878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>England</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>England</td>\n",
       "      <td>5 wickets</td>\n",
       "      <td>Melbourne</td>\n",
       "      <td>Nov 13, 2022</td>\n",
       "      <td>T20I # 1879</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          team1     team2    winner      margin     ground     matchDate  \\\n",
       "40   Bangladesh  Pakistan  Pakistan   5 wickets   Adelaide   Nov 6, 2022   \n",
       "41        India  Zimbabwe     India     71 runs  Melbourne   Nov 6, 2022   \n",
       "42  New Zealand  Pakistan  Pakistan   7 wickets     Sydney   Nov 9, 2022   \n",
       "43      England     India   England  10 wickets   Adelaide  Nov 10, 2022   \n",
       "44      England  Pakistan   England   5 wickets  Melbourne  Nov 13, 2022   \n",
       "\n",
       "       match_id  \n",
       "40  T20I # 1872  \n",
       "41  T20I # 1873  \n",
       "42  T20I # 1877  \n",
       "43  T20I # 1878  \n",
       "44  T20I # 1879  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_match.rename({'scorecard' : 'match_id'}, axis = 1, inplace=True)\n",
    "df_match.tail()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Batting summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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",
       "  </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  \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  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('t20_json_files/t20_wc_batting_summary.json') as f:\n",
    "    data = json.load(f)\n",
    "\n",
    "    all_records = []\n",
    "    for rec in data:\n",
    "        all_records.extend(rec['battingSummary']) #concat arrays\n",
    "\n",
    "df_batting = pd.DataFrame(all_records)\n",
    "df_batting.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert dismissals column from strings to out/notout"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>4</td>\n",
       "      <td>Danushka Gunathilaka</td>\n",
       "      <td>c â€ Green b Shikongo</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>12</th>\n",
       "      <td>Namibia Vs Sri Lanka</td>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>5</td>\n",
       "      <td>Bhanuka Rajapaksa</td>\n",
       "      <td>c la Cock b Scholtz</td>\n",
       "      <td>20</td>\n",
       "      <td>21</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>95.23</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",
       "11  Namibia Vs Sri Lanka   Sri Lanka           4    Danushka Gunathilaka   \n",
       "12  Namibia Vs Sri Lanka   Sri Lanka           5       Bhanuka Rajapaksa   \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  \n",
       "11            c â€ Green b Shikongo    0     1  0  0    0.00         out  \n",
       "12              c la Cock b Scholtz   20    21  2  0   95.23         out  "
      ]
     },
     "execution_count": 21,
     "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(13)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "\"['dismissal'] not found in axis\"",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32md:\\Python\\Projects\\PowerBI\\T20WC\\transform.ipynb Cell 11\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> <a href='vscode-notebook-cell:/d%3A/Python/Projects/PowerBI/T20WC/transform.ipynb#X13sZmlsZQ%3D%3D?line=0'>1</a>\u001b[0m df_batting\u001b[39m.\u001b[39;49mdrop(columns\u001b[39m=\u001b[39;49m[\u001b[39m'\u001b[39;49m\u001b[39mdismissal\u001b[39;49m\u001b[39m'\u001b[39;49m],inplace\u001b[39m=\u001b[39;49m\u001b[39mTrue\u001b[39;49;00m)\n\u001b[0;32m      <a href='vscode-notebook-cell:/d%3A/Python/Projects/PowerBI/T20WC/transform.ipynb#X13sZmlsZQ%3D%3D?line=1'>2</a>\u001b[0m df_batting\u001b[39m.\u001b[39mtail(\u001b[39m3\u001b[39m)\n",
      "File \u001b[1;32mc:\\Python310\\lib\\site-packages\\pandas\\util\\_decorators.py:311\u001b[0m, in \u001b[0;36mdeprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    305\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mlen\u001b[39m(args) \u001b[39m>\u001b[39m num_allow_args:\n\u001b[0;32m    306\u001b[0m     warnings\u001b[39m.\u001b[39mwarn(\n\u001b[0;32m    307\u001b[0m         msg\u001b[39m.\u001b[39mformat(arguments\u001b[39m=\u001b[39marguments),\n\u001b[0;32m    308\u001b[0m         \u001b[39mFutureWarning\u001b[39;00m,\n\u001b[0;32m    309\u001b[0m         stacklevel\u001b[39m=\u001b[39mstacklevel,\n\u001b[0;32m    310\u001b[0m     )\n\u001b[1;32m--> 311\u001b[0m \u001b[39mreturn\u001b[39;00m func(\u001b[39m*\u001b[39margs, \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mkwargs)\n",
      "File \u001b[1;32mc:\\Python310\\lib\\site-packages\\pandas\\core\\frame.py:4956\u001b[0m, in \u001b[0;36mDataFrame.drop\u001b[1;34m(self, labels, axis, index, columns, level, inplace, errors)\u001b[0m\n\u001b[0;32m   4808\u001b[0m \u001b[39m@deprecate_nonkeyword_arguments\u001b[39m(version\u001b[39m=\u001b[39m\u001b[39mNone\u001b[39;00m, allowed_args\u001b[39m=\u001b[39m[\u001b[39m\"\u001b[39m\u001b[39mself\u001b[39m\u001b[39m\"\u001b[39m, \u001b[39m\"\u001b[39m\u001b[39mlabels\u001b[39m\u001b[39m\"\u001b[39m])\n\u001b[0;32m   4809\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39mdrop\u001b[39m(\n\u001b[0;32m   4810\u001b[0m     \u001b[39mself\u001b[39m,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   4817\u001b[0m     errors: \u001b[39mstr\u001b[39m \u001b[39m=\u001b[39m \u001b[39m\"\u001b[39m\u001b[39mraise\u001b[39m\u001b[39m\"\u001b[39m,\n\u001b[0;32m   4818\u001b[0m ):\n\u001b[0;32m   4819\u001b[0m \u001b[39m    \u001b[39m\u001b[39m\"\"\"\u001b[39;00m\n\u001b[0;32m   4820\u001b[0m \u001b[39m    Drop specified labels from rows or columns.\u001b[39;00m\n\u001b[0;32m   4821\u001b[0m \n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   4954\u001b[0m \u001b[39m            weight  1.0     0.8\u001b[39;00m\n\u001b[0;32m   4955\u001b[0m \u001b[39m    \"\"\"\u001b[39;00m\n\u001b[1;32m-> 4956\u001b[0m     \u001b[39mreturn\u001b[39;00m \u001b[39msuper\u001b[39;49m()\u001b[39m.\u001b[39;49mdrop(\n\u001b[0;32m   4957\u001b[0m         labels\u001b[39m=\u001b[39;49mlabels,\n\u001b[0;32m   4958\u001b[0m         axis\u001b[39m=\u001b[39;49maxis,\n\u001b[0;32m   4959\u001b[0m         index\u001b[39m=\u001b[39;49mindex,\n\u001b[0;32m   4960\u001b[0m         columns\u001b[39m=\u001b[39;49mcolumns,\n\u001b[0;32m   4961\u001b[0m         level\u001b[39m=\u001b[39;49mlevel,\n\u001b[0;32m   4962\u001b[0m         inplace\u001b[39m=\u001b[39;49minplace,\n\u001b[0;32m   4963\u001b[0m         errors\u001b[39m=\u001b[39;49merrors,\n\u001b[0;32m   4964\u001b[0m     )\n",
      "File \u001b[1;32mc:\\Python310\\lib\\site-packages\\pandas\\core\\generic.py:4279\u001b[0m, in \u001b[0;36mNDFrame.drop\u001b[1;34m(self, labels, axis, index, columns, level, inplace, errors)\u001b[0m\n\u001b[0;32m   4277\u001b[0m \u001b[39mfor\u001b[39;00m axis, labels \u001b[39min\u001b[39;00m axes\u001b[39m.\u001b[39mitems():\n\u001b[0;32m   4278\u001b[0m     \u001b[39mif\u001b[39;00m labels \u001b[39mis\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39mNone\u001b[39;00m:\n\u001b[1;32m-> 4279\u001b[0m         obj \u001b[39m=\u001b[39m obj\u001b[39m.\u001b[39;49m_drop_axis(labels, axis, level\u001b[39m=\u001b[39;49mlevel, errors\u001b[39m=\u001b[39;49merrors)\n\u001b[0;32m   4281\u001b[0m \u001b[39mif\u001b[39;00m inplace:\n\u001b[0;32m   4282\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_update_inplace(obj)\n",
      "File \u001b[1;32mc:\\Python310\\lib\\site-packages\\pandas\\core\\generic.py:4323\u001b[0m, in \u001b[0;36mNDFrame._drop_axis\u001b[1;34m(self, labels, axis, level, errors, consolidate, only_slice)\u001b[0m\n\u001b[0;32m   4321\u001b[0m         new_axis \u001b[39m=\u001b[39m axis\u001b[39m.\u001b[39mdrop(labels, level\u001b[39m=\u001b[39mlevel, errors\u001b[39m=\u001b[39merrors)\n\u001b[0;32m   4322\u001b[0m     \u001b[39melse\u001b[39;00m:\n\u001b[1;32m-> 4323\u001b[0m         new_axis \u001b[39m=\u001b[39m axis\u001b[39m.\u001b[39;49mdrop(labels, errors\u001b[39m=\u001b[39;49merrors)\n\u001b[0;32m   4324\u001b[0m     indexer \u001b[39m=\u001b[39m axis\u001b[39m.\u001b[39mget_indexer(new_axis)\n\u001b[0;32m   4326\u001b[0m \u001b[39m# Case for non-unique axis\u001b[39;00m\n\u001b[0;32m   4327\u001b[0m \u001b[39melse\u001b[39;00m:\n",
      "File \u001b[1;32mc:\\Python310\\lib\\site-packages\\pandas\\core\\indexes\\base.py:6644\u001b[0m, in \u001b[0;36mIndex.drop\u001b[1;34m(self, labels, errors)\u001b[0m\n\u001b[0;32m   6642\u001b[0m \u001b[39mif\u001b[39;00m mask\u001b[39m.\u001b[39many():\n\u001b[0;32m   6643\u001b[0m     \u001b[39mif\u001b[39;00m errors \u001b[39m!=\u001b[39m \u001b[39m\"\u001b[39m\u001b[39mignore\u001b[39m\u001b[39m\"\u001b[39m:\n\u001b[1;32m-> 6644\u001b[0m         \u001b[39mraise\u001b[39;00m \u001b[39mKeyError\u001b[39;00m(\u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m{\u001b[39;00m\u001b[39mlist\u001b[39m(labels[mask])\u001b[39m}\u001b[39;00m\u001b[39m not found in axis\u001b[39m\u001b[39m\"\u001b[39m)\n\u001b[0;32m   6645\u001b[0m     indexer \u001b[39m=\u001b[39m indexer[\u001b[39m~\u001b[39mmask]\n\u001b[0;32m   6646\u001b[0m \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mdelete(indexer)\n",
      "\u001b[1;31mKeyError\u001b[0m: \"['dismissal'] not found in axis\""
     ]
    }
   ],
   "source": [
    "df_batting.drop(columns=['dismissal'],inplace=True)\n",
    "df_batting.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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  \n",
       "0     6  0  0   50.00         out  \n",
       "1     9  1  0  100.00         out  \n",
       "2    12  1  2  166.66         out  \n",
       "3    24  2  0  108.33         out  \n",
       "4    24  0  0   83.33         out  \n",
       "5    28  4  0  157.14         out  \n",
       "6     1  0  0    0.00         out  \n",
       "7    16  2  2  193.75     not out  \n",
       "8    10  1  0   90.00         out  \n",
       "9     6  0  0  100.00         out  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove special chars\n",
    "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(10)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "join two tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Namibia Vs Sri Lanka': 'T20I # 1823',\n",
       " 'Sri Lanka Vs Namibia': 'T20I # 1823',\n",
       " 'Netherlands Vs U.A.E.': 'T20I # 1825',\n",
       " 'U.A.E. Vs Netherlands': 'T20I # 1825',\n",
       " 'Scotland Vs West Indies': 'T20I # 1826',\n",
       " 'West Indies Vs Scotland': 'T20I # 1826',\n",
       " 'Ireland Vs Zimbabwe': 'T20I # 1828',\n",
       " 'Zimbabwe Vs Ireland': 'T20I # 1828',\n",
       " 'Namibia Vs Netherlands': 'T20I # 1830',\n",
       " 'Netherlands Vs Namibia': 'T20I # 1830',\n",
       " 'Sri Lanka Vs U.A.E.': 'T20I # 1832',\n",
       " 'U.A.E. Vs Sri Lanka': 'T20I # 1832',\n",
       " 'Ireland Vs Scotland': 'T20I # 1833',\n",
       " 'Scotland Vs Ireland': 'T20I # 1833',\n",
       " 'West Indies Vs Zimbabwe': 'T20I # 1834',\n",
       " 'Zimbabwe Vs West Indies': 'T20I # 1834',\n",
       " 'Netherlands Vs Sri Lanka': 'T20I # 1835',\n",
       " 'Sri Lanka Vs Netherlands': 'T20I # 1835',\n",
       " 'Namibia Vs U.A.E.': 'T20I # 1836',\n",
       " 'U.A.E. Vs Namibia': 'T20I # 1836',\n",
       " 'Ireland Vs West Indies': 'T20I # 1837',\n",
       " 'West Indies Vs Ireland': 'T20I # 1837',\n",
       " 'Scotland Vs Zimbabwe': 'T20I # 1838',\n",
       " 'Zimbabwe Vs Scotland': 'T20I # 1838',\n",
       " 'Australia Vs New Zealand': 'T20I # 1839',\n",
       " 'New Zealand Vs Australia': 'T20I # 1839',\n",
       " 'Afghanistan Vs England': 'T20I # 1840',\n",
       " 'England Vs Afghanistan': 'T20I # 1840',\n",
       " 'Ireland Vs Sri Lanka': 'T20I # 1841',\n",
       " 'Sri Lanka Vs Ireland': 'T20I # 1841',\n",
       " 'India Vs Pakistan': 'T20I # 1842',\n",
       " 'Pakistan Vs India': 'T20I # 1842',\n",
       " 'Bangladesh Vs Netherlands': 'T20I # 1843',\n",
       " 'Netherlands Vs Bangladesh': 'T20I # 1843',\n",
       " 'South Africa Vs Zimbabwe': 'T20I # 1844',\n",
       " 'Zimbabwe Vs South Africa': 'T20I # 1844',\n",
       " 'Australia Vs Sri Lanka': 'T20I # 1845',\n",
       " 'Sri Lanka Vs Australia': 'T20I # 1845',\n",
       " 'England Vs Ireland': 'T20I # 1846',\n",
       " 'Ireland Vs England': 'T20I # 1846',\n",
       " 'Afghanistan Vs New Zealand': 'T20I # 1846a',\n",
       " 'New Zealand Vs Afghanistan': 'T20I # 1846a',\n",
       " 'Bangladesh Vs South Africa': 'T20I # 1847',\n",
       " 'South Africa Vs Bangladesh': 'T20I # 1847',\n",
       " 'India Vs Netherlands': 'T20I # 1848',\n",
       " 'Netherlands Vs India': 'T20I # 1848',\n",
       " 'Pakistan Vs Zimbabwe': 'T20I # 1849',\n",
       " 'Zimbabwe Vs Pakistan': 'T20I # 1849',\n",
       " 'Afghanistan Vs Ireland': 'T20I # 1849a',\n",
       " 'Ireland Vs Afghanistan': 'T20I # 1849a',\n",
       " 'Australia Vs England': 'T20I # 1849b',\n",
       " 'England Vs Australia': 'T20I # 1849b',\n",
       " 'New Zealand Vs Sri Lanka': 'T20I # 1850',\n",
       " 'Sri Lanka Vs New Zealand': 'T20I # 1850',\n",
       " 'Bangladesh Vs Zimbabwe': 'T20I # 1851',\n",
       " 'Zimbabwe Vs Bangladesh': 'T20I # 1851',\n",
       " 'Netherlands Vs Pakistan': 'T20I # 1852',\n",
       " 'Pakistan Vs Netherlands': 'T20I # 1852',\n",
       " 'India Vs South Africa': 'T20I # 1853',\n",
       " 'South Africa Vs India': 'T20I # 1853',\n",
       " 'Australia Vs Ireland': 'T20I # 1855',\n",
       " 'Ireland Vs Australia': 'T20I # 1855',\n",
       " 'Afghanistan Vs Sri Lanka': 'T20I # 1856',\n",
       " 'Sri Lanka Vs Afghanistan': 'T20I # 1856',\n",
       " 'England Vs New Zealand': 'T20I # 1858',\n",
       " 'New Zealand Vs England': 'T20I # 1858',\n",
       " 'Netherlands Vs Zimbabwe': 'T20I # 1859',\n",
       " 'Zimbabwe Vs Netherlands': 'T20I # 1859',\n",
       " 'Bangladesh Vs India': 'T20I # 1860',\n",
       " 'India Vs Bangladesh': 'T20I # 1860',\n",
       " 'Pakistan Vs South Africa': 'T20I # 1861',\n",
       " 'South Africa Vs Pakistan': 'T20I # 1861',\n",
       " 'Ireland Vs New Zealand': 'T20I # 1862',\n",
       " 'New Zealand Vs Ireland': 'T20I # 1862',\n",
       " 'Australia Vs Afghanistan': 'T20I # 1864',\n",
       " 'Afghanistan Vs Australia': 'T20I # 1864',\n",
       " 'England Vs Sri Lanka': 'T20I # 1867',\n",
       " 'Sri Lanka Vs England': 'T20I # 1867',\n",
       " 'Netherlands Vs South Africa': 'T20I # 1871',\n",
       " 'South Africa Vs Netherlands': 'T20I # 1871',\n",
       " 'Bangladesh Vs Pakistan': 'T20I # 1872',\n",
       " 'Pakistan Vs Bangladesh': 'T20I # 1872',\n",
       " 'India Vs Zimbabwe': 'T20I # 1873',\n",
       " 'Zimbabwe Vs India': 'T20I # 1873',\n",
       " 'New Zealand Vs Pakistan': 'T20I # 1877',\n",
       " 'Pakistan Vs New Zealand': 'T20I # 1877',\n",
       " 'England Vs India': 'T20I # 1878',\n",
       " 'India Vs England': 'T20I # 1878',\n",
       " 'England Vs Pakistan': 'T20I # 1879',\n",
       " 'Pakistan Vs England': 'T20I # 1879'}"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_ids_dict = {}\n",
    "for index, row in df_match.iterrows():\n",
    "    key1 = row['team1'] + ' Vs ' + row['team2']\n",
    "    key2 = row['team2'] + ' Vs ' + row['team1']\n",
    "\n",
    "    match_ids_dict[key1] = row['match_id']\n",
    "    match_ids_dict[key2] = row['match_id']\n",
    "match_ids_dict\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'T20I # 1836'"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_ids_dict[\"Namibia Vs U.A.E.\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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",
       "  </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": 29,
     "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": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_batting.to_csv('t20_csv_files/fact_batting_summary.csv',index=False)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transform Bowling summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "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",
    "df_bowling = pd.DataFrame(all_records)\n",
    "df_bowling['match_id'] = df_bowling['match'].map(match_ids_dict)\n",
    "df_bowling.to_csv('t20_csv_files/fact_bowling_summary.csv', index = False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transform Players info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('t20_json_files/t20_wc_player_info.json') as f:\n",
    "    data = json.load(f)\n",
    "df_players = pd.DataFrame(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cleanup special chars\n",
    "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', ''))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_players[df_players['team'] == 'India']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_players.to_csv('t20_csv_files/dim_players_no_images.csv', index = False)\n",
    "# manually images have to be added later"
   ]
  }
 ],
 "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.10.1"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}