problem24 v2

Final Exam, Spring 2022: March (through May) Madness Version 1.0 All of the header information is important. Please read it.. Topics, number of exercises: This problem builds on your knowledge of Pandas, SQL, and numeric computation. It has 9 exercises, numbered 0 to 8. There are 19 available points. However, to earn 100% the threshold is 12 points. (Therefore, once you hit 12 points, you can stop. There is no extra credit for exceeding this threshold.) Exercise ordering: Each exercise builds logically on previous exercises, but you may solve them in any order. That is, if you can't solve an exercise, you can still move on and try the next one. Use this to your advantage, as the exercises are not necessarily ordered in terms of difficulty. Higher point values generally indicate more difficult exercises. Demo cells: Code cells starting with the comment ### define demo inputs load results from prior exercises applied to the entire data set and use those to build demo inputs. These must be run for subsequent demos to work properly, but they do not affect the test cells. The data loaded in these cells may be rather large (at least in terms of human readability). You are free to print or otherwise use Python to explore them, but we did not print them in the starter code. Debugging you code: Right before each exercise test cell, there is a block of text explaining the variables available to you for debugging. You may use these to test your code and can print/display them as needed (careful when printing large objects, you may want to print the head or chunks of rows at a time). Exercise point breakdown: Exercise 0: 1 point Exercise 1: 3 point Exercise 2: 1 point Exercise 3: 3 point Exercise 4: 2 point Exercise 5: 3 point Exercise 6: 3 point Exercise 7: 1 point Exercise 8: 2 point Final reminders: Submit after every exercise Review the generated grade report after you submit to see what errors were returned Stay calm, skip problems as needed, and take short breaks at your leisure Basketball basics In this notebook we want to predict which team will win a basketball game based on their past performance in a given season. You do not have to know anything about basketball aside from the background below to complete this notebook. Games. A basketball game is played between two teams. In most games, there is a "home" team and an "away" team. However sometimes games are played at a neutral site, in which case neither team is "home" nor "away." Scoring and winning. In a game, the team that scores more points wins. There are no ties. Possessions. A possession is an event where one team continuously controls the ball. In [1]: import sqlite3 import numpy as np import pandas as pd import re conn = sqlite3.connect('resource/asnlib/publicdata/basketball_db.sqlite') Exercise 0 - (1 Points):

We have a big collection of real data from several seasons of men's NCAA basketball. We are most interested in one table of this database, named MRegularSeasonDetailedResults . Complete the function get_cols(conn) to return a list of the columns in the MRegularSeasonDetailedResults table in the db connection conn . The order of the columns in the list should be the same as the order they appear in the database table. In [2]: ### Define get_cols def get_cols(conn): ### ### YOUR CODE HERE ### return pd.read_sql('''select * from MRegularSeasonDetailedResults limit 1''', conn)\ .columns\ .tolist() The demo cell below should display the following output: ['index', 'Season', 'DayNum', 'WTeamID', 'WScore', 'LTeamID', 'LScore', 'WLoc', 'NumOT', 'WFGM', 'WFGA', ' WFGM3', 'WFGA3', 'WFTM', 'WFTA', 'WOR', 'WDR', 'WAst', 'WTO', 'WStl', 'WBlk', 'WPF', 'LFGM', 'LFGA', 'LFGM 3', 'LFGA3', 'LFTM', 'LFTA', 'LOR', 'LDR', 'LAst', 'LTO', 'LStl', 'LBlk', 'LPF'] In [3]: ### call demo funtion print(get_cols(conn)) The cell below will test your solution for Exercise 0. The testing variables will be available for debugging under the following names in a dictionary format. input_vars - Input variables for your solution. original_input_vars - Copy of input variables from prior to running your solution. These should be the same as input_vars - otherwise the inputs were modified by your solution. returned_output_vars - Outputs returned by your solution. true_output_vars - The expected output. This should "match" returned_output_vars based on the question requirements - otherwise, your solution is not returning the correct output. In [4]: ### test_cell_ex0 from tester_fw.testers import Tester_ex0 tester = Tester_ex0() for _ in range(20): try : tester.run_test(get_cols) (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars() except : (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars() raise ### ### AUTOGRADER TEST - DO NOT REMOVE ### print('Passed! Please submit.') ['index', 'Season', 'DayNum', 'WTeamID', 'WScore', 'LTeamID', 'LScore', 'WLoc', 'NumOT', 'WFGM', 'WF GA', 'WFGM3', 'WFGA3', 'WFTM', 'WFTA', 'WOR', 'WDR', 'WAst', 'WTO', 'WStl', 'WBlk', 'WPF', 'LFGM', ' LFGA', 'LFGM3', 'LFGA3', 'LFTM', 'LFTA', 'LOR', 'LDR', 'LAst', 'LTO', 'LStl', 'LBlk', 'LPF'] initializing tester_fw.tester_6040 Passed! Please submit.

We want to extract some information about each team involved in a game from that team's perspective. We will use the term "primary team" to refer to this team and "primary team's opponent" to refer to the other team in the game. Do not infer any other meaning from this term. Since there are two participants in each game there will be a record from the perspective of both teams after the extraction. (i.e. one record where the winning team is the primary team and one record where the losing team is the primary team will be in the result. Complete the function get_pace_adjusted(conn) to query the table MRegularSeasonDetailedResults using the db connection conn and return a Pandas DataFrame with the columns outlined below. The "Winning Team" and "Losing Team" headers in the chart below indicate which columns in MRegularSeasonDetailedResults to use in your computations. Column Name dtype description Special Considerations Winning Team Losing Team Won int64 1 if the primary team won the game, 0 if the primary team lost 1 0 Season int64 Current Season DayNum int64 Current DayNum TeamID int64 Team id for primary team WTeamID LTeamID OppID int64 Team id for primary team's opponent LTeamID WTeamID Home int64 1 if the primary team is at home for the game, 0 otherwise. WLoc is 'H' WLoc is 'A' Pos float64 Estimated number of possessions for primary team Round to 5 decimal places W prefix L prefix OppPos float64 Estimated number of possessions for primary team's opponent Round to 5 decimal places L prefix W prefix PtsForPer100 float64 100 primary team's score estimate of primary team's possessions Round to 5 decimal places (only round after division) W prefix L prefix PtsAgstPer100 float64 100 primary team's opponent's score estimate of primary team's opponent's possessions Round to 5 decimal places (only round after division) L prefix W prefix There is no column for possessions in our source data. We will need to estimate it! The formula below is widely used in sports analytics to estimate the number of possessions a team had in a basketball game. ( is # of possessions): = + 0.44( ) + - You can derive all required results from these columns: Season, DayNum, WTeamID, WScore, LTeamID, LScore, WLoc, WFGA, WFTA, WOR, WTO, LFGA, LFTA, LOR, LTO . Notes: Each record in the database table MRegularSeasonDetailedResults will correspond to two records in the result. One record where the winning team is the primary team and one record where the losing team is the primary team. For neutral site games, neither team will be home or away. The WLoc column will have an 'N' entry. The columns should be in the exact order given above, but the records can be sorted any way you like. The dtypes attribute of your result must match exactly . This question can be answered using either SQL or Pandas. The UNION ALL phrase may be helpful if you are using SQL. The pd.concat() method may be helpful if you are using Pandas. One strategy that will work is making a table/df for all the winning teams and another for all the losing teams then vertically "stacking" them.

In [5]: ### Define get_pace_adjusted def get_pace_adjusted(conn): ### ### YOUR CODE HERE ### return pd.read_sql(''' -- winning team select 1 as Won, season, DayNum, wteamid as TeamID, lteamid as OppID, wloc='H' as Home, wFGA + 0.44*(wFTA) + wTO - wOR as Pos, lFGA + 0.44*(lFTA) + lTO - lOR as OppPos, 100 * wscore / (wFGA + 0.44*(wFTA) + wTO - wOR) as PtsForPer100, 100 * lscore / (lFGA + 0.44*(lFTA) + lTO - lOR) as PtsAgstPer100 from mregularseasondetailedresults union all -- losing team select 0 as Won, season, DayNum, lteamid as TeamID, wteamid as OppID, wloc='A' as Home, lFGA + 0.44*(lFTA) + lTO - lOR as Pos, wFGA + 0.44*(wFTA) + wTO - wOR as OppPos, 100 * lscore / (lFGA + 0.44*(lFTA) + lTO - lOR) as PtsForPer100, 100 * wscore / (wFGA + 0.44*(wFTA) + wTO - wOR) as PtsAgstPer100 from mregularseasondetailedresults ''', conn).apply( lambda f: round(f, 5)) This is the demo input: Season DayNum WTeamID WScore LTeamID LScore WLoc WFGA WFTA WOR WTO LFGA LFTA LOR LTO 0 2003 10 1104 68 1328 62 N 58 18 14 23 53 22 10 18 The demo cell below should display the following output: Won Season DayNum TeamID OppID Home Pos OppPos PtsForPer100 PtsAgstPer100 0 1 2003 10 1104 1328 0 74.92 70.68 90.76348 87.71930 1 0 2003 10 1328 1104 0 70.68 74.92 87.71930 90.76348 Note that there are two rows, but they come from a single input row. In [6]: ### define demo inputs from tester_fw.test_utils import dfs_to_conn demo_df_1 = pd.read_sql('select Season, DayNum, WTeamID, WScore, LTeamID, LScore, WLoc, WFGA, WFTA, WOR, WTO, LFG A, LFTA, LOR, LTO from MRegularSeasonDetailedResults limit 1', conn) demo_conn_1 = dfs_to_conn({'MRegularSeasonDetailedResults': demo_df_1}) # demo_df_1 In [7]: ### call demo funtion print(get_pace_adjusted(demo_conn_1)) Won Season DayNum TeamID OppID Home Pos OppPos PtsForPer100 \ 0 1 2003 10 1104 1328 0 74.92 70.68 90.76348 1 0 2003 10 1328 1104 0 70.68 74.92 87.71930 PtsAgstPer100 0 87.71930 1 90.76348

The demo cell below should display the following output: Season DayNum Pos Pph Count 0 2010 94 277.28 433.33581 4 1 2010 108 281.64 415.62023 4 2 2010 122 265.88 395.26418 4 3 2016 49 365.04 467.93858 5 4 2016 115 204.08 309.66380 3 In [10]: ### define demo inputs from tester_fw.test_utils import dfs_to_conn demo_df_2 = pd.read_sql('select * from PaceAdjustedSample', conn) demo_conn_2 = dfs_to_conn({'PaceAdjusted': demo_df_2}) # demo_df_2.sort_values(['Season', 'DayNum']).reset_index(drop=True) In [11]: ### call demo funtion print(daily_totals(demo_conn_2)) The cell below will test your solution for Exercise 2. The testing variables will be available for debugging under the following names in a dictionary format. input_vars - Input variables for your solution. original_input_vars - Copy of input variables from prior to running your solution. These should be the same as input_vars - otherwise the inputs were modified by your solution. returned_output_vars - Outputs returned by your solution. true_output_vars - The expected output. This should "match" returned_output_vars based on the question requirements - otherwise, your solution is not returning the correct output. In [12]: ### test_cell_ex2 from tester_fw.testers import Tester_ex2 tester = Tester_ex2() for _ in range(20): try : tester.run_test(daily_totals) (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars() except : (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars() raise ### ### AUTOGRADER TEST - DO NOT REMOVE ### print('Passed! Please submit.') On Window Functions... This is useful information Window functions define a slice of data relative to each row in a dataset and then perform an aggregate or order-based calculation on the slice. For example, say you want to perform the following calculation for each row in DailyTotals : SumPos Sum of the Pos column for all rows where Season = Season and DayNum DayNum Window functions can do this. Below are examples of this computation in Pandas and SQL. First, we will load some data to demonstrate. Season DayNum Pos Pph Count 0 2010 94 277.28 433.33581 4 1 2010 108 281.64 415.62023 4 2 2010 122 265.88 395.26418 4 3 2016 49 365.04 467.93858 5 4 2016 115 204.08 309.66380 3 initializing tester_fw.tester_6040 Passed! Please submit.

In [13]: # define demo data from tester_fw.test_utils import dfs_to_conn demo_conn_dfs_ex3 = {} demo_conn_dfs_ex3['DailyTotals'] = pd.read_sql('select * from DailyTotalsSample', conn) demo_conn_ex3 = dfs_to_conn(demo_conn_dfs_ex3) daily_totals_df = demo_conn_dfs_ex3['DailyTotals'] The code below will return the sample data with a new column "SumPos". In [14]: ### Pandas Example def pandas_window_helper(group): group = group.sort_values('DayNum') group['SumPos'] = group['Pos'].expanding(1).sum() return group pandas_ex = daily_totals_df.groupby('Season', as_index= False ).apply(pandas_window_helper) The code below will return the sample data with a new column "SumPos". In [15]: ### SQL Example query = ''' SELECT *, SUM(pos) OVER(PARTITION BY Season ORDER BY DayNum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Su mPos FROM DailyTotals ORDER BY Season, DayNum ''' sql_ex = pd.read_sql(query, demo_conn_ex3) The code below will print the result and verify that the two approaches give equivalent results. In [16]: display(sql_ex) print(f'''SQL and Pandas results match? { ((sql_ex.reset_index(drop=True) - pandas_ex.reset_index(drop=True)).abs() <= 0.00001).all().all()}''') Exercise 3 - (3 Points): Season DayNum Pos Pph Count SumPos 0 2014 12 8725.84 13482.43561 128 8725.84 1 2014 15 6146.96 9299.04948 88 14872.80 2 2014 18 7301.64 11764.86247 108 22174.44 3 2014 41 4452.52 6702.86892 66 26626.96 4 2014 44 5381.96 8625.92495 82 32008.92 5 2014 59 8673.96 13265.97248 128 40682.88 6 2014 102 1400.72 2062.01746 20 42083.60 7 2014 127 1696.20 2684.90264 26 43779.80 8 2021 29 4150.56 5600.57650 58 4150.56 9 2021 96 13485.76 19887.61275 194 17636.32 SQL and Pandas results match? True

In [18]: ### define demo inputs from tester_fw.test_utils import dfs_to_conn demo_conn_dfs_ex3 = {} demo_conn_dfs_ex3['DailyTotals'] = pd.read_sql('select * from DailyTotalsSample', conn) demo_conn_ex3 = dfs_to_conn(demo_conn_dfs_ex3) # demo_conn_dfs_ex3['DailyTotals'].sort_values(['Season', 'DayNum']).reset_index(drop=True) In [19]: ### call demo funtion print(get_running_avg_pace(demo_conn_ex3)) The cell below will test your solution for Exercise 3. The testing variables will be available for debugging under the following names in a dictionary format. input_vars - Input variables for your solution. original_input_vars - Copy of input variables from prior to running your solution. These should be the same as input_vars - otherwise the inputs were modified by your solution. returned_output_vars - Outputs returned by your solution. true_output_vars - The expected output. This should "match" returned_output_vars based on the question requirements - otherwise, your solution is not returning the correct output. In [20]: ### test_cell_ex3 from tester_fw.testers import Tester_ex3 tester = Tester_ex3() for _ in range(20): try : tester.run_test(get_running_avg_pace) (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars() except : (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars() raise ### ### AUTOGRADER TEST - DO NOT REMOVE ### print('Passed! Please submit.') Exercise 4 - (2 Points): Season DayNum AvgPace AvgPPH 0 2014 12 68.170625 105.331528 1 2014 15 68.855556 105.469838 2 2014 18 68.439630 106.624530 3 2014 41 68.274256 105.767222 4 2014 44 67.815508 105.667673 5 2014 59 67.804800 105.235190 6 2014 102 67.876774 105.166341 7 2014 127 67.770588 105.089836 8 2021 29 71.561379 96.561664 9 2021 96 69.985397 101.143608 initializing tester_fw.tester_6040 Passed! Please submit.

Suppose you are given two SQL tables: 1. PaceAdjusted , structured the same as the output of Exercise 1. 2. RunningAvgPace , structured the same as the output of Exercise 3. Complete the function get_center_pace_adjusted(conn) to query tables RunningAvgPace and PaceAdjusted and return a Pandas DataFrame with the following columns: Column Name dtype description Won int64 Unchanged from PaceAdjusted Season int64 Unchanged from PaceAdjusted DayNum int64 Unchanged from PaceAdjusted TeamID int64 Unchanged from PaceAdjusted OppID int64 Unchanged from PaceAdjusted Home int64 Unchanged from PaceAdjusted Pos float64 PaceAdjusted.Pos - RunningAvgPace.AvgPace for corresponding Season and DayNum OppPos float64 PaceAdjusted.OppPos - RunningAvgPace.AvgPace for corresponding Season and DayNum PtsAgstPer100 float64 PaceAdjusted.PtsAgstPer100 - RunningAvgPace.AvgPPH for corresponding Season and DayNum PtsForPer100 float64 PaceAdjusted.PtsForPer100 - RunningAvgPace.AvgPPH for corresponding Season and DayNum Notes: The columns should be in the exact order given above, but the records can be sorted any way you like. The dtypes attribute of your result must match exactly . This question can be answered using either SQL or Pandas. In [21]: ### Define get_center_pace_adjusted def get_center_pace_adjusted(conn): ### ### YOUR CODE HERE ### return pd.read_sql(''' select pa.Won, pa.Season, pa.DayNum, pa.TeamID, pa.OppID, pa.Home, pa.pos - rap.avgpace as Pos, pa.opppos - rap.avgpace as OppPos, pa.ptsagstper100 - rap.avgpph as PtsAgstPer100, pa.ptsforper100 - rap.avgpph PtsForPer100 from PaceAdjusted pa join RunningAvgPace rap on pa.season = rap.season and pa.daynum = rap.daynum ''', conn)

The cell below will test your solution for Exercise 4. The testing variables will be available for debugging under the following names in a dictionary format. input_vars - Input variables for your solution. original_input_vars - Copy of input variables from prior to running your solution. These should be the same as input_vars - otherwise the inputs were modified by your solution. returned_output_vars - Outputs returned by your solution. true_output_vars - The expected output. This should "match" returned_output_vars based on the question requirements - otherwise, your solution is not returning the correct output. In [24]: ### test_cell_ex4 from tester_fw.testers import Tester_ex4 tester = Tester_ex4() for _ in range(20): try : tester.run_test(get_center_pace_adjusted) (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars() except : (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars() raise ### ### AUTOGRADER TEST - DO NOT REMOVE ### print('Passed! Please submit.') Exercise 5 - (3 Points): initializing tester_fw.tester_6040 Passed! Please submit.

Now we have centered and pace adjusted stats for each Season, DayNum, and TeamID, it's time to compute running averages to set as inputs to our model. Since we are using these stats as inputs to a predictive model, we need to introduce a lag of 1 game so that the results of the game we are trying to predict are not part of these inputs. Complete the function get_team_stats(conn) to query the PaceAdjustedCentered (structured the same as the output of the previous exercise) table with the db connection conn and return a Pandas DataFrame with the following columns: Column Name dtype description Special Considerations Won int64 Unchanged from PaceAdjustedCentered Season int64 Current Season Unchanged from PaceAdjustedCentered DayNum int64 Current Day Unchanged from PaceAdjustedCentered TeamID int64 Current TeamID Unchanged from PaceAdjustedCentered OppID int64 Unchanged from PaceAdjustedCentered Home int64 Unchanged from PaceAdjustedCentered Pos float64 Running Average of PaceAdjustedCentered.Pos including all rows with the current Season and TeamID with PaceAdjustedCentered.DayNum < DayNum Round to 5 decimal places OppPos float64 Running Average of PaceAdjustedCentered.OppPos including all rows with the current Season and TeamID with PaceAdjustedCentered.DayNum < DayNum Round to 5 decimal places PtsAgstPer100 float64 Running Average of PaceAdjustedCentered.PtsAgstPer100 including all rows with the current Season and TeamID with PaceAdjustedCentered.DayNum < DayNum Round to 5 decimal places PtsForPer100 float64 Running Average of PaceAdjustedCentered.PtsForPer100 including all rows with the current Season and TeamID with PaceAdjustedCentered.DayNum < DayNum Round to 5 decimal places WinPct float64 Running Average of PaceAdjustedCentered.Won including all rows with the current Season and TeamID with DayNum < PaceAdjustedCentered.DayNum Round to 5 decimal places Notes: The columns should be in the exact order given above, but the records can be sorted any way you like. The dtypes attribute of your result must match exactly . This question can be answered using either SQL or Pandas. See "On Window Functions..." above Exercise 3 for more info on computing a running average. For Pandas pd.DataFrame.groupby().apply() and pd.Series.expanding() may be useful. For SQL the "window function" syntax may be useful. AVG(...) OVER(PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) In [25]: ### Define get_team_stats def get_team_stats(conn): def window_helper(agg_col, partition_cols, sort_col): return f'AVG( {agg_col} ) OVER(PARTITION BY {partition_cols} ORDER BY {sort_col} ROWS BETWEEN UNBOUNDED PRE CEDING AND 1 preceding)' return pd.read_sql(f''' select Won, Season, DayNum, TeamID, OppID, Home, {window_helper('pos', 'season, teamid', 'daynum')} Pos, {window_helper('OppPos', 'season, teamid', 'daynum')} OppPos, {window_helper('PtsAgstPer100', 'season, teamid', 'daynum')} PtsAgstPer100, {window_helper('PtsForPer100', 'season, teamid', 'daynum')} PtsForPer100, {window_helper('won', 'season, teamid', 'daynum')} WinPct from PaceAdjustedCentered ''', conn)

In [27]: ### call demo funtion print(get_team_stats(demo_conn_ex5)) The cell below will test your solution for Exercise 5. The testing variables will be available for debugging under the following names in a dictionary format. input_vars - Input variables for your solution. original_input_vars - Copy of input variables from prior to running your solution. These should be the same as input_vars - otherwise the inputs were modified by your solution. returned_output_vars - Outputs returned by your solution. true_output_vars - The expected output. This should "match" returned_output_vars based on the question requirements - otherwise, your solution is not returning the correct output. In [28]: ### test_cell_ex5 from tester_fw.testers import Tester_ex5 tester = Tester_ex5() for _ in range(20): try : tester.run_test(get_team_stats) (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars() except : (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars() raise ### ### AUTOGRADER TEST - DO NOT REMOVE ### print('Passed! Please submit.') Won Season DayNum TeamID OppID Home Pos OppPos \ 0 0 2012 17 1108 1452 0 NaN NaN 1 0 2012 25 1108 1408 0 8.256970 8.536970 2 0 2012 26 1108 1360 0 5.653140 5.933140 3 1 2012 105 1108 1115 1 2.763683 2.097017 4 0 2012 16 1355 1208 0 NaN NaN 5 1 2012 31 1355 1237 0 -5.812330 -5.332330 6 0 2012 45 1355 1315 0 5.074030 4.814030 7 1 2012 124 1355 1237 0 4.120487 4.840487 8 0 2012 29 1431 1332 0 NaN NaN 9 1 2012 52 1431 1155 0 -4.583530 -5.263530 10 0 2012 72 1431 1409 0 -5.657850 -7.117850 11 1 2012 79 1431 1187 1 -6.813323 -8.173323 12 1 2012 103 1431 1408 1 -6.715882 -7.965883 13 1 2017 47 1252 1399 0 NaN NaN 14 1 2017 50 1252 1122 0 9.789010 10.229010 15 1 2017 68 1252 1244 1 7.093995 8.633995 16 0 2017 82 1252 1316 0 6.622637 8.369303 17 1 2017 86 1252 1367 1 7.553130 8.323130 18 0 2017 108 1252 1316 1 6.328930 6.936930 19 0 2017 122 1252 1316 1 6.913647 7.393647 PtsAgstPer100 PtsForPer100 WinPct 0 NaN NaN NaN 1 26.441150 -18.487660 0.000000 2 21.511805 -30.284065 0.000000 3 16.340047 -20.099290 0.000000 4 NaN NaN NaN 5 14.270570 -2.295660 0.000000 6 13.422930 6.860260 0.500000 7 16.602553 4.687520 0.333333 8 NaN NaN NaN 9 2.304850 -6.733670 0.000000 10 -7.959420 -3.232260 0.500000 11 -4.292583 -8.203230 0.333333 12 -5.031885 -2.515215 0.500000 13 NaN NaN NaN 14 -5.626380 -2.591590 1.000000 15 0.336590 13.163360 1.000000 16 -0.571780 10.262900 1.000000 17 2.378032 7.858930 0.750000 18 2.686288 9.021872 0.800000 19 4.373267 7.306922 0.666667 initializing tester_fw.tester_6040 Passed! Please submit.

In [29]: returned_output_vars['df'].dtypes In [30]: true_output_vars['df'].dtypes Exercise 6 - (3 Points): Out[29]: Won int64 Season int64 DayNum int64 TeamID int64 OppID int64 Home int64 Pos float64 OppPos float64 PtsAgstPer100 float64 PtsForPer100 float64 WinPct float64 dtype: object Out[30]: Won int64 Season int64 DayNum int64 TeamID int64 OppID int64 Home int64 Pos float64 OppPos float64 PtsAgstPer100 float64 PtsForPer100 float64 WinPct float64 dtype: object

The demo cell below should display the following output: Won Season DayNum TeamID OppID Home_x Pos_x OppPos_x \ 2 1 2019 78 1210 1323 1 -0.90244 -1.03355 4 1 2011 72 1253 1413 1 2.31956 1.98356 PtsAgstPer100_x PtsForPer100_x WinPct_x Home_y Pos_y OppPos_y \ 2 -11.59260 -3.70151 0.55556 0 -2.65178 -2.09178 4 4.31931 -0.18928 0.46667 0 -2.84824 -2.20252 PtsAgstPer100_y PtsForPer100_y WinPct_y 2 -2.28041 6.55518 0.61111 4 3.37227 -0.80974 0.28571 In [32]: ### define demo inputs from tester_fw.test_utils import dfs_to_conn demo_conn_dfs_ex6 = {} demo_conn_dfs_ex6['LaggedTeamStats'] = pd.read_sql('select * from LaggedTeamStatsSample', conn) demo_conn_ex6 = dfs_to_conn(demo_conn_dfs_ex6) # display(pd.read_sql('select * from LaggedTeamStats', demo_conn_ex6)) In [33]: ### call demo funtion print(get_matchup_stats(demo_conn_ex6)) The cell below will test your solution for Exercise 6. The testing variables will be available for debugging under the following names in a dictionary format. input_vars - Input variables for your solution. original_input_vars - Copy of input variables from prior to running your solution. These should be the same as input_vars - otherwise the inputs were modified by your solution. returned_output_vars - Outputs returned by your solution. true_output_vars - The expected output. This should "match" returned_output_vars based on the question requirements - otherwise, your solution is not returning the correct output. In [34]: ### test_cell_ex6 from tester_fw.testers import Tester_ex6 tester = Tester_ex6() for _ in range(20): try : tester.run_test(get_matchup_stats) (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars() except : (input_vars, original_input_vars, returned_output_vars, true_output_vars) = tester.get_test_vars() raise ### ### AUTOGRADER TEST - DO NOT REMOVE ### print('Passed! Please submit.') Won Season DayNum TeamID OppID Home_x Pos_x OppPos_x \ 2 1 2019 78 1210 1323 1 -0.90244 -1.03355 4 1 2011 72 1253 1413 1 2.31956 1.98356 PtsAgstPer100_x PtsForPer100_x WinPct_x Home_y Pos_y OppPos_y \ 2 -11.59260 -3.70151 0.55556 0 -2.65178 -2.09178 4 4.31931 -0.18928 0.46667 0 -2.84824 -2.20252 PtsAgstPer100_y PtsForPer100_y WinPct_y 2 -2.28041 6.55518 0.61111 4 3.37227 -0.80974 0.28571 initializing tester_fw.tester_6040 Passed! Please submit.

Aside - Random Forest classification model Feel free to skip to the next exercise. Logistic regression relies on a few assumptions about the predictive variables which are not met here (namely normality and each one individually having a significant effect on the response variable). Instead, we will use the off the shelf Scikit-Learn modules to implement the random forest modeling technique. We're building the model for you, but you will have to do some analysis of the predictions it makes. In [35]: from sklearn.ensemble import RandomForestClassifier from sklearn.metrics import log_loss, roc_auc_score from sklearn.model_selection import GridSearchCV, train_test_split import warnings import pickle import os ### Example code showing how we got X and Y from the output of `get_matchup_stats` # stats_df = get_matchup_stats(conn) # # Horizontally partition our stats into predictive (we hope!) variables and the response variable # X = stats_df.drop(columns=['Won', 'Season', 'DayNum', 'TeamID', 'OppID']) # Remove the response and identificat ion data # y = stats_df['Won'] # response variable. There are two classes, 0 and 1, which correspond to a loss and a win f or the primary team. # load pre-computed X and y with open('resource/asnlib/publicdata/X.pkl', 'rb') as f: X = pickle.load(f) with open('resource/asnlib/publicdata/y.pkl', 'rb') as f: y = pickle.load(f) # Vertically partition data into training and test sets X_train, X_test, y_train, y_test = train_test_split( X, y, test_size=0.3, random_state=6040) # Check to see if we already saved the model if not os.path.exists('resource/asnlib/publicdata/clf.pkl'): # if not - train and save the model clf = GridSearchCV(RandomForestClassifier(), scoring='roc_auc', param_grid={ 'max_depth': [2,4,6,8,9,10] }, n_jobs=-1, verbose=2).fit(X_train,y_train) with open('resource/asnlib/publicdata/clf.pkl', 'wb') as f: pickle.dump(clf, f) # open the saved model from a pickle file with open('resource/asnlib/publicdata/clf.pkl', 'rb') as f: with warnings.catch_warnings(): warnings.simplefilter('ignore') clf = pickle.load(f) # this is how you predict the probability that the response variable belongs to each class # probs[:, 0] is the estimated probabilites of losing and probs[:, 1] is the estimated probabilities of winning. probs = clf.predict_proba(X_test) # this is how you predict which class each record belongs to preds = clf.predict(X_test) # printing some metrics about how well the model fits the test set print(f''' Log-Loss: \t\t {round(log_loss(y_test, probs),4)} - 0 is perfect and 0.693 is considered "non-informative". Area under ROC curve: \t {round(roc_auc_score(y_test, probs[:, 1]),4)} - 1 is perfect and 0.5 is considered "non-in formative". ''') Exercise 7 - (1 Points): Log-Loss: 0.5674 - 0 is perfect and 0.693 is considered "non-informative". Area under ROC curve: 0.7775 - 1 is perfect and 0.5 is considered "non-informative".