{"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":"- I didn't know much about the field of horse racing, so I explored the data by searching for information on the Internet.\n- It's hard to say that there's a clear conclusion, but I hope you enjoy it.\n- Distance and speed units were used in kilometers.\n- There may be awkward parts in English because I used a translator after organizing them in Korean.","metadata":{}},{"cell_type":"markdown","source":"- 경마 분야는 잘 알지 못해 이것저것 정보를 인터넷에서 찾아보며 데이터를 탐험했습니다.\n- 뚜렷한 결론이 있다고 말하긴 힘든 노트북이지만, 재밌게 보셨으면 합니다.\n- 거리, 속도 단위는 km 단위를 사용했습니다.\n- 한글로 정리해둔 뒤 번역기를 썼기 때문에 영문법상 어색한 부분이 있을 수 있습니다.","metadata":{}},{"cell_type":"code","source":"# !pip install --upgrade seaborn\n\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n# import seaborn.objects as so\n\nimport scipy.stats as stats\n\nfrom haversine import haversine","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-11-10T15:57:17.692917Z","iopub.execute_input":"2022-11-10T15:57:17.693479Z","iopub.status.idle":"2022-11-10T15:57:17.701262Z","shell.execute_reply.started":"2022-11-10T15:57:17.693414Z","shell.execute_reply":"2022-11-10T15:57:17.699191Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def preprocess_race():\n\n race_df = pd.read_csv('../input/big-data-derby-2022/nyra_race_table.csv')\n\n race_df['race_id'] = race_df[['track_id', 'race_date', 'race_number']].astype('str').agg('_'.join, axis = 1)\n race_df.insert(0, 'race_id', race_df.pop('race_id'))\n \n \n race_df['distance_feet'] = race_df['distance_id'].apply(lambda x : int(6.6 * x)) # to_feet\n race_df['distance_km'] = race_df['distance_feet'].apply(lambda x : x / 3281)\n\n race_df['race_date'] = pd.to_datetime(race_df['race_date'])\n \n race_df['track_condition'] = race_df['track_condition'].str.replace(\" \", \"\")\n\n # Dirt Condition Separate \n race_df.loc[race_df['course_type'] == 'D', 'dirt_condition'] = race_df['track_condition']\n dirt_condition_order = pd.CategoricalDtype(['FT', 'GD', 'MY', 'SY'], ordered = True)\n race_df['dirt_condition'] = race_df['dirt_condition'].astype(dirt_condition_order)\n \n # Turf Condition Separate\n race_df.loc[race_df['course_type'].isin(['I', 'O', 'T']), 'turf_condition'] = race_df['track_condition']\n turf_condition_order = pd.CategoricalDtype(['FM', 'GD', 'YL', 'SF'], ordered = True)\n race_df['turf_condition'] = race_df['turf_condition'].astype(turf_condition_order)\n \n race_df.drop(['purse', 'post_time', 'track_condition'], axis = 1, inplace = True)\n \n # drop hurdle race\n race_df.drop(race_df[race_df['course_type'] == 'M'].index, inplace = True)\n \n return race_df\n\ndef preprocess_start():\n start_df = pd.read_csv('../input/big-data-derby-2022/nyra_start_table.csv', \n names = ['track_id', 'race_date', 'race_number', 'program_number', 'weight_carried', 'jockey', 'odds', 'position_at_finish'])\n \n start_df['race_date'] = pd.to_datetime(start_df['race_date'])\n start_df['race_id'] = start_df[['track_id', 'race_date', 'race_number']].astype('str').agg('_'.join, axis = 1)\n\n start_df['program_number'] = start_df['program_number'].str.replace(\" \", \"\")\n return start_df\n\ndef preprocess_track():\n track_df = pd.read_csv('../input/big-data-derby-2022/nyra_tracking_table.csv')\n \n track_df['race_date'] = pd.to_datetime(track_df['race_date'])\n# track_df['distance_feet'] = track_df['distance_id'].apply(lambda x : int(6.6 * x))\n track_df['race_id'] = track_df[['track_id', 'race_date', 'race_number']].astype('str').agg('_'.join, axis = 1)\n\n track_df['program_number'] = track_df['program_number'].str.replace(\" \", \"\")\n\n return track_df\n\ndef preprocess_2022_derby():\n race_df = preprocess_race()\n start_df = preprocess_start()\n track_df = preprocess_track()\n return race_df, start_df, track_df","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T15:57:17.741646Z","iopub.execute_input":"2022-11-10T15:57:17.742166Z","iopub.status.idle":"2022-11-10T15:57:17.762159Z","shell.execute_reply.started":"2022-11-10T15:57:17.742128Z","shell.execute_reply":"2022-11-10T15:57:17.760446Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_df, start_df, track_df = preprocess_2022_derby()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T15:57:17.771067Z","iopub.execute_input":"2022-11-10T15:57:17.771582Z","iopub.status.idle":"2022-11-10T16:01:03.204707Z","shell.execute_reply.started":"2022-11-10T15:57:17.771543Z","shell.execute_reply":"2022-11-10T16:01:03.203049Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 1. Seasons and Tracks","metadata":{}},{"cell_type":"markdown","source":"### Summary\n1. Each season consists of Spring (3, 4, 5), Summer (6, 7, 8), Autumn (9, 10, 11), and Winter (12, 1, 2)\n2. `Dirt` competition is more than `Turf` competition:\n 1. Total number of matches - $Dirt : Turf = 2 : 1$\n 2. In terms of seasons, 'Dirt' always has around 300 games, while 'Turf' has a significant decrease in the number of games in spring and winter\n 3. About `Dirt`: The condition of the ground often deteriorates in spring and winter, while it is good to run in summer and autumn","metadata":{}},{"cell_type":"markdown","source":"### 요약\n1. 각 계절은 Spring(3, 4, 5), Summer(6, 7, 8), Autumn(9, 10, 11), Winter(12, 1, 2)로 구성\n2. `Dirt` 경기가 `Turf` 경기보다 많음 :\n 1. 전체 경기수 - $Dirt : Turf = 2 : 1$\n 2. 계절로 놓고 봤을 때, `Dirt`는 항상 300경기 내외의 경기 수가 있는 반면 `Turf`는 봄, 겨울에 경기 수가 확연히 감소함\n 3. `Dirt`에 관해 : 봄, 겨울에는 땅의 상태가 달리기 나빠지는 경우가 많고 반면 여름, 가을에는 달리기 좋은 상태\n ","metadata":{}},{"cell_type":"markdown","source":"- 트랙 컨디션 [위키](https://en.wikipedia.org/wiki/Going_(horse_racing))\n - `Dirt` \n - `FT : Fast` : 건조하고 균일하며 탄력 있는 표면\n - `GD : Good` : `FT`보다는 느린 듯\n - `MY : Muddy` : 젖었음, 고인 물은 없음\n - `SY : Sloppy` : 고인 물이 보일 정도로 물로 포화된 트랙\n - `Turf`\n - `FM : Firm` : 단단하고 탄력 있는 표면\n - `GD : Good` : `FM`보다 약간 부드러운 잔디 코스\n - `YL : Yielding` : 최근 비가 와서 지면에 상당한 양의 `give(탄성)`가 있음\n - `SF : Soft` : 수분이 많음. 말이 깊게 가라앉을 수 있음","metadata":{}},{"cell_type":"code","source":"# 1. Seasons and track conditions\nrace_df['month'] = race_df['race_date'].dt.month\n\nrace_df.loc[race_df['month'].isin([3, 4, 5]), 'season'] = 'spring'\nrace_df.loc[race_df['month'].isin([6, 7, 8]), 'season'] = 'summer'\nrace_df.loc[race_df['month'].isin([9, 10, 11]), 'season'] = 'autumn'\nrace_df.loc[race_df['month'].isin([12, 1, 2]), 'season'] = 'winter'\n\nseason_order = pd.CategoricalDtype(['spring', 'summer', 'autumn', 'winter'],\n ordered = True)\nrace_df['season'] = race_df['season'].astype(season_order)\n\nrace_df","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:03.207214Z","iopub.execute_input":"2022-11-10T16:01:03.207579Z","iopub.status.idle":"2022-11-10T16:01:03.253298Z","shell.execute_reply.started":"2022-11-10T16:01:03.207547Z","shell.execute_reply":"2022-11-10T16:01:03.251834Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dirt_color = ['#783f04', '#bb8632', '#cca466', '#ddc299']\ndirt_palette = sns.color_palette(dirt_color, as_cmap = True)\n\nturf_color = ['#38761d', '#739f60', '#afc8a4', '#ebf1e8']\nturf_palette = sns.color_palette(turf_color, as_cmap = True)\n\ndirt_turf_color = ['#783f04', '#38761d']\ndt_palette = sns.color_palette(dirt_turf_color, as_cmap = True)\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:03.254815Z","iopub.execute_input":"2022-11-10T16:01:03.255466Z","iopub.status.idle":"2022-11-10T16:01:03.263140Z","shell.execute_reply.started":"2022-11-10T16:01:03.255429Z","shell.execute_reply":"2022-11-10T16:01:03.261690Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_df = race_df.groupby('course_type')['race_id'].count().to_frame().reset_index().rename(columns = {'race_id' : 'races'})\ntemp_df.loc[temp_df['course_type'] == 'T', 'races'] = temp_df.loc[1:, 'races'].sum()\ntemp_df.drop(temp_df[temp_df['course_type'].isin(['I', 'O'])].index, inplace = True)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:03.265883Z","iopub.execute_input":"2022-11-10T16:01:03.266440Z","iopub.status.idle":"2022-11-10T16:01:03.283189Z","shell.execute_reply.started":"2022-11-10T16:01:03.266404Z","shell.execute_reply":"2022-11-10T16:01:03.281329Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ax = sns.barplot(data = temp_df, x = 'course_type', y = 'races', palette = ['#783f04', '#38761d'])\nax.bar_label(ax.containers[0])\nplt.title('Total Races and Course Type')\nplt.figure(figsize = (10, 5))","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:03.285253Z","iopub.execute_input":"2022-11-10T16:01:03.286086Z","iopub.status.idle":"2022-11-10T16:01:03.522917Z","shell.execute_reply.started":"2022-11-10T16:01:03.286048Z","shell.execute_reply":"2022-11-10T16:01:03.521460Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_df = race_df.copy()\ntemp_df.loc[temp_df['course_type'].isin(['I', 'O']), 'course_type'] = 'T'\ntemp_df = temp_df.groupby(['season', 'course_type'])['race_id'].count().to_frame().reset_index().rename(columns = {'race_id' : 'races'})\n# temp_df.drop(temp_df[temp_df['course_type'].isin(['I', 'O'])].index, inplace = True)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:03.524668Z","iopub.execute_input":"2022-11-10T16:01:03.526023Z","iopub.status.idle":"2022-11-10T16:01:03.545149Z","shell.execute_reply.started":"2022-11-10T16:01:03.525959Z","shell.execute_reply":"2022-11-10T16:01:03.543755Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ax = sns.barplot(data = temp_df, x = 'season', y = 'races', hue = 'course_type', palette = ['#783f04', '#38761d'])\n\nax.bar_label(ax.containers[0])\nax.bar_label(ax.containers[1])\n\nbox = ax.get_position()\nax.set_position([box.x0, box.y0, box.width * 0.8, box.height])\nax.legend(loc = 'center left', bbox_to_anchor = (1, 0.5))\n\nplt.title('Races & Season')\nplt.figure(figsize = (10, 5))","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:03.547049Z","iopub.execute_input":"2022-11-10T16:01:03.547468Z","iopub.status.idle":"2022-11-10T16:01:03.857665Z","shell.execute_reply.started":"2022-11-10T16:01:03.547422Z","shell.execute_reply":"2022-11-10T16:01:03.856263Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sub_dirt_df = (race_df.groupby(['season', 'dirt_condition'])['race_id'].count()\n .to_frame()\n .reset_index()\n .rename(columns = {\"race_id\" : 'counts'})\n )\nsub_dirt_df['season_counts'] = sub_dirt_df.groupby('season')['counts'].transform('sum')\nsub_dirt_df['seasonal_condition_rate'] = sub_dirt_df.apply(lambda x : round((x['counts'] / x ['season_counts']), 2), axis = 1)\nsub_dirt_df = sub_dirt_df[sub_dirt_df['counts'] != 0]\n\nsub_turf_df = race_df.copy()\nsub_turf_df.loc[sub_turf_df['course_type'].isin(['I', 'O']), 'course_type'] = 'T'\nsub_turf_df = (race_df.groupby(['season', 'turf_condition'])['race_id'].count()\n .to_frame()\n .reset_index()\n .rename(columns = {\"race_id\" : 'counts'})\n )\nsub_turf_df = sub_turf_df[sub_turf_df['counts'] != 0]","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:03.859371Z","iopub.execute_input":"2022-11-10T16:01:03.859844Z","iopub.status.idle":"2022-11-10T16:01:03.897887Z","shell.execute_reply.started":"2022-11-10T16:01:03.859801Z","shell.execute_reply":"2022-11-10T16:01:03.896344Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sub_dirt_df.head(2)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:03.900402Z","iopub.execute_input":"2022-11-10T16:01:03.900840Z","iopub.status.idle":"2022-11-10T16:01:03.915941Z","shell.execute_reply.started":"2022-11-10T16:01:03.900805Z","shell.execute_reply":"2022-11-10T16:01:03.914466Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(1, 2, figsize = (15, 5), sharey = True)\n\n\nsns.barplot(data = sub_dirt_df,\n x = 'season',\n y = 'counts',\n hue = 'dirt_condition',\n ax = ax[0],\n palette = dirt_palette\n )\n\nax[0].set_title('Seasonal Dirt Track Condition')\nax[0].set_ylabel('Races')\nax[0].legend().set_visible(False)\nbox = ax[0].get_position()\nax[0].set_position([box.x0, box.y0, box.width * 0.8, box.height])\nax[0].legend(loc = 'center left', bbox_to_anchor = (1, 0.5))\n\n\n\nsns.barplot(data = sub_turf_df,\n x = 'season',\n y = 'counts',\n hue = 'turf_condition',\n ax = ax[1],\n palette = turf_palette\n )\n\nax[1].set_title('Seasonal Turf Track Condition')\nax[1].set_ylabel(None)\n\n# legend\nbox = ax[1].get_position()\nax[1].set_position([box.x0, box.y0, box.width * 0.8, box.height])\n\nax[1].legend(loc = 'center left', bbox_to_anchor=(1, 0.5))\n\nfor i in range(2):\n for j in range(4):\n ax[i].bar_label(ax[i].containers[j])","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:03.922218Z","iopub.execute_input":"2022-11-10T16:01:03.922803Z","iopub.status.idle":"2022-11-10T16:01:04.589683Z","shell.execute_reply.started":"2022-11-10T16:01:03.922760Z","shell.execute_reply":"2022-11-10T16:01:04.588015Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.lineplot(data = (sub_dirt_df\n [sub_dirt_df['dirt_condition'].isin(['FT', 'SY'])][:]\n ), \n x = 'season', \n y = 'seasonal_condition_rate', \n hue = 'dirt_condition',\n palette = dirt_palette,\n alpha = 0.75)\n# plt.gca().legend().set_visible(False)\n\nplt.title('Seasonal Dirt Condition Rate : FT vs SY')","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:04.592093Z","iopub.execute_input":"2022-11-10T16:01:04.592627Z","iopub.status.idle":"2022-11-10T16:01:04.900474Z","shell.execute_reply.started":"2022-11-10T16:01:04.592589Z","shell.execute_reply":"2022-11-10T16:01:04.899332Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"$SeasonalDirt(Turf)Condition Rate = \\frac{Dirt Condition Races}{Total Dirt(Turf) Races}$","metadata":{}},{"cell_type":"markdown","source":"# 2. Jockey","metadata":{}},{"cell_type":"code","source":"merged_df = start_df.merge(race_df,\n on = ['race_id'])\n# merged_df.insert(0, 'race_id', merged_df.pop('race_id'))\nmerged_df.head(2)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:04.901969Z","iopub.execute_input":"2022-11-10T16:01:04.903685Z","iopub.status.idle":"2022-11-10T16:01:04.954483Z","shell.execute_reply.started":"2022-11-10T16:01:04.903614Z","shell.execute_reply":"2022-11-10T16:01:04.953163Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Jockeys, number of competitions, average ranking, average ranking per course type\n### Summary : Use all data","metadata":{}},{"cell_type":"markdown","source":"1. The null hypothesis is that \"the number of games and the order of participation have no relationship,\" and the results differ depending on how the data is prepared.\n 1. ***Excluding the range exceeding 'IQR'**: Regarding the number of matches and the average ranking, **There is little correlation + the p-value value is also large**\n 2. **Full data**: **The correlation coefficient is high + the p-value is also a value that can reject the null hypothesis.**\n2. When each of the riders is placed on the scatterplot, the ranking of the riders is integrated into the mean for the number of participants, and then the correlation coefficient and p-value value are different.\n\n- What can be said to be right?\n - There are a total of 162 players, and 29 players have played more than \"Upper IQR Boundary: 82 games,\" accounting for about 17%. I don't think it's a negligible figure.","metadata":{}},{"cell_type":"markdown","source":"1. 귀무가설은 \"경기 수와 출전 순위는 아무런 관계가 없다\"이고, 데이터를 어떻게 준비하느냐에 따라 다른 결과가 나왔음.\n 1. **`IQR`을 넘는 범위 제외** : 경기수와 평균 순위에 관해, **상관관계가 거의 없음 + p-value 값도 크게 나옴**\n 2. **전체 데이터** : **상관계수가 높게 나옴 + p-value도 귀무가설을 기각할 수 있는 수치가 나옴**\n2. 기수 각각을 산점도에 놨을 때 순위와, 출전수에 대해 기수들의 순위를 평균으로 통합한 다음 산점도에 놨을 때, 상관계수와 p-value값은 다르게 나옴\n\n- 무엇이 맞다고 할 수 있을까?\n - 총 162명인데 `Upper IQR Boundary : 82경기` 이상 뛴 기수가 29명으로 약 17%를 차지한다. 무시할만한 수치는 아니라고 생각됨.","metadata":{"jupyter":{"source_hidden":true}}},{"cell_type":"markdown","source":"### Find Outliers","metadata":{}},{"cell_type":"code","source":"jockey_df = merged_df.loc[:, ['race_id', 'jockey', 'course_type', 'dirt_condition', 'turf_condition', 'position_at_finish']]\njockey_df['num_of_competitions'] = jockey_df.groupby('jockey')['race_id'].transform('count')\njockey_df['avg_rank_by_jockey']= jockey_df.groupby(['jockey'])['position_at_finish'].transform('mean')\njockey_app_rank = jockey_df[['jockey', 'num_of_competitions', 'avg_rank_by_jockey']].drop_duplicates()\njockey_app_rank['jockey'].count()\njockey_app_rank.describe()","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:01:04.956764Z","iopub.execute_input":"2022-11-10T16:01:04.957684Z","iopub.status.idle":"2022-11-10T16:01:05.004893Z","shell.execute_reply.started":"2022-11-10T16:01:04.957635Z","shell.execute_reply":"2022-11-10T16:01:05.003104Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.boxplot(data = jockey_app_rank, x = 'num_of_competitions')\n\nplt.xlim([0, 80])\nplt.title(\"num_of_competitions Data Except Outliers\")","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:01:05.007433Z","iopub.execute_input":"2022-11-10T16:01:05.007876Z","iopub.status.idle":"2022-11-10T16:01:05.223284Z","shell.execute_reply.started":"2022-11-10T16:01:05.007841Z","shell.execute_reply":"2022-11-10T16:01:05.221418Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# upper_boundary\nx = jockey_app_rank['num_of_competitions']\nq_75, q_25 = np.percentile(x, [75, 25])\n\niqr = q_75 - q_25\nup_boundary = q_75 + 1.5 * iqr\nup_boundary","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:01:05.225481Z","iopub.execute_input":"2022-11-10T16:01:05.225927Z","iopub.status.idle":"2022-11-10T16:01:05.234349Z","shell.execute_reply.started":"2022-11-10T16:01:05.225891Z","shell.execute_reply":"2022-11-10T16:01:05.233476Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockey_app_rank[jockey_app_rank['num_of_competitions'] >= up_boundary]['jockey'].count()","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:05.237109Z","iopub.execute_input":"2022-11-10T16:01:05.237572Z","iopub.status.idle":"2022-11-10T16:01:05.250971Z","shell.execute_reply.started":"2022-11-10T16:01:05.237535Z","shell.execute_reply":"2022-11-10T16:01:05.248483Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### 1. num_of_competitions : Exclude outlier data beyond IQR","metadata":{}},{"cell_type":"code","source":"jockey_app_rank_inner = jockey_app_rank[jockey_app_rank['num_of_competitions'] <= up_boundary][:]\njockey_app_rank_inner","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:01:05.253323Z","iopub.execute_input":"2022-11-10T16:01:05.253811Z","iopub.status.idle":"2022-11-10T16:01:05.276283Z","shell.execute_reply.started":"2022-11-10T16:01:05.253774Z","shell.execute_reply":"2022-11-10T16:01:05.274837Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.regplot(data = jockey_app_rank_inner, x = 'num_of_competitions', y = 'avg_rank_by_jockey')\nplt.title(\"without outlier : jockey and avg_rank\")\nr, p_value = stats.pearsonr(x = jockey_app_rank_inner['num_of_competitions'], y = jockey_app_rank_inner['avg_rank_by_jockey'])\nprint(f\"corcoef = {r}, p-value = {p_value}\")","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:01:05.278344Z","iopub.execute_input":"2022-11-10T16:01:05.279394Z","iopub.status.idle":"2022-11-10T16:01:05.667058Z","shell.execute_reply.started":"2022-11-10T16:01:05.279306Z","shell.execute_reply":"2022-11-10T16:01:05.665441Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ntemp_df = jockey_app_rank_inner.groupby('num_of_competitions')['avg_rank_by_jockey'].mean().to_frame().reset_index().rename(columns = {'avg_rank_by_jockey' : 'avg_rank'})\nsns.regplot(data = temp_df, x = 'num_of_competitions', y = 'avg_rank')\nplt.title('without outlier : num_of_competitions and avg_rank')\nr, p_value =stats.pearsonr(x = temp_df['num_of_competitions'], y = temp_df['avg_rank'])\nprint(f\"corcoef = {r}, p-value = {p_value}\")","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:05.669479Z","iopub.execute_input":"2022-11-10T16:01:05.670738Z","iopub.status.idle":"2022-11-10T16:01:06.053189Z","shell.execute_reply.started":"2022-11-10T16:01:05.670688Z","shell.execute_reply":"2022-11-10T16:01:06.051322Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### 2. Plot without removing outlier data\n- The total number of jockeys is 162, and the value of 'nunique()' is 58.","metadata":{}},{"cell_type":"code","source":"sns.regplot(data = jockey_app_rank,\n x = 'num_of_competitions',\n y = 'avg_rank_by_jockey',\n# ci = None,\n scatter_kws = {'color' : 'green'})\n\nplt.title(\"with outlier : jockey and avg_rank\")\nstats.pearsonr(x = jockey_app_rank['num_of_competitions'], y = jockey_app_rank['avg_rank_by_jockey'])","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:06.055220Z","iopub.execute_input":"2022-11-10T16:01:06.055713Z","iopub.status.idle":"2022-11-10T16:01:06.370123Z","shell.execute_reply.started":"2022-11-10T16:01:06.055666Z","shell.execute_reply":"2022-11-10T16:01:06.368460Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dirt_appear_df = (jockey_df[jockey_df['course_type'] == 'D'].groupby('jockey')['race_id'].count()\n .to_frame()\n .reset_index()\n .rename(columns = {'race_id' : 'dirt_num_of_competitions'}))\nturf_appear_df = (jockey_df[jockey_df['course_type'].isin(['T', 'I', 'O'])].groupby('jockey')['race_id'].count()\n .to_frame()\n .reset_index()\n .rename(columns = {'race_id' : 'turf_num_of_competitions'}))\n\njockey_df = (jockey_df.merge(dirt_appear_df, how = 'left', on = 'jockey')\n .merge(turf_appear_df, how = 'left', on = 'jockey'))\njockey_df.info()\n","metadata":{"jupyter":{"source_hidden":true,"outputs_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:06.371994Z","iopub.execute_input":"2022-11-10T16:01:06.372425Z","iopub.status.idle":"2022-11-10T16:01:06.417727Z","shell.execute_reply.started":"2022-11-10T16:01:06.372375Z","shell.execute_reply":"2022-11-10T16:01:06.415957Z"},"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"jockey_df['dirt_num_of_competitions'].fillna(0, inplace = True)\njockey_df['turf_num_of_competitions'].fillna(0, inplace = True)\n\njockey_df.info()","metadata":{"jupyter":{"source_hidden":true,"outputs_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:06.420207Z","iopub.execute_input":"2022-11-10T16:01:06.420853Z","iopub.status.idle":"2022-11-10T16:01:06.445236Z","shell.execute_reply.started":"2022-11-10T16:01:06.420797Z","shell.execute_reply":"2022-11-10T16:01:06.443413Z"},"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 1. 출전 수와 평균 순위\njockey_df['num_of_competitions'] = jockey_df.groupby('jockey')['race_id'].transform('nunique')\napp_rank = (jockey_df.groupby('num_of_competitions')['position_at_finish'].mean()\n .to_frame()\n .reset_index()\n .rename(columns = {'position_at_finish' : 'avg_rank'}))\n\n# 2. Dirt 출전 수와 평균 순위\ndirt_app_rank = (jockey_df[jockey_df['course_type'] == 'D'].groupby('dirt_num_of_competitions')['position_at_finish']\n .mean()\n .to_frame()\n .reset_index()\n .rename(columns = {'position_at_finish' : 'dirt_avg_rank'}))\n\nturf_app_rank = (jockey_df[jockey_df['course_type'].isin(['T', 'I', 'O'])].groupby('turf_num_of_competitions')['position_at_finish']\n .mean()\n .to_frame()\n .reset_index()\n .rename(columns = {'position_at_finish' : 'turf_avg_rank'}))","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:01:06.447367Z","iopub.execute_input":"2022-11-10T16:01:06.448028Z","iopub.status.idle":"2022-11-10T16:01:06.479993Z","shell.execute_reply.started":"2022-11-10T16:01:06.447984Z","shell.execute_reply":"2022-11-10T16:01:06.478872Z"},"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_regplot(df, col1_name, col2_name, color = 'blue'):\n sns.regplot(data = df,\n x = col1_name,\n y = col2_name,\n# ci = None,\n color = color,\n line_kws = {\n# 'color' : 'grey',\n 'lw' : 3\n },\n scatter_kws = {'s' : 10,\n# 'color' : 'grey'\n })\n \n plt.ylim([3, 8])\n\ndef get_pearsonr(col1, col2):\n corrcoef, p_value = stats.pearsonr(x = col1,\n y = col2)\n print(f\"corrcoef : {corrcoef}, p-value : {p_value}\")\n \nget_regplot(app_rank, 'num_of_competitions', 'avg_rank')\nget_pearsonr(app_rank['num_of_competitions'], app_rank['avg_rank'])\nplt.title(\"Total num_of_competitions and Average Rank\", fontsize = 20)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:01:06.481687Z","iopub.execute_input":"2022-11-10T16:01:06.482360Z","iopub.status.idle":"2022-11-10T16:01:06.798493Z","shell.execute_reply.started":"2022-11-10T16:01:06.482322Z","shell.execute_reply":"2022-11-10T16:01:06.796866Z"},"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# sns.set_palette(sns.color_palette(dirt_colors))\nget_regplot(dirt_app_rank, 'dirt_num_of_competitions', 'dirt_avg_rank', color = 'brown')\nget_pearsonr(dirt_app_rank['dirt_num_of_competitions'], dirt_app_rank['dirt_avg_rank'])\nplt.title(\"Dirt : num_of_competitions and Average Rank\", fontsize = 20)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:01:06.800534Z","iopub.execute_input":"2022-11-10T16:01:06.801532Z","iopub.status.idle":"2022-11-10T16:01:07.104947Z","shell.execute_reply.started":"2022-11-10T16:01:06.801477Z","shell.execute_reply":"2022-11-10T16:01:07.103708Z"},"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"turf_palette\nget_regplot(turf_app_rank, 'turf_num_of_competitions', 'turf_avg_rank', color = 'green')\nget_pearsonr(turf_app_rank['turf_num_of_competitions'], turf_app_rank['turf_avg_rank'])\nplt.title(\"Turf : num_of_competitions and Average Rank\", fontsize = 20)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:01:07.106635Z","iopub.execute_input":"2022-11-10T16:01:07.107022Z","iopub.status.idle":"2022-11-10T16:01:07.433563Z","shell.execute_reply.started":"2022-11-10T16:01:07.106987Z","shell.execute_reply":"2022-11-10T16:01:07.432177Z"},"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### View the stadium\n- [link](https://www.kaggle.com/code/iamleonie/big-data-derby-step-by-step-analysis#Missing-Data) \n- I didn't think I could draw the stadium by looking at the longitude and latitude data. I'll just look at the idea and implement it myself","metadata":{}},{"cell_type":"code","source":"track = start_df['track_id'].unique()\ntotal_df = race_df.merge(track_df, on = ['track_id', 'race_date', 'race_number'])","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:07.435575Z","iopub.execute_input":"2022-11-10T16:01:07.435972Z","iopub.status.idle":"2022-11-10T16:01:11.773100Z","shell.execute_reply.started":"2022-11-10T16:01:07.435938Z","shell.execute_reply":"2022-11-10T16:01:11.771897Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"track_lst = race_df['track_id'].unique()\ntrack_lst","metadata":{"jupyter":{"source_hidden":true,"outputs_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:01:11.781627Z","iopub.execute_input":"2022-11-10T16:01:11.782044Z","iopub.status.idle":"2022-11-10T16:01:11.789756Z","shell.execute_reply.started":"2022-11-10T16:01:11.782011Z","shell.execute_reply":"2022-11-10T16:01:11.788594Z"},"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(3, 1, figsize = (15, 45))\ntrack_palette = {'D' : '#744700',\n 'T' : '#38761d',\n 'I' : '#91cd76',\n 'O' : '#91cd76', \n 'M' : '#d9ead3'}\n\nfor i, v in enumerate(track_lst):\n temp_df = (\n total_df[(total_df['track_id'] == v) & \n (total_df['trakus_index'].isin(list(range(0, 200)))) &\n (total_df['program_number'].isin(['1', '2', '3']))\n ]\n [['course_type', 'latitude', 'longitude']]\n )\n\n sns.scatterplot(data = temp_df,\n x = 'latitude',\n y = 'longitude',\n hue = 'course_type',\n ax = ax[i],\n palette = track_palette)\n \n ax[i].set_title(f'Track : {v}')","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:01:11.791832Z","iopub.execute_input":"2022-11-10T16:01:11.792260Z","iopub.status.idle":"2022-11-10T16:02:04.679635Z","shell.execute_reply.started":"2022-11-10T16:01:11.792227Z","shell.execute_reply":"2022-11-10T16:02:04.678497Z"},"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3. business analysis\n- Only analyzed the largest 'Stakes' race","metadata":{}},{"cell_type":"markdown","source":"- Race distance ranges from 3630 to 10560 (unit: feet)\n- `run_up_distance` : `9 ~ 246`\n- 'trakus_index' is well stamped in chronological order.","metadata":{}},{"cell_type":"code","source":"race_track = track_df.merge(race_df, on = ['race_id', 'track_id', 'race_date', 'race_number'])\n# race_track.insert(0, 'race_id', race_track.pop('race_id'))\nrace_track.drop(['track_id', 'race_date', 'race_number'], axis = 1, inplace = True)\nrace_track.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:02:04.681145Z","iopub.execute_input":"2022-11-10T16:02:04.682510Z","iopub.status.idle":"2022-11-10T16:02:09.810197Z","shell.execute_reply.started":"2022-11-10T16:02:04.682459Z","shell.execute_reply":"2022-11-10T16:02:09.808613Z"},"jupyter":{"source_hidden":true,"outputs_hidden":true},"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stakes = race_track[race_track['race_type'] == 'STK'][:] # 경기 거리는 3630 ~ 10560까지 있다\nstk_dirt = stakes[stakes['course_type'] == 'D'][:].drop('turf_condition', axis = 1)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:02:09.811620Z","iopub.execute_input":"2022-11-10T16:02:09.812020Z","iopub.status.idle":"2022-11-10T16:02:10.393821Z","shell.execute_reply.started":"2022-11-10T16:02:09.811987Z","shell.execute_reply":"2022-11-10T16:02:10.392099Z"},"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Selecting and Analyzing a Race\n- STK will catch any competition and measure the speed of each driver's Trakus Index\n- Speed can be obtained because the interval between Trakus indexes is stated to be 0.25 seconds\n- Apply 'haversine' library","metadata":{}},{"cell_type":"code","source":"# trakus_index로 계산\nrace_name = 'SAR_2019-08-10_8'\ntest_race = stk_dirt[stk_dirt['race_id'] == race_name][:]\n\ntest_race['next_lat'] = test_race.sort_values(by = ['program_number', 'trakus_index']).groupby('program_number')['latitude'].shift(-1)\ntest_race['next_lon'] = test_race.sort_values(by = ['program_number', 'trakus_index']).groupby('program_number')['longitude'].shift(-1)\n\ntest_race.loc[test_race['next_lat'].notnull(), 'moved'] = (test_race.apply\n (lambda x : haversine((x['latitude'], x['longitude']), \n (x['next_lat'], x['next_lon']), \n unit = 'ft'), \n axis = 1)\n )\n\ntest_race['velocity_ft/s'] = test_race['moved'] / 0.25 # feet per second\ntest_race['moved_km'] = test_race['moved'].apply(lambda x : x / 3281)\ntest_race['velocity_km/h'] = test_race['velocity_ft/s'].apply(lambda x : x / 3281 * 3600)\ntest_race['elapsed_time'] = (test_race['trakus_index'] - 1) * 0.25\ntest_race['total_distance'] = test_race.sort_values(by = ['program_number', 'trakus_index']).groupby('program_number')['moved'].cumsum()\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:10.396324Z","iopub.execute_input":"2022-11-10T16:02:10.396848Z","iopub.status.idle":"2022-11-10T16:02:10.534649Z","shell.execute_reply.started":"2022-11-10T16:02:10.396755Z","shell.execute_reply":"2022-11-10T16:02:10.532959Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_race.describe()","metadata":{"jupyter":{"source_hidden":true,"outputs_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:10.536805Z","iopub.execute_input":"2022-11-10T16:02:10.537285Z","iopub.status.idle":"2022-11-10T16:02:10.614539Z","shell.execute_reply.started":"2022-11-10T16:02:10.537249Z","shell.execute_reply":"2022-11-10T16:02:10.612895Z"},"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(1, 2, figsize = (30, 10))\n\nsns.lineplot(data = test_race,\n x = 'elapsed_time',\n y = 'velocity_km/h',\n hue = 'program_number',\n ax = ax[0])\nplt.title(f\"race {'SAR_2019-08-10_8'} : Time and Velocity\")\n\nax[0].legend().set_visible(False)\n\nsns.lineplot(data = test_race,\n x = 'elapsed_time',\n y = 'total_distance',\n hue = 'program_number',\n ax = ax[1])\n\nplt.title(f\"race {'SAR_2019-08-10_8'} : Time and Distance\")","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:10.616884Z","iopub.execute_input":"2022-11-10T16:02:10.617586Z","iopub.status.idle":"2022-11-10T16:02:11.505516Z","shell.execute_reply.started":"2022-11-10T16:02:10.617529Z","shell.execute_reply":"2022-11-10T16:02:11.503927Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 30초 ~ 50초 즘에서 나오는 속도가 일정하게 유지되는 구간이 궁금하다\n\ndata = test_race[test_race['elapsed_time'].between(31, 50, inclusive = \"both\")][:]\nelse_data = test_race[test_race['elapsed_time'].between(31, 50, inclusive = \"both\") == False][:]\n\nsns.set_palette(turf_palette)\nsns.scatterplot(data = data,\n x = 'latitude',\n y = 'longitude',\n color = 'red')\n\nsns.scatterplot(data = else_data,\n x = 'latitude',\n y = 'longitude',\n color = 'grey')\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:11.507450Z","iopub.execute_input":"2022-11-10T16:02:11.507813Z","iopub.status.idle":"2022-11-10T16:02:11.780900Z","shell.execute_reply.started":"2022-11-10T16:02:11.507782Z","shell.execute_reply":"2022-11-10T16:02:11.779350Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Get speed from the stakes table\n- Apply the above process for individual races to the entire Stake Races","metadata":{"execution":{"iopub.status.busy":"2022-11-03T05:51:59.619870Z","iopub.execute_input":"2022-11-03T05:51:59.620205Z","iopub.status.idle":"2022-11-03T05:51:59.625099Z","shell.execute_reply.started":"2022-11-03T05:51:59.620169Z","shell.execute_reply":"2022-11-03T05:51:59.624230Z"}}},{"cell_type":"code","source":"stakes_raw = race_track[race_track['race_type'] == 'STK'][:] \nstakes_raw.drop(['distance_id', 'race_type', 'month', 'season'], axis = 1, inplace = True)\nstakes_raw.head(2)","metadata":{"jupyter":{"source_hidden":true,"outputs_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:11.782889Z","iopub.execute_input":"2022-11-10T16:02:11.784326Z","iopub.status.idle":"2022-11-10T16:02:12.338245Z","shell.execute_reply.started":"2022-11-10T16:02:11.784266Z","shell.execute_reply":"2022-11-10T16:02:12.336841Z"},"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stakes_raw['next_lat'] = stakes_raw.sort_values(by = ['race_id', 'program_number', 'trakus_index']).groupby(['race_id', 'program_number'])['latitude'].shift(-1)\nstakes_raw['next_lon'] = stakes_raw.sort_values(by = ['race_id', 'program_number', 'trakus_index']).groupby(['race_id', 'program_number'])['longitude'].shift(-1)\n\nstakes_raw.loc[stakes_raw['next_lat'].notnull(), 'moved_ft'] = (stakes_raw.apply\n (lambda x : haversine((x['latitude'], x['longitude']), (x['next_lat'], x['next_lon']), \n unit = 'ft'\n ),\n axis = 1)\n )\nstakes_raw['velocity_ft'] = stakes_raw['moved_ft'] / 0.25 # feet per second\nstakes_raw.loc[stakes_raw['next_lat'].notnull(), 'moved_km'] = (stakes_raw.apply\n (lambda x : haversine((x['latitude'], x['longitude']), (x['next_lat'], x['next_lon']), \n unit = 'km'\n ),\n axis = 1)\n )\nstakes_raw['velocity_km/h'] = stakes_raw['moved_km'].apply(lambda x : x / 0.25 * 3600)\nstakes_raw['elapsed_time'] = (stakes_raw['trakus_index'] - 1) * 0.25\nstakes_raw['player_cumulative_moved'] = stakes_raw.sort_values(by = ['race_id', 'program_number', 'trakus_index']).groupby(['race_id', 'program_number'])['moved_km'].cumsum()\n\nstakes_raw","metadata":{"jupyter":{"source_hidden":true,"outputs_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:12.340949Z","iopub.execute_input":"2022-11-10T16:02:12.341555Z","iopub.status.idle":"2022-11-10T16:02:53.380054Z","shell.execute_reply.started":"2022-11-10T16:02:12.341502Z","shell.execute_reply":"2022-11-10T16:02:53.378714Z"},"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stakes_raw.describe()","metadata":{"jupyter":{"source_hidden":true,"outputs_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:53.381743Z","iopub.execute_input":"2022-11-10T16:02:53.382863Z","iopub.status.idle":"2022-11-10T16:02:53.861251Z","shell.execute_reply.started":"2022-11-10T16:02:53.382826Z","shell.execute_reply":"2022-11-10T16:02:53.859699Z"},"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Clean up outlier data\n- The maximum instantaneous speed taken by a horse is said to be 88 km/h.\n- [Related Information](http://www.speedofanimals.com/animals/horse)\n- Any value larger than $89.584 km/h$ is treated as an outlier, excluding races with outliers\n - The latitude and longitude data represented up to the sixth decimal place have an error of $0.11 m = 0.00011 km$.\n - And the error speed can have is $0.00011 \\times 4 \\times 3600$ = $1.584 km/h$. (location is taken every 0.25 seconds)\n - I don't know if the calculation is correct. Welcome criticism.","metadata":{}},{"cell_type":"markdown","source":"#### 참고 : 위도, 경도 데이터의 소수점 갯수와 그에 따른 오차\n- [관련 링크](https://gis.stackexchange.com/questions/8650/measuring-accuracy-of-latitude-and-longitude)\n- 내가 제대로 이해한 게 맞다면\n - 일반적으로 위도`latitude` 1도는 111111m를 덮는다고 알려져 있다. $\\frac{10^7}{90}$\n - 경도`longitude` 또한 위도와 비슷하거나 작다. 작아지는 이유는 극으로 갈수록 폭이 좁아지기 때문임\n - 따라서 소수점 6째자리까지 표현된 위도, 경도 데이터가 갖는 오차는 $0.11m = 0.00011km$ 정도이다.\n - 그리고 속도가 가질 수 있는 오차는 $0.00011 \\times 4 \\times 3600$ = $1.584km/h$이다. (위치는 0.25초마다 찍힘)\n - 계산이 맞나 모르겠음. 비판 환영합니다..\n- 따라서 $89.584km/h$보다 큰 값은 모두 이상치 취급하고, 이상치가 들어간 레이스는 제외하겠음","metadata":{"jupyter":{"source_hidden":true}}},{"cell_type":"code","source":"# outliers = stakes[stakes['velocity_km/h'] >= 89.584][:] \noutliers = stakes_raw[stakes_raw['velocity_km/h'] >= 89.584][:] \n\noutliers.shape # (218, 24)\noutliers_races = outliers['race_id'].unique() # 64개의 레이스에 이상치 데이터가 있음 / Stakes 전체 253개","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:53.863368Z","iopub.execute_input":"2022-11-10T16:02:53.863834Z","iopub.status.idle":"2022-11-10T16:02:53.874798Z","shell.execute_reply.started":"2022-11-10T16:02:53.863797Z","shell.execute_reply":"2022-11-10T16:02:53.872861Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stakes = stakes_raw.drop(stakes_raw[stakes_raw['race_id'].isin(outliers_races)].index)\nstakes['race_id'].nunique() # 189","metadata":{"jupyter":{"source_hidden":true,"outputs_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:53.877452Z","iopub.execute_input":"2022-11-10T16:02:53.878075Z","iopub.status.idle":"2022-11-10T16:02:54.130543Z","shell.execute_reply.started":"2022-11-10T16:02:53.878018Z","shell.execute_reply":"2022-11-10T16:02:54.128756Z"},"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Dirt vs Turf","metadata":{"execution":{"iopub.status.busy":"2022-11-05T07:42:42.881746Z","iopub.execute_input":"2022-11-05T07:42:42.882431Z","iopub.status.idle":"2022-11-05T07:42:42.921937Z","shell.execute_reply.started":"2022-11-05T07:42:42.882373Z","shell.execute_reply":"2022-11-05T07:42:42.920612Z"}}},{"cell_type":"markdown","source":"### Summary\n1. The average speed of `Turf` was higher than that of `Dirt` ($67.97 km/h$ vs $66.30 km/h$)\n - Other variables need to be investigated further. The streets of Gyeongju, the weight of the horse.\n2. In the case of stadium condition (`dirt_condition` and `turf_condition`), the average speed decreases in common as the ground holds moisture\n - Compared to the best and worst conditions, the average speed reduction of `Turf` was higher ($6.7%), 'Dirt': $3.2%)\n3. There was little difference according to Inner Turf, Turf, and Outer Turf.\n - I think it would be okay to tie it with 'Turf' as long as the nature of the track itself is laid down.","metadata":{}},{"cell_type":"markdown","source":"1. Average speed depending on track condition: There seems to be no difference","metadata":{}},{"cell_type":"code","source":"ax = stakes.groupby('course_type')['velocity_km/h'].mean().plot(kind = 'bar', color = ['brown'] + ['green'] * 3)\nax.bar_label(ax.containers[0])\nplt.title(\"course_type vs Average Speed\")\nplt.ylabel('average speed')","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:02:54.132505Z","iopub.execute_input":"2022-11-10T16:02:54.133034Z","iopub.status.idle":"2022-11-10T16:02:54.462405Z","shell.execute_reply.started":"2022-11-10T16:02:54.132985Z","shell.execute_reply":"2022-11-10T16:02:54.460683Z"},"jupyter":{"source_hidden":true},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"2. Average speed depending on track and track conditions","metadata":{}},{"cell_type":"code","source":"# 1. Dirt\ndirt = stakes[stakes['course_type'] == 'D'].groupby('dirt_condition')['velocity_km/h'].mean().to_frame().reset_index()\n\nplt.figure(figsize = (10, 5))\nax = sns.barplot(data = dirt,\n x = 'dirt_condition',\n y = 'velocity_km/h',\n palette = dirt_palette)\nax.bar_label(ax.containers[0])\nplt.title(\"Dirt : Track Condition and Average Speed\")","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:54.464636Z","iopub.execute_input":"2022-11-10T16:02:54.465493Z","iopub.status.idle":"2022-11-10T16:02:54.832136Z","shell.execute_reply.started":"2022-11-10T16:02:54.465449Z","shell.execute_reply":"2022-11-10T16:02:54.830333Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 2. turf\nturf = stakes[stakes['course_type'].isin(['T', 'I', 'O'])].groupby(['course_type', 'turf_condition'])['velocity_km/h'].mean().to_frame().reset_index()\ntio = stakes[stakes['course_type'].isin(['T', 'I', 'O'])].groupby(['course_type'])['velocity_km/h'].mean().to_frame().reset_index()\nfig, ax = plt.subplots(1, 2, figsize = (20, 7))\n\nsns.barplot(data = tio,\n x = 'course_type',\n y = 'velocity_km/h',\n color = 'green',\n ax = ax[0])\nax[0].set_title('turf position and velocity')\nax[0].bar_label(ax[0].containers[0])\n\n\nsns.barplot(data = turf,\n x = 'course_type',\n y = 'velocity_km/h',\n hue = 'turf_condition',\n palette = turf_palette,\n ax = ax[1])\nax[1].set_title('turf condition and velocity')\n\nbox = ax[1].get_position()\nax[1].set_position([box.x0, box.y0, box.width * 0.8, box.height])\nax[1].legend(loc = 'center left', bbox_to_anchor = (1, 0.5))","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:54.834776Z","iopub.execute_input":"2022-11-10T16:02:54.835279Z","iopub.status.idle":"2022-11-10T16:02:55.496001Z","shell.execute_reply.started":"2022-11-10T16:02:54.835238Z","shell.execute_reply":"2022-11-10T16:02:55.494687Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"turf = stakes[stakes['course_type'].isin(['T', 'I', 'O'])][:]\nturf.loc[turf['course_type'].isin(['I', 'O']), 'course_type'] = 'T'\nturf = turf.groupby(['course_type', 'turf_condition'])['velocity_km/h'].mean().to_frame().reset_index()","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:55.497803Z","iopub.execute_input":"2022-11-10T16:02:55.498187Z","iopub.status.idle":"2022-11-10T16:02:55.606138Z","shell.execute_reply.started":"2022-11-10T16:02:55.498151Z","shell.execute_reply":"2022-11-10T16:02:55.604574Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 시각화는 Dirt와 동시에 보자\nfig, ax = plt.subplots(1, 2, figsize = (16, 7), sharey = True)\n\nsns.barplot(data = turf, \n x = 'turf_condition', \n y = 'velocity_km/h', \n ax = ax[0])\nax[0].set_title(\"Turf : Track Condition and Average Speed\")\n\n\nsns.barplot(data = dirt,\n x = 'dirt_condition',\n y = 'velocity_km/h',\n palette = dirt_palette,\n ax = ax[1])\nax[1].set_title(\"Dirt : Track Condition and Average Speed\")\n\nfor i in range(2):\n for j in ['left', 'top', 'right']:\n ax[i].bar_label(ax[i].containers[0])\n ax[i].spines[j].set_visible(False)\n ax[i].get_yaxis().set_visible(False)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:55.607923Z","iopub.execute_input":"2022-11-10T16:02:55.608268Z","iopub.status.idle":"2022-11-10T16:02:56.006566Z","shell.execute_reply.started":"2022-11-10T16:02:55.608238Z","shell.execute_reply":"2022-11-10T16:02:56.005120Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stakes.loc[stakes['course_type'].isin(['I', 'O']), 'course_type'] = 'T'\nplt.figure(figsize = (10, 7))\nstakes.groupby('course_type')['velocity_km/h'].mean().plot(kind = 'bar', color = ['brown', 'green'])\nplt.gca().bar_label(plt.gca().containers[0])\nfor j in ['left', 'top', 'right']:\n plt.gca().spines[j].set_visible(False)\n\nplt.yticks(ticks = [])\n\nplt.title(\"Course Type and Average Speed\")\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:56.009017Z","iopub.execute_input":"2022-11-10T16:02:56.009917Z","iopub.status.idle":"2022-11-10T16:02:56.279692Z","shell.execute_reply.started":"2022-11-10T16:02:56.009871Z","shell.execute_reply":"2022-11-10T16:02:56.278504Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stakes = stakes[['race_id', 'program_number', 'trakus_index', 'latitude', 'longitude', 'next_lat', 'next_lon',\n 'course_type', 'dirt_condition', 'turf_condition', 'run_up_distance', 'moved_km', 'velocity_km/h', 'elapsed_time']]\n# stakes['track_id'] = stakes['race_id'].apply(lambda x : x[0:3])\nstakes = stakes.merge(start_df, on = ['race_id', 'program_number'])\nstakes.insert(1, 'track_id', stakes.pop('track_id'))\nstakes.loc[stakes['course_type'].isin(['I', 'O']), 'course_type'] = 'T'","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:56.283512Z","iopub.execute_input":"2022-11-10T16:02:56.283908Z","iopub.status.idle":"2022-11-10T16:02:56.667797Z","shell.execute_reply.started":"2022-11-10T16:02:56.283872Z","shell.execute_reply":"2022-11-10T16:02:56.666675Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Condition 2 Weight and Average Speed\n\n#### Summary\n- Contrary to the conventional wisdom that the heavier the weight, the lower the average speed, **did not show a distinct tendency.**\n- Rather - On the contrary, **The above once again shows that you can run faster on the Turf**\n- The average speed according to the average weight of each competition also does not have a clear trend","metadata":{}},{"cell_type":"code","source":"sns.histplot(data = stakes,\n x = 'weight_carried',\n# kde = True,\n bins = 20)\nplt.title(\"weight histogram\")","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:56.669026Z","iopub.execute_input":"2022-11-10T16:02:56.669359Z","iopub.status.idle":"2022-11-10T16:02:57.041597Z","shell.execute_reply.started":"2022-11-10T16:02:56.669330Z","shell.execute_reply":"2022-11-10T16:02:57.040644Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_df = stakes[['race_id', 'program_number', 'course_type', 'dirt_condition', 'turf_condition', 'weight_carried', 'velocity_km/h', 'elapsed_time', 'position_at_finish']]\ntemp_df = temp_df[temp_df['weight_carried'].between(110, 130, inclusive = 'both')][:]\n# print(temp_df.head(2))\ntemp_df = temp_df.groupby(['weight_carried', 'course_type'])['velocity_km/h'].agg(['mean', 'count']).dropna().reset_index()\n\nsns.lineplot(data = temp_df,\n x = 'weight_carried',\n y = 'mean',\n hue = 'course_type',\n palette = [dirt_palette[0], turf_palette[0]])\n\nplt.title('weight and average_speed')\nplt.ylabel(\"average speed\")","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:57.044515Z","iopub.execute_input":"2022-11-10T16:02:57.045685Z","iopub.status.idle":"2022-11-10T16:02:57.594495Z","shell.execute_reply.started":"2022-11-10T16:02:57.045643Z","shell.execute_reply":"2022-11-10T16:02:57.593140Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## ","metadata":{}},{"cell_type":"markdown","source":"### Average weight and average speed","metadata":{"execution":{"iopub.status.busy":"2022-11-07T08:13:25.696536Z","iopub.execute_input":"2022-11-07T08:13:25.697107Z","iopub.status.idle":"2022-11-07T08:13:25.928133Z","shell.execute_reply.started":"2022-11-07T08:13:25.697062Z","shell.execute_reply":"2022-11-07T08:13:25.927098Z"}}},{"cell_type":"code","source":"temp_df = stakes[['race_id', 'program_number', 'course_type', 'velocity_km/h', 'weight_carried']]\ntemp_df.head(2)","metadata":{"jupyter":{"source_hidden":true,"outputs_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:57.596172Z","iopub.execute_input":"2022-11-10T16:02:57.596585Z","iopub.status.idle":"2022-11-10T16:02:57.635471Z","shell.execute_reply.started":"2022-11-10T16:02:57.596550Z","shell.execute_reply":"2022-11-10T16:02:57.634195Z"},"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_df.loc[:, 'race_avg_weight'] = temp_df.groupby('race_id')['weight_carried'].transform('mean')\ntemp_df.loc[:, 'race_avg_speed'] = temp_df.groupby('race_id')['velocity_km/h'].transform('mean')\ntemp_df.loc[:, 'player_avg_speed'] = temp_df.groupby(['race_id', 'program_number'])['velocity_km/h'].transform('mean')\ntemp_df.head(2)","metadata":{"jupyter":{"source_hidden":true,"outputs_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:57.637314Z","iopub.execute_input":"2022-11-10T16:02:57.638071Z","iopub.status.idle":"2022-11-10T16:02:57.917745Z","shell.execute_reply.started":"2022-11-10T16:02:57.638031Z","shell.execute_reply":"2022-11-10T16:02:57.916293Z"},"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_df2 = temp_df.groupby(['course_type', 'race_avg_weight'])['race_avg_speed'].mean()\ntemp_df2","metadata":{"jupyter":{"source_hidden":true,"outputs_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:02:57.920149Z","iopub.execute_input":"2022-11-10T16:02:57.920964Z","iopub.status.idle":"2022-11-10T16:02:57.997674Z","shell.execute_reply.started":"2022-11-10T16:02:57.920912Z","shell.execute_reply":"2022-11-10T16:02:57.996194Z"},"collapsed":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(1, 2, figsize = (12, 6))\nsns.regplot(data = temp_df[temp_df['course_type'] == 'D'][:], \n x = 'race_avg_weight', \n y = 'race_avg_speed', \n color = dirt_palette[0],\n# ci = True,\n ax = ax[0]\n )\nax[0].set_title('Dirt : Average Weight and Average Speed')\n\n\nsns.regplot(data = temp_df[temp_df['course_type'] == 'T'][:], \n x = 'race_avg_weight', \n y = 'race_avg_speed', \n color = 'green',\n# ci = True,\n ax = ax[1]\n )\nax[1].set_title('Turf : Average Weight and Average Speed')\n","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:02:57.999769Z","iopub.execute_input":"2022-11-10T16:02:58.000210Z","iopub.status.idle":"2022-11-10T16:04:30.394878Z","shell.execute_reply.started":"2022-11-10T16:02:58.000174Z","shell.execute_reply":"2022-11-10T16:04:30.393195Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Condition 3: Average speed according to total mileage\n\n#### Summary\n1. Naturally, **The longer the distance, the lower the average speed**\n2. **The mileage of each race** is not continuous and **discrete** (now I know this)\n - Of the 1991 data excluding the hurdle race in `race_df`, there are 16 unique values of the mileage\n - If you come to 'stakes', the race has a total of 189 data and 14 unique values for the mileage\n3. It seems difficult to explain the reduction depending on the track condition except for the best management condition","metadata":{}},{"cell_type":"code","source":"temp_df = stakes[['race_id', 'course_type', 'dirt_condition', 'turf_condition', 'program_number', 'moved_km', 'velocity_km/h', 'elapsed_time']]\ntemp_df = temp_df.merge(race_df[['race_id', 'distance_feet']], on = 'race_id')\ntemp_df['race_distance_km'] = temp_df['distance_feet'].apply(lambda x : x / 3281)\ntemp_df = temp_df.drop('distance_feet', axis = 1)\ntemp_df['race_avg_speed_km/h'] = temp_df.groupby(['race_id'])['velocity_km/h'].transform('mean')","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:30.396534Z","iopub.execute_input":"2022-11-10T16:04:30.397211Z","iopub.status.idle":"2022-11-10T16:04:30.986603Z","shell.execute_reply.started":"2022-11-10T16:04:30.397169Z","shell.execute_reply":"2022-11-10T16:04:30.984973Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp_df = temp_df[['race_id', 'course_type', 'dirt_condition', 'turf_condition', 'race_distance_km', 'race_avg_speed_km/h']].drop_duplicates()\n\n# fig, ax = plt.subplots(1, 2, figsize = (16, 9), sharex = True, sharey = True)\n\nsns.lmplot(data = temp_df[temp_df['course_type'] == 'D'][:], \n x = 'race_distance_km', \n y = 'race_avg_speed_km/h', \n hue = 'dirt_condition',\n palette = dirt_palette,\n# ax = ax[0],\n ci = False\n )\nplt.title(\"Dirt : Race Distance and Average Speed\")\n# ax[0].set_title(\"Dirt : Race Distance and Average Speed\")","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:30.988369Z","iopub.execute_input":"2022-11-10T16:04:30.988765Z","iopub.status.idle":"2022-11-10T16:04:32.211615Z","shell.execute_reply.started":"2022-11-10T16:04:30.988733Z","shell.execute_reply":"2022-11-10T16:04:32.210397Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.lmplot(data = temp_df[temp_df['course_type'] == 'T'][:], \n x = 'race_distance_km', \n y = 'race_avg_speed_km/h', \n hue = 'turf_condition',\n palette = turf_palette,\n# ax = ax[1],\n ci = False\n )\n# ax[1].set_title(\"Turf : Race Distance and Average Speed\")\nplt.title(\"Turf : Race Distance and Average Speed\")","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:32.213534Z","iopub.execute_input":"2022-11-10T16:04:32.214473Z","iopub.status.idle":"2022-11-10T16:04:33.162246Z","shell.execute_reply.started":"2022-11-10T16:04:32.214421Z","shell.execute_reply":"2022-11-10T16:04:33.160886Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.histplot(temp_df['race_distance_km'], bins = 14)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:33.164175Z","iopub.execute_input":"2022-11-10T16:04:33.164625Z","iopub.status.idle":"2022-11-10T16:04:33.419897Z","shell.execute_reply.started":"2022-11-10T16:04:33.164564Z","shell.execute_reply":"2022-11-10T16:04:33.418937Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Condition 4: Average speed and average distance depending on the condition of the stadium and the ground\n\n#### 1. In terms of the stadium alone, the average speed is **BEL > SAR > AQU**.\n1. In `BEL` and `SAR`, relatively many `Turf` games were held & there were many games that maintained the bottom condition well\n2. On the other hand, `AQU` had a higher proportion of `Dirt` games than the other two stadiums, and there were many games that did not maintain the best floor condition\n\n#### 2. Meanwhile, there is a question about `AQU - DIRT - FT`\n- Measured lower average speed despite shorter mileage compared to the other two stadiums\n- Not even unusual cases with a small number of data. \n","metadata":{}},{"cell_type":"code","source":"race_df['race_distance_km'] = race_df['distance_feet'].apply(lambda x : x / 3281)\nstakes = stakes.merge(race_df[['race_id', 'distance_feet', 'race_distance_km']], on = 'race_id')\nstadium_df = stakes[['race_id', 'track_id', 'course_type', 'dirt_condition', 'turf_condition', 'race_distance_km', 'velocity_km/h']]\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:33.420955Z","iopub.execute_input":"2022-11-10T16:04:33.421274Z","iopub.status.idle":"2022-11-10T16:04:33.893056Z","shell.execute_reply.started":"2022-11-10T16:04:33.421245Z","shell.execute_reply":"2022-11-10T16:04:33.891985Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 중복 제거\nno_dup_stdium = stadium_df[['race_id', 'track_id', 'course_type', 'dirt_condition', 'turf_condition', 'race_distance_km']].drop_duplicates()\n\ndirt_stdium = no_dup_stdium[no_dup_stdium['course_type'] == 'D'][:]\nturf_stdium = no_dup_stdium[no_dup_stdium['course_type'] == 'T'][:]","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:33.894352Z","iopub.execute_input":"2022-11-10T16:04:33.894690Z","iopub.status.idle":"2022-11-10T16:04:34.082014Z","shell.execute_reply.started":"2022-11-10T16:04:33.894661Z","shell.execute_reply":"2022-11-10T16:04:34.080687Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"id_dist = dict(x = 'track_id', y = 'race_distance_km')\n''\nfig, ax = plt.subplots(2, 2, figsize = (12, 12), sharey = True)\n\nfig.suptitle(\"Stakes Race : Stadium, Track Condition, Distance\")\n\nsns.swarmplot(data = no_dup_stdium, **id_dist, ax = ax[0][0], palette = 'tab10')\nsns.swarmplot(data = no_dup_stdium, **id_dist, hue = 'course_type', palette = dt_palette, alpha = 0.75, ax = ax[0][1])\n# ax[0][1].ylabel.set_visible(False)\nsns.swarmplot(data = dirt_stdium, **id_dist, hue = 'dirt_condition', palette = dirt_palette, ax = ax[1][0])\nsns.swarmplot(data = turf_stdium, **id_dist, hue = 'turf_condition', palette = turf_palette, ax = ax[1][1])\n# ax[1][1].ylabel.set_visible(False)\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:34.083616Z","iopub.execute_input":"2022-11-10T16:04:34.083968Z","iopub.status.idle":"2022-11-10T16:04:35.473817Z","shell.execute_reply.started":"2022-11-10T16:04:34.083937Z","shell.execute_reply":"2022-11-10T16:04:35.472476Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stadium_df = stakes[['race_id', 'track_id', 'course_type', 'dirt_condition', 'turf_condition', 'velocity_km/h', 'moved_km']]\nstadium_df = stadium_df.merge(race_df[['race_id','race_distance_km']], on = 'race_id')\nstadium_df","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:35.475480Z","iopub.execute_input":"2022-11-10T16:04:35.476580Z","iopub.status.idle":"2022-11-10T16:04:35.620538Z","shell.execute_reply.started":"2022-11-10T16:04:35.476533Z","shell.execute_reply":"2022-11-10T16:04:35.619324Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dist_speed = stadium_df.groupby(['track_id'])[['velocity_km/h', 'race_distance_km']].mean().reset_index().dropna()\ntrack_dist_speed = stadium_df.groupby(['track_id', 'course_type'])[['velocity_km/h', 'race_distance_km']].mean().reset_index().dropna()\ndirt_cond_dist_speed = stadium_df[stadium_df['course_type'] == 'D'].groupby(['track_id', 'dirt_condition'])[['velocity_km/h', 'race_distance_km']].mean().reset_index().dropna()\nturf_cond_dist_speed = stadium_df[stadium_df['course_type'] == 'T'].groupby(['track_id', 'turf_condition'])[['velocity_km/h', 'race_distance_km']].mean().reset_index().dropna()","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:35.622308Z","iopub.execute_input":"2022-11-10T16:04:35.623075Z","iopub.status.idle":"2022-11-10T16:04:35.956341Z","shell.execute_reply.started":"2022-11-10T16:04:35.623029Z","shell.execute_reply":"2022-11-10T16:04:35.955354Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\nfig, ax = plt.subplots(4, 2, figsize = (12, 15))\nfig.suptitle(\"DISTANCE AND VELOCITY\")\nsns.set_style(\"ticks\",{'axes.grid' : True})\nsns.barplot(\n data = dist_speed, \n x = 'track_id', \n y = 'velocity_km/h',\n palette = 'tab10',\n ax = ax[0][0]\n)\n\nax[0][0].set_ylim([65, 70])\nax[0][0].set_title(\"Average Speed\")\n\nsns.barplot(\n data = dist_speed, \n x = 'track_id', \n y = 'race_distance_km',\n palette = 'tab10',\n ax = ax[0][1]\n)\n\nax[0][1].set_ylim([1.6, 1.8])\nax[0][1].set_title(\"Average Race Distance\")\n\nsns.barplot(\n data = track_dist_speed, \n x = 'track_id', \n y = 'velocity_km/h',\n hue = 'course_type',\n palette = dt_palette,\n ax = ax[1][0]\n)\nsns.barplot(\n data = track_dist_speed, \n x = 'track_id', \n y = 'race_distance_km',\n hue = 'course_type',\n palette = dt_palette,\n ax = ax[1][1]\n)\nax[1][0].legend().set_visible(False)\nbox = ax[1][1].get_position()\nax[1][1].set_position([box.x0, box.y0, box.width * 0.8, box.height])\nax[1][1].legend(loc = 'center left', bbox_to_anchor = (1, 0.5))\n\nsns.barplot(\n data = dirt_cond_dist_speed, \n x = 'track_id', \n y = 'velocity_km/h',\n hue = 'dirt_condition',\n palette = dirt_palette,\n ax = ax[2][0]\n)\nsns.barplot(\n data = dirt_cond_dist_speed, \n x = 'track_id', \n y = 'race_distance_km',\n hue = 'dirt_condition',\n palette = dirt_palette,\n ax = ax[2][1]\n)\n\nax[2][0].legend().set_visible(False)\nbox = ax[2][1].get_position()\nax[2][1].set_position([box.x0, box.y0, box.width * 0.8, box.height])\nax[2][1].legend(loc = 'center left', bbox_to_anchor = (1, 0.5))\n\nsns.barplot(\n data = turf_cond_dist_speed, \n x = 'track_id', \n y = 'velocity_km/h',\n hue = 'turf_condition',\n palette = turf_palette,\n ax = ax[3][0]\n)\nsns.barplot(\n data = turf_cond_dist_speed, \n x = 'track_id', \n y = 'race_distance_km',\n hue = 'turf_condition',\n palette = turf_palette,\n ax = ax[3][1]\n)\n\nax[3][0].legend().set_visible(False)\nbox = ax[3][1].get_position()\nax[3][1].set_position([box.x0, box.y0, box.width * 0.8, box.height])\nax[3][1].legend(loc = 'center left', bbox_to_anchor = (1, 0.5))\n\nplt.setp([ax[i][0] for i in range(4)], ylim = (60, 70))\nplt.setp([ax[i][1] for i in range(4)], ylim = (1.25, 2.1))\n\n# 값 표시\n# for i in range(4):\n# for j in range(2):\n# for k in range(len(ax[i][j].containers)):\n# ax[i][j].bar_label(ax[i][j].containers[k])\n\nfig.tight_layout()","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:35.957996Z","iopub.execute_input":"2022-11-10T16:04:35.958761Z","iopub.status.idle":"2022-11-10T16:04:37.442259Z","shell.execute_reply.started":"2022-11-10T16:04:35.958713Z","shell.execute_reply":"2022-11-10T16:04:37.440808Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"race_counts = stadium_df[['race_id', 'track_id', 'course_type', 'dirt_condition', 'turf_condition', 'race_distance_km']].drop_duplicates()\nrace_counts.head(3)","metadata":{"execution":{"iopub.status.busy":"2022-11-10T16:04:37.444279Z","iopub.execute_input":"2022-11-10T16:04:37.445141Z","iopub.status.idle":"2022-11-10T16:04:37.640897Z","shell.execute_reply.started":"2022-11-10T16:04:37.445084Z","shell.execute_reply":"2022-11-10T16:04:37.639587Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dirt= pd.pivot_table(race_counts[race_counts['course_type'] == 'D'],\n index = 'track_id',\n columns = ['course_type', 'dirt_condition'],\n values = 'race_id',\n aggfunc = 'count')\n\ndirt.columns = ['D_FT', 'D_GD', 'D_MY', 'D_SY']\ndirt= dirt.reset_index()\n\nturf= pd.pivot_table(race_counts[race_counts['course_type'] == 'T'],\n index = 'track_id',\n columns = ['course_type', 'turf_condition'],\n values = 'race_id',\n aggfunc = 'count')\nturf.columns = ['T_FM', 'T_GD', 'T_YL', 'T_SF']\nturf= turf.reset_index()\n\nmerged = dirt.merge(turf, on = 'track_id')\n\nmerged","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:37.642419Z","iopub.execute_input":"2022-11-10T16:04:37.642906Z","iopub.status.idle":"2022-11-10T16:04:37.688891Z","shell.execute_reply.started":"2022-11-10T16:04:37.642861Z","shell.execute_reply":"2022-11-10T16:04:37.687762Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merged_palette = dirt_palette + turf_palette\n\nfig, ax = plt.subplots(figsize = (12, 6))\n\nbtm = np.zeros(len(merged))\n\nfor i, val in enumerate(merged.columns[1:]):\n ax.bar(merged['track_id'], merged[val], bottom = btm, label = val, color = merged_palette[i])\n btm += np.array(merged[val])\n \nplt.title(\"Stakes : The Number Of Races and Ground Condition\")\n\nbox = ax.get_position()\nax.set_position([box.x0, box.y0, box.width * 0.8, box.height])\nax.legend(loc = 'center left', bbox_to_anchor = (1, 0.5))\n\n# ax.bar_label(ax.containers[0])\n# ax.bar_label(ax.containers[4])","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:37.690234Z","iopub.execute_input":"2022-11-10T16:04:37.690576Z","iopub.status.idle":"2022-11-10T16:04:38.001107Z","shell.execute_reply.started":"2022-11-10T16:04:37.690543Z","shell.execute_reply":"2022-11-10T16:04:37.999823Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Condition 5. `run_up_distance`\n\n#### Summary\n- **'trakus_index' is not exactly the beginning to end of the game**\n- When they left the gate ~ Recorded even after the competition ends\n- Therefore, the average speed obtained above is not an accurate value: an integral calculation is likely to be necessary to obtain a more accurate value.","metadata":{}},{"cell_type":"markdown","source":"- [Description 1](https://paulickreport.com/horseplayers-category/thoroughbred-idea-foundation-run-up-made-final-time-misleading-in-jessamine/)\n- [Description 2](https://www.youtube.com/watch?v=9xx-0LvEAk8)\n - If you look at `2:00`, there are two intuitive examples\n- The total distance of the race is from gate to destination.\n- However, measuring time in the horse race is not when you leave the gate, but at a certain point, which is a certain from the gate.\n- The distance from the gate to the point where the time starts is called `run_up_distance`.","metadata":{}},{"cell_type":"markdown","source":"**Is 'trakus_index' measured from the time you started leaving 'run_up_distance'? Or is it being measured from the point of departure from the gate?**\n- How can I get this?\n - Find the cumulative distance\n - Is this value greater than `race_distance`? Or is it smaller than `race_distance` and larger than `race_distance` - `run_up_distance`?\n - I think we can compare these two","metadata":{}},{"cell_type":"code","source":"stakes['run_up_distance_km'] = stakes['run_up_distance'].apply(lambda x : x / 3281)\nstakes['without_run_up_km'] = stakes['race_distance_km'] - stakes['run_up_distance_km']\nstakes = stakes.sort_values(by = 'trakus_index', ascending = True)\nstakes['total_moved_km'] = (stakes.groupby(['race_id', 'program_number'])['moved_km']\n .cumsum()\n )\ntest = stakes.sample(6)['race_id'].values","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:38.002465Z","iopub.execute_input":"2022-11-10T16:04:38.002808Z","iopub.status.idle":"2022-11-10T16:04:38.662253Z","shell.execute_reply.started":"2022-11-10T16:04:38.002777Z","shell.execute_reply":"2022-11-10T16:04:38.660728Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(len(test), 3, figsize = (18, 54))\ntrakus_idx_min = 0\ntrakus_idx_max = 10\n\n\nfor i in range(len(test)):\n sample = stakes[stakes['race_id'] == test[i]][:]\n sns.lineplot(\n data = sample[sample['trakus_index'].between(trakus_idx_min, trakus_idx_max, inclusive = 'both')],\n x = 'trakus_index', \n y = 'velocity_km/h', \n hue = 'program_number', \n ax = ax[i][0],\n palette = 'tab10'\n ) \n ax[i][0].legend().set_visible(False)\n# ax[i][0].axvline(x = sample.loc[sample.index[0], 'run_up_distance_km'], color = 'black', linestyle = '--')\n# ax[i][0].text(10.1,0,'blah',rotation=90)\n sns.lineplot(\n data = sample[sample['trakus_index'].between(trakus_idx_min, trakus_idx_max, inclusive = 'both')],\n x = 'total_moved_km', \n y = 'velocity_km/h', \n hue = 'program_number', \n ax = ax[i][1],\n palette = 'tab10'\n )\n ax[i][1].axvline(x = sample.loc[sample.index[0], 'run_up_distance_km'], color = 'black', linestyle = '--')\n ax[i][1].legend().set_visible(False)\n\n sns.scatterplot(data = sample[sample['trakus_index'].between(trakus_idx_min, trakus_idx_max, inclusive = 'both')],\n x = 'latitude' ,\n y = 'longitude', \n hue = 'program_number', \n ax = ax[i][2],\n palette = 'tab10'\n )\n box = ax[i][2].get_position()\n ax[i][2].set_position([box.x0, box.y0, box.width * 0.8, box.height])\n ax[i][2].legend(loc = 'center left', bbox_to_anchor = (1, 0.5))\n ax[i][2].grid(False)\n \nfig.tight_layout()\n# fig.suptitle(f'Observe {trakus_idx_max * 0.25}sec after the start of the match')\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:38.664300Z","iopub.execute_input":"2022-11-10T16:04:38.664669Z","iopub.status.idle":"2022-11-10T16:04:46.048638Z","shell.execute_reply.started":"2022-11-10T16:04:38.664639Z","shell.execute_reply":"2022-11-10T16:04:46.047698Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Looks like coordinates are starting to take place as soon as you leave the gate\n- If the speed starts to be taken after `run_up_distance`, the measurement will start at some speed.\n- Each row represents a sample competition, and the second graph is a plot showing the cumulative travel distance and the speed at that time. The black dotted line corresponds to `run_up_distance`.","metadata":{}},{"cell_type":"markdown","source":"- On the other hand, compare the total distance taken in the above-mentioned latitude/longitude coordinates with the average value of the actual distance for each horse","metadata":{}},{"cell_type":"code","source":"comp = stakes[['race_id', 'trakus_index', 'program_number', 'race_distance_km', 'run_up_distance_km', 'without_run_up_km', 'total_moved_km']]\ncomp2 = (comp.groupby('race_id')['trakus_index'].max()-1).to_frame().reset_index()\nmerged = comp.merge(comp2, on = ['race_id', 'trakus_index'])\n\nmerged[['avg_moved_km', 'avg_without_run_up']] = merged.groupby('race_id')[['total_moved_km', 'without_run_up_km']].transform('mean')\nmerged = merged[['race_id', 'race_distance_km', 'avg_moved_km', 'avg_without_run_up']].drop_duplicates()\n(merged[['race_distance_km', 'avg_moved_km', 'avg_without_run_up']].rename(columns = {'race_distance_km' : 'known_distance',\n 'avg_moved_km' : 'actual_moved',\n 'avg_withpout_run_up' : 'actual_moved_without_run_up'})\n .mean()\n .plot(kind = 'bar', color = ['red', 'blue', 'green'])\n)\nplt.xticks(rotation = 0)\nplt.ylabel('distance')\nplt.gca().bar_label(plt.gca().containers[0])\nplt.title(\"Known Distance vs Actual Moved\")","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:46.050134Z","iopub.execute_input":"2022-11-10T16:04:46.050735Z","iopub.status.idle":"2022-11-10T16:04:46.610071Z","shell.execute_reply.started":"2022-11-10T16:04:46.050700Z","shell.execute_reply":"2022-11-10T16:04:46.608819Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- `trakus_index` does not accurately measure the start - end of a competition.\n- This is because the distance of movement (red bar) specified in the game is about 160 meters different from the actual distance of movement (blue bar).\n- On the other hand, on average, the value excluding `run_up_distance` has only an error of about 20m with the travel distance specified in the game.","metadata":{}},{"cell_type":"markdown","source":"### So to put it together with what we talked about above\n1. **The time when the actual race is measured and the time when 'trakus_index' is measured are different.** The former is included in the latter.\n2. If only the recorded game is to be measured, the accumulated moving distance and average speed obtained above for the reason of 1.\n- However, it is not necessary to improve each instantaneous speed measured by 'trakus_index'.\n - The time when the first word passes by `run_up_distance`\n - Time each horse passes through the end position (`race_distance`)\n- If you only get the above two, you'll be able to get the average horse speed in each game\n- I thought of a heuristic approach first, but **There is an error with the actual ranking due to an interval of 0.25 seconds**\n- So if you want to use the data to **exact point**, you'd be right to **use the integration**","metadata":{}},{"cell_type":"markdown","source":"-----------------------------------","metadata":{}},{"cell_type":"markdown","source":"#### Heuristic approach: failure\n\n#### summary\n- Use `trakus_index` to record the start and finish of the competition (**0.25 seconds of error**)\n- Even if it was heuristic, there was an assumption that the average speed of the recorded interval would be ranked in the order of high speed\n - It wasn't like that in reality\n- The conclusion that the race needs to save continuity even if it is not continuous data, and integration will be necessary for this","metadata":{}},{"cell_type":"markdown","source":"- Find the average speed for each race\n- It looks like the best way to find integration, but I'll go with heuristics\n - How many seconds after the start of the game, I thought there would be data on whether it passed `run_up_distance` so I approached like this.\n - I don't know if there will be a calculation cost issue.\n- Heuristics: The data taken at trackus: n is measured by the distance and speed traveled to n -> n+1\n - Therefore, you can select the smallest value among the values of `trakus_index` greater than `run_up_distance`","metadata":{}},{"cell_type":"code","source":"# 특정 레이스부터 보겠음\nsample_race = stakes['race_id'].sample(1).values[0]\nsample_run = stakes[stakes['race_id'] == sample_race][:]","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:46.612368Z","iopub.execute_input":"2022-11-10T16:04:46.613120Z","iopub.status.idle":"2022-11-10T16:04:46.752922Z","shell.execute_reply.started":"2022-11-10T16:04:46.613055Z","shell.execute_reply":"2022-11-10T16:04:46.751432Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_run.columns","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:46.755081Z","iopub.execute_input":"2022-11-10T16:04:46.755732Z","iopub.status.idle":"2022-11-10T16:04:46.766335Z","shell.execute_reply.started":"2022-11-10T16:04:46.755623Z","shell.execute_reply":"2022-11-10T16:04:46.764687Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_run = sample_run[['program_number', 'trakus_index', 'elapsed_time', 'total_moved_km', 'velocity_km/h', 'race_distance_km', 'run_up_distance_km']]\nsample_run['elapsed_time'] = sample_run['elapsed_time'] + 0.25\nsample_run","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:46.768740Z","iopub.execute_input":"2022-11-10T16:04:46.769460Z","iopub.status.idle":"2022-11-10T16:04:46.796076Z","shell.execute_reply.started":"2022-11-10T16:04:46.769346Z","shell.execute_reply":"2022-11-10T16:04:46.794367Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"find_start_time = (sample_run[sample_run['total_moved_km'] >= sample_run['run_up_distance_km']]\n .sort_values(by = 'trakus_index'))\nsample_run['start_time'] = find_start_time.loc[find_start_time.index[0], 'elapsed_time']","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:46.798359Z","iopub.execute_input":"2022-11-10T16:04:46.798913Z","iopub.status.idle":"2022-11-10T16:04:46.813827Z","shell.execute_reply.started":"2022-11-10T16:04:46.798872Z","shell.execute_reply":"2022-11-10T16:04:46.812452Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### It can be said that the game started between 0.25 seconds before `start_time` and `start_time`\n- I set it up like this to make sure that the game area is clear\n- The end of the competition is also set to the point at which the cumulative travel distance exceeds `race_distance_km`","metadata":{}},{"cell_type":"code","source":"find_end_time = (sample_run[sample_run['total_moved_km'] >= sample_run['race_distance_km']].sort_values(by = 'trakus_index')\n .groupby('program_number')['elapsed_time']\n .min())\nsample_run = sample_run.merge(find_end_time.to_frame().rename(columns = {'elapsed_time' : 'finish_time'}), on = 'program_number')\nsample_run['avg_speed'] = (sample_run['race_distance_km'] - sample_run['run_up_distance_km']) / (sample_run['finish_time'] - sample_run['start_time']) * 3600\nresult = sample_run[['program_number', 'avg_speed']].drop_duplicates()","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:46.815789Z","iopub.execute_input":"2022-11-10T16:04:46.816356Z","iopub.status.idle":"2022-11-10T16:04:46.836953Z","shell.execute_reply.started":"2022-11-10T16:04:46.816318Z","shell.execute_reply":"2022-11-10T16:04:46.835645Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"result = result.merge(start_df[start_df['race_id'] == sample_race][['program_number', 'position_at_finish']], on = 'program_number')","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:46.838983Z","iopub.execute_input":"2022-11-10T16:04:46.839329Z","iopub.status.idle":"2022-11-10T16:04:46.853959Z","shell.execute_reply.started":"2022-11-10T16:04:46.839297Z","shell.execute_reply":"2022-11-10T16:04:46.852671Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"result.sort_values(by = 'avg_speed', ascending = False)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:46.856718Z","iopub.execute_input":"2022-11-10T16:04:46.857152Z","iopub.status.idle":"2022-11-10T16:04:46.872762Z","shell.execute_reply.started":"2022-11-10T16:04:46.857116Z","shell.execute_reply":"2022-11-10T16:04:46.871275Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Conclusion: The method of measuring the average speed after heuristic selection of the game time is wrong\n- Therefore, it is important to select the exact time of the game, but I think it will be possible using integration (although this is not a completely accurate value)\n- However, I can't proceed with this process because I don't have time and I want to try something else","metadata":{}},{"cell_type":"markdown","source":"----------------------------------\n","metadata":{}},{"cell_type":"markdown","source":"#### AQU_DIRT_FT\n- About 'DIRT_FT': Why is the average speed slow even though the average race length is short?\n#### Summary\n- I tried 2 criteria, but neither of them got a satisfactory explanation.\n1. The **average weight** of `AQU` was not particularly heavier.\n2. The tendency of the average speed to decrease with **number of participants** is weak. However, races with a large number of participants are often long.\n3. If you fix both the floor condition and length and look at the trend according to the number of participants, you can see a tendency to decrease the speed from 5 to 8 participants, but it cannot be said that always.","metadata":{}},{"cell_type":"code","source":"dirt_ft = stakes[(stakes['course_type'] == 'D') & \n (stakes['dirt_condition'] == 'FT')][:]\ndirt_ft['elapsed_time'] += 0.25\ndirt_ft = dirt_ft.drop(['latitude', 'longitude', 'next_lat', 'next_lon', 'course_type', 'dirt_condition', 'turf_condition',\n 'race_date'], axis = 1)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:46.874309Z","iopub.execute_input":"2022-11-10T16:04:46.874854Z","iopub.status.idle":"2022-11-10T16:04:47.001729Z","shell.execute_reply.started":"2022-11-10T16:04:46.874797Z","shell.execute_reply":"2022-11-10T16:04:47.000296Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 1. 위에서도 봤지만 다시 한번 평균 거리와 평균 속도\ntemp = dirt_ft.groupby('track_id')['velocity_km/h', 'race_distance_km'].mean().reset_index()\n\nfig, ax = plt.subplots(1, 2, figsize = (10, 5))\n\nfig.suptitle(\"Average Distance and Velocity\")\n\nsns.barplot(data = temp, x = 'track_id', y = 'race_distance_km', palette = 'tab10', ax = ax[0])\nax[0].bar_label(ax[0].containers[0])\nax[0].set_ylim(1.5, 1.8)\n\nsns.barplot(data = temp, x = 'track_id', y = 'velocity_km/h', palette = 'tab10', ax = ax[1])\nax[1].bar_label(ax[1].containers[0])\nax[1].set_ylim(65, 70)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:47.003351Z","iopub.execute_input":"2022-11-10T16:04:47.003742Z","iopub.status.idle":"2022-11-10T16:04:47.392916Z","shell.execute_reply.started":"2022-11-10T16:04:47.003710Z","shell.execute_reply":"2022-11-10T16:04:47.391630Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# AQU = dirt_ft[dirt_ft['track_id'] == 'AQU'][:]\n# BEL = dirt_ft[dirt_ft['track_id'] == 'BEL'][:]\n# SAR = dirt_ft[dirt_ft['track_id'] == 'SAR'][:]","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:47.394290Z","iopub.execute_input":"2022-11-10T16:04:47.394671Z","iopub.status.idle":"2022-11-10T16:04:47.400099Z","shell.execute_reply.started":"2022-11-10T16:04:47.394638Z","shell.execute_reply":"2022-11-10T16:04:47.398860Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Approach 1. Average Weight\n- Although no clear trend was found between weight and speed above, it can be inferred that there may be a relationship.\n- Conclusion) No. There is little difference in average weight.","metadata":{}},{"cell_type":"code","source":"tab10 = sns.color_palette('tab10')","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:47.402002Z","iopub.execute_input":"2022-11-10T16:04:47.402368Z","iopub.status.idle":"2022-11-10T16:04:47.412665Z","shell.execute_reply.started":"2022-11-10T16:04:47.402331Z","shell.execute_reply":"2022-11-10T16:04:47.411643Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize = (10, 6))\n\nax = (dirt_ft[['race_id', 'track_id', 'program_number', 'weight_carried']]\n .drop_duplicates()\n .groupby('track_id')['weight_carried']\n .mean()\n .plot(kind = 'bar', color = tab10[:3]))\n\nax.bar_label(ax.containers[0])\nax.set_title('DIRT_FT : Stadium and average weight')\nplt.xticks(rotation = 0)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:47.413891Z","iopub.execute_input":"2022-11-10T16:04:47.414196Z","iopub.status.idle":"2022-11-10T16:04:47.796157Z","shell.execute_reply.started":"2022-11-10T16:04:47.414168Z","shell.execute_reply":"2022-11-10T16:04:47.794964Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Approach 2. Number of competitors\n- Wouldn't it be slower on average because the more people participate in each game, the more distractions they get?","metadata":{}},{"cell_type":"code","source":"ax = (dirt_ft[['race_id', 'track_id', 'program_number']]\n .drop_duplicates()\n .groupby(['track_id', 'race_id'])['program_number']\n .count()\n .groupby('track_id')\n .mean()\n .plot(kind = 'bar', color = tab10[:3])\n )\nax.set_title('DIRT_FT : Average Participants')\nax.bar_label(ax.containers[0])","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:47.806927Z","iopub.execute_input":"2022-11-10T16:04:47.807314Z","iopub.status.idle":"2022-11-10T16:04:48.125075Z","shell.execute_reply.started":"2022-11-10T16:04:47.807283Z","shell.execute_reply":"2022-11-10T16:04:48.123395Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Is a decrease in the number of people and average speed a common phenomenon?\n- There is a tendency like that, but I don't think it is enough to make a difference of about 3km/h.\n- In addition, **Tends to have a large number of participants in long-distance races**","metadata":{}},{"cell_type":"code","source":"temp2 = (stakes[['race_id', 'program_number']].drop_duplicates()\n .groupby('race_id')['program_number']\n .count()\n .to_frame()\n .reset_index()\n .rename(columns = {'program_numbers' : 'members'})\n )\nmembers_races = temp2.groupby('program_number')['race_id'].count().to_frame().reset_index().rename(columns = {'program_number' : 'participants','race_id' : 'races'})\n\nrace_members = (stakes[['race_id', 'program_number']].drop_duplicates()\n .groupby('race_id')['program_number']\n .count()\n .to_frame()\n .reset_index()\n .rename(columns = {'program_numbers' : 'members'})\n )\nrace_speed = (stakes[['race_id', 'velocity_km/h']].groupby(['race_id'])['velocity_km/h']\n .mean()\n .to_frame()\n .reset_index()\n .rename(columns = {'velocity_km/h' : 'avg_speed'})\n)\n\nrace_members_speed = race_members.merge(race_speed, on = 'race_id')\nrace_members_speed = race_members_speed.groupby('program_number')['avg_speed'].mean().to_frame().reset_index()\n\n\nrace_dist = (stakes[['race_id', 'race_distance_km']].groupby(['race_id'])['race_distance_km']\n .mean()\n .to_frame()\n .reset_index()\n .rename(columns = {'race_distance_km' : 'avg_distance'})\n)\nrace_members_dist = race_members.merge(race_dist, on = 'race_id')\nrace_members_dist = race_members_dist.groupby('program_number')['avg_distance'].mean().to_frame().reset_index()\n\n\n# 시각화\nfig, ax = plt.subplots(1, 3, figsize = (16, 9))\n\nsns.barplot(data = members_races,\n x = 'participants',\n y = 'races',\n ax = ax[0])\n\nax[0].set_title('Overall : # of Participants and Races')\nax[0].bar_label(ax[0].containers[0])\n\n\nsns.barplot(data = race_members_speed, x = 'program_number', y = 'avg_speed', ax = ax[1])\nax[1].set_title('Overall : # of Participants and Average Speed')\nax[1].set_ylim([65, 70])\n\nsns.barplot(data = race_members_dist, x = 'program_number', y = 'avg_distance', ax = ax[2])\nax[2].set_title('Overall : # of Participants and Average Distance')\nax[2].set_ylim([1.25, 2.1])","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:48.126974Z","iopub.execute_input":"2022-11-10T16:04:48.127482Z","iopub.status.idle":"2022-11-10T16:04:49.668569Z","shell.execute_reply.started":"2022-11-10T16:04:48.127438Z","shell.execute_reply":"2022-11-10T16:04:49.667189Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp2 = (dirt_ft[['race_id', 'program_number']].drop_duplicates()\n .groupby('race_id')['program_number']\n .count()\n .to_frame()\n .reset_index()\n .rename(columns = {'program_numbers' : 'members'})\n )\nmembers_races = temp2.groupby('program_number')['race_id'].count().to_frame().reset_index().rename(columns = {'program_number' : 'participants','race_id' : 'races'})\n\nrace_members = (dirt_ft[['race_id', 'program_number']].drop_duplicates()\n .groupby('race_id')['program_number']\n .count()\n .to_frame()\n .reset_index()\n .rename(columns = {'program_numbers' : 'members'})\n )\nrace_speed = (dirt_ft[['race_id', 'velocity_km/h']].groupby(['race_id'])['velocity_km/h']\n .mean()\n .to_frame()\n .reset_index()\n .rename(columns = {'velocity_km/h' : 'avg_speed'})\n)\n\nrace_members_speed = race_members.merge(race_speed, on = 'race_id')\nrace_members_speed = race_members_speed.groupby('program_number')['avg_speed'].mean().to_frame().reset_index()\n\n# # 시각화\n# fig, ax = plt.subplots(1, 2, figsize = (16, 9))\n\n# sns.barplot(data = members_races,\n# x = 'participants',\n# y = 'races',\n# ax = ax[0],\n# color = dirt_palette[0])\n\n\n# ax[0].set_title('DIRT_FT : # of Participants and Races')\n# ax[0].bar_label(ax[0].containers[0])\n\n\n# sns.barplot(data = race_members_speed, x = 'program_number', y = 'avg_speed', color = dirt_palette[1], ax = ax[1])\n# ax[1].set_title('DIRT_FT : # of Participants and Average Speed')\n# ax[1].set_ylim([65, 70])","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:49.670486Z","iopub.execute_input":"2022-11-10T16:04:49.670817Z","iopub.status.idle":"2022-11-10T16:04:49.978191Z","shell.execute_reply.started":"2022-11-10T16:04:49.670788Z","shell.execute_reply":"2022-11-10T16:04:49.977218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 주행거리가 영향을 미치니까 주행거리도 살펴보자 : x축은 레이스 당 참가인원 수, y축은 주행 거리 및 평균 속도\ntemp = dirt_ft[['race_id', 'program_number', 'race_distance_km']].drop_duplicates()\ntemp['participants'] = temp.groupby(['race_id'])['program_number'].transform('count')\ntemp = temp.drop('program_number', axis = 1).drop_duplicates()\ntemp","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:49.979752Z","iopub.execute_input":"2022-11-10T16:04:49.980064Z","iopub.status.idle":"2022-11-10T16:04:50.119328Z","shell.execute_reply.started":"2022-11-10T16:04:49.980035Z","shell.execute_reply":"2022-11-10T16:04:50.118143Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 레이스의 평균 속도도 뽑자\ntemp2 = dirt_ft[['race_id', 'program_number', 'velocity_km/h']]\ntemp2['race_avg_velocity'] = temp2.groupby('race_id')['velocity_km/h'].transform('mean')\ntemp2 = temp2.drop(['program_number', 'velocity_km/h'], axis = 1).drop_duplicates() \ntemp2.head()","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:50.121293Z","iopub.execute_input":"2022-11-10T16:04:50.122065Z","iopub.status.idle":"2022-11-10T16:04:50.279342Z","shell.execute_reply.started":"2022-11-10T16:04:50.122015Z","shell.execute_reply":"2022-11-10T16:04:50.277955Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"temp3 = temp.merge(temp2, on = 'race_id')\ntemp3.head(1)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:50.280700Z","iopub.execute_input":"2022-11-10T16:04:50.281041Z","iopub.status.idle":"2022-11-10T16:04:50.297669Z","shell.execute_reply.started":"2022-11-10T16:04:50.281010Z","shell.execute_reply":"2022-11-10T16:04:50.296289Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(1, 3, figsize = (16, 9))\n\nsns.barplot(data = members_races,\n x = 'participants',\n y = 'races',\n ax = ax[0],\n color = dirt_palette[0])\n\nax[0].set_title('DIRT_FT : # of Participants and # of Races')\n# ax[0].bar_label(ax[0].containers[0])\n\nsns.barplot(data = race_members_speed.rename(columns = {'program_number' : 'participants'}), x = 'participants', y = 'avg_speed', color = dirt_palette[1], ax = ax[1])\nax[1].set_title('DIRT_FT : # of Participants and Average Speed')\nax[1].set_ylim([65, 70])\n\nsns.barplot(data = temp3, x = 'participants', y = 'race_distance_km', ci = False, ax = ax[2], color = dirt_palette[2])\nax[2].set_ylim([1.2, 2.2])\nax[2].bar_label(ax[2].containers[0])\nax[2].set_title('DIRT_FT : # of Participants and Average Distance')\n","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:50.299820Z","iopub.execute_input":"2022-11-10T16:04:50.300255Z","iopub.status.idle":"2022-11-10T16:04:51.088184Z","shell.execute_reply.started":"2022-11-10T16:04:50.300220Z","shell.execute_reply":"2022-11-10T16:04:51.087032Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- It is a graph about the number of games, average speed, and distance.\n- When comparing participants 6 and 8 with the most similar average distance (6m difference), it can be seen that the average speed of 8 is low\n- It seems necessary to compare values that have the same distance and have different numbers of participants","metadata":{}},{"cell_type":"code","source":"dist_values = temp3['race_distance_km'].value_counts()\ndist_mode_idx = dist_values.index[:5]","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:51.089854Z","iopub.execute_input":"2022-11-10T16:04:51.091114Z","iopub.status.idle":"2022-11-10T16:04:51.097319Z","shell.execute_reply.started":"2022-11-10T16:04:51.091072Z","shell.execute_reply":"2022-11-10T16:04:51.096255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(len(dist_mode_idx), 1, figsize = (7, 30))\n\nfor i in range(len(dist_mode_idx)):\n t = (temp3[temp3['race_distance_km'] == dist_mode_idx[i]][:]\n .groupby('participants')['race_avg_velocity']\n .mean()\n .to_frame()\n .reset_index()\n )\n sns.barplot(data = t, x = 'participants', y = 'race_avg_velocity', color = 'brown', ax = ax[i])\n ax[i].set_ylim([60, 72])\n ax[i].bar_label(ax[i].containers[0])\n ax[i].set_title(f\"DIRT_FT : When Distance Race = {round(dist_mode_idx[i], 2)}km / samples : {dist_values[dist_mode_idx[i]]}\")\nplt.xticks(rotation = 0)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2022-11-10T16:04:51.098545Z","iopub.execute_input":"2022-11-10T16:04:51.098898Z","iopub.status.idle":"2022-11-10T16:04:52.291274Z","shell.execute_reply.started":"2022-11-10T16:04:51.098866Z","shell.execute_reply":"2022-11-10T16:04:52.289489Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- It will be difficult to conclude because each race distance has different patterns.","metadata":{}},{"cell_type":"markdown","source":"- That's all I've done.\n- If you have any advice, please leave a comment. That'll be really helpful for me. Thank you.","metadata":{}}]}