{"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 \n#from shapely.geometry import Point\n#import geopandas as gpd\n#from geopandas import GeoDataFrame\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-10T11:16:34.429281Z","iopub.execute_input":"2022-11-10T11:16:34.430002Z","iopub.status.idle":"2022-11-10T11:16:35.078393Z","shell.execute_reply.started":"2022-11-10T11:16:34.429966Z","shell.execute_reply":"2022-11-10T11:16:35.076994Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tracking_table = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_tracking_table.csv')\ntracking_table.head()\n \nAQU_20190101= tracking_table.loc[(tracking_table['track_id']=='AQU') &(tracking_table['race_date']=='2019-01-01')]\nAQU_20190101.info()\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:16:35.080960Z","iopub.execute_input":"2022-11-10T11:16:35.081449Z","iopub.status.idle":"2022-11-10T11:16:44.263698Z","shell.execute_reply.started":"2022-11-10T11:16:35.081398Z","shell.execute_reply":"2022-11-10T11:16:44.262367Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"start_table = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_start_table.csv')\nstart_table.head()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:16:44.265292Z","iopub.execute_input":"2022-11-10T11:16:44.265614Z","iopub.status.idle":"2022-11-10T11:16:44.313891Z","shell.execute_reply.started":"2022-11-10T11:16:44.265585Z","shell.execute_reply":"2022-11-10T11:16:44.312588Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_table = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_race_table.csv')\nrace_table.head()\nrace_table.loc[race_table['race_number'] ==9]","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:16:44.316445Z","iopub.execute_input":"2022-11-10T11:16:44.316814Z","iopub.status.idle":"2022-11-10T11:16:44.348205Z","shell.execute_reply.started":"2022-11-10T11:16:44.316775Z","shell.execute_reply":"2022-11-10T11:16:44.346867Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"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\ncomplete = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_2019_complete.csv', names=columns, dtype={'program_number':'string'}, index_col=False) \ncomplete.tail(20)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:16:44.349591Z","iopub.execute_input":"2022-11-10T11:16:44.349968Z","iopub.status.idle":"2022-11-10T11:17:05.273955Z","shell.execute_reply.started":"2022-11-10T11:16:44.349907Z","shell.execute_reply":"2022-11-10T11:17:05.272051Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.head(20)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:05.275795Z","iopub.execute_input":"2022-11-10T11:17:05.276395Z","iopub.status.idle":"2022-11-10T11:17:05.307409Z","shell.execute_reply.started":"2022-11-10T11:17:05.276322Z","shell.execute_reply":"2022-11-10T11:17:05.306201Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"complete.info()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:05.308829Z","iopub.execute_input":"2022-11-10T11:17:05.309215Z","iopub.status.idle":"2022-11-10T11:17:05.329941Z","shell.execute_reply.started":"2022-11-10T11:17:05.309180Z","shell.execute_reply":"2022-11-10T11:17:05.327749Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gr = complete.groupby(['race_date','race_number','jockey'] ).size()\ngr.head(60)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:05.331531Z","iopub.execute_input":"2022-11-10T11:17:05.332021Z","iopub.status.idle":"2022-11-10T11:17:06.430939Z","shell.execute_reply.started":"2022-11-10T11:17:05.331979Z","shell.execute_reply":"2022-11-10T11:17:06.429434Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"columns_gr = ['track_id', 'race_date', 'race_number', 'program_number',  'distance_id', 'course_type', \n           'track_condition', 'run_up_distance', 'race_type','purse', 'post_time', 'weight_carried', 'jockey', 'odds', 'position_at_finish']\n\n\ngr_program_gr = complete.groupby(columns_gr ).nunique().reset_index()\ngr_program_gr['place'] = pd.cut(gr_program_gr['position_at_finish'], [0 ,3, 6,15], labels=['1-3', '4-6', '6-15'])\ngr_program_gr['place_num'] = pd.cut(gr_program_gr['position_at_finish'], [0 ,3, 6,15], labels=[1, 4, 6]).astype('int')\ngr_program_gr['month'] = pd.DatetimeIndex(gr_program_gr['race_date']).month\ngr_program_gr['year'] = pd.DatetimeIndex(gr_program_gr['race_date']).year\n\n\ngr_program_gr.head(10)\n\n#gr_program_gr.info()\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:06.432376Z","iopub.execute_input":"2022-11-10T11:17:06.432732Z","iopub.status.idle":"2022-11-10T11:17:15.125684Z","shell.execute_reply.started":"2022-11-10T11:17:06.432700Z","shell.execute_reply":"2022-11-10T11:17:15.124541Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gr_program_gr=gr_program_gr.drop(gr_program_gr.loc[: ,'trakus_index':'longitude'].columns , axis=1) \ngr_program_gr","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:15.129977Z","iopub.execute_input":"2022-11-10T11:17:15.130369Z","iopub.status.idle":"2022-11-10T11:17:15.176712Z","shell.execute_reply.started":"2022-11-10T11:17:15.130332Z","shell.execute_reply":"2022-11-10T11:17:15.175545Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"בדיקה מפתח יחודי של הטבלה תאריך מרוץ מספר מרוץ ומספר סוס וכמות הדגימות שנלקחו בזמן המירוץ  ****","metadata":{}},{"cell_type":"code","source":"gr_program_number = complete.groupby(['race_date','race_number','program_number']).size()\ngr_program_number.head(60)\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:15.178498Z","iopub.execute_input":"2022-11-10T11:17:15.178896Z","iopub.status.idle":"2022-11-10T11:17:16.501480Z","shell.execute_reply.started":"2022-11-10T11:17:15.178860Z","shell.execute_reply":"2022-11-10T11:17:16.500222Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gr.describe()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:16.503107Z","iopub.execute_input":"2022-11-10T11:17:16.503612Z","iopub.status.idle":"2022-11-10T11:17:16.518249Z","shell.execute_reply.started":"2022-11-10T11:17:16.503562Z","shell.execute_reply":"2022-11-10T11:17:16.516879Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"gr_ind = complete.groupby(['race_date','race_number','jockey'])\ngr_ind.head(10)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:16.520085Z","iopub.execute_input":"2022-11-10T11:17:16.520591Z","iopub.status.idle":"2022-11-10T11:17:17.866756Z","shell.execute_reply.started":"2022-11-10T11:17:16.520543Z","shell.execute_reply":"2022-11-10T11:17:17.865540Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockey_position_at_finish = complete.groupby(['race_date','jockey','race_number'], )['position_at_finish'].nunique()\njockey_position_at_finish = jockey_position_at_finish.reset_index()\njockey_position_at_finish","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:17.869515Z","iopub.execute_input":"2022-11-10T11:17:17.869862Z","iopub.status.idle":"2022-11-10T11:17:19.294450Z","shell.execute_reply.started":"2022-11-10T11:17:17.869831Z","shell.execute_reply":"2022-11-10T11:17:19.293184Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#plt.figure(figsize=(12,10)) , sns.scatterplot(jockey_position_at_finish.corr(),legend='auto')  ,plt.show()\n\n \n \nsns.set(style='whitegrid')\nsns.scatterplot(x=\"jockey\",\n                    y=\"position_at_finish\",\n                    data=jockey_position_at_finish)\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:19.295706Z","iopub.execute_input":"2022-11-10T11:17:19.296038Z","iopub.status.idle":"2022-11-10T11:17:26.386810Z","shell.execute_reply.started":"2022-11-10T11:17:19.296007Z","shell.execute_reply":"2022-11-10T11:17:26.385463Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gr_program_gr['place_num']=gr_program_gr['place_num'].astype('int')\n\ngr_program_gr.info()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:26.388082Z","iopub.execute_input":"2022-11-10T11:17:26.388429Z","iopub.status.idle":"2022-11-10T11:17:26.411392Z","shell.execute_reply.started":"2022-11-10T11:17:26.388398Z","shell.execute_reply":"2022-11-10T11:17:26.409958Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(12,10))\nsns.heatmap(gr_program_gr.corr(),cmap='YlGnBu',annot=True)\nplt.show","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:26.413212Z","iopub.execute_input":"2022-11-10T11:17:26.413816Z","iopub.status.idle":"2022-11-10T11:17:27.363451Z","shell.execute_reply.started":"2022-11-10T11:17:26.413778Z","shell.execute_reply":"2022-11-10T11:17:27.362157Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#track_id\trace_date\trace_number\tprogram_number\tdistance_id\tcourse_type\ttrack_condition\trun_up_distance\trace_type\tpurse\tpost_time\tweight_carried\tjockey\nlist_col=gr_program_gr.columns\nprint(list_col)\n\nsns.relplot(data=gr_program_gr, x=\"weight_carried\", y=\"position_at_finish\")\n\nfor i in list_col:\n    sns.relplot(data=gr_program_gr, x=i, y=\"position_at_finish\")\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:27.365163Z","iopub.execute_input":"2022-11-10T11:17:27.366181Z","iopub.status.idle":"2022-11-10T11:17:56.551484Z","shell.execute_reply.started":"2022-11-10T11:17:27.366127Z","shell.execute_reply":"2022-11-10T11:17:56.549424Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tracking_table.loc[(tracking_table['track_id']=='AQU') &(tracking_table['race_date']=='2019-01-01')]\n\nDylan_Davis=gr_program_gr.loc[(gr_program_gr['jockey']=='Dylan Davis')]\nDylan_Davis\n\n\n#jockey_position_at_finish = complete.groupby(['race_date','program_number','jockey','race_number'], )['position_at_finish'].nunique()\ngr_program_gr.groupby(['race_date','jockey'],sort=True ).size()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:56.553316Z","iopub.execute_input":"2022-11-10T11:17:56.553855Z","iopub.status.idle":"2022-11-10T11:17:57.305017Z","shell.execute_reply.started":"2022-11-10T11:17:56.553802Z","shell.execute_reply":"2022-11-10T11:17:57.303754Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"****מי מהרוכבים זכה במקום ראשון הכי הרבה פעמים ","metadata":{}},{"cell_type":"code","source":"\njockey_races = gr_program_gr.groupby(['jockey','position_at_finish'])['race_number'].count()#.reset_index()\njockey_races\n \n \n\njockey_results = jockey_races.unstack(level=[1])\n \njockey_results['count_race']= jockey_results.sum(axis=1)\n#jockey_results['Retio_1']=jockey_results['1']/jockey_results['Count']\njockey_results=jockey_results.fillna(0)\njockey_results_sort=jockey_results.sort_values(by='count_race',ascending=False)\njockey_results_sort=jockey_results_sort.head(30)\njockey_results_sort.reset_index()\njockey_results_sort=jockey_results_sort.iloc[: ,0:17]\njockey_results_sort\n ","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:57.306900Z","iopub.execute_input":"2022-11-10T11:17:57.307398Z","iopub.status.idle":"2022-11-10T11:17:57.379456Z","shell.execute_reply.started":"2022-11-10T11:17:57.307358Z","shell.execute_reply":"2022-11-10T11:17:57.377992Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**מה אחוז הזכיה במקום ראשון לכל רוכב******","metadata":{}},{"cell_type":"code","source":"jockey_results_sort['retio'] = jockey_results_sort.apply(lambda x: x[1] / x['count_race'], axis=1)\njockey_results_sort=jockey_results_sort.sort_values(by='retio',ascending=False)\n\njockey_results_sort","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:57.380802Z","iopub.execute_input":"2022-11-10T11:17:57.381313Z","iopub.status.idle":"2022-11-10T11:17:57.443965Z","shell.execute_reply.started":"2022-11-10T11:17:57.381275Z","shell.execute_reply":"2022-11-10T11:17:57.442436Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**מי הרוכבים זכה בשלישיה הראשונה הכי הרבה פעמים**","metadata":{}},{"cell_type":"code","source":" \njockey_races = gr_program_gr.groupby(['jockey','place'])['race_number'].count()#.reset_index()\njockey_races","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:57.445502Z","iopub.execute_input":"2022-11-10T11:17:57.445844Z","iopub.status.idle":"2022-11-10T11:17:57.679708Z","shell.execute_reply.started":"2022-11-10T11:17:57.445815Z","shell.execute_reply":"2022-11-10T11:17:57.678340Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockey_results = jockey_races.unstack(level=[1])\n \njockey_results['count_race']= jockey_results.sum(axis=1)\n\njockey_results=jockey_results.fillna(0)\njockey_results_sort=jockey_results.sort_values(by='count_race',ascending=False)\njockey_results_sort=jockey_results_sort.head(30)\njockey_results_sort.reset_index()\njockey_results_sort2=jockey_results_sort.iloc[: ,0:15]\njockey_results_sort2","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:57.681336Z","iopub.execute_input":"2022-11-10T11:17:57.682290Z","iopub.status.idle":"2022-11-10T11:17:57.703234Z","shell.execute_reply.started":"2022-11-10T11:17:57.682250Z","shell.execute_reply":"2022-11-10T11:17:57.702010Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"משקל משפיע על התוצאה * כפי שניתן לראות כל מי שלקח מקום ראשון המשקל שלו פחות מ 125","metadata":{}},{"cell_type":"code","source":"jockey_results_sort['retio'] = jockey_results_sort.apply(lambda x: x['1-3'] / x['count_race'], axis=1)\njockey_results_sort=jockey_results_sort.sort_values(by='retio',ascending=False)\n\njockey_results_sort\n#jockey_results_sort['Place'] = pd.cut(jockey_results_sort['position_at_finish'], [0 ,3, 6,15])\njockey_results_sort\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:57.704757Z","iopub.execute_input":"2022-11-10T11:17:57.705255Z","iopub.status.idle":"2022-11-10T11:17:57.725173Z","shell.execute_reply.started":"2022-11-10T11:17:57.705219Z","shell.execute_reply":"2022-11-10T11:17:57.723862Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"כפי שניתן לראות הסיכוי לנבות את הזוכים במקומות הראשונים הרבה יותר גבוהה\n","metadata":{}},{"cell_type":"markdown","source":"עכשיו נבדוק את נושא העונתיות של הרוכבים לכל שחקן יש תקופה טובה או פחות טובה ","metadata":{}},{"cell_type":"code","source":"#gr_program_gr=gr_program_gr.loc[(gr_program_gr['place']=='1-3')]\njockey_races = gr_program_gr.groupby(['jockey','month','place'])['race_number'].count()#.reset_index()\njockey_races\n  \n#jockey_races.loc[(jockey_races['place']=='1-3')]\n\njockey_results = jockey_races.unstack(level=[1])\njockey_results\njockey_results['count_race']= jockey_results.sum(axis=1)\njockey_results\njockey_results=jockey_results.fillna(0)\njockey_results_sort=jockey_results.sort_values(by=['count_race','jockey'],ascending=False)\nresult = pd.merge(jockey_results_sort, jockey_results_sort2, on=\"jockey\")\njockey_results_sort#result ","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:57.726868Z","iopub.execute_input":"2022-11-10T11:17:57.727249Z","iopub.status.idle":"2022-11-10T11:17:57.780265Z","shell.execute_reply.started":"2022-11-10T11:17:57.727216Z","shell.execute_reply":"2022-11-10T11:17:57.779095Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockey_results_sort=jockey_results_sort.reset_index()\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:57.782012Z","iopub.execute_input":"2022-11-10T11:17:57.782467Z","iopub.status.idle":"2022-11-10T11:17:57.792903Z","shell.execute_reply.started":"2022-11-10T11:17:57.782423Z","shell.execute_reply":"2022-11-10T11:17:57.791300Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockey_results_by_month=jockey_results_sort.groupby('jockey').sum()\njockey_results_by_month=jockey_results_by_month.sort_values(by=['count_race','jockey'],ascending=False)\njockey_results_by_month=jockey_results_by_month.reset_index()\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:57.795065Z","iopub.execute_input":"2022-11-10T11:17:57.795584Z","iopub.status.idle":"2022-11-10T11:17:57.810597Z","shell.execute_reply.started":"2022-11-10T11:17:57.795536Z","shell.execute_reply":"2022-11-10T11:17:57.809646Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"result = pd.merge(jockey_results_sort, jockey_results_by_month,how='left' , on='jockey')\nresult=result.sort_values(by=['count_race_y','jockey'],ascending=False)\nresult.head()\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:57.816641Z","iopub.execute_input":"2022-11-10T11:17:57.818376Z","iopub.status.idle":"2022-11-10T11:17:57.851105Z","shell.execute_reply.started":"2022-11-10T11:17:57.818327Z","shell.execute_reply":"2022-11-10T11:17:57.849773Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"result['retio_1'] = result['1_x']/result['1_y'].fillna(0) \nresult['retio_2'] = result['2_x']/result['2_y'] \nresult['retio_3'] = result['3_x']/result['3_y'] \nresult['retio_4'] = result['4_x']/result['4_y'] \nresult['retio_5'] = result['5_x']/result['5_y'] \nresult['retio_6'] = result['6_x']/result['6_y'] \nresult['retio_7'] = result['7_x']/result['7_y'] \nresult['retio_8'] = result['8_x']/result['8_y'] \nresult['retio_9'] = result['9_x']/result['9_y'] \nresult['retio_10'] = result['10_x']/result['10_y'] \nresult['retio_11'] = result['11_x']/result['11_y'] \nresult['retio_12'] = result['12_x']/result['12_y'] \n\nresult.info()\nresult.head(10)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:57.852433Z","iopub.execute_input":"2022-11-10T11:17:57.852762Z","iopub.status.idle":"2022-11-10T11:17:57.912109Z","shell.execute_reply.started":"2022-11-10T11:17:57.852733Z","shell.execute_reply":"2022-11-10T11:17:57.910703Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockey_New= gr_program_gr.groupby(['jockey','distance_id','course_type','track_condition','run_up_distance','race_type','purse','weight_carried','position_at_finish'])['race_number'].count()\njockey_New\n\n ","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:57.913838Z","iopub.execute_input":"2022-11-10T11:17:57.914259Z","iopub.status.idle":"2022-11-10T11:17:57.946816Z","shell.execute_reply.started":"2022-11-10T11:17:57.914205Z","shell.execute_reply":"2022-11-10T11:17:57.945577Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockey_New=jockey_New.unstack(level=8)\njockey_New['count race']= jockey_New.sum(axis=1)\n\njockey_New=jockey_New.fillna(0)\njockey_New","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:57.948589Z","iopub.execute_input":"2022-11-10T11:17:57.949735Z","iopub.status.idle":"2022-11-10T11:17:58.012628Z","shell.execute_reply.started":"2022-11-10T11:17:57.949673Z","shell.execute_reply":"2022-11-10T11:17:58.011600Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"כמות התחרויות בחודש האחרון- משפיע על ביצוע הסוס בתחרות הבאה  \nמטרה לבדוק איך משפיע תוצאות הריצות האחרונות  (מיקום ) על ביצוע הסוס בתחרויות הבאות ","metadata":{}},{"cell_type":"markdown","source":"# Import libraries","metadata":{}},{"cell_type":"code","source":"import folium\nfrom geopandas.tools import geocode","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:17:58.014052Z","iopub.execute_input":"2022-11-10T11:17:58.014594Z","iopub.status.idle":"2022-11-10T11:17:58.906823Z","shell.execute_reply.started":"2022-11-10T11:17:58.014560Z","shell.execute_reply":"2022-11-10T11:17:58.905446Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n# Here we get one unique coordinate for each track.","metadata":{}},{"cell_type":"code","source":"tracks = complete[['track_id', 'latitude','longitude',]]\nind = 1\nframe = []\ntmp=['AQU','BEL','SAR']\nfor track in tmp:\n    all_track_coord = tracks.loc[(tracks['track_id']==track)]\n    track_coord = all_track_coord[:1]\n    track_coord.index = [ind]\n    frame.append(track_coord)\n    ind=ind+1\n   \ntrack_points = pd.concat(frame)\ntrack_points","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:51:41.039841Z","iopub.execute_input":"2022-11-10T11:51:41.040243Z","iopub.status.idle":"2022-11-10T11:51:42.326819Z","shell.execute_reply.started":"2022-11-10T11:51:41.040211Z","shell.execute_reply":"2022-11-10T11:51:42.325486Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# ----------------------\n# We mark them on the map and see where the races physically took place.","metadata":{}},{"cell_type":"code","source":"mapit = folium.Map( location=[0, 0], zoom_start=1 )\nfor lat , lon in zip(track_points.latitude , track_points.longitude):\n    folium.Marker(location=[ lat,lon ], fill_color='#43d9de', radius=6).add_to(mapit)\n             \nmapit","metadata":{"execution":{"iopub.status.busy":"2022-11-10T11:57:10.348957Z","iopub.execute_input":"2022-11-10T11:57:10.349421Z","iopub.status.idle":"2022-11-10T11:57:10.369187Z","shell.execute_reply.started":"2022-11-10T11:57:10.349384Z","shell.execute_reply":"2022-11-10T11:57:10.367670Z"},"collapsed":true,"jupyter":{"outputs_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# ----------------------\n# Display on the map of the movement of horses during one race.","metadata":{}},{"cell_type":"code","source":"\n# selection of one race from the general data array\none_race = complete[['track_id', 'race_date','race_number','program_number','latitude','longitude']]\none_race = one_race.loc[(one_race['track_id'] == 'AQU')].loc[(one_race['race_date'] == '2019-01-01')]\none_race = one_race.loc[(one_race['race_number'] == 9)]\none_race.head(100)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T12:00:01.935720Z","iopub.execute_input":"2022-11-10T12:00:01.936180Z","iopub.status.idle":"2022-11-10T12:00:03.100985Z","shell.execute_reply.started":"2022-11-10T12:00:01.936141Z","shell.execute_reply":"2022-11-10T12:00:03.100108Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Assign a unique color to each horse in a race\n","metadata":{}},{"cell_type":"code","source":"colors = one_race[['program_number']].drop_duplicates()\ncolors['color'] = ['#de5a43','#deb743','#adde43','#43de8b','#7dd8e8','#161e8c','#7716d9','#d916d9','#e0162b',]\n\ncolors = colors.set_index('program_number')\ncolors = colors.sort_index(ascending=True)\n\nres = pd.merge(one_race, colors, on='program_number')\nraces = res[['program_number','latitude','longitude','color']]\nraces = races.set_index('program_number')\nraces","metadata":{"execution":{"iopub.status.busy":"2022-11-10T12:00:54.980449Z","iopub.execute_input":"2022-11-10T12:00:54.980894Z","iopub.status.idle":"2022-11-10T12:00:55.012850Z","shell.execute_reply.started":"2022-11-10T12:00:54.980859Z","shell.execute_reply":"2022-11-10T12:00:55.011640Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#Mark points on the map","metadata":{}},{"cell_type":"code","source":"mapit1 = folium.Map(location=[0, 0], zoom_start=2 )\nfor loc, col  in zip(zip(races.latitude , races.longitude), races.color):\n    folium.Circle(location=loc, color=col, radius=0.1, fill=True).add_to(mapit1)\n   \nmapit1","metadata":{"execution":{"iopub.status.busy":"2022-11-10T12:04:44.617032Z","iopub.execute_input":"2022-11-10T12:04:44.618123Z","iopub.status.idle":"2022-11-10T12:04:46.561398Z","shell.execute_reply.started":"2022-11-10T12:04:44.618079Z","shell.execute_reply":"2022-11-10T12:04:46.560164Z"},"trusted":true},"execution_count":null,"outputs":[]}]}