Data_preparation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os \n",
    "from os import walk\n",
    "import json\n",
    "from pandas.io.json import json_normalize\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.width', 1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\DS\\Anaconda\\lib\\site-packages\\ipykernel_launcher.py:75: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "D:\\DS\\Anaconda\\lib\\site-packages\\ipykernel_launcher.py:70: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "match_files =f\"Data\\Match_data\"\n",
    "\n",
    "files = []\n",
    "for (dirpath, dirnames, filenames) in walk(match_files):\n",
    "    files.extend(filenames)\n",
    "    break\n",
    "    \n",
    "\n",
    "ds_match =pd.DataFrame()\n",
    "ds_events =pd.DataFrame()\n",
    "ds_teams =pd.DataFrame()\n",
    "print(ds_match.shape[0])\n",
    "\n",
    "def JSONparse(js):\n",
    "    global ds_match\n",
    "    global ds_events\n",
    "    global ds_teams\n",
    "    _dict ={}\n",
    "    match_id = js[\"match_detail\"][\"match_id\"]\n",
    "    match_number = js[\"match_detail\"][\"match_number\"]\n",
    "    date = js[\"match_detail\"][\"date\"]\n",
    "    start_time = js[\"match_detail\"][\"start_time\"]\n",
    "    try:\n",
    "        result = js[\"match_detail\"][\"result\"][\"value\"]\n",
    "    except:\n",
    "        result = np.nan\n",
    "    try:\n",
    "        player_id_of_the_match = js[\"match_detail\"][\"player_of_the_match\"][0][\"id\"]\n",
    "        player_name_of_the_match = js[\"match_detail\"][\"player_of_the_match\"][0][\"id\"]\n",
    "    except:\n",
    "        player_id_of_the_match =np.nan\n",
    "        player_name_of_the_match =np.nan\n",
    "    series_id = js[\"match_detail\"][\"series\"][\"id\"]\n",
    "    series_name = js[\"match_detail\"][\"series\"][\"name\"]\n",
    "    status = js[\"match_detail\"][\"status\"]\n",
    "    toss_winner = js[\"match_detail\"][\"toss\"][\"winner\"]\n",
    "    toss_selection = js[\"match_detail\"][\"toss\"][\"selection\"]\n",
    "    venue_id = js[\"match_detail\"][\"venue\"][\"id\"]\n",
    "    venue_name = js[\"match_detail\"][\"venue\"][\"name\"]\n",
    "    try:\n",
    "        home_team_id = js[\"teams\"][\"home_team_id\"]\n",
    "        home_team_name = js[\"teams\"][\"home_team_name\"]\n",
    "    except:\n",
    "        home_team_id = np.nan\n",
    "        home_team_name = np.nan\n",
    "    \n",
    "    events = pd.DataFrame(json_normalize(distros_dict[\"events\"],\"event\"))\n",
    "    events[\"match_id\"] = match_id\n",
    "    teams=pd.DataFrame(json_normalize(js[\"teams\"], 'team'))\n",
    "    teams[\"match_id\"] = match_id\n",
    "    \n",
    "    \n",
    "    \n",
    "    _dict ={\"match_id\":match_id,\"match_number\":match_number,\n",
    "           \"date\":date,\"start_time\":start_time,\"result\":result,\n",
    "            \"player_id_of_the_match\":player_id_of_the_match,\"player_name_of_the_match\":player_name_of_the_match,\n",
    "          \"series_id\":series_id,\"series_name\":series_name,\"status\":status,\n",
    "           \"toss_winner\":toss_winner,\"toss_selection\":toss_selection,\"venue_id\":venue_id,\"venue_name\":venue_name,\n",
    "           \"home_team_id\":home_team_id,\"home_team_name\":home_team_name} \n",
    "    #print(_dict)\n",
    "    #_temp=pd.DataFrame(_dict)\n",
    "    if(ds_match.shape[0] ==0):\n",
    "        ds_match = pd.DataFrame(_dict,index=[0])\n",
    "    else:\n",
    "        ds_match = ds_match.append(_dict, ignore_index=True)\n",
    "    \n",
    "    if (ds_teams.shape[0]==0):\n",
    "        ds_teams = teams.copy()\n",
    "    else:\n",
    "        ds_teams = pd.concat([ds_teams,teams])\n",
    "    \n",
    "    if(ds_events.shape[0]==0):\n",
    "        ds_events = events.copy()\n",
    "    else:\n",
    "        ds_events = pd.concat([ds_events,events])\n",
    "    \n",
    "    \n",
    "    \n",
    "    \n",
    "    \n",
    "for i in files:\n",
    "    file = match_files+ f\"\\\\\" + i\n",
    "    \n",
    "    with open(file, 'r') as f:\n",
    "        distros_dict = json.load(f)\n",
    "        JSONparse(distros_dict)\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    " with open(\"Data\\\\Match_data\\\\101.json\", 'r') as f:\n",
    "        distros_dict = json.load(f)\n",
    "        #print(json_normalize(distros_dict[\"teams\"], 'team'))\n",
    "        print(distros_dict[\"match_detail\"][\"player_of_the_match\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'match_id', 'name', 'score', 'short_name', 'squad', 'state_of_play', 'state_of_play.is_raiding_now', 'state_of_play.last_raid.id', 'state_of_play.last_raid.result', 'state_of_play.players', 'state_of_play.players_on_court', 'stats.all_outs', 'stats.declare', 'stats.points.all_out', 'stats.points.declare', 'stats.points.extras', 'stats.points.raid_points.raid_bonus', 'stats.points.raid_points.total', 'stats.points.raid_points.touch', 'stats.points.tackle_points.capture', 'stats.points.tackle_points.capture_bonus', 'stats.points.tackle_points.total', 'stats.points.total', 'stats.raids.Empty', 'stats.raids.successful', 'stats.raids.super_raids', 'stats.raids.total', 'stats.raids.unsuccessful', 'stats.tackles.successful', 'stats.tackles.super_tackles', 'stats.tackles.total', 'stats.tackles.unsuccessful'], dtype='object')"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_teams.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "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>index</th>\n",
       "      <th>player_id</th>\n",
       "      <th>player_name</th>\n",
       "      <th>player_short_name</th>\n",
       "      <th>player_skill</th>\n",
       "      <th>player_role</th>\n",
       "      <th>player_red_card</th>\n",
       "      <th>player_yellow_card</th>\n",
       "      <th>player_green_card</th>\n",
       "      <th>player_jersey</th>\n",
       "      <th>player_played</th>\n",
       "      <th>player_captain</th>\n",
       "      <th>player_on_court</th>\n",
       "      <th>player_on_starter</th>\n",
       "      <th>player_total_points</th>\n",
       "      <th>player_raid_points_total</th>\n",
       "      <th>player_raid_points_touch</th>\n",
       "      <th>player_raid_points_raid_bonus</th>\n",
       "      <th>player_tackle_points_total</th>\n",
       "      <th>player_tackle_points_touch</th>\n",
       "      <th>player_tackle_points_capture_bonus</th>\n",
       "      <th>player_raids_total</th>\n",
       "      <th>player_raids_successful</th>\n",
       "      <th>player_raids_unsuccessful</th>\n",
       "      <th>player_raids_Empty</th>\n",
       "      <th>player_tackles_total</th>\n",
       "      <th>player_tackles_successful</th>\n",
       "      <th>player_tackles_unsuccessful</th>\n",
       "      <th>match_id</th>\n",
       "      <th>team_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>Sanjay Shrestha</td>\n",
       "      <td>Sanjay Shrestha</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>88</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>96</td>\n",
       "      <td>Rohit Rana</td>\n",
       "      <td>Rohit Rana</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>10</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>72</td>\n",
       "      <td>Navneet Gautam</td>\n",
       "      <td>Navneet Gautam</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>9</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>52</td>\n",
       "      <td>Jasvir Singh</td>\n",
       "      <td>Jasvir Singh</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>12</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>Wei Yang</td>\n",
       "      <td>Wei Yang</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>20</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  player_id      player_name player_short_name player_skill player_role  player_red_card  player_yellow_card  player_green_card  player_jersey  player_played  player_captain  player_on_court  player_on_starter  player_total_points  player_raid_points_total  player_raid_points_touch  player_raid_points_raid_bonus  player_tackle_points_total  player_tackle_points_touch  player_tackle_points_capture_bonus  player_raids_total  player_raids_successful  player_raids_unsuccessful  player_raids_Empty  player_tackles_total  player_tackles_successful  player_tackles_unsuccessful  match_id  team_id\n",
       "0      0         15  Sanjay Shrestha   Sanjay Shrestha                                     False               False              False             88           True           False             True               True                    0                         0                         0                              0                           0                           0                                   0                   0                        0                          0                   0                     0                          0                            0         1        3\n",
       "0      0         96       Rohit Rana        Rohit Rana                                     False               False              False             10           True           False             True               True                    0                         0                         0                              0                           0                           0                                   0                   1                        0                          0                   1                     0                          0                            0         1        3\n",
       "0      0         72   Navneet Gautam    Navneet Gautam                                     False               False              False              9           True            True             True               True                    0                         0                         0                              0                           0                           0                                   0                   1                        0                          0                   1                     0                          0                            0         1        3\n",
       "0      0         52     Jasvir Singh      Jasvir Singh                                     False               False              False              2           True           False            False               True                   12                         8                         7                              1                           4                           4                                   1                  12                        7                          3                   2                    10                          3                            7         1        3\n",
       "0      0         22         Wei Yang          Wei Yang                                     False               False              False             20           True           False             True               True                    0                         0                         0                              0                           0                           0                                   0                   0                        0                          0                   0                     0                          0                            0         1        3"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#preparing Player Stats\n",
    "ds_players=pd.DataFrame()\n",
    "\n",
    "for i in range(0,ds_teams.shape[0]):\n",
    "    players =ds_teams.iloc[i].squad\n",
    "    for j in range(len(players)):\n",
    "        \n",
    "        _dict={}\n",
    "        \n",
    "        player_id = players[j][\"id\"]\n",
    "        player_name = players[j][\"name\"]\n",
    "        player_short_name = players[j][\"short_name\"]\n",
    "        player_skill = players[j][\"skill\"]\n",
    "        player_role = players[j][\"role\"]\n",
    "        player_red_card = players[j][\"red_card\"]\n",
    "        player_yellow_card = players[j][\"yellow_card\"]\n",
    "        player_green_card = players[j][\"green_card\"]\n",
    "        player_jersey = players[j][\"jersey\"]\n",
    "        player_played = players[j][\"played\"]\n",
    "        player_captain = players[j][\"captain\"]\n",
    "        player_on_court = players[j][\"on_court\"]\n",
    "        player_on_starter = players[j][\"starter\"]\n",
    "        player_total_points = players[j][\"points\"][\"total\"]\n",
    "        player_raid_points_total = players[j][\"points\"][\"raid_points\"][\"total\"]\n",
    "        player_raid_points_touch = players[j][\"points\"][\"raid_points\"][\"touch\"]\n",
    "        player_raid_points_raid_bonus = players[j][\"points\"][\"raid_points\"][\"raid_bonus\"]\n",
    "        \n",
    "        player_tackle_points_total = players[j][\"points\"][\"tackle_points\"][\"total\"]\n",
    "        player_tackle_points_touch = players[j][\"points\"][\"tackle_points\"][\"capture\"]\n",
    "        player_tackle_points_capture_bonus = players[j][\"points\"][\"tackle_points\"][\"capture_bonus\"]\n",
    "        \n",
    "        player_raids_total = players[j][\"raids\"][\"total\"]\n",
    "        player_raids_successful = players[j][\"raids\"][\"successful\"]\n",
    "        player_raids_unsuccessful = players[j][\"raids\"][\"unsuccessful\"]\n",
    "        player_raids_Empty = players[j][\"raids\"][\"Empty\"]\n",
    "        \n",
    "        player_tackles_total = players[j][\"tackles\"][\"total\"]\n",
    "        player_tackles_successful = players[j][\"tackles\"][\"successful\"]\n",
    "        player_tackles_unsuccessful = players[j][\"tackles\"][\"unsuccessful\"]\n",
    "        #strong_zones =str((players[j][\"strong_zones\"]))\n",
    "        #weak_zones =players[j][\"weak_zones\"]\n",
    "        #print(players[j][\"weak_zones\"])\n",
    "        \n",
    "        top_defender = players[j][\"strong_zones\"]\n",
    "        \n",
    "        _dict = {\"player_id\":player_id,\"player_name\":player_name,\"player_short_name\":player_short_name,\"player_short_name\":player_short_name,\n",
    "                \"player_skill\":player_skill,\"player_role\":player_role,\"player_red_card\":player_red_card,\"player_yellow_card\":player_yellow_card,\n",
    "                \"player_green_card\":player_green_card,\"player_jersey\":player_jersey,\"player_played\":player_played,\"player_captain\":player_captain,\n",
    "                \"player_on_court\":player_on_court,\"player_on_starter\":player_on_starter,\"player_total_points\":player_total_points,\n",
    "               \"player_raid_points_total\":player_raid_points_total,\"player_raid_points_touch\":player_raid_points_touch,\"player_raid_points_raid_bonus\":player_raid_points_raid_bonus,\n",
    "                \"player_tackle_points_total\":player_tackle_points_total,\"player_tackle_points_touch\":player_tackle_points_touch,\n",
    "                \"player_tackle_points_capture_bonus\":player_tackle_points_capture_bonus,\n",
    "                \"player_raids_total\":player_raids_total,\"player_raids_successful\":player_raids_successful,\"player_raids_unsuccessful\":player_raids_unsuccessful,\n",
    "                \"player_raids_Empty\":player_raids_Empty, \"player_tackles_total\":player_tackles_total,\n",
    "                 \"player_tackles_successful\":player_tackles_successful,\"player_tackles_unsuccessful\":player_tackles_unsuccessful                 \n",
    "                }\n",
    "        _temp = pd.DataFrame(_dict,index=[0])\n",
    "        \n",
    "        _temp[\"match_id\"] = ds_teams.iloc[i].match_id\n",
    "        _temp[\"team_id\"] = int(ds_teams.iloc[i].id)\n",
    "        _temp=_temp.reset_index()\n",
    "        \n",
    "        if(ds_players.shape[0] ==0):\n",
    "            ds_players = _temp\n",
    "        else:\n",
    "            ds_players = pd.concat([ds_players,_temp])\n",
    "            \n",
    "ds_players.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_match.to_csv(\"DS_match.csv\")\n",
    "ds_teams.to_csv(\"DS_team.csv\")\n",
    "ds_events.to_csv(\"DS_events.csv\")\n",
    "ds_players.to_csv(\"DS_players.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(684, 16)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "(1368, 33)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "(66568, 40)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "(16892, 30)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(ds_match.shape,ds_teams.shape,ds_events.shape,ds_players.shape)"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}