{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np \nimport pandas as pd\nimport os\nimport re\n\npd.set_option('display.max_rows', None)\npd.set_option('display.max_columns', None)\n\n# for dirname, _, filenames in os.walk('/kaggle/input'):\n#     for filename in filenames:\n#         print(os.path.join(dirname, filename))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# container_dir = '/kaggle/input/march-madness-analytics-2020/2020DataFiles/2020DataFiles/2020-Mens-Data/MDataFiles_Stage1'\ncontainer_dir = '/kaggle/input/march-madness-analytics-2020/MDataFiles_Stage2/'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Purpose of the notebook\nIn this notebook we are going to tranform into a __tidy format__ the next dataframes:\n 1. MRegularSeasonCompactResults\n 2. MNCAATourneyCompactResults\n 3. MSecondaryTourneyCompactResults\n 4. MRegularSeasonDetailedResults\n 5. MNCAATourneyDetailedResults\n 6. MSeasons\n 7. cities\n 8. MGameCities\n \n \n \n__Notes:__\n- There are no functions in this notebook for educational purposes. You can play around with each step and feel free to stop and print variables to get a better understanding if needed.\n- This notebook's final result is based on tidy data Hadley Wickham's definition https://en.wikipedia.org/wiki/Tidy_data ","execution_count":null},{"metadata":{},"cell_type":"markdown","source":"# Dataframes with game detail level\nIt's pretty important in a multi-dataframe modelling process take into account the level of detail in each dataframe (also known as granularity). The main idea is avoid duplicate values and keep everything in one place.","execution_count":null},{"metadata":{},"cell_type":"markdown","source":"### Working with compact data first\nThis dataframes share the same structured, so let's append and merge them.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"#loading data Compact data\nregular_compact = pd.read_csv(os.path.join(container_dir, 'MRegularSeasonCompactResults.csv'))\ntourney_compact = pd.read_csv(os.path.join(container_dir, 'MNCAATourneyCompactResults.csv'))\nsecondary_compact = pd.read_csv(os.path.join(container_dir, 'MSecondaryTourneyCompactResults.csv'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#creating identifier fields:;\nregular_compact['identifier'] = 'regular'\ntourney_compact['identifier'] = 'tourney'\nsecondary_compact['identifier'] = 'secondary'\n\n#appending compact dataframes:\nappend_compact = pd.concat([regular_compact, tourney_compact, secondary_compact])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Same process for detailed data","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"#loading detailed data:\nregular_detailed = pd.read_csv(os.path.join(container_dir, 'MRegularSeasonDetailedResults.csv'))\ntourney_detailed = pd.read_csv(os.path.join(container_dir, 'MNCAATourneyDetailedResults.csv'))\n#Note: secondaryDetailed data wasn't delivered.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#creating detailed identifier fields:\nregular_detailed['identifier'] = 'regular'\ntourney_detailed['identifier'] = 'tourney'\n\n#appending detailed dataframes:\nappend_detailed = pd.concat([regular_detailed, tourney_detailed])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Merge dataframes","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"merge_results = append_detailed.merge(append_compact, how = 'outer', on=['Season', 'DayNum', 'WTeamID', 'WScore', 'LTeamID', 'LScore', 'WLoc',\n       'NumOT', 'identifier'])\nmerge_results['game_id'] = np.arange(0, merge_results.shape[0])\nmerge_results['score_diff'] = merge_results.WScore - merge_results.LScore","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Unpivoting data\nThe main idea is try to integrate columns with the same meaning, for example, WTeamID (winner team id) and LTeamID (loser team id). Doesn't make sense keep them  separated so lets join them.\nLet's use **__melt__** to achieve this.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"# renaming columns to unpivot\nmerge_results.rename(columns = {'WTeamID':'Winner','LTeamID': 'Loser'}, inplace = True)\n\nvalue_vars = ['Winner', 'Loser']\nid_vars = [i for i in merge_results.columns if i not in value_vars]\nmelted_results = merge_results.melt(id_vars = id_vars,\n                  value_vars = value_vars,\n                  var_name = 'game_status',\n                  value_name = 'TeamID')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"melted_results.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Ok, maybe this looks a little ugly, we have some repeated registers. For example in __season__ 2003, __season day__ 10,  __teams__ 1104 and 1328 were matched. Team 1104 won, so we just need those 'Winner regiters' and drop 'Loser registers' opposite applies to team 1328 ","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"mask =  (melted_results.Season== 2003) & (melted_results.DayNum == 10) &(melted_results.TeamID == 1104)\nmelted_results.loc[mask, :]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"mask =  (melted_results.Season== 2003) & (melted_results.DayNum == 10) &(melted_results.TeamID == 1328)\nmelted_results.loc[mask, :]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"melted_cols = melted_results.columns\n\n# Columns beginning with L\nloser_mask_cols = melted_cols.str.contains('^L')\nloser_cols = melted_cols[loser_mask_cols]\n\n# Columns beginning with W living Wloc aside\nwinner_mask_cols = (melted_cols.str.contains('^W')) & (melted_cols != 'WLoc')\nwinner_cols = melted_cols[winner_mask_cols]\n\ncleaned_cols = winner_cols.str.extract(r'([^W].*)')[0].tolist()\n\nfor winner_col, loser_col, new_col in zip(winner_cols, loser_cols, cleaned_cols):\n    melted_results[new_col] = np.where(melted_results['game_status'] == 'Winner',\n                                      melted_results[winner_col],\n                                      melted_results[loser_col])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"useless_cols = list(winner_cols)+list(loser_cols)\nmelted_results.drop(useless_cols, axis=1, inplace = True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"melted_results.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Much better!","execution_count":null},{"metadata":{},"cell_type":"markdown","source":"### Fixing Wloc behavior\nWloc columns represents 'Winner's location'. It specifies if the games was played in home ('H'), no-home ('A') and neutral ('N').\nDoesn't make sense to keep filled each 'Loser' register.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"melted_results['WLoc'] = np.where(melted_results['game_status']== 'Loser' ,\n                                 np.nan,\n                                 melted_results['WLoc'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# working with seasons\nFrom this table we can estimate the exactly date for each game.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"seasons = pd.read_csv(os.path.join(container_dir, 'MSeasons.csv'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Dayzero to datetime\nseasons['DayZero'] = pd.to_datetime(seasons.DayZero)\nday_zeros = seasons.loc[:, ['Season', 'DayZero']].copy()\nresults = melted_results.merge(day_zeros, on='Season', how='inner')\n\n#adding game date:\nresults['date'] = results.DayZero + pd.to_timedelta(results.DayNum, unit='day')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# working with cities\nFrom this dataframe we can extract city and state.\n\n__first__\nwe need to merge it with game cities to obtain a __game detail level__","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"# loading cities\ncities = pd.read_csv(os.path.join(container_dir, 'Cities.csv'))\ncities['city_state'] = cities.City + '-' + cities.State\n\n#loading games cities\ngame_cities = pd.read_csv(os.path.join(container_dir, 'MGameCities.csv'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"game_cities = game_cities.merge(\n    cities,\n    on = 'CityID',\n    how = 'outer'\n)\ngame_cities.drop('CityID', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"game_cities.rename(columns = {'WTeamID':'Winner', 'LTeamID':'Loser'}, inplace=True)\nvalue_vars = ['Winner', 'Loser']\nid_vars = [i for i in game_cities.columns if i not in value_vars]\n\ngame_cities_melt = game_cities.melt(\n                    id_vars = id_vars,\n                    value_vars = value_vars,\n                    var_name = 'game_status',\n                    value_name = 'TeamID'\n                    \n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"game_cities.Season.min() #note we have registers since 2010 so there will be many missing values","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#merging results with game cities\nresults = results.merge(game_cities_melt, on=['Season', 'DayNum', 'game_status', 'TeamID'], how='outer')\nresults.columns = results.columns.str.lower()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"results.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Next step depends on your preferences so I wrote it as a function. You can get rid of that if you want.","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"def create_derived_cols(games):\n    key_days = [\n                games['daynum'].between(134, 135), games['daynum'].between(136, 137),\n                games['daynum'].between(138, 139),games['daynum'].between(143, 144),\n                games['daynum'].between(145, 146),games['daynum'] == 152,\n                games['daynum'] == 154\n\n               ]\n\n    key_meanings = [\n                'Play In', '64 to 32',\n                '32 to 16','Sweet Sixteen',\n                'Elite Eight', 'Final Four',\n                'Nacional Final'\n\n               ]\n\n    games['season_progress'] = 'regular'\n    for days, meaning in zip(key_days, key_meanings):\n        games['season_progress'] = np.where(\n            days, meaning, games['season_progress']\n    )\n\n    games['season_progress'] = np.where(\n        games.identifier == 'secondary',\n        'secondary',\n        games['season_progress']\n    )\n\n    champ_condition = (games.daynum == 154) &\\\n                        (games.game_status == 'Winner')\n    games['champion'] = np.where(champ_condition, 1, 0)\n\n    champions = games.loc[games.champion==1, ['season', 'teamid', 'champion']].set_index(['season', 'teamid'])\n    games['champion'] = games.set_index(['season', 'teamid']).index.map(champions.champion).fillna(0)\n\n    games['games_played'] = games.loc[games.identifier == 'regular'].groupby(['season', 'teamid']).cumcount() + 1\n\n    # Cumulative loses Till next victory\n    games.sort_values(['season', 'teamid', 'daynum'], inplace=True)\n    winners_cumsum = (games.game_status == 'Winner').cumsum()\n    games['cum_loses'] = games.groupby(winners_cumsum).cumcount()\n    condition = (games.game_status == 'Loser') & (games.cum_loses == 0)\n    games['cum_loses'] = np.where(condition, 1, games.cum_loses)\n\n    # Final stage achieved for current team in current season\n    games['final_stage'] = games.groupby(['season', 'teamid'])['season_progress'].transform('last')\n    games['final_stage'] = pd.Categorical(\n                                games['final_stage'],\n                                categories=['regular']+key_meanings,\n                                ordered=True\n    )\n    games['final_stage'].fillna('regular', inplace=True)\n\n    games.sort_values(['final_stage','games_played','season'], inplace=True)\n    \n    # basic measurments\n    games['off_ratio'] = games['or'] / (games['or']+games.dr)\n    games['def_ratio'] = games['dr'] / (games['or']+games.dr)\n    games['fgm_efectuation'] = games['fgm'] / games['fga']\n    games['fgm_efectuation3'] = games['fgm3'] / games['fga3']\n\n    games['score_diff'] = np.where(games.game_status == 'Loser',\n                                  games.score_diff * (-1),\n                                  games.score_diff)\n    games['lose_status'] = games.game_status == 'Loser'\n    \n    return games","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"results = create_derived_cols(results)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## tidy dataframe","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"results.sort_values('city_state').head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# If you find it useful please don't forget __upvote__ and please give me your __feedback__\n","execution_count":null}],"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":4,"nbformat_minor":1}