{"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":"markdown","source":"

Big Data Derby 2022 - EDA - Exploring Competitions Among The Jockeys with Network Analysis

\n\n\n
\n \nIn the notebook, Networks Analysis is used to first understand how jockeys are inter-connected via the relationship of racing in same races. Then, a more sophisticated network is created to explore how one jockey was outperforming another jockey.\n \n
\n\n","metadata":{}},{"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport matplotlib.pyplot as plt\nimport matplotlib.lines as mlines\nimport pandas as pd\nimport statsmodels.api as sm\nimport seaborn as sns\n\nimport pyarrow.parquet as pq\nimport pyarrow as pa\n\n#---network visualization-----------\nimport re\nimport networkx as nx\n\n\nimport warnings\nwarnings.filterwarnings(\"ignore\")","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-31T15:02:51.924789Z","iopub.execute_input":"2022-10-31T15:02:51.925697Z","iopub.status.idle":"2022-10-31T15:02:52.601593Z","shell.execute_reply.started":"2022-10-31T15:02:51.925620Z","shell.execute_reply":"2022-10-31T15:02:52.600306Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#/kaggle/input/derby-2022-merge-and-convert-data-to-parquet-file/nyra_2019_complete.parquet\n\nrace_table = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_race_table.csv')\nstart_table = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_start_table.csv', header=None)\n# tracking_table = pd.read_csv('/kaggle/input/big-data-derby-2022/nyra_tracking_table.csv')\nstart_table.columns=['track_id', 'race_date', 'race_number', 'program_number', 'weight_carried', 'jockey', 'odds', 'position_at_finish']","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-31T15:02:52.608155Z","iopub.execute_input":"2022-10-31T15:02:52.608874Z","iopub.status.idle":"2022-10-31T15:02:52.636749Z","shell.execute_reply.started":"2022-10-31T15:02:52.608841Z","shell.execute_reply":"2022-10-31T15:02:52.635990Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_table['race_id']=range(1, race_table.shape[0]+1)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-31T15:02:52.637857Z","iopub.execute_input":"2022-10-31T15:02:52.638156Z","iopub.status.idle":"2022-10-31T15:02:52.643081Z","shell.execute_reply.started":"2022-10-31T15:02:52.638131Z","shell.execute_reply":"2022-10-31T15:02:52.642263Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_type_dict = { 'STK': 'Stakes', \n 'WCL': 'Waiver Claiming', \n 'WMC': 'Waiver Maiden Claiming', \n 'SST': 'Starter Stakes', \n 'SHP': 'Starter Handicap',\n 'CLM': 'Claiming', \n 'STR': 'Starter Allowance', \n 'AOC': 'Allowance Optionl Claimer', \n 'SOC': 'Starter Optional Claimer', \n 'MCL': 'Maiden Claiming', \n 'ALW': 'Allowance', \n 'MSW': 'Maiden Special Weight'\n }\n\ntrack_id_dict = dict(AQU = 'Aqueduct', BEL = 'Belmont', SAR = 'Saratoga')\ncourse_type_dict = dict(M = 'Hurdle', D = 'Dirt', O = 'Outer turf', I = 'Inner turf', T = 'turf')\ntrack_condition_dict = dict(YL = 'Yielding', FM ='Firm', SY = 'Sloppy', GD = 'Good', FT = 'Fast', MY = 'Muddy', SF = 'Soft')\n\nrace_table['race_type'] = race_table['race_type'].map(race_type_dict)\nrace_table['track_id'] = race_table['track_id'].map(track_id_dict)\nrace_table['course_type'] = race_table['course_type'].map(course_type_dict)\n\nrace_table['track_condition']=race_table['track_condition'].apply(lambda x:x.replace(\" \", ''))\nrace_table['track_condition'] = race_table['track_condition'].map(track_condition_dict)\n\n\nstart_table['track_id'] = start_table['track_id'].map(track_id_dict)\n\nprint(start_table.shape, race_table.shape)\nstart_table = start_table.merge(race_table, on=['track_id','race_date', 'race_number'], how='inner')\nprint(start_table.shape, race_table.shape)","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-10-31T15:02:53.585924Z","iopub.execute_input":"2022-10-31T15:02:53.586371Z","iopub.status.idle":"2022-10-31T15:02:53.618614Z","shell.execute_reply.started":"2022-10-31T15:02:53.586339Z","shell.execute_reply":"2022-10-31T15:02:53.617558Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\n

Races Jockeys Ran Together

\n\n\n\n
\n \nIn the Network, each NODE represents a Jockey, and each EDGE (i.e. the line connecting any two NODES) represents the races both end of the Nodes (Jockeys) ran together. The thickness of the EDGE corresponds to the number of races ran together, meaning the thicker the Edge, the more number of races the two jockeys ran together.\n \n
\n\n","metadata":{}},{"cell_type":"code","source":"cs = start_table[['race_id', 'track_id', 'race_date', 'race_number', 'jockey', 'program_number']]\n#cs.set_index(keys=['track_id', 'race_date', 'jockey'], inplace=True)\npcs = cs.pivot(index=['track_id', 'race_date', 'race_number',], columns='jockey', values='program_number')\n\npcs[pcs>'0']=1\npcs.fillna(0, inplace=True)\ncoocc = pcs.T.dot(pcs)\nnp.fill_diagonal(coocc.values, 0)\n\ncoocc_s = coocc.unstack().sort_values()\ncoocc_s.index.names = ['jockey1', 'jockey2']\ncoocc_s = coocc_s.reset_index()\ncoocc_s.columns = ['jockey1', 'jockey2', 'race_cnt']","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-31T15:02:56.103592Z","iopub.execute_input":"2022-10-31T15:02:56.104016Z","iopub.status.idle":"2022-10-31T15:02:56.284552Z","shell.execute_reply.started":"2022-10-31T15:02:56.103985Z","shell.execute_reply":"2022-10-31T15:02:56.283473Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n
\n \nTop 10 Jockeys by Number of Races Ran Together for Jockey Dylan Davis\t\n \n
\n","metadata":{}},{"cell_type":"code","source":"coocc_s[coocc_s['jockey1']=='Dylan Davis'].sort_values(by='race_cnt').tail(10)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-31T15:03:00.668380Z","iopub.execute_input":"2022-10-31T15:03:00.668795Z","iopub.status.idle":"2022-10-31T15:03:00.689185Z","shell.execute_reply.started":"2022-10-31T15:03:00.668760Z","shell.execute_reply":"2022-10-31T15:03:00.687990Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create network plot \nG = nx.Graph()\n\n\nfor _, row in coocc_s[coocc_s['race_cnt']>50].iterrows():\n G.add_edge(row['jockey1'], row['jockey2'], weight=row['race_cnt'])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-31T15:03:02.171817Z","iopub.execute_input":"2022-10-31T15:03:02.172217Z","iopub.status.idle":"2022-10-31T15:03:02.207978Z","shell.execute_reply.started":"2022-10-31T15:03:02.172187Z","shell.execute_reply":"2022-10-31T15:03:02.206991Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\npos_kkl = nx.kamada_kawai_layout(G)\nf, ax = plt.subplots(figsize=(16, 16))\n\n\nd = dict(nx.degree(G))\nedges = G.edges()\nweights = [G[u][v]['weight']/50 for u,v in edges]\n\nnx.draw(G, pos_kkl, \n with_labels=True, \n node_size=[v * 100 for v in d.values()],\n nodelist=d.keys(), \n width=weights, \n edge_color='grey', #node_color=list(df_skills_stats['core_number']), cmap=\"coolwarm_r\", \n alpha=0.9,\n )\n#node_labels = nx.draw_networkx_labels(G, pos_kkl, labels, font_size=10)\n# Set title\nax.set_title('Network of Jockeys: edge - ran in the same race', \n fontdict={'fontsize': 26,\n 'fontweight': 'bold',\n 'color': 'salmon', \n 'verticalalignment': 'baseline',\n 'horizontalalignment': 'center'}, \n loc='center')\n# Set edge color\nplt.gca().collections[0].set_edgecolor(\"#000000\")","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-10-31T15:03:03.631890Z","iopub.execute_input":"2022-10-31T15:03:03.632358Z","iopub.status.idle":"2022-10-31T15:03:04.024149Z","shell.execute_reply.started":"2022-10-31T15:03:03.632321Z","shell.execute_reply":"2022-10-31T15:03:04.023162Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pip install pyvis","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-10-31T15:03:12.763224Z","iopub.execute_input":"2022-10-31T15:03:12.764185Z","iopub.status.idle":"2022-10-31T15:03:21.698782Z","shell.execute_reply.started":"2022-10-31T15:03:12.764149Z","shell.execute_reply":"2022-10-31T15:03:21.697685Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":" from pyvis.network import Network","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-31T15:03:21.701455Z","iopub.execute_input":"2022-10-31T15:03:21.701836Z","iopub.status.idle":"2022-10-31T15:03:21.717266Z","shell.execute_reply.started":"2022-10-31T15:03:21.701806Z","shell.execute_reply":"2022-10-31T15:03:21.716213Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create network plot \nG = nx.Graph()\n\n#edge_attr = {}\nfor _, row in coocc_s[coocc_s['race_cnt']>50].iterrows():\n G.add_edge(row['jockey1'], row['jockey2'], weight=row['race_cnt']/100)\n #edge_attr[(row['jockey1'], row['jockey2'])]={\"weight\": row['race_cnt']}","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-31T15:03:21.718795Z","iopub.execute_input":"2022-10-31T15:03:21.719114Z","iopub.status.idle":"2022-10-31T15:03:21.756224Z","shell.execute_reply.started":"2022-10-31T15:03:21.719087Z","shell.execute_reply":"2022-10-31T15:03:21.754577Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\nd = dict(G.degree)\n\n#Updating dict\nd.update((x, 1*y) for x, y in d.items())\n\n#Setting up size attribute\nnx.set_node_attributes(G,d,'size')\n#nx.set_edge_attributes(G, edge_attr)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-31T15:03:21.758271Z","iopub.execute_input":"2022-10-31T15:03:21.758618Z","iopub.status.idle":"2022-10-31T15:03:21.764162Z","shell.execute_reply.started":"2022-10-31T15:03:21.758587Z","shell.execute_reply":"2022-10-31T15:03:21.762906Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\nnt = Network(\n height=\"600px\",\n width=\"100%\",\n directed=False,\n notebook=True,\n neighborhood_highlight=True,\n select_menu=True,\n #filter_menu=True,\n bgcolor=\"#ffffff\",\n font_color=False,\n layout=None,\n heading=\"\",\n cdn_resources=\"remote\" \n )\n#nt.force_atlas_2based()#\nnt.repulsion()\nnt.from_nx(G)\n#nt.show_buttons()\n#nt.show_buttons(filter_=['physics']) \nnt.show('nx.html')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-31T15:03:36.394580Z","iopub.execute_input":"2022-10-31T15:03:36.396054Z","iopub.status.idle":"2022-10-31T15:03:36.437791Z","shell.execute_reply.started":"2022-10-31T15:03:36.395997Z","shell.execute_reply":"2022-10-31T15:03:36.436841Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\n

A Jockey's Advantage Over Another Jockey

\n\n\n\n
\n \nIn the Network, each NODE represents a Jockey. \n
\nEach EDGE represents the following: If Jockey A And Jockey B ran 200 races together, among which Jockey A finished earlier than Jockey B (i.e. finishing position of Jockey A is smaller than Jockey B) in 150 races, and the remaining 50 races Joceky B finished earlier than Jockey A. The net score for Jockey A is 100, and for Jockey B is -100. The thickness of the Edge represents the net score between the connecting two jockeys. And arrow points to the one with positive net score, namely the one outperformed.\n
\n
\nFor example, Jose Lezcano is a big Node in the network, and has most edges pointing towards him while only a few edges pointing outward to others. This indicates overall he was maintaining advantage over other jockeys (but may not neccesarily win the race).\n \n
\n\n","metadata":{}},{"cell_type":"code","source":"compare_dfs = []\nfor jockey in cs['jockey'].unique().tolist(): \n t1 = cs[cs['jockey']==jockey][['race_id', 'jockey', 'program_number']].copy()\n t1.columns = ['race_id', 'jockey1', 'pos1']\n t2 = cs[cs['jockey']!=jockey][['race_id', 'jockey', 'program_number']].copy()\n t2.columns = ['race_id', 'jockey2', 'pos2']\n t3 = t1.merge(t2, on='race_id', how='inner')\n t3['win'] = (t3['pos1']>t3['pos2']).astype(int)\n compare_dfs.append(t3.groupby(['jockey1','jockey2'])['win'].sum().reset_index())","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-10-31T15:04:45.633564Z","iopub.execute_input":"2022-10-31T15:04:45.633949Z","iopub.status.idle":"2022-10-31T15:04:47.615876Z","shell.execute_reply.started":"2022-10-31T15:04:45.633909Z","shell.execute_reply":"2022-10-31T15:04:47.614909Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_compare = pd.concat(compare_dfs)\nt1 = df_compare[df_compare['jockey1']>df_compare['jockey2']].copy(deep=True)\nt2 = df_compare[df_compare['jockey1']5:\n if net_win>0:\n G2.add_edge(row['jockey1'], row['jockey2'], weight=net_win*multiplier)\n else:\n G2.add_edge(row['jockey2'], row['jockey1'], weight=np.abs(net_win)*multiplier)\n #edge_attr[(row['jockey1'], row['jockey2'])]={\"weight\": row['race_cnt']}","metadata":{"execution":{"iopub.status.busy":"2022-10-31T15:05:11.754819Z","iopub.execute_input":"2022-10-31T15:05:11.755251Z","iopub.status.idle":"2022-10-31T15:05:11.894953Z","shell.execute_reply.started":"2022-10-31T15:05:11.755219Z","shell.execute_reply":"2022-10-31T15:05:11.893232Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\nd = dict(G2.degree)\n\n#Updating dict\nd.update((x, 1*y) for x, y in d.items())\n\n#Setting up size attribute\nnx.set_node_attributes(G2,d,'size')\n#nx.set_edge_attributes(G2, edge_attr)","metadata":{"execution":{"iopub.status.busy":"2022-10-31T15:05:28.755727Z","iopub.execute_input":"2022-10-31T15:05:28.756123Z","iopub.status.idle":"2022-10-31T15:05:28.761784Z","shell.execute_reply.started":"2022-10-31T15:05:28.756092Z","shell.execute_reply":"2022-10-31T15:05:28.760990Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\nnt2 = Network(\n height=\"600px\",\n width=\"100%\",\n directed=True,\n notebook=True,\n neighborhood_highlight=True,\n select_menu=True,\n #filter_menu=True,\n bgcolor=\"#ffffff\",\n font_color=False,\n layout=None,\n heading=\"\",\n cdn_resources=\"remote\" \n )\n#nt.force_atlas_2based()#\nnt2.repulsion()\nnt2.from_nx(G2)\n#nt.show_buttons()\n#nt.show_buttons(filter_=['physics']) \nnt2.show('nx2.html')","metadata":{"execution":{"iopub.status.busy":"2022-10-31T15:05:42.960486Z","iopub.execute_input":"2022-10-31T15:05:42.961641Z","iopub.status.idle":"2022-10-31T15:05:42.997058Z","shell.execute_reply.started":"2022-10-31T15:05:42.961605Z","shell.execute_reply":"2022-10-31T15:05:42.996316Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"
\n

References:

\n
\n\n\n- [stackoverflow.com: Pyvis network keeps on moving](https://stackoverflow.com/questions/68117561/pyvis-network-keeps-on-moving)\n- [Customizing the Pyvis Interactive Network Graphs](https://www.askpython.com/python/examples/customizing-pyvis-interactive-network-graphs)\n- [networkx documents](https://networkx.org/documentation/stable/reference/generated/networkx.classes.function.set_edge_attributes.html)\n- [pyvis documents](https://pyvis.readthedocs.io/en/latest/_modules/pyvis/network.html)\n- [Exploring PhD Community with Network Analysis](https://www.kaggle.com/code/artvolgin/exploring-phd-community-with-network-analysis/notebook)","metadata":{}},{"cell_type":"markdown","source":"---","metadata":{}}]}