data_manipulation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c8a5e407-9897-40f5-a565-f0fb59c997c7",
   "metadata": {},
   "source": [
    "### Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "34570aeb-6b7d-4c59-921a-611488eee075",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4eb32da0-27ae-4fd7-9720-0178f3a48dfb",
   "metadata": {},
   "source": [
    "### Making Connection to Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94aac770-8e91-4c2f-ae79-7e10b766041f",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect(\"my_database.db\")\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1444d33-b7fd-40b3-a275-d7d50b62cb5a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "abcdc6f1-72e9-4760-936c-a5c111d0a70e",
   "metadata": {},
   "source": [
    "### Loading Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4b1a12a9-767b-40c5-90e1-feccec56789c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"datasets/batting_summary.csv\").rename(columns={\"Batsman_Name\":\"player_name\"})\n",
    "player_info = pd.read_csv(\"datasets/world_cup_players_info.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8c599576-212d-4f75-8284-4b44663aa1ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "batsman = pd.merge(player_info,df,on='player_name',how='right')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "05a7e5e3-c227-41a3-ba35-06d34b871295",
   "metadata": {},
   "outputs": [],
   "source": [
    "batsman = batsman.drop(['team_name','image_of_player','description','bowlingStyle'],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bf06530-584a-49ca-ad77-e931b8506f69",
   "metadata": {},
   "source": [
    "### Removing Missing Values and Changing Datatype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "819ac0a1-03ef-4e2b-809f-c0e9d5b186c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = batsman.drop(batsman[batsman['Strike_Rate']=='---'].index)\n",
    "temp['Strike_Rate'] = temp['Strike_Rate'].astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "642ca408-7ac2-43fc-9f18-4e52a6ce3e33",
   "metadata": {},
   "source": [
    "### Making a new Table using Group by on Batsman Name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "b10c0e6f-6095-4410-a706-2113961f66b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def count4runs(series):\n",
    "    return series.sum()*4\n",
    "def count6runs(series):\n",
    "    return series.sum()*6\n",
    "result = temp.groupby('player_name').agg({\n",
    "    'Team_Innings': 'first',    \n",
    "    'Runs': 'sum',               \n",
    "    'Balls': 'sum',              \n",
    "    'Strike_Rate': \"mean\",       \n",
    "    'player_name': 'size',      \n",
    "    '4s':count4runs,\n",
    "    '6s':count6runs,\n",
    "    'Batting_Position': lambda x: round(x.mean(), 0),\n",
    "    'battingStyle':'first',\n",
    "    'playingRole':'first'\n",
    "}).rename(columns={'player_name': 'Matches','Team_Innings':'Team'}).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "901c21f4-e7ca-46aa-ad6f-de1d6ac59a46",
   "metadata": {},
   "source": [
    "### Adding More Columns by simple calculations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "868aec3e-cd18-4f57-88ab-069049502dfa",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = result.sort_values('Matches', ascending=False)\n",
    "result['Batting_Average'] = result['Runs']/result['Matches']\n",
    "result['Runs By Boundaries'] = result['4s'] + result['6s']\n",
    "result['Batting_Position'] = result['Batting_Position'].astype(int)\n",
    "result['Boundary%'] = (result['Runs By Boundaries']*100)/result['Runs']\n",
    "result['Boundary%'].fillna(0,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23732d0b-5097-46ee-8552-c2336b89f15b",
   "metadata": {},
   "source": [
    "### Droping unecessary Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "fa7db636-580b-44ed-a2dc-b8bf5019cafe",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = result.drop(['Runs','Balls','4s','6s','Runs By Boundaries'],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53e3d96a-426e-4019-b239-659064cb6259",
   "metadata": {},
   "source": [
    "### Rounding off Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "90799d72-b0af-47a1-8b7a-391b066bc4bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = result.round({'Strike_Rate':2,\"Batting_Average\":2,\"Boundary%\":2})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f23b8f9-e118-4124-a738-1ca73938375e",
   "metadata": {},
   "source": [
    "### Rearanging column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "5295659d-16d6-4022-bab0-26605b75bcf8",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_batting = result.loc[:,['player_name','Team','battingStyle','playingRole','Batting_Position','Matches','Strike_Rate','Batting_Average','Boundary%']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d76f154-eeb6-43aa-9486-f533c7d8ee89",
   "metadata": {},
   "source": [
    "### Writing data into database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "ebbe9639-660b-4d5a-b603-1981e8cb2024",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "146"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_batting.to_sql('batters', conn, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68d7ed4d-18bd-41cc-9a4b-0738db444cc4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6dcdb6a5-56d1-43ca-a015-38ac72df3195",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Prep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4fabc45a-747e-41de-8c0d-05cc5934d79a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "3dbab9ec-2f5c-465a-a1d4-6909a5001e74",
   "metadata": {},
   "source": [
    "### Loading Bowling Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "9c70c761-feea-4069-957c-0ffb473af9af",
   "metadata": {},
   "outputs": [],
   "source": [
    "bowlers = pd.read_csv(\"datasets/bowling_summary.csv\").rename(columns={'Bowler_Name':'player_name'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "9a372c1b-8b9c-4fdf-8aa4-a13c6da25666",
   "metadata": {},
   "outputs": [],
   "source": [
    "bowlers = pd.merge(bowlers,player_info,on='player_name',how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "440f2a30-5fae-4c3d-8a30-81ea0ba829b8",
   "metadata": {},
   "source": [
    "### Making a new Table using group by on Bowler Name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "0c93db85-0986-43c9-a707-b7ba5af56524",
   "metadata": {},
   "outputs": [],
   "source": [
    "b_result = bowlers.groupby('player_name').agg({\n",
    "    'team_name':'first',\n",
    "    'Overs':'sum',\n",
    "    'Runs':'sum',\n",
    "    'Wickets':'sum',\n",
    "    'Economy':'mean',\n",
    "    'bowlingStyle':'first',\n",
    "    'player_name':'size',\n",
    "}).rename(columns={'player_name':'Matches'}).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "597032cf-a555-4da5-90b9-0ec14796460c",
   "metadata": {},
   "source": [
    "### Adding more Columns by simple calculations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "ff13c607-1026-49ab-bcef-0c9add45e1f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "b_result['Balls'] = ((b_result['Overs'].astype(int) * 6) + (b_result['Overs']-b_result['Overs'].astype(int))*10).astype(int)\n",
    "b_result['strike_rate'] = b_result['Balls']/b_result['Wickets']\n",
    "b_result['strike_rate'] = b_result['strike_rate'].replace(np.inf,0)\n",
    "b_result['Bowling_Average'] = b_result['Runs']/b_result['Wickets']\n",
    "b_result['Bowling_Average'] = b_result['Bowling_Average'].replace(np.inf,0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02dd9c31-9137-4df2-8650-3dbf2738a87e",
   "metadata": {},
   "source": [
    "### Droping unnecssary columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "1e79348f-39c3-4ee6-8225-365b530e32dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "b_temp = b_result.drop(['Overs','Balls','Runs','Wickets'],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17494a6d-e151-4bac-adbd-36ffe0ccd311",
   "metadata": {},
   "source": [
    "### Rounding off values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "d611db9e-56b7-4b0b-9faa-9cf68cca777b",
   "metadata": {},
   "outputs": [],
   "source": [
    "b_temp = b_temp.round({'Economy':2,\"strike_rate\":2,\"Bowling_Average\":2})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35e72555-10b3-41f0-8e31-e30dfde1ca64",
   "metadata": {},
   "source": [
    "### Changing order of columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "6eda51d6-9d2b-4491-b14e-5e64b348b99c",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_bowling = b_temp.loc[:,['player_name','team_name','bowlingStyle','Matches','Economy','strike_rate','Bowling_Average']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75c312a0-0520-4e38-8bda-3a712e5832cd",
   "metadata": {},
   "source": [
    "### Extracting Top Fast Bowlers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "a2026295-dc96-4743-a742-b5b6b997d3aa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "102"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_bowling.to_sql('bowlers', conn, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b42271f-914e-4f29-8324-0e105c1c6b3a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "a893d2e4-51ac-4f90-81b4-479fb021765d",
   "metadata": {},
   "source": [
    "## Preparing All Rounders Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "266a0bcc-476a-402d-8bc9-089dbdf88e81",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "b5a0dae3-92b7-4ed2-84aa-ca95352f141c",
   "metadata": {},
   "source": [
    "### Merging Bowling and Batting Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "4ffa1272-5c26-41b6-a74c-85670435c905",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_rounder = pd.merge(final_bowling,final_batting,on='player_name',how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96598263-3072-4e5b-9aa7-631c6fa4ae1e",
   "metadata": {},
   "source": [
    "### Droping unnecessary columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "2978d519-9ee1-4e99-bc4e-6170472838d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_rounder = all_rounder.drop(['team_name','bowlingStyle','Bowling_Average','battingStyle','Boundary%'],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83e3b1a0-bf66-4da6-b756-d0998a1f9719",
   "metadata": {},
   "source": [
    "### Changing Columns name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "fe652e7c-6f46-477f-858b-f4d9a74b2acf",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_rounder = all_rounder.rename(columns = {'strike_rate':'Bowling_Sr','Matches_y':'Innings_batted','Matches_x':'Innings_bowled','Strike_Rate':'Batting_Sr'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eebe4f87-d47c-412c-b806-8c8e9076c51c",
   "metadata": {},
   "source": [
    "### Rearranging Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "f3511399-462e-4868-af81-93c8d195b246",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_rounder = all_rounder.loc[:,['player_name','Team','playingRole','Innings_batted','Batting_Position','Batting_Average','Batting_Sr','Innings_bowled','Economy','Bowling_Sr']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f92a8a24-beda-49e5-b3a3-1ed5352ea97c",
   "metadata": {},
   "source": [
    "### Writing data into database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "15924432-6b16-4a9f-9684-db8b713f6bd4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_rounder.to_sql('all_rounders', conn, if_exists='replace', index= False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1662d335-8679-4c48-8c24-95c8250282b9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56f64eb2-3f33-4f3d-abca-333bd6741776",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4107ce2-5707-44f8-8442-f8212dabf059",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22c1b222-de40-4d62-9e6b-6ea286fa5f8f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1fff9b2d-7f1b-4645-b75c-576aafb00c92",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "560cf82b-4d1d-4d02-aa6a-1b52f2ff866a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "d4d5a57c-c892-429b-859b-ca904fa26ece",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "d586ff63-a7c9-4c1e-a466-294a747f7bce",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c73425db-7946-415d-86cb-9bb0f9a45a0e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "9dad2318-554e-4245-aff4-36707aafe70e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "102"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "e1100825-3e49-4b9b-a584-8f3ab8bcab7f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ef8759d-38bf-41b9-b8cc-ef826c48a63c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}