{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nsns.set(style=\"ticks\", context=\"talk\")\nplt.style.use('fivethirtyeight')\n\nfrom termcolor import colored\n\nimport plotly.express as px #graphing\nimport plotly.graph_objects as go #graphing\nfrom plotly.subplots import make_subplots #graphing\nimport plotly.figure_factory as ff #graphing\n\n\n# Iut data files are available in the read-only \"../iut/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the iut directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-08-17T18:10:10.692920Z","iopub.execute_input":"2022-08-17T18:10:10.693255Z","iopub.status.idle":"2022-08-17T18:10:13.374249Z","shell.execute_reply.started":"2022-08-17T18:10:10.693230Z","shell.execute_reply":"2022-08-17T18:10:13.372942Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#reading csv\n\ntrack = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_tracking_table.csv')\nstart = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_start_table.csv')\nrace = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_race_table.csv',parse_dates=['race_date'])\n# df = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_2019_complete.csv',parse_dates=['race_date'])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T18:10:13.376229Z","iopub.execute_input":"2022-08-17T18:10:13.376584Z","iopub.status.idle":"2022-08-17T18:10:19.751815Z","shell.execute_reply.started":"2022-08-17T18:10:13.376551Z","shell.execute_reply":"2022-08-17T18:10:19.750475Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **EDA :**\n* Distance_id : 100 = 1 furlong = 220 yards = 201.9m\n* purse :  refers to the total amount of money paid out to the owners of horses racing at a particular track\n* \"RACE\" is an event which consits of multiple types of race's.\n     - four factors determine one unique race.\n        - race_number\n        - race_type\n        - track_id\n        - race_date\n     - Therefore we'll have to introduce a unique identifier for every race like a **\"race_id\"**\n         - all four features we get in race table","metadata":{}},{"cell_type":"code","source":"# creating plotting functions \n\ndef barplot(a=None,b=None,data=None,title=None,hovertext=None,):\n    x = a\n    y = b\n    fig = go.Figure(data=[go.Bar(x=x,\n                                 y=y,\n                                 text=y,\n                                 textposition='auto',\n                                hovertext=hovertext)]\n                   )\n    # Customize aspect\n    fig.update_traces(marker_color=px.colors.qualitative.Alphabet,\n                      marker_line_width=1.5\n                     )\n    fig.update_xaxes(tickmode='linear')\n    fig.update_layout(paper_bgcolor=\"white\",\n                      template='simple_white',\n                      title_text=f'<b>{str(title)}<b>'\n                     )\n    fig.show()\n    \ndef multiple_plots(df,lst):\n    for i in range(len(lst)):\n        x=start[lst[i]].value_counts().index.values\n        y=start[lst[i]].value_counts().values\n        barplot(a=x,b=y,title=lst[i])","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Start table**","metadata":{}},{"cell_type":"code","source":"start.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:19.128194Z","iopub.execute_input":"2022-08-17T17:59:19.128520Z","iopub.status.idle":"2022-08-17T17:59:19.150457Z","shell.execute_reply.started":"2022-08-17T17:59:19.128488Z","shell.execute_reply":"2022-08-17T17:59:19.149182Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start['program_number'] = start['program_number'].replace(r'1A','17',regex=True)\nstart['program_number'] = start['program_number'].replace(r'1X','18',regex=True)\nstart['program_number'] = start['program_number'].replace(r'2B','19',regex=True)\nstart['program_number'] = start['program_number'].replace(r'3X','20',regex=True)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:19.152926Z","iopub.execute_input":"2022-08-17T17:59:19.153412Z","iopub.status.idle":"2022-08-17T17:59:19.265048Z","shell.execute_reply.started":"2022-08-17T17:59:19.153375Z","shell.execute_reply":"2022-08-17T17:59:19.263692Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start = start.astype({'race_number':'int8',\n                      'weight_carried':'int32',\n                     'odds':'int32',\n                     'program_number':'int8'})\nstart.info()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:19.266513Z","iopub.execute_input":"2022-08-17T17:59:19.266864Z","iopub.status.idle":"2022-08-17T17:59:19.301940Z","shell.execute_reply.started":"2022-08-17T17:59:19.266834Z","shell.execute_reply":"2022-08-17T17:59:19.300694Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"lst=start.columns.tolist()\n\nlst_remove=['race_date','odds','jockey','track_id']\n\n# using filter() + lambda to remove certain columns\nlst=filter(lambda i: i not in lst_remove, lst)\nlst=list(lst)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:19.303440Z","iopub.execute_input":"2022-08-17T17:59:19.304748Z","iopub.status.idle":"2022-08-17T17:59:19.312814Z","shell.execute_reply.started":"2022-08-17T17:59:19.304693Z","shell.execute_reply":"2022-08-17T17:59:19.311213Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# def multiple_plots(df,lst,figsize=(20,6)):\n#     for i in range(len(lst)):\n#             plt.figure(figsize=figsize)\n#             plt.xlabel(lst[i],fontsize = 17)\n#             plt.ylabel('count')\n#             plt.xticks(rotation=90)\n#             sns.barplot(x=df[lst[i]].value_counts().index.values,\n#                         y=df[lst[i]].value_counts().values)\n ","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-17T17:59:19.314422Z","iopub.execute_input":"2022-08-17T17:59:19.315084Z","iopub.status.idle":"2022-08-17T17:59:19.322635Z","shell.execute_reply.started":"2022-08-17T17:59:19.315047Z","shell.execute_reply":"2022-08-17T17:59:19.321188Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# using custom function\nmultiple_plots(start,lst)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:19.340071Z","iopub.execute_input":"2022-08-17T17:59:19.340837Z","iopub.status.idle":"2022-08-17T17:59:19.470217Z","shell.execute_reply.started":"2022-08-17T17:59:19.340798Z","shell.execute_reply":"2022-08-17T17:59:19.468706Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# referance: @iamleonie https://www.kaggle.com/code/iamleonie/big-data-derby-step-by-step-analysis\n\n# When were the Races held ?\n\ntemp = pd.pivot(start.groupby(['track_id','race_date'])['race_number'].nunique().reset_index(drop=False),\n        columns=['race_date'],index=['track_id'])\nsns.set(style=\"ticks\", context=\"notebook\")\nplt.figure(figsize=(20,4))\nsns.heatmap(temp.race_number,cmap=\"Blues\")","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:19.522781Z","iopub.execute_input":"2022-08-17T17:59:19.523525Z","iopub.status.idle":"2022-08-17T17:59:20.911915Z","shell.execute_reply.started":"2022-08-17T17:59:19.523451Z","shell.execute_reply":"2022-08-17T17:59:20.910772Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## **All about odds**","metadata":{}},{"cell_type":"markdown","source":"* if odds of a jockey are 1000 i.e (divide by 100) have a 10-1 chance of winning. i.e for every dollar i wagered,i recieve 10 extra dollars.\n*           Probability of :\n            - Winning = ($10 / 11$) or 90.9091%\n            - Losing = ($1 / 11$) or 9.0909%\n*         So if i bet \\\\$100, my profit will be \\\\$1000 and payout \\\\$1100\n\n    >The horse that customers have bet the most money on is called the \"favourite\". This horse will have the lowest odds.The \"longshots\" have the least amount of money wagered on them and therefore have the highest odds. The racetrack handles the money, keeps a percentage (called a “take-out”) and calculates the horses' odds based on the amount of money wagered on each horse. The remaining money is then paid back to the players who have winning tickets.","metadata":{}},{"cell_type":"code","source":"# plotting odds \nplt.subplot(1, 2, 1)\n(start['odds']).plot.hist(bins=50, figsize=(20,10), edgecolor='black')\nplt.xlabel('odds', fontsize=17)\nplt.ylabel('frequency', fontsize=17)\nplt.tick_params(labelsize=15)\nplt.title('Odds distribution', fontsize=17)\n\nplt.subplot(1, 2, 2)\nnp.log(start['odds']+1).plot.hist(bins=50, figsize=(30,10), edgecolor='black')\nplt.xlabel('log(odds+1)', fontsize=17)\nplt.ylabel('frequency', fontsize=17)\nplt.tick_params(labelsize=15)\nplt.title('Log(odds) Distribution', fontsize=17)\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:20.913153Z","iopub.execute_input":"2022-08-17T17:59:20.913511Z","iopub.status.idle":"2022-08-17T17:59:21.533282Z","shell.execute_reply.started":"2022-08-17T17:59:20.913459Z","shell.execute_reply":"2022-08-17T17:59:21.531960Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(30,6))\n\nsns.boxplot(x=start.odds)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-17T17:59:21.535534Z","iopub.execute_input":"2022-08-17T17:59:21.536310Z","iopub.status.idle":"2022-08-17T17:59:21.779044Z","shell.execute_reply.started":"2022-08-17T17:59:21.536251Z","shell.execute_reply":"2022-08-17T17:59:21.777430Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Some jockeys have really high odds of winning, This means majority of population bet on their \"favourite\" for that day","metadata":{"execution":{"iopub.status.busy":"2022-08-15T13:08:58.906872Z","iopub.execute_input":"2022-08-15T13:08:58.907807Z","iopub.status.idle":"2022-08-15T13:08:58.919121Z","shell.execute_reply.started":"2022-08-15T13:08:58.907760Z","shell.execute_reply":"2022-08-15T13:08:58.916829Z"}}},{"cell_type":"code","source":"fig = px.scatter(start,x='weight_carried',y='odds',color='odds',hover_data=['race_number','jockey'],\n          color_continuous_scale=px.colors.sequential.Jet)\nfig.update_layout(paper_bgcolor=\"white\",\n                      template='simple_white',title_text='<b>Scatter Plot of Odds vs Weight<b>')\nfig.update_xaxes(tickmode='linear')","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:21.781108Z","iopub.execute_input":"2022-08-17T17:59:21.781608Z","iopub.status.idle":"2022-08-17T17:59:22.256458Z","shell.execute_reply.started":"2022-08-17T17:59:21.781549Z","shell.execute_reply":"2022-08-17T17:59:22.255008Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"x= start.groupby('program_number')['odds'].mean().index.values\ny= start.groupby('program_number')['odds'].median().values\n\nbarplot(a=x,b=y,title='Mean of odds vs Program number')","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:22.258271Z","iopub.execute_input":"2022-08-17T17:59:22.259293Z","iopub.status.idle":"2022-08-17T17:59:22.306337Z","shell.execute_reply.started":"2022-08-17T17:59:22.259248Z","shell.execute_reply":"2022-08-17T17:59:22.305399Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Race table**","metadata":{}},{"cell_type":"code","source":"race","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:22.307693Z","iopub.execute_input":"2022-08-17T17:59:22.308040Z","iopub.status.idle":"2022-08-17T17:59:22.332666Z","shell.execute_reply.started":"2022-08-17T17:59:22.308008Z","shell.execute_reply":"2022-08-17T17:59:22.331670Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# extracting month, week and weekday\nrace['Month'] = pd.DatetimeIndex(race[\"race_date\"]).month\nrace['Week'] = pd.DatetimeIndex(race[\"race_date\"]).week\nrace['Day'] = pd.DatetimeIndex(race[\"race_date\"]).weekday","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:22.334281Z","iopub.execute_input":"2022-08-17T17:59:22.334829Z","iopub.status.idle":"2022-08-17T17:59:22.348046Z","shell.execute_reply.started":"2022-08-17T17:59:22.334792Z","shell.execute_reply":"2022-08-17T17:59:22.346417Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# assigning unique race_id\n\nrace.reset_index(inplace=True)\nrace.rename(columns={'index':'race_id'},inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:22.349441Z","iopub.execute_input":"2022-08-17T17:59:22.350692Z","iopub.status.idle":"2022-08-17T17:59:22.358751Z","shell.execute_reply.started":"2022-08-17T17:59:22.350653Z","shell.execute_reply":"2022-08-17T17:59:22.357339Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"lst = race.columns.to_list()\n\nlst_remove=['race_id','race_date','post_time','run_up_distance','purse','distance_id']\n\n# using filter() + lambda to remove certain columns\nlst=filter(lambda i: i not in lst_remove, lst)\nlst=list(lst)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:22.360067Z","iopub.execute_input":"2022-08-17T17:59:22.361259Z","iopub.status.idle":"2022-08-17T17:59:22.369240Z","shell.execute_reply.started":"2022-08-17T17:59:22.361220Z","shell.execute_reply":"2022-08-17T17:59:22.367937Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for i in range(len(lst)):\n    barplot(a=race[lst[i]].value_counts().index.values,b=race[lst[i]].value_counts().values,title=lst[i])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:22.370611Z","iopub.execute_input":"2022-08-17T17:59:22.371533Z","iopub.status.idle":"2022-08-17T17:59:22.856966Z","shell.execute_reply.started":"2022-08-17T17:59:22.371464Z","shell.execute_reply":"2022-08-17T17:59:22.855112Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# highest paid race_types\n\nx= race.groupby('race_type')['purse'].sum().index\ny= race.groupby('race_type')['purse'].sum().values\n\nbarplot(a=x,b=y,title='Highest paid race types')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# from datetime import datetime\n# race['Year'] = pd.to_datetime(race['race_date']).dt.to_period('Y')\n# race['Month'] = pd.to_datetime(race['race_date']).dt.to_period('M')\n# race[\"Weekday\"] = pd.DatetimeIndex(race[\"race_date\"]).weekday\n# race['Day'] = pd.to_datetime(race['race_date']).dt.to_period('D')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-08-17T17:59:22.858574Z","iopub.execute_input":"2022-08-17T17:59:22.859487Z","iopub.status.idle":"2022-08-17T17:59:22.865168Z","shell.execute_reply.started":"2022-08-17T17:59:22.859429Z","shell.execute_reply":"2022-08-17T17:59:22.863557Z"},"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('There are total %d unique races ' %race.groupby(['track_id'])['race_id'].count().sum())\nprint ('and total %d unique dates'%race.groupby(['track_id'])['race_date'].nunique().sum())","metadata":{"execution":{"iopub.status.busy":"2022-08-17T17:59:23.059854Z","iopub.execute_input":"2022-08-17T17:59:23.060623Z","iopub.status.idle":"2022-08-17T17:59:23.071412Z","shell.execute_reply.started":"2022-08-17T17:59:23.060585Z","shell.execute_reply":"2022-08-17T17:59:23.070499Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **track table**","metadata":{}},{"cell_type":"code","source":"# referance : @kushtrivedi14728 https://www.kaggle.com/code/kushtrivedi14728/advance-eda-with-ml-big-data-derby\n\ndef downcastMemoryUsage(dataFrame):\n    startMemoryOptimization = dataFrame.memory_usage().sum() / 1024 ** 2\n    print('Memory usage of dataframe is: \\033[1m{:.2f} MB\\033[0m'.format(startMemoryOptimization))\n    subTypeInt = ['uint8','uint16','uint32','uint64','int8','int16','int32','int64']\n    subTypeFloat = ['float16','float32','float64']\n    for column in dataFrame.columns:\n        columnType = str(dataFrame[column].dtypes)\n        maximumColumn = dataFrame[column].max()\n        minimumColumn = dataFrame[column].min()\n        if 'int' in columnType:\n            for element in subTypeInt:\n                if minimumColumn > np.iinfo(element).min and maximumColumn < np.iinfo(element).max:\n                    dataFrame[column] = dataFrame[column].astype(element)\n                    break\n        elif 'float' in columnType:\n            for element in subTypeFloat:\n                if minimumColumn > np.finfo(element).min and maximumColumn < np.finfo(element).max:\n                    dataFrame[column] = dataFrame[column].astype(element)\n                    break\n        elif 'object' in columnType:\n            numberOfUnique = len(dataFrame[column].unique())\n            numberOfTotal = len(dataFrame[column])\n            if numberOfUnique / numberOfTotal < 0.5:\n                dataFrame[column] = dataFrame[column].astype('category')\n    endMemoryOptimization = dataFrame.memory_usage().sum() / 1024 ** 2\n    print('Memory usage after optimization is: \\033[1m{:.2f} MB\\033[0m'.format(endMemoryOptimization))\n    print('Compressed by: \\033[1m{:.2f} %\\033[0m'.format(100*(startMemoryOptimization - endMemoryOptimization) / startMemoryOptimization))\n    return dataFrame","metadata":{"execution":{"iopub.status.busy":"2022-08-17T18:13:58.347941Z","iopub.execute_input":"2022-08-17T18:13:58.348290Z","iopub.status.idle":"2022-08-17T18:13:58.361279Z","shell.execute_reply.started":"2022-08-17T18:13:58.348264Z","shell.execute_reply":"2022-08-17T18:13:58.359970Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"downcastMemoryUsage(track)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T18:14:09.159083Z","iopub.execute_input":"2022-08-17T18:14:09.159395Z","iopub.status.idle":"2022-08-17T18:14:13.048313Z","shell.execute_reply.started":"2022-08-17T18:14:09.159372Z","shell.execute_reply":"2022-08-17T18:14:13.047262Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":" ","metadata":{}},{"cell_type":"markdown","source":"# **TO BE CONTINUED..**✌\n- AN **UPVOTE** WOULD BE HIGHLY APPRECIATED ☝🤩","metadata":{}}]}