{"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)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input 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-11-09T09:53:59.044312Z","iopub.execute_input":"2022-11-09T09:53:59.045015Z","iopub.status.idle":"2022-11-09T09:53:59.085012Z","shell.execute_reply.started":"2022-11-09T09:53:59.044876Z","shell.execute_reply":"2022-11-09T09:53:59.084057Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<div style=\"width:100%; border-bottom:2px solid #000; font-size:24px; font-weight:bold;\">\n    Data Loading...\n</div>","metadata":{}},{"cell_type":"code","source":"df_race = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_race_table.csv')\ndf_start = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_start_table.csv', header=None)\ndf_tracking = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_tracking_table.csv')\ndf_complete = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_2019_complete.csv', header=None)\n\nprint(\"Dataset Loading Process Done...\")","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:27:20.906843Z","iopub.execute_input":"2022-11-09T09:27:20.907578Z","iopub.status.idle":"2022-11-09T09:27:34.750467Z","shell.execute_reply.started":"2022-11-09T09:27:20.907535Z","shell.execute_reply":"2022-11-09T09:27:34.749243Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<div style=\"width:100%; border-bottom:2px solid #000; font-size:24px; font-weight:bold;\">\n    EDA\n</div>","metadata":{"execution":{"iopub.status.busy":"2022-11-09T04:30:36.587555Z","iopub.execute_input":"2022-11-09T04:30:36.587882Z","iopub.status.idle":"2022-11-09T04:30:36.594077Z","shell.execute_reply.started":"2022-11-09T04:30:36.587857Z","shell.execute_reply":"2022-11-09T04:30:36.592535Z"}}},{"cell_type":"code","source":"print(\"Shape Race: \", df_race.shape)\nprint(\"Shape Start: \", df_start.shape)\nprint(\"Shape Tracking: \", df_tracking.shape)\nprint(\"Shape Complete: \", df_complete.shape)\n\n\ndf_complete.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:27:38.771355Z","iopub.execute_input":"2022-11-09T09:27:38.772488Z","iopub.status.idle":"2022-11-09T09:27:38.805426Z","shell.execute_reply.started":"2022-11-09T09:27:38.772431Z","shell.execute_reply":"2022-11-09T09:27:38.804547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_start.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:27:39.048472Z","iopub.execute_input":"2022-11-09T09:27:39.049201Z","iopub.status.idle":"2022-11-09T09:27:39.062276Z","shell.execute_reply.started":"2022-11-09T09:27:39.049147Z","shell.execute_reply":"2022-11-09T09:27:39.061244Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_complete.columns=['track_id', 'race_date', 'race_number', 'program_number', 'trakus_index', 'latitude', 'longitude', 'distance_id', 'course_type', \n                  'track_condition', 'run_up_distance', 'race_type', 'purse', 'post_time', 'weight_carried', 'jockey', 'odds', 'position_at_finish']\n\ndf_start.columns=['track_id', 'race_date', 'race_number', 'program_number', 'weight_carried', 'jockey', 'odds', 'position_at_finish']\n\ndf_complete.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:27:39.276689Z","iopub.execute_input":"2022-11-09T09:27:39.277313Z","iopub.status.idle":"2022-11-09T09:27:39.303065Z","shell.execute_reply.started":"2022-11-09T09:27:39.277276Z","shell.execute_reply":"2022-11-09T09:27:39.302127Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_start.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:27:39.509455Z","iopub.execute_input":"2022-11-09T09:27:39.510319Z","iopub.status.idle":"2022-11-09T09:27:39.523046Z","shell.execute_reply.started":"2022-11-09T09:27:39.510272Z","shell.execute_reply":"2022-11-09T09:27:39.521908Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"-\"*20, \" SHAPES OF DATASETS \", \"-\"*20)\nprint(\"Complete: \", df_complete.shape)\nprint(\"Race: \", df_race.shape)\nprint(\"Start: \", df_start.shape)\nprint(\"Trackiing: \", df_tracking.shape)\n\nprint(\"\\n\")\nprint(\"-\"*20, \" COLUMNS OF DATASETS \", \"-\"*20)\nprint(\"\\nComplete: \", df_complete.columns)\nprint(\"\\nRace: \", df_race.columns)\nprint(\"\\nStart: \", df_start.columns)\nprint(\"\\nTrackiing: \", df_tracking.columns)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:27:40.210378Z","iopub.execute_input":"2022-11-09T09:27:40.211428Z","iopub.status.idle":"2022-11-09T09:27:40.220470Z","shell.execute_reply.started":"2022-11-09T09:27:40.211386Z","shell.execute_reply":"2022-11-09T09:27:40.219077Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_complete.info()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:27:40.461844Z","iopub.execute_input":"2022-11-09T09:27:40.462866Z","iopub.status.idle":"2022-11-09T09:27:40.487009Z","shell.execute_reply.started":"2022-11-09T09:27:40.462823Z","shell.execute_reply":"2022-11-09T09:27:40.485435Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for x, xCols in df_complete.iteritems():\n    if (df_complete[x].dtype == 'object'):\n        df_complete[x] = df_complete[x].str.strip()\n        \ndf_complete.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:27:41.346977Z","iopub.execute_input":"2022-11-09T09:27:41.347692Z","iopub.status.idle":"2022-11-09T09:27:55.163353Z","shell.execute_reply.started":"2022-11-09T09:27:41.347641Z","shell.execute_reply":"2022-11-09T09:27:55.162163Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_complete['race_date'] = pd.to_datetime(df_complete['race_date'])\ndf_complete.info()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:27:55.165208Z","iopub.execute_input":"2022-11-09T09:27:55.165655Z","iopub.status.idle":"2022-11-09T09:27:56.029836Z","shell.execute_reply.started":"2022-11-09T09:27:55.165621Z","shell.execute_reply":"2022-11-09T09:27:56.028690Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_complete['year'] = df_complete['race_date'].dt.year\ndf_complete['month'] = df_complete['race_date'].dt.month\ndf_complete['day'] = df_complete['race_date'].dt.day\ndf_complete['month_name'] = df_complete['race_date'].dt.month_name()\ndf_complete['day_name'] = df_complete['race_date'].dt.day_name()\ndf_complete.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:27:56.032009Z","iopub.execute_input":"2022-11-09T09:27:56.032357Z","iopub.status.idle":"2022-11-09T09:28:00.820124Z","shell.execute_reply.started":"2022-11-09T09:27:56.032326Z","shell.execute_reply":"2022-11-09T09:28:00.818987Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_complete['post_time'] = df_complete['post_time'].astype('str')\n#df_complete['post_time'].astype('str')[0][0:-2]+\" Hours\"\n#df_complete['post_time'][0][1:]+\" Mins\"\n\ndf_complete['hours'] = df_complete['post_time'].apply(lambda x:x[0:-2])\ndf_complete['mins'] = df_complete['post_time'].apply(lambda x:x[1:] if (len(x) <= 3) else x[2:] )\n\nprint(\"df_complete Shape: \", df_complete.shape)\ndf_complete.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:00.822745Z","iopub.execute_input":"2022-11-09T09:28:00.823090Z","iopub.status.idle":"2022-11-09T09:28:07.458092Z","shell.execute_reply.started":"2022-11-09T09:28:00.823058Z","shell.execute_reply":"2022-11-09T09:28:07.456964Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"xdf_complete = df_complete.drop(['race_date', 'post_time'], axis=1)\nprint(\"xdf_complete Shape: \", xdf_complete.shape)\nxdf_complete.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:07.459457Z","iopub.execute_input":"2022-11-09T09:28:07.459798Z","iopub.status.idle":"2022-11-09T09:28:11.048910Z","shell.execute_reply.started":"2022-11-09T09:28:07.459765Z","shell.execute_reply":"2022-11-09T09:28:11.047553Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n#xdf_complete_race_pivot.replace(np.NaN, \"-\", inplace=True)\n\ntrackID_dict = {'AQU':'Aqueduct', 'BEL':'Belmont', 'SAR':'Saratoga' }\ncourse_type_dict = {'M':'Hurdle', 'D':'Dirt', 'O':'Outer Turf', 'I':'Inner Turf', 'T':'Turf' }\ntrack_condition_dict = {'YL':'Yielding', 'FM':'Firm', 'SY':'Sloppy', 'GD':'Good', 'FT':'Fast', 'MY':'Muddy', 'SF':'Soft'}\nrace_type_dict = {'STK':'Stakes', 'WCL':'Waiver Claiming', 'WMC':'Waiver Maiden Claiming', 'SST':'Starter Stakes', 'SHP':'Starter Handicap', \n                  'CLM':'Claiming', 'STR':'Starter Allowance', 'AOC':'Allowance Optionl Claimer', 'SOC':'Starter Optional Claimer', \n                  'MCL':'Maiden Claiming', 'ALW':'Allowance', 'MSW':'Maiden Special Weight'}\n\nxdf_complete['track_name'] = xdf_complete['track_id']\nxdf_complete['course_name'] = xdf_complete['course_type']\nxdf_complete['track_condition_name'] = xdf_complete['track_condition']\nxdf_complete['race_type_name'] = xdf_complete['race_type']\n\n\nxdf_complete['track_name'].replace(trackID_dict, inplace=True)\nxdf_complete['course_name'].replace(course_type_dict, inplace=True)\nxdf_complete['track_condition_name'].replace(track_condition_dict, inplace=True)\nxdf_complete['race_type_name'].replace(race_type_dict, inplace=True)\n\n\nxdf_complete.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:11.050797Z","iopub.execute_input":"2022-11-09T09:28:11.051281Z","iopub.status.idle":"2022-11-09T09:28:18.493149Z","shell.execute_reply.started":"2022-11-09T09:28:11.051235Z","shell.execute_reply":"2022-11-09T09:28:18.491919Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"xdf_complete.info()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:18.494859Z","iopub.execute_input":"2022-11-09T09:28:18.496055Z","iopub.status.idle":"2022-11-09T09:28:18.509373Z","shell.execute_reply.started":"2022-11-09T09:28:18.496010Z","shell.execute_reply":"2022-11-09T09:28:18.507886Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<div style=\"width:100%; border-bottom:2px solid #000; font-size:24px; font-weight:bold;\">\n    Visualization\n</div>","metadata":{}},{"cell_type":"code","source":"import plotly.graph_objects as go\nfrom plotly.subplots import make_subplots\nfrom plotly.offline import iplot\n\nimport matplotlib.pyplot as plt\nimport seaborn as sns\ncolors=sns.color_palette('Set3')\nimport random\ncolors","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:18.510650Z","iopub.execute_input":"2022-11-09T09:28:18.510992Z","iopub.status.idle":"2022-11-09T09:28:19.172633Z","shell.execute_reply.started":"2022-11-09T09:28:18.510959Z","shell.execute_reply":"2022-11-09T09:28:19.171450Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# for Number and Percentage\ndef xplot(df, xVal, yVal, xTitle):\n    getPielbl = yVal.replace(\"_\", \"<br>\").title()\n    \n    getFontSize = 20\n    if(len(getPielbl)>11): getFontSize = 16\n       \n    fig = make_subplots(rows=1, cols=2,\n                        subplot_titles = ('Total Numbers', 'Percentages'),\n                        specs = [[{\"type\": \"xy\"}, {'type':'domain'}]])\n\n    fig.add_trace(go.Bar(y = df['count'],\n                         x = df[xVal],\n                         text = df['count'].tolist(),\n                         textfont = dict(size=15),\n                         textposition = 'outside',\n                         showlegend = False,\n                         marker = dict(color = random.choice(colors), #'lightblue',\n                                       #line_color = '#666', #'black',\n                                       line_width = 2)\n                        ), \n                  row = 1, col = 1)\n\n    fig.add_trace((go.Pie(labels = df[xVal],\n                          values = df['percent'],\n                          hole=0.3,\n                          textfont = dict(size = 16, color='#fff'),\n                          textposition = 'auto',\n                          direction = 'clockwise',\n                          sort = False,\n                          pull = df['percent'].apply(lambda x : 0.1 if(x < 10) else 0),\n                          marker = dict(colors = colors,\n                                       line = dict(color = '#fff', width=2)\n                                       ),\n                          showlegend = True)\n                  ), \n                  row = 1, col = 2)\n    \n    fig.update_layout(title={'text': xTitle,\n                             'y':0.9,\n                             'x':0.5,\n                             'xanchor': 'center',\n                             'yanchor': 'top'}, \n                      width=850, height=600,\n                      template = 'plotly_white',\n                      annotations = [dict(text=getPielbl, x=0.776, y=0.43, font_size=getFontSize)]\n    )\n    \n    #fig.update_yaxes(range=[0,22000])\n    return fig\n    #iplot(fig)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:37.734407Z","iopub.execute_input":"2022-11-09T09:28:37.734841Z","iopub.status.idle":"2022-11-09T09:28:37.749323Z","shell.execute_reply.started":"2022-11-09T09:28:37.734804Z","shell.execute_reply":"2022-11-09T09:28:37.748340Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"xdf_complete.columns","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:38.331741Z","iopub.execute_input":"2022-11-09T09:28:38.332158Z","iopub.status.idle":"2022-11-09T09:28:38.339427Z","shell.execute_reply.started":"2022-11-09T09:28:38.332123Z","shell.execute_reply":"2022-11-09T09:28:38.338227Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"visual_features = ['track_name', 'course_name', 'track_condition_name', 'race_type_name', 'jockey', 'month_name', 'day_name']\nxdf_complete[visual_features].head()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:39.468661Z","iopub.execute_input":"2022-11-09T09:28:39.469682Z","iopub.status.idle":"2022-11-09T09:28:42.713054Z","shell.execute_reply.started":"2022-11-09T09:28:39.469641Z","shell.execute_reply":"2022-11-09T09:28:42.711809Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Statics of Visual Features withour 'jockey'\n\nfor y in visual_features:\n    if (y != 'jockey'):\n        df_visual = (xdf_complete[y].value_counts(normalize=True)*100).reset_index()\n        df_visual[y] = df_visual[y].round(1) # manage round values\n        df_visual = df_visual.sort_values(['index'], ascending=True)\n        df_visual.rename({y: 'percent'}, axis=1, inplace=True)\n        df_visual_cunt = xdf_complete[y].value_counts()\n        df_visual['count'] = df_visual_cunt.values\n        \n        g_title = y\n        if(y == 'track_name'): g_title = ' Track'\n        if(y == 'course_name'): g_title = ' Cource Type'\n        if(y == 'track_condition_name'): g_title = ' Track Condition'\n        if(y == 'race_type_name'): g_title = ' Race Type'\n        if(y == 'month_name'): g_title = ' Month Name'\n        if(y == 'day_name'): g_title = ' Day Name'\n        \n        xtitle = 'Statics of ' + ' Derby 2022 ' + str(g_title)\n        getFig = xplot(df_visual, 'index', y, xtitle)\n        iplot(getFig)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:42.714910Z","iopub.execute_input":"2022-11-09T09:28:42.715352Z","iopub.status.idle":"2022-11-09T09:28:47.386643Z","shell.execute_reply.started":"2022-11-09T09:28:42.715316Z","shell.execute_reply":"2022-11-09T09:28:47.385449Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Top 15 Number of Jockey's Compitition Participations\nxdf_complete['jockey'].value_counts(sort=True).head(15)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:47.388866Z","iopub.execute_input":"2022-11-09T09:28:47.389796Z","iopub.status.idle":"2022-11-09T09:28:47.717671Z","shell.execute_reply.started":"2022-11-09T09:28:47.389746Z","shell.execute_reply":"2022-11-09T09:28:47.716829Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"xdf_complete.columns","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:47.718838Z","iopub.execute_input":"2022-11-09T09:28:47.719761Z","iopub.status.idle":"2022-11-09T09:28:47.729989Z","shell.execute_reply.started":"2022-11-09T09:28:47.719724Z","shell.execute_reply":"2022-11-09T09:28:47.729123Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#xdf_complete['position_at_finish']\n\n# Total Number of Race Type Occur\nxdf_complete['race_type_name'].value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:47.732236Z","iopub.execute_input":"2022-11-09T09:28:47.733163Z","iopub.status.idle":"2022-11-09T09:28:47.957567Z","shell.execute_reply.started":"2022-11-09T09:28:47.733115Z","shell.execute_reply":"2022-11-09T09:28:47.956164Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#xdf_complete_position = xdf_complete[['jockey', 'race_type', 'position_at_finish']].groupby(['jockey', 'race_type', 'position_at_finish'])['race_type'].count().reset_index(name='count').sort_values(['count'], ascending=False)\n#xdf_complete_position[xdf_complete_position['position_at_finish'] == 1]\n\n# Jockey's Information with Race Type Only\nxdf_complete_race = xdf_complete[['jockey', 'race_type']].groupby(['jockey', 'race_type'])['race_type'].count().reset_index(name='count').sort_values(['count'], ascending=False)\nxdf_complete_race","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:28:48.803442Z","iopub.execute_input":"2022-11-09T09:28:48.803848Z","iopub.status.idle":"2022-11-09T09:28:50.316062Z","shell.execute_reply.started":"2022-11-09T09:28:48.803810Z","shell.execute_reply":"2022-11-09T09:28:50.314871Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#xdf_complete_race\nxdf_complete_race_pivot = xdf_complete_race.pivot_table(values='count',\n                                                        index = xdf_complete_race.jockey, \n                                                        columns = 'race_type', \n                                                        aggfunc = 'sum').reset_index()\n# Replace NaN with (-) hyphen\nxdf_complete_race_pivot.replace(np.NaN, \"-\", inplace=True)\n\n# To remove index label\nxdf_complete_race_pivot = xdf_complete_race_pivot.rename_axis(None, axis=1)\n\nxdf_complete_race_pivot","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:02.293763Z","iopub.execute_input":"2022-11-09T09:29:02.294191Z","iopub.status.idle":"2022-11-09T09:29:02.346747Z","shell.execute_reply.started":"2022-11-09T09:29:02.294140Z","shell.execute_reply":"2022-11-09T09:29:02.345491Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"xdf_jockey_race_top10 = xdf_complete_race_pivot.sort_values(['ALW', 'AOC', 'CLM', 'MCL', 'MSW', 'SHP', 'SOC', 'SST', 'STK', 'STR', 'WCL', 'WMC'], ascending=True).head(10)\nxdf_jockey_race_top10","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:03.412257Z","iopub.execute_input":"2022-11-09T09:29:03.413354Z","iopub.status.idle":"2022-11-09T09:29:03.444791Z","shell.execute_reply.started":"2022-11-09T09:29:03.413311Z","shell.execute_reply":"2022-11-09T09:29:03.443685Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Jockey's Information with Race and Position\nxdf_complete_position = xdf_complete[['jockey', 'race_type', 'position_at_finish']].groupby(['jockey', 'race_type', 'position_at_finish'])['race_type'].count().reset_index(name='count').sort_values(['count'], ascending=False)\nxdf_complete_position","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:03.931777Z","iopub.execute_input":"2022-11-09T09:29:03.932218Z","iopub.status.idle":"2022-11-09T09:29:05.552654Z","shell.execute_reply.started":"2022-11-09T09:29:03.932165Z","shell.execute_reply":"2022-11-09T09:29:05.551506Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"xdf_complete_position.sort_values(by=['position_at_finish'], ascending=True).head(10)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:05.554688Z","iopub.execute_input":"2022-11-09T09:29:05.555042Z","iopub.status.idle":"2022-11-09T09:29:05.568992Z","shell.execute_reply.started":"2022-11-09T09:29:05.555008Z","shell.execute_reply":"2022-11-09T09:29:05.567552Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Total Jockeys: \", len(xdf_complete_position['jockey'].unique()))\nxdf_complete_position.head(10)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:05.570631Z","iopub.execute_input":"2022-11-09T09:29:05.571302Z","iopub.status.idle":"2022-11-09T09:29:05.588010Z","shell.execute_reply.started":"2022-11-09T09:29:05.571257Z","shell.execute_reply":"2022-11-09T09:29:05.586875Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#xdf_complete_position\nxdf_complete_position_pivot = xdf_complete_position.pivot_table(values='count', \n                                                                index = ['jockey', 'race_type'], \n                                                                columns = 'position_at_finish').sort_index(level=1, axis=0).reset_index()\n\n# Replace NaN with (-) hyphen\nxdf_complete_position_pivot.replace(np.NaN, 0, inplace=True)\nxdf_complete_position_pivot['Sum All'] = xdf_complete_position_pivot.iloc[:,2:].sum(axis=1)\nxdf_complete_position_pivot.replace(0, \"-\", inplace=True)\n\n# To remove index label\nxdf_complete_position_pivot = xdf_complete_position_pivot.rename_axis(None, axis=1)\n\n# Dictionary for Change the Column Names\ncol_names = {1:'1st', 2:'2nd', 3:'3rd', 4:'4th', 5:'5th', 6:'6th', 7:'7th', 8:'8th', 9:'9th', 10:'10th', 11:'11th', 12:'12th', 13:'13th', 14:'14th'}\nxdf_complete_position_pivot.rename(col_names, axis=1, inplace=True) # Change Column Names with Defined Dictionary.\nxdf_complete_position_pivot = xdf_complete_position_pivot.sort_values(['Sum All'], axis=0, ascending=False)\n\nxdf_complete_position_pivot","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:05.816348Z","iopub.execute_input":"2022-11-09T09:29:05.816752Z","iopub.status.idle":"2022-11-09T09:29:05.888700Z","shell.execute_reply.started":"2022-11-09T09:29:05.816719Z","shell.execute_reply":"2022-11-09T09:29:05.887544Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"xdf_complete_position_pivot.head(20)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:06.111148Z","iopub.execute_input":"2022-11-09T09:29:06.111563Z","iopub.status.idle":"2022-11-09T09:29:06.139107Z","shell.execute_reply.started":"2022-11-09T09:29:06.111528Z","shell.execute_reply":"2022-11-09T09:29:06.137890Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import matplotlib.pyplot as plt\n\nxdf_complete_position_pivot.replace(\"-\", 0, inplace=True)\nxdf_complete_position_pivot_top20 = xdf_complete_position_pivot.head(20)\nxdf_complete_position_pivot.replace(0, \"-\", inplace=True)\n\nxdf_complete_position_pivot_top20 = xdf_complete_position_pivot_top20.set_index('jockey')\nax = xdf_complete_position_pivot_top20[['1st','2nd','3rd','4th','5th']].plot(kind='bar', \n                                                                             stacked=True, figsize=(20, 8), \n                                                                             rot=90, width=0.85)\n\nax.set_title('Average Number of Position of Top 20 Jockeys and Number of Races Participations', fontsize=20)\nax.set_xlabel('Jockeys', fontsize=14)\nax.set_ylabel('Race Count', fontsize=14)\nax.get_legend().set_title('Race Types', prop={'size':12})\n\nfor x in ax.containers:\n    labels=[]\n    for v in x:\n        if v.get_height() > 0: \n            labels.append(v.get_height())\n        else:\n            labels = ''\n    lbl_txt = (labels/sum(labels)*100).round(1)\n    ax.bar_label(x, labels=lbl_txt, label_type='center', rotation=90, color='white', size=12)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:07.388501Z","iopub.execute_input":"2022-11-09T09:29:07.388899Z","iopub.status.idle":"2022-11-09T09:29:08.671076Z","shell.execute_reply.started":"2022-11-09T09:29:07.388865Z","shell.execute_reply":"2022-11-09T09:29:08.670232Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = xdf_complete_position_pivot_top20.plot(\n                             y=['1st','2nd','3rd'], \n                             kind=\"bar\", \n                             figsize=(25,8), align='center', width=0.8\n                            )\n\nfig.set_title(\"Top 20 Jokeys Position in Race\", pad=20, fontdict={'fontsize':24})\nplt.xticks(rotation=30, horizontalalignment=\"center\")\nplt.xlabel(\"Jockeys\", fontsize=15)\nplt.ylabel(\"Race Counts\", fontsize=15)\nfig.legend(loc=1,fontsize=10)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:08.672526Z","iopub.execute_input":"2022-11-09T09:29:08.673067Z","iopub.status.idle":"2022-11-09T09:29:09.315120Z","shell.execute_reply.started":"2022-11-09T09:29:08.673034Z","shell.execute_reply":"2022-11-09T09:29:09.314058Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def pie_plot_positions(xdf, xval, xposition):\n    #getPielbl = xdf_complete_position_pivot_top20.index\n    getPielbl = xposition\n    getPielbl = getPielbl.replace(\" \", \"<br>\")\n\n    fig = make_subplots(subplot_titles = ('Percent'))\n    fig.add_trace(go.Pie(labels = xdf.index,\n                          values = xdf[xval],\n                          hole=0.3,\n                          textfont = dict(size = 16, color='#fff'),\n                          textposition = 'auto',\n                          direction = 'clockwise',\n                          sort = False,\n                          pull = xdf[xval].apply(lambda x : 0.2 if(x < 10000) else 0),\n                          marker = dict(colors = colors, line = dict(color = '#fff', width=2) ), showlegend = True)\n                  )\n    fig.update_layout(title={'text': \"Percentage of Top 20 Jockey's Position\", 'y':0.9, 'x':0.5, 'xanchor': 'center', 'yanchor': 'top'}, \n                      width=850, height=600, template = 'plotly_white', annotations = [dict(text = getPielbl, x=0.5, y=0.44, font_size=25)]\n                     )\n    \n    return fig","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:09.317293Z","iopub.execute_input":"2022-11-09T09:29:09.317649Z","iopub.status.idle":"2022-11-09T09:29:09.326984Z","shell.execute_reply.started":"2022-11-09T09:29:09.317610Z","shell.execute_reply":"2022-11-09T09:29:09.325871Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for x, xcol_names in xdf_complete_position_pivot_top20.iteritems():\n    if ((x == '1st') | (x == '2nd') | (x == '3rd') | (x == '4th') | (x == '5th')):\n        if (x == '1st'): position_title = \"First Position\"\n        if (x == '2nd'): position_title = \"Second Position\"\n        if (x == '3rd'): position_title = \"Third Position\"\n        if (x == '4th'): position_title = \"Fourth Position\"\n        if (x == '5th'): position_title = \"Fifth Position\"\n        plot_fig = pie_plot_positions(xdf_complete_position_pivot_top20, x, position_title)\n        iplot(plot_fig)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:09.328614Z","iopub.execute_input":"2022-11-09T09:29:09.329131Z","iopub.status.idle":"2022-11-09T09:29:09.672133Z","shell.execute_reply.started":"2022-11-09T09:29:09.329096Z","shell.execute_reply":"2022-11-09T09:29:09.671031Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"xdf_race_by_distance_track = df_race.pivot_table(values='race_date',index = ['distance_id'], columns = 'track_id', aggfunc= np.size)#.sort_index(level=1, axis=0).reset_index()\nxdf_race_by_distance_track['sum'] = xdf_race_by_distance_track.sum(axis=1)\nxdf_race_by_distance_track.replace(np.NaN, 0, inplace=True)\nxdf_race_by_distance_track.sort_values(by='sum', ascending=False, inplace=True)\nxdf_race_by_distance_track = xdf_race_by_distance_track.rename_axis(None, axis=0)\nxdf_race_by_distance_track.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:10.812037Z","iopub.execute_input":"2022-11-09T09:29:10.813046Z","iopub.status.idle":"2022-11-09T09:29:10.839781Z","shell.execute_reply.started":"2022-11-09T09:29:10.813008Z","shell.execute_reply":"2022-11-09T09:29:10.838589Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Number of Race Distributed by Distance on Each Tracks\nax = xdf_race_by_distance_track[['AQU','BEL','SAR']].head(15).plot(kind='bar', stacked=True, figsize=(20, 8), rot=90, width=0.85)\nax.set_title('Distribution of Number of Races by Distance on Each Tracks', fontsize=20)\nax.set_xlabel('Distance', fontsize=14)\nax.set_ylabel('Number of Races', fontsize=14)\nax.get_legend().set_title('Tracks', prop={'size':14})\n\nfor x in ax.containers:\n    labels=[]\n    for v in x:\n        if v.get_height() > 0: \n            labels.append(v.get_height())\n\n    lbl_txt = (labels/sum(labels)*100).round(1)\n    ax.bar_label(x, labels=lbl_txt, label_type='center', rotation=90, color='white', size=12)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:13.547594Z","iopub.execute_input":"2022-11-09T09:29:13.548520Z","iopub.status.idle":"2022-11-09T09:29:14.258995Z","shell.execute_reply.started":"2022-11-09T09:29:13.548467Z","shell.execute_reply":"2022-11-09T09:29:14.257876Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"xdf_race_by_distance_racetype = df_race.pivot_table(values='race_date',index = ['distance_id'], columns = 'race_type', aggfunc= np.size)#.sort_index(level=1, axis=0).reset_index()\nxdf_race_by_distance_racetype['sum'] = xdf_race_by_distance_racetype.sum(axis=1)\nxdf_race_by_distance_racetype.replace(np.NaN, 0, inplace=True)\nxdf_race_by_distance_racetype.sort_values(by='sum', ascending=False, inplace=True)\nxdf_race_by_distance_racetype = xdf_race_by_distance_racetype.rename_axis(None, axis=0)\nxdf_race_by_distance_racetype.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:14.260885Z","iopub.execute_input":"2022-11-09T09:29:14.261263Z","iopub.status.idle":"2022-11-09T09:29:14.299575Z","shell.execute_reply.started":"2022-11-09T09:29:14.261228Z","shell.execute_reply":"2022-11-09T09:29:14.298441Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Number of Race Distributed by Distance on Each Tracks\nax = xdf_race_by_distance_racetype[['ALW','AOC','CLM','MCL','MSW','SHP','SOC','SST','STK','STR','WCL','WMC']].head(10).plot(kind='bar', stacked=True, figsize=(20, 8), rot=90, width=0.85)\nax.set_title('Distribution of Number of Races by Distance on Each Race Type', fontsize=20)\nax.set_xlabel('Distance', fontsize=14)\nax.set_ylabel('Number of Races', fontsize=14)\nax.get_legend().set_title('Race Types', prop={'size':12})\n\nfor x in ax.containers:\n    labels=[]\n    for v in x:\n        if v.get_height() > 0: \n            labels.append(v.get_height())\n    lbl_txt = (labels/sum(labels)*100).round(1)\n    ax.bar_label(x, labels=lbl_txt, label_type='center', rotation=90, color='white', size=12)","metadata":{"execution":{"iopub.status.busy":"2022-11-09T09:29:15.996318Z","iopub.execute_input":"2022-11-09T09:29:15.997122Z","iopub.status.idle":"2022-11-09T09:29:17.321114Z","shell.execute_reply.started":"2022-11-09T09:29:15.997078Z","shell.execute_reply":"2022-11-09T09:29:17.319932Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Thanks**","metadata":{}}]}