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.

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.

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', 'LFGM3', 'LFGA3', 'LFTM', 'LFTA', 'LOR', 'LDR', 'LAst', 'LTO', 'LStl', 'LBlk', 'LPF']

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.

Abriged data dictionary¶

In the MRegularSeasonDetailedResults table, each record is one game. All columns prefixed by 'W' refer to the winning team, and columns prefixed by 'L' refer to the losing team. We are primarily interested in the following columns:

For example, the column with the winning team's ID will be WTeamID, and the column with the losing team's free-throw attempts will be LFTA.

Exercise 1 - (3 Points):¶

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.

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. ($POS$ is # of possessions):

$POS$ = $FGA$ + 0.44($FTA$) + $TO$ - $OR$

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.

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.

The cell below will test your solution for Exercise 1. 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.

Exercise 2 - (1 Points):¶

Our next task is to compute daily totals of Pos, OppPos, PtsForPer100, and PtsAgstPer100 as well as a count of the number of teams participating in games that day.

Complete the function daily_totals(conn) to query the table PaceAdjusted (structured the same as the output of the previous exercise) in the db connection conn and return a Pandas DataFrame with the following columns:

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.

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

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.

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 $i$ in DailyTotals:

SumPos$_i$ $:=$ Sum of the Pos column for all rows where Season = Season$_i$ and DayNum $\le$ DayNum$_i$

Window functions can do this. Below are examples of this computation in Pandas and SQL. First, we will load some data to demonstrate.

The code below will return the sample data with a new column "SumPos".

The code below will return the sample data with a new column "SumPos".

The code below will print the result and verify that the two approaches give equivalent results.

Exercise 3 - (3 Points):¶

Our next task is to compute the weighted running average for Pos and Pph for all games played in a Season up to and including each DayNum. We want a snapshot of these averages as if it were that particular day and we had no knowledge of events occurring after that day.

Complete the function get_running_avg_pace(conn) to query the table DailyTotals (structured the same as the output of the previous exercise) using the db connection conn and return a Pandas DataFrame with the columns mentioned below. You should calculate the intermediate values then use division to get the end result.

Intermediate values

• SumPos - Running sum of the "Pos" column for current "Season" up to and including the current "DayNum".
• SumPph - Running sum of the "Pph" column for current "Season" up to and including the current "DayNum".
• SumCount - Running sum of the "Count" column for current "Season" up to and including the current "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.
  • See "On Window Functions..." above this exercise for more info on computing a running sum.
  • For Pandas pd.DataFrame.groupby().apply() and pd.Series.expanding() may be useful in calculating intermediate values.
  • For SQL the "window function" syntax may be useful in calculating intermediate values.
    • SUM(...) OVER(PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

The demo cell below should display the following output:

   Season  DayNum   AvgPace     AvgPPH
0    2014      12  68.17063  105.33153
1    2014      15  68.85556  105.46984
2    2014      18  68.43963  106.62453
3    2014      41  68.27426  105.76722
4    2014      44  67.81551  105.66767
5    2014      59  67.80480  105.23519
6    2014     102  67.87677  105.16634
7    2014     127  67.77059  105.08984
8    2021      29  71.56138   96.56166
9    2021      96  69.98540  101.14361

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.

Exercise 4 - (2 Points):¶

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:

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.

The demo cell below should display the following output:

   Won  Season  DayNum  TeamID  OppID  Home       Pos    OppPos  \
0    1    2014      94    1374   1396     1  -3.86907  -2.18907   
1    0    2016     119    1224   1313     0   2.65129   5.57129   
2    0    2016      90    1353   1277     0   4.05371   2.13371   
3    1    2016     119    1300   1366     1  -8.78871  -9.30871   
4    1    2014      94    1383   1384     1  -2.30907  -4.66907   
5    1    2014      94    1190   1297     0  -9.42907  -9.62907   
6    0    2014      94    1223   1101     0   0.29093   0.69093   
7    0    2014      94    1218   1364     1  -1.98907  -3.58907   
8    0    2014      92    1159   1221     1 -13.28500 -12.96500   
9    0    2014      81    1297   1252     1   1.52248   1.76248   

   PtsAgstPer100  PtsForPer100  
0      -25.32271      13.22592  
1        7.78985     -12.71682  
2       29.99420     -19.84044  
3      -31.11146      -3.56274  
4      -22.13333      14.99904  
5      -18.41211       5.59099  
6       22.95005      14.80074  
7       12.70182      -7.11914  
8       22.07960      20.97894  
9        3.17351       2.09897

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.

Exercise 5 - (3 Points):¶

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:

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)

The demo cell below should display the following output:

    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.25697   8.53697   
2     0    2012      26    1108   1360     0  5.65314   5.93314   
3     1    2012     105    1108   1115     1  2.76368   2.09702   
4     0    2012      16    1355   1208     0      NaN       NaN   
5     1    2012      31    1355   1237     0 -5.81233  -5.33233   
6     0    2012      45    1355   1315     0  5.07403   4.81403   
7     1    2012     124    1355   1237     0  4.12049   4.84049   
8     0    2012      29    1431   1332     0      NaN       NaN   
9     1    2012      52    1431   1155     0 -4.58353  -5.26353   
10    0    2012      72    1431   1409     0 -5.65785  -7.11785   
11    1    2012      79    1431   1187     1 -6.81332  -8.17332   
12    1    2012     103    1431   1408     1 -6.71588  -7.96588   
13    1    2017      47    1252   1399     0      NaN       NaN   
14    1    2017      50    1252   1122     0  9.78901  10.22901   
15    1    2017      68    1252   1244     1  7.09400   8.63400   
16    0    2017      82    1252   1316     0  6.62264   8.36930   
17    1    2017      86    1252   1367     1  7.55313   8.32313   
18    0    2017     108    1252   1316     1  6.32893   6.93693   
19    0    2017     122    1252   1316     1  6.91365   7.39365   

    PtsAgstPer100  PtsForPer100   WinPct  
0             NaN           NaN      NaN  
1        26.44115     -18.48766  0.00000  
2        21.51181     -30.28407  0.00000  
3        16.34005     -20.09929  0.00000  
4             NaN           NaN      NaN  
5        14.27057      -2.29566  0.00000  
6        13.42293       6.86026  0.50000  
7        16.60255       4.68752  0.33333  
8             NaN           NaN      NaN  
9         2.30485      -6.73367  0.00000  
10       -7.95942      -3.23226  0.50000  
11       -4.29258      -8.20323  0.33333  
12       -5.03189      -2.51522  0.50000  
13            NaN           NaN      NaN  
14       -5.62638      -2.59159  1.00000  
15        0.33659      13.16336  1.00000  
16       -0.57178      10.26290  1.00000  
17        2.37803       7.85893  0.75000  
18        2.68629       9.02187  0.80000  
19        4.37327       7.30692  0.66667

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.

Exercise 6 - (3 Points):¶

Up to this point we have calculated our metrics for each team before they play each game. We're almost ready to start modeling! There is still one last task. Remember that there were two records in PaceAdjusted for each game. This is also true of LaggedTeamStats, which is a running average of that table. If "Team A" and "Team B" played a game on in some Season on some DayNum then there will be one record where "Team A" is the primary team and "Team B" is the opponent team and one record where "Team B" is the pirmary team and "Team A" is the opponent team. We need to join these records together so that we will have one record identifying each game.

Complete the function get_matchup_stats(conn) to query the LaggedTeamStats table using the db connection conn and return a Pandas DataFrame with the following columns:

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. Pandas will give the required suffixes by default though.

Implementation Strategy

• You should start by joining/merging LaggedTeamStats to itself where the OppID in the left table is the same as the TeamID in the right table. The Season and DayNum should be the same for both tables.
• After joining/merging:
  • Remove any rows where the "left" TeamID is larger than the "left" OppID. This will avoid duplicating the data.
  • Rows occurring before DayNum 56 should not be included in the result to make sure there is a reasonable sample of games so that the stats are meaningful.
  • Drop any unnecessary columns.
  • Rename the columns as necessary to meet the requirements above.
  • Any rows containing NULL or NaN values should be dropped.

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

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.

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.

Exercise 7 - (1 Points):¶

Without advanced statistical training, these fit metrics would probably not make sense. We see that they are both somewhere in between "perfect" and "non-informative", but what is that telling us? One understandable metric we can calculate is the prediction accuracy.

Complete the function pred_accuracy(obs, preds) to calculate the relative frequency $\left (\frac{\# matches}{\# observations} \right )$ that the predicted value preds is the same as the ovserved value obs. You can assume that both will be array-like, 1-dimensional, the same length and that they only contain the integers 0 and 1. Round your result to 5 decimal places.

Note: There will be no type check. If your number matches our number you will pass.

The demo cell below should display the following output:

0.53333

The cell below will test your solution for Exercise 7. 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.

Exercise 8 - (2 Points):¶

Another way to evaluate our model is to check how accuracy of the probabilities it generates. Suppose you have a list of the observations obs (0's and 1's indicating whether the primary team wins (1) or not(0)) and a list of the predicted probability probs that the primary team won for each observation. We can bucket the predictions based on the probabilities and then calculate the accuracy of our model within that bucket by taking the mean of the observations. If the observed win % for each bucket is between or close to its bounds, we have a decent model.

Complete the function bucket_evaluation(obs, prob, n_buckets) to partition the interval $[0, 1)$ into n_buckets equal intervals and compute the observed relative win percentage for the events in that bucket. You should return your result as a Pandas DataFrame with the following columns:

Notes:

• The columns should be in the exact order given above, and the records should be sorted with the 'lower' column in ascending order and a reset index.
• The dtypes attribute of your result must match exactly.
• The bounds should be rounded before deciding which bucket each observation belongs in.
• The "obs_win_pct" column should have NaN entries for any "empty" buckets which have no observations.
  • Your solution raising a warning will not cause it to fail the test cell.

Implementation Strategy

• np.linspace() can be helpful for calculating the bounds for each bucket. Don't forget to round!
• The function zip(bounds[:-1], bounds[1:] can be iterated over to sequentially examine each pair of bounds (bounds would be the output of linspace after rounding).
  • Use each pair of bounds to filter the obs to only include observations where the prob is between the lower and upper bounds. You can calculate the observed relative frequency and count from the filtered results.
  • Keep track of the endpoints, relative frequency, and count for each interval.
• After evaluating for all intervals, construct a DataFrame using the stored results for each interval.

The demo cell below should display the following output:

   lower  upper  obs_win_pct   n
0   0.00   0.25        25.00   4
1   0.25   0.50        23.08  13
2   0.50   0.75        66.67   3
3   0.75   1.00        40.00   5

Unlike previous exercises the order and index must match (in addition to the usual requirements) to produce an easily understood result.

The cell below will test your solution for Exercise 8. 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.

Fin. If you have made it this far, congratulations on completing the semester. Don't forget to submit!