{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Pull NCAA Basketball Player Data\n- Pull data sports-reference.com and format."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import pandas as pd\nimport requests\nfrom tqdm.notebook import tqdm\npd.set_option('max_columns', 500)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"MENS_PBP_DIR = '../input/march-madness-analytics-2020/MPlayByPlay_Stage2'\n\nMPlayers = pd.read_csv(f'{MENS_PBP_DIR}/MPlayers.csv', error_bad_lines=False)\nMTeamSpelling = pd.read_csv('../input/march-madness-analytics-2020/MDataFiles_Stage2/MTeamSpellings.csv',\n                            engine='python')\nMTeams = pd.read_csv('../input/march-madness-analytics-2020/MDataFiles_Stage2/MTeams.csv')\nMPlayers = MPlayers.merge(MTeams[['TeamID','TeamName']], on='TeamID', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"MTeamSpelling['SpellingLen'] = MTeamSpelling['TeamNameSpelling'].str.len()\n# Sort team names by spelling lenghth (longer first)\nMTeamSpelling = MTeamSpelling \\\n    .sort_values('SpellingLen', ascending=True) \\\n    .sort_values('TeamID')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Determine the Team URL Names\n- Loop through each proposed team name and check if the url works.\n- Replace spaces with dash '-'\n- Store a DataFrame with the team url name"},{"metadata":{"trusted":true},"cell_type":"code","source":"SR_Team_URL_df = pd.DataFrame()\nfor d in tqdm(MTeamSpelling.itertuples(), total=len(MTeamSpelling)):\n    t = d[1]\n    t_id = d[2]\n    t_url = t.lower().replace(' ','-')\n    url = f'https://www.sports-reference.com/cbb/schools/{t_url}/'\n    SR_Team_URL_df.loc[t_url, 'TeamID'] = t_id\n    request = requests.get(url)\n    if request.status_code == 200:\n        SR_Team_URL_df.loc[t_url, 'isURL'] = True\n    else:\n        SR_Team_URL_df.loc[t_url, 'isURL'] = False","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Determine Teams where no correct name was found\n- Manually find the corrent url name for these missing teams and add them to the dataframe."},{"metadata":{"trusted":true},"cell_type":"code","source":"isMissing = SR_Team_URL_df.groupby('TeamID')['isURL'].sum() == 0\nmissingTeamIds = SR_Team_URL_df.groupby('TeamID')['isURL'].sum().loc[isMissing].index.tolist()\nprint(len(missingTeamIds))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Two Teams missing\nMTeams.loc[MTeams['TeamID'].isin(missingTeamIds)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"SR_Team_URL_df.query('TeamID == 1446')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# I can't find this school on the website.\nSR_Team_URL_df.query('TeamID == 1445')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"SR_Team_URL_df.loc['west-texas-am', 'TeamID'] = 1446\nSR_Team_URL_df.loc['west-texas-am', 'isURL'] = True","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"SR_Team_URL_df = SR_Team_URL_df.reset_index().rename(columns= {'index':'TeamNameSpelling'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"SR_Team_URL_df.to_csv('SRTeamURL.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Loop Through Teams Season 2015-2019\n- Data:\n    - Player Roster (Height, Weight, Etc info)\n    - Player Per-Game Stats (Minutes Played, Shot % etc)\n    - Team Level Stats (PPG, etc)"},{"metadata":{"trusted":true},"cell_type":"code","source":"seasons = [2015, 2016, 2017, 2018, 2019]\nteam_ids = MTeams['TeamID'].unique()\nrosters = []\nteam_stats = []\nper_game_stats = []\nworked_url_team = []\ntest_run = False\ncount = 0\nfor t_id in tqdm(team_ids, total=len(team_ids)):\n    t_name_proper = MTeams.query('TeamID == @t_id')['TeamName'].values[0]\n    try:\n        t_url = SR_Team_URL_df.query('isURL and TeamID == @t_id')['TeamNameSpelling'].values[0]\n    except:\n        t_url = '-'\n    for s in seasons:\n        try:\n            data = pd.read_html(f'https://www.sports-reference.com/cbb/schools/{t_url}/{s}.html')\n            ros = data[0]\n            team_opp = data[1]\n            per_game = data[2]\n            ros['TeamName'] = t_name_proper\n            ros['TeamID'] = t_id\n            ros['Season'] = s\n\n            team_opp['TeamName'] = t_name_proper\n            team_opp['TeamID'] = t_id\n            team_opp['Season'] = s\n\n            per_game['TeamName'] = t_name_proper\n            per_game['TeamID'] = t_id\n            per_game['Season'] = s\n\n            rosters.append(ros)\n            team_stats.append(team_opp)\n            per_game_stats.append(per_game)\n            worked_url_team.append(t)\n            print(f'Worked for {t_name_proper} {s} - using url name: {t_url}')\n        except Exception as e:\n            print(f'Broke for {t} {s}  - using url name: {t_url} - with exception: {e}')\n    count += 1\n    if test_run and count == 10:\n        break","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Combine Results and Save"},{"metadata":{},"cell_type":"markdown","source":"## Rosters"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Combine and Save\nrosters_df = pd.concat(rosters, sort=False)\nrosters_df['FirstName'] = rosters_df['Player'].str.split(' ', expand=True)[0]\nrosters_df['LastName'] = rosters_df['Player'].str.split(' ', expand=True)[1]\nrosters_df['AdditionalName'] = rosters_df['Player'].str.split(' ', expand=True)[2]\n# rosters_df = rosters_df.loc[~((rosters_df['Player'] == \"Georgi Funtarov\") & (rosters_df['Class'].isna()))]\n# rosters_df = rosters_df.loc[~((rosters_df['Player'] == \"Joseph Battle\") & (rosters_df['Class'].isna()))]\n# Order Columns\ncol = rosters_df.pop(\"AdditionalName\")\nrosters_df.insert(0, col.name, col)\ncol = rosters_df.pop(\"LastName\")\nrosters_df.insert(0, col.name, col)\ncol = rosters_df.pop(\"FirstName\")\nrosters_df.insert(0, col.name, col)\ncol = rosters_df.pop(\"TeamName\")\nrosters_df.insert(0, col.name, col)\ncol = rosters_df.pop(\"TeamID\")\nrosters_df.insert(0, col.name, col)\ncol = rosters_df.pop(\"Season\")\nrosters_df.insert(0, col.name, col)\ncol = rosters_df.pop(\"Player\")\nrosters_df.insert(0, col.name, col)\nrosters_df = rosters_df.reset_index(drop=True)\nrosters_df.to_csv('SRRosters.csv', index=False)\nrosters_df.to_parquet('SRRosters.parquet')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"rosters_df.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Team Season Stats"},{"metadata":{"trusted":true},"cell_type":"code","source":"team_df = pd.concat(team_stats, sort=False)\n# Order Columns\ncol = team_df.pop(\"Season\")\nteam_df.insert(0, col.name, col)\ncol = team_df.pop(\"TeamID\")\nteam_df.insert(0, col.name, col)\ncol = team_df.pop(\"TeamName\")\nteam_df.insert(0, col.name, col)\nteam_df = team_df.rename(columns={'Unnamed: 0': 'Team/Opp'})\nteam_df.loc[1, 'Team/Opp'] = 'Team_Rank'\nteam_df.loc[3, 'Team/Opp'] = 'Opponent_Rank'\nteam_df = team_df.reset_index(drop=True)\nteam_df.to_csv('SRTeamSeasonStats.csv', index=False)\nteam_df.to_parquet('SRTeamSeasonStats.parquet')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"team_df.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Per Game Player/Season Stats"},{"metadata":{"trusted":true},"cell_type":"code","source":"per_game_df = pd.concat(per_game_stats, sort=False)\nper_game_df['FirstName'] = per_game_df['Player'].str.split(' ', expand=True)[0]\nper_game_df['LastName'] = per_game_df['Player'].str.split(' ', expand=True)[1]\nper_game_df['AdditionalName'] = per_game_df['Player'].str.split(' ', expand=True)[2]\n# team_df = team_df.loc[~((team_df['Player'] == \"Georgi Funtarov\") & (team_df['Class'].isna()))]\n# team_df = team_df.loc[~((team_df['Player'] == \"Joseph Battle\") & (team_df['Class'].isna()))]\ncol = per_game_df.pop(\"AdditionalName\")\nper_game_df.insert(0, col.name, col)\ncol = per_game_df.pop(\"LastName\")\nper_game_df.insert(0, col.name, col)\ncol = per_game_df.pop(\"FirstName\")\nper_game_df.insert(0, col.name, col)\ncol = per_game_df.pop(\"TeamName\")\nper_game_df.insert(0, col.name, col)\ncol = per_game_df.pop(\"TeamID\")\nper_game_df.insert(0, col.name, col)\ncol = per_game_df.pop(\"Season\")\nper_game_df.insert(0, col.name, col)\ncol = per_game_df.pop(\"Player\")\nper_game_df.insert(0, col.name, col)\n\nper_game_df = per_game_df.reset_index(drop=True)\nper_game_df.to_csv('SRPerGameStats.csv', index=False)\nper_game_df.to_parquet('SRPerGameStats.parquet')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"per_game_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"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":4}