Midterm 2: Soccer player impact¶

Version 1.0.0-rc1 All of the header information is important. Please read it.

Topics number of exercises: This problem builds on your knowledge of Data selection, filtering, and aggregating with SQL; DataFrame construction and concatenation; and basic data manipulation with Pandas. It has 9 exercises numbered 0 to 8. There are 18 available points. However to earn 100% the threshold is 13 points. (Therefore once you hit 13 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.

"Run Me" cells: Code cells starting with the comment ### Run Me!!! load pre-processed objects used to further the analysis problem. Make sure to execute them so that all the demonstrations in this notebook will work properly.

Demos: Each exercise will be accompanied by a short example demonstrating expected input and output format. By their nature, these examples are simplified and will not necessarily cover all possible scenarios. In other words, it is necessary to get the correct demo result for a solution to pass, but getting the correct demo result is not sufficient for a solution to pass.

Debugging your 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 (database_schema_FREE) - : 1 point(s)
  • Exercise 1 (most_substitutions) - : 2 point(s)
  • Exercise 2 (player_appearances) - : 1 point(s)
  • Exercise 3 (running_goal_margin) - : 2 point(s)
  • Exercise 4 (substitution_score_margin) - : 3 point(s)
  • Exercise 5 (initialize_player_states) - : 2 point(s)
  • Exercise 6 (consolidate_substitutions) - : 3 point(s)
  • Exercise 7 (add_stint_times) - : 2 point(s)
  • Exercise 8 (apply_substitutions) - : 2 point(s)

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

The Problem¶

As evidenced by the 2026 FIFA World Cup, soccer is the world's most popular sport. However, it is also one of the hardest to analyze. Unlike baseball or basketball, goals are infrequent and the game rarely stops. This makes it difficult to measure how much any one player contributes to a match's final result.

The Regularized Adjusted Plus-Minus (RAPM) model is a statistical approach that tries to answer a simple question: how much does a player affect the score, after accounting for the quality of their teammates and opponents? It was first developed for basketball analytics and has since been adapted for soccer. In the final leg of this course, you will learn how to fit models like these. For now, your job is to build a data structure to make that possible.

In order to build a RAPM model, a match must be broken into segments called "stints." We will define each stint as a period of play between substitutions. For each stint, we need to know which players were on the field, which team they played for, and what the score was during that time. With enough stints across enough matches, a RAPM model can be built to estimate each player's true impact.

Your goal in this exam is to build the stint dataset from raw match data.

Your Overall Task¶

You will do the following:

  • Section 0: Data Exploration (Exercises 0-1)

    • Preview the database contents and do some quick exploratory investigations.
  • Section 1: Data Querying (Exercises 2-4)

    • Obtain records about player appearances, goals, and substitutions from a relational database. You will also compute the running goal margin across each match.
  • Section 2: Data Cleaning (Exercises 5-6)

    • Use Pandas to perform additional data cleaning and calculations on the tables you obtained with SQL. You will also use DataFrames to track the state of the match as players are substituted.
  • Section 3: Engineering The Stints (Exercises 7-8)

    • Turn the substitution data into stints, and write a function that tracks which players are on the field. We then use your function to assemble the final stint table.

Sources and Attributions¶

The data used in this exam were obtained from Fjelstul, Joshua C. "The Fjelstul World Cup Database v.1.2.0." July 19, 2023, https://www.github.com/jfjelstul/worldcup.

The project listed above has grown into https://www.worldcups.ai/.

While the site contains documentation about the dataset we will use in this exam, we do not recommend you spend much time reading their documentation. You are welcome to consult it, but we make no guarantee that their documentation of the dataset will perfectly mirror the dataset used in this exam.

In [ ]:
### CSE 6040 Utilities
import cse6040_devkit.utils as utils

### Global Imports
import sqlite3
import pandas as pd
import numpy as np
from IPython.display import display
from cse6040_devkit.utils.sql_utils import dfs_to_conn

Section 0: Data Exploration¶

As we mentioned earlier, you are going to obtain the data you need from a SQLite database. Before we go any further, let's take a look at the structure of the database.

It can be helpful to query a few rows from a table to get a sense of the nature of data it holds. Exercise 0 does this for you.

In [ ]:
### Run Me
database_schema_dfs = utils.serialization_utils.load_serialized_object('database_schema_dfs', 'preload')

Exercise 0: database_schema_FREE (1 points)¶

Example: we have defined database_schema_FREE as follows:

Returns a list of simple SELECT queries that can be used to inspect the database structure.

This is a free exercise. Submit the notebook to receive the point.

In [ ]:
### Solution - Exercise 0  
def database_schema_FREE():
    # Nothing to implement here! The solution is already provided.
    table_names = (
            'tournaments',
            'player_appearances',
            'substitutions',
            'goals',
    )

    select_queries = [
        f'SELECT * FROM {table_name} LIMIT 5'
        for table_name
        in table_names
    ]

    return select_queries


### Exercise Demo
schema_queries = database_schema_FREE()

with dfs_to_conn(database_schema_dfs) as database_schema_conn:
    for select_query in schema_queries:
        schema_df = pd.read_sql(select_query, database_schema_conn)
        display(select_query)
        display(schema_df.head())  
The test cell below will always pass. Please submit to collect your free points for `database_schema_FREE` (exercise 0).
In [ ]:
### Check Cell - Exercise 0  
In [ ]:
### BEGIN HIDDEN TESTS
### Test Cell - Exercise 0  
# This exercise is free!
# Run the cell and submit the assignment to earn your free points!

print('Passed! Please submit.')
### END HIDDEN TESTS
In [ ]:
### Run Me
most_substitutions_data_dict = utils.serialization_utils.load_serialized_object('most_substitutions_data_dict', 'preload')

Great! Now we know a bit about the data-structures we are going to use.

Eventually, we are going to need to build "stints" for matches. A stint is a section of a match, or "game," without any substitutions. We might reasonably be curious as to which teams and tournaments have historically made the most substitutions. Let's take a look.

Exercise 1: DEBUG most_substitutions (2 points)¶

Your task: define most_substitutions_query as follows:

For each combination of tournament_id and team_id, determine the average number of substitutions per match. Order results from highest to lowest.

Relevant Tables:

  • substitutions: Each row represents one substitution for a given tournament, match, and team.

Requirements:

  • Your query should produce a table with the following structure. Use these lowercase names for the output columns:
    1. tournament_id: A tournament ID contained in substitutions
    2. team_id: A team ID contained in substitutions
    3. unique_matches: A count of total distinct matches played by the team in the tournament
    4. total_substitutions: A count of total substitutions by the team in the tournament
    5. average_substitutions_per_match: A count of total substitutions, divided by the distinct matches for a given tournament and team. This value must be a floating-point number, not a rounded integer.
  • Restrict your counting to substitutions where a player is coming onto the field.
    • You can use the coming_on column to do this. A value of 1 means the player is coming onto the field.
  • Make sure you order the result in DESCending order, using the following columns:
    1. average_substitutions_per_match
    2. total_substitutions
    3. team_id

Hints:

  1. The easiest way to do this is to use a GROUP BY clause and then COUNT the appropriate values.
    • Make sure you're not double-counting duplicate entries!
  2. Integer division in SQL will produce integers, and not floating-point values! Be careful!
In [ ]:
### Solution - Exercise 1  
most_substitutions_query = """
    SELECT
    tournament_id,
    team_id,
    COUNT(DISTINCT match_id) AS unique_matches,
    COUNT(substitution_id) AS total_substitutions,
    1.0 * COUNT(substitution_id) / COUNT(DISTINCT match_id) AS average_substitutions_per_match
FROM substitutions
WHERE coming_on = 1
GROUP BY
    tournament_id,
    team_id
ORDER BY
    average_substitutions_per_match DESC,
    total_substitutions DESC,
    team_id DESC
"""


### Exercise Demo
with dfs_to_conn(most_substitutions_data_dict) as conn:
    most_substitutions_df = pd.read_sql(most_substitutions_query, conn)

display(most_substitutions_df.head(10))  

The demo should display this output:


Demo Function Call

tournament_id team_id unique_matches total_substitutions average_substitutions_per_match
0 WC-2022 T-38 3 16 5.333333
1 WC-2022 T-03 7 36 5.142857
2 WC-2022 T-47 7 35 5.000000
3 WC-2022 T-58 5 25 5.000000
4 WC-2022 T-09 5 25 5.000000
5 WC-2022 T-73 4 20 5.000000
6 WC-2022 T-44 4 20 5.000000
7 WC-2022 T-66 3 15 5.000000
8 WC-2022 T-32 3 15 5.000000
9 WC-2022 T-31 3 15 5.000000

The cell below will check your solution for most_substitutions (exercise 1).

These 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.
    • Any key:value pair in original_input_vars should also exist in 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.

Note: Unlike the inline demo in the cell where you defined your solution, this check cell is intended to be a complete test of your solution.

  • There is a hidden test cell below which executes the same check for grading.
  • Alert the course staff if the grading fails but the check passes. This is unintended behavior.
In [ ]:
### Check Cell - Exercise 1  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Check
test_name = 'most_substitutions_sampler_0'
input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=most_substitutions_query
)
if err:
    raise err


print('Check passed! Please submit.')
In [ ]:
### BEGIN HIDDEN TESTS
### Test Cell - Exercise 1  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Test
test_name = 'most_substitutions_sampler_0'

input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=most_substitutions_query)
if err:
    raise err


input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'AB-o6StwV2l-SAkN0Tzvd3jABeBcpGhEwNg8qsA8rtc=',
    func=most_substitutions_query,
    hidden=True)
if err:
    raise err

print('Passed! Please submit.')
### END HIDDEN TESTS

It looks like substitutions have been more common in recent tournaments. That's good news for us.

Now that we are familiar with the contents of the database, let's start collecting the data we need to build the match stints.

Section 1: Data Querying¶

You do not need to read the following explanation to solve the exercises, but you may find it helps you understand the context of the problem and what you are trying to achieve.

In this section, you will use SQL to obtain data you need to construct the match stint tables.

The stint table you will eventually construct will look something like this:

      data player_indicators
      minute_start minute_end score_margin player_a player_b ... player_z
tournament_id match_id stint_id              
T-AAAA-XX M-aaaa-xx S-1 0 23 0 1 -1 ... 0
S-2 23 51 2 0 -1 ... 1
S-3 51 90 1 0 0 ... 1
M-aaaa-yy S-1 0 38 -1 0 0 ... 1
S-2 38 70 -1 1 -1 ... 0
S-3 70 78 0 1 -1 ... 0
S-4 78 90 0 1 -1 ... 0
T-BBBB-YY M-bbbb-zz S-1 0 43 0 1 0 ... 0
S-2 43 62 0 1 -1 ... 1
S-3 62 90 1 0 -1 ... 1

In the structure above, we can see that each tournament has matches, and matches are split into stints. Each stint has:

  1. Some data associated with it, namely:
    • The stint duration (determined by the start and end of the stint)
    • The match score margin at the end of the stint
  2. Player indicator flags for every possible player we're interested in
    • A value of 1 indicates that a player is on the field for the home team.
    • A value of 0 indicates that a player is not on the field.
    • A value of -1 indicates that a player is on the field for the away team.

With that in mind, we can start querying the data we need. We fundamentally need to know:

  • Which players were in each game (player_appearances)
  • When players came on and off the field in each game (substitutions)
  • In a match, how many more points the home team had than the away team over time (goals)

To start, we need to know which players appeared in each match.

In [ ]:
### Run Me
player_appearances_data = utils.serialization_utils.load_serialized_object('player_appearances_data', 'preload')

Exercise 2: player_appearances (1 points)¶

Your task: define player_appearances_query as follows:

Return all rows from player_appearances where the appearance occurs after January 1, 1970.

Relevant Tables:

  • player_appearances: A table containing records of player appearances in matches.

Requirements:

  • Use the start_date column to filter the table.
  • Return all of the table columns without changing them.

Hints:

  • start_date is stored as a number of days since the Unix epoch (which is, conveniently, January 1, 1970).
In [ ]:
### Solution - Exercise 2  
player_appearances_query = """
    SELECT *
FROM player_appearances
WHERE date(start_date * 24 * 60 * 60, 'unixepoch') > date('1970-01-01')
"""


### Exercise Demo
with dfs_to_conn(player_appearances_data) as player_appearances_conn:
    player_appearances_df = pd.read_sql(player_appearances_query, player_appearances_conn)

display(player_appearances_df.head(15))  

The demo should display this output:


Demo Function Call

tournament_id match_id team_id home_team away_team player_id shirt_number position_name position_code starter substitute start_date
0 WC-1970 M-1970-01 T-46 1 0 P-07812 19 forward FW 1 0 150
1 WC-1970 M-1970-01 T-46 1 0 P-32657 18 midfielder MF 1 0 150
2 WC-1970 M-1970-01 T-46 1 0 P-38555 14 defender DF 1 0 150
3 WC-1970 M-1970-01 T-46 1 0 P-42664 5 defender DF 1 0 150
4 WC-1970 M-1970-01 T-46 1 0 P-48001 21 forward FW 1 0 150
5 WC-1970 M-1970-01 T-46 1 0 P-56971 10 forward FW 1 0 150
6 WC-1970 M-1970-01 T-46 1 0 P-64553 3 defender DF 1 0 150
7 WC-1970 M-1970-01 T-46 1 0 P-66980 1 goal keeper GK 1 0 150
8 WC-1970 M-1970-01 T-46 1 0 P-69898 6 midfielder MF 1 0 150
9 WC-1970 M-1970-01 T-46 1 0 P-85808 15 midfielder MF 1 0 150
10 WC-1970 M-1970-01 T-46 1 0 P-96177 13 defender DF 1 0 150
11 WC-1970 M-1970-01 T-46 1 0 P-96873 8 midfielder MF 0 1 150
12 WC-1970 M-1970-01 T-72 0 1 P-02102 16 forward FW 1 0 150
13 WC-1970 M-1970-01 T-72 0 1 P-17002 5 defender DF 1 0 150
14 WC-1970 M-1970-01 T-72 0 1 P-28651 9 defender DF 1 0 150

The cell below will check your solution for player_appearances (exercise 2).

These 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.
    • Any key:value pair in original_input_vars should also exist in 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.

Note: Unlike the inline demo in the cell where you defined your solution, this check cell is intended to be a complete test of your solution.

  • There is a hidden test cell below which executes the same check for grading.
  • Alert the course staff if the grading fails but the check passes. This is unintended behavior.
In [ ]:
### Check Cell - Exercise 2  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Check
test_name = 'player_appearances_sampler_0'
input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=player_appearances_query
)
if err:
    raise err


print('Check passed! Please submit.')
In [ ]:
### BEGIN HIDDEN TESTS
### Test Cell - Exercise 2  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Test
test_name = 'player_appearances_sampler_0'

input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=player_appearances_query)
if err:
    raise err


input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'AB-o6StwV2l-SAkN0Tzvd3jABeBcpGhEwNg8qsA8rtc=',
    func=player_appearances_query,
    hidden=True)
if err:
    raise err

print('Passed! Please submit.')
### END HIDDEN TESTS

Now, we need to use our database to calculate the score margin of the game over time.

You will use the goals table to do this.

In [ ]:
### Run Me
running_goal_margin_data_dict = utils.serialization_utils.load_serialized_object('running_goal_margin_data_dict', 'preload')

Exercise 3: running_goal_margin (2 points)¶

Your task: define running_goal_margin_query as follows:

For each goal in the goals table, compute the cumulative score margin up to and including that goal for a given match.

Relevant Tables:

  • goals: Each row represents one goal scored in a match.

Requirements:

  • Your query should produce a table with the following structure. Use these lowercase names for the output columns:
    1. tournament_id: The tournament in which the goal was scored.
    2. match_id: The match in which the goal was scored.
    3. minute_regulation: The minute in regulation time at which the goal was scored.
    4. minute_stoppage: The minute in "overtime" at which the goal was scored.
    5. goal_id: A unique identifier for the goal.
    6. score_margin: The cumulative score margin at the time of the goal, from the home team's perspective. A positive value means the home team is ahead.
  • Order results by tournament_id, match_id, minute_regulation, minute_stoppage, and goal_id.

Hints:

  1. You may find it easiest to use a window function to calculate score_margin.
    • A statement like SUM(...) OVER (...) AS score_margin can be used to calculate the running total.
    • Each goal contributes +1 or -1 to the home team's margin depending on who scored. Scores by the home team add +1, while scores from the away team add -1.
    • If a team scores against itself, reverse the scoring team's contribution. Use own_goal to determine the direction (a value of 1 for own_goal means a player scored against their own team).
  2. Alternatively, you could use JOIN and GROUP BY logic to calculate the solution.
    • This will require you to think carefully about how to join your tables.
    • One approach is to join the goals table against itself and only keep the records where the minute_regulation and minute_stoppage in one table is less than or equal to those fields in the other table (why might this be a sensible thing to do?)
In [ ]:
### Solution - Exercise 3  
running_goal_margin_query = """
    SELECT
    tournament_id,
    match_id,
    minute_regulation,
    minute_stoppage,
    goal_id,
    SUM((HOME_TEAM - AWAY_TEAM) * CASE WHEN own_goal THEN -1 ELSE 1 END)
        OVER (PARTITION BY MATCH_ID ORDER BY minute_regulation, minute_stoppage)
        AS score_margin
    FROM goals
    ORDER BY
        tournament_id,
        match_id,
        minute_regulation,
        minute_stoppage,
        goal_id
"""


### Exercise Demo
with dfs_to_conn(running_goal_margin_data_dict) as running_goal_margins_conn:
    running_goal_margin_df = pd.read_sql(running_goal_margin_query, running_goal_margins_conn)

display(running_goal_margin_df[running_goal_margin_df['match_id'].isin(['M-1978-04', 'M-2003-29'])])  

The demo should display this output:


Demo Function Call

tournament_id match_id minute_regulation minute_stoppage goal_id score_margin
955 WC-1978 M-1978-04 9 0 G-0956 -1
956 WC-1978 M-1978-04 14 0 G-0957 0
957 WC-1978 M-1978-04 83 0 G-0958 1
2331 WC-2003 M-2003-29 15 0 G-2332 -1
2332 WC-2003 M-2003-29 90 1 G-2333 -2
2333 WC-2003 M-2003-29 90 3 G-2334 -3

The cell below will check your solution for running_goal_margin (exercise 3).

These 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.
    • Any key:value pair in original_input_vars should also exist in 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.

Note: Unlike the inline demo in the cell where you defined your solution, this check cell is intended to be a complete test of your solution.

  • There is a hidden test cell below which executes the same check for grading.
  • Alert the course staff if the grading fails but the check passes. This is unintended behavior.
In [ ]:
### Check Cell - Exercise 3  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Check
test_name = 'running_goal_margin_sampler_0'
input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=running_goal_margin_query
)
if err:
    raise err


print('Check passed! Please submit.')
In [ ]:
### BEGIN HIDDEN TESTS
### Test Cell - Exercise 3  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Test
test_name = 'running_goal_margin_sampler_0'

input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=running_goal_margin_query)
if err:
    raise err


input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'AB-o6StwV2l-SAkN0Tzvd3jABeBcpGhEwNg8qsA8rtc=',
    func=running_goal_margin_query,
    hidden=True)
if err:
    raise err

print('Passed! Please submit.')
### END HIDDEN TESTS

Now that we know the running score margin for each goal scored in a match, we can use that information to determine the score margin when each substitution occurs.

In [ ]:
### Run Me
substitution_score_margin_data_dict = utils.serialization_utils.load_serialized_object('substitution_score_margin_data_dict', 'preload')

Exercise 4: DEBUG substitution_score_margin (3 points)¶

Your task: define substitution_score_margin_query as follows:

For each substitution in the substitutions table, determine the score margin at the time of the substitution.

Relevant Tables:

  • substitutions: Each row represents one substitution event.
  • goal_margins: Each row represents one goal, and includes the cumulative score_margin up to that goal (as calculated in Exercise 3).

Requirements:

  • Your query should produce one row per substitution, with all columns from substitutions except for shirt_number. Also include the following:
    1. most_recent_goal_minute: The minute of the most recent goal at or before the substitution.
    2. home_team_goal: The home_team column from goal_margins for that goal.
    3. score_margin: The cumulative score margin at the time of that goal.
  • Substitutions which happened before any goals were scored should be kept. The score margin for those instances should equal 0.

Hints: We recommend the following approach:

  1. Join substitutions to goal_margins on matching tournament_id and match_id, and where the goal's minute_regulation is at or before the substitution's minute_regulation.
    • For each substitution, this collects every goal that could have happened before it.
  2. After the join, each substitution may match multiple goals. Use the ROW_NUMBER window function to rank the matched goals by recency, then keep only the most recent one.
    • Partition by substitution_id so that ranking is independent per substitution.
    • Order by minute_regulation and goal_id, in descending order, to break ties.
  3. Then, filter the results to only the most recent goal using the goal_priority field (a priority of 1 means it's most recent!)
In [ ]:
### Solution - Exercise 4  
substitution_score_margin_query = """
    WITH collated_substitutions AS (
    SELECT
        s.*,
        gm.MINUTE_REGULATION AS most_recent_goal_minute,
        gm.home_team AS home_team_goal,
        ROW_NUMBER() OVER (PARTITION BY s.substitution_id ORDER BY gm.minute_regulation DESC, gm.goal_id DESC) AS goal_priority,
        gm.score_margin AS score_margin
    FROM substitutions s
    LEFT JOIN goal_margins gm
        ON s.tournament_id = gm.tournament_id
        AND s.match_id = gm.match_id
        AND s.minute_regulation >= gm.minute_regulation
)
SELECT
    substitution_id,
    tournament_id,
    match_id,
    team_id,
    home_team,
    away_team,
    player_id,
    minute_label,
    minute_regulation,
    minute_stoppage,
    match_period,
    going_off,
    coming_on,
    most_recent_goal_minute,
    home_team_goal,
    IFNULL(score_margin, 0) AS score_margin
FROM collated_substitutions
WHERE goal_priority = 1
"""


### Exercise Demo
with dfs_to_conn(substitution_score_margin_data_dict) as substitution_score_margin_conn:
    substitution_score_margin_df = pd.read_sql(substitution_score_margin_query, substitution_score_margin_conn)

display(substitution_score_margin_df.head(10))  

The demo should display this output:


Demo Function Call

substitution_id tournament_id match_id team_id home_team away_team player_id minute_label minute_regulation minute_stoppage match_period going_off coming_on most_recent_goal_minute home_team_goal score_margin
0 S-0001 WC-1970 M-1970-01 T-72 0 1 P-60181 46' 46 0 second half 1 0 NaN NaN 0
1 S-0002 WC-1970 M-1970-01 T-72 0 1 P-59828 46' 46 0 second half 0 1 NaN NaN 0
2 S-0003 WC-1970 M-1970-01 T-72 0 1 P-43733 66' 66 0 second half 1 0 NaN NaN 0
3 S-0004 WC-1970 M-1970-01 T-72 0 1 P-98262 66' 66 0 second half 0 1 NaN NaN 0
4 S-0005 WC-1970 M-1970-01 T-46 1 0 P-32657 67' 67 0 second half 1 0 NaN NaN 0
5 S-0006 WC-1970 M-1970-01 T-46 1 0 P-96873 67' 67 0 second half 0 1 NaN NaN 0
6 S-0007 WC-1970 M-1970-02 T-84 1 0 P-47892 12' 12 0 first half 1 0 NaN NaN 0
7 S-0008 WC-1970 M-1970-02 T-84 1 0 P-82186 12' 12 0 first half 0 1 NaN NaN 0
8 S-0009 WC-1970 M-1970-02 T-40 0 1 P-86314 46' 46 0 second half 1 0 23.0 1.0 1
9 S-0010 WC-1970 M-1970-02 T-40 0 1 P-08449 46' 46 0 second half 0 1 23.0 1.0 1

The cell below will check your solution for substitution_score_margin (exercise 4).

These 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.
    • Any key:value pair in original_input_vars should also exist in 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.

Note: Unlike the inline demo in the cell where you defined your solution, this check cell is intended to be a complete test of your solution.

  • There is a hidden test cell below which executes the same check for grading.
  • Alert the course staff if the grading fails but the check passes. This is unintended behavior.
In [ ]:
### Check Cell - Exercise 4  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Check
test_name = 'substitution_score_margin_sampler_0'
input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=substitution_score_margin_query
)
if err:
    raise err


print('Check passed! Please submit.')
In [ ]:
### BEGIN HIDDEN TESTS
### Test Cell - Exercise 4  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Test
test_name = 'substitution_score_margin_sampler_0'

input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=substitution_score_margin_query)
if err:
    raise err


input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'AB-o6StwV2l-SAkN0Tzvd3jABeBcpGhEwNg8qsA8rtc=',
    func=substitution_score_margin_query,
    hidden=True)
if err:
    raise err

print('Passed! Please submit.')
### END HIDDEN TESTS

That should be everything we need from the database.

In the next sections, you will use Python to clean and organize the data you queried to build the stint data.

Section 2: Data Cleaning¶

At this point, we have extracted all of the information we need to build the stints out of the database. In section 3, we will use this information to create the actual stint table. But first, we need to do a bit of clean up on the data we have.

First, we need more meaningful indicators of who is on the field over the course of the game. A RAPM model uses a ternary variable (a variable that can take on three possible values) to represent a player's status during a match:

  • A value of 1 indicates that a player is on the field for the home team.
  • A value of 0 indicates that a player is not on the field.
  • A value of -1 indicates that a player is on the field for the away team.

Later, you will determine the values of this variable for each stint. For now, we need to know what these values should be at the start of the match.

In [ ]:
### Run Me
eligible_appearances = utils.serialization_utils.load_serialized_object('eligible_appearances', 'preload')

Exercise 5: initialize_player_states (2 points)¶

Your task: define initialize_player_states as follows:

For a given match, create new columns in a copy of the DataFrame to indicate the status of each player at the start of the game.

Args:

  • player_appearances_df (pd.DataFrame): A DataFrame of player appearances across all matches.
  • match_id (str): The ID of the match to initialize.

Returns:

  • A copy of player_appearances_df as defined below.

Requirements:

  • Do not modify the input DataFrame.
  • Filter player_appearances_df to only rows matching match_id.
  • Compute the following additional columns:
    • team_flag: computed so the values equal 1 for home team players and -1 for away team players.
    • field_state: computed so that the value equals team_flag when a player starts on the field, and equal to 0 when they do not start on the field.
  • Sort the result by player_id.

Hints:

  1. home_team and away_team are binary columns. Only one of them will be 1 for a given player.
  2. starter is also binary. Multiplying team_flag by starter is a compact way to zero out players who are not yet on the field.
In [ ]:
### Solution - Exercise 5  
def initialize_player_states(player_appearances_df, match_id):
    ### BEGIN SOLUTION
    players = player_appearances_df[player_appearances_df['match_id'] == match_id].copy()
    players['team_flag'] = players['home_team'] - players['away_team']
    players['field_state'] = players['team_flag'] * players['starter']
    return players.sort_values('player_id').reset_index(drop=True)
    ### END SOLUTION


### Exercise Demo
match_id = eligible_appearances['match_id'].iloc[0]
player_states_df = initialize_player_states(eligible_appearances, match_id)
display(player_states_df.head(10))  

The demo should display this output:


Demo Function Call

tournament_id match_id team_id home_team away_team player_id shirt_number position_name position_code starter substitute start_date team_flag field_state
0 WC-1970 M-1970-01 T-72 0 1 P-02102 16 forward FW 1 0 150 -1 -1
1 WC-1970 M-1970-01 T-46 1 0 P-07812 19 forward FW 1 0 150 1 1
2 WC-1970 M-1970-01 T-72 0 1 P-17002 5 defender DF 1 0 150 -1 -1
3 WC-1970 M-1970-01 T-72 0 1 P-28651 9 defender DF 1 0 150 -1 -1
4 WC-1970 M-1970-01 T-46 1 0 P-32657 18 midfielder MF 1 0 150 1 1
5 WC-1970 M-1970-01 T-72 0 1 P-33189 11 midfielder MF 1 0 150 -1 -1
6 WC-1970 M-1970-01 T-46 1 0 P-38555 14 defender DF 1 0 150 1 1
7 WC-1970 M-1970-01 T-46 1 0 P-42664 5 defender DF 1 0 150 1 1
8 WC-1970 M-1970-01 T-72 0 1 P-43733 19 forward FW 1 0 150 -1 -1
9 WC-1970 M-1970-01 T-46 1 0 P-48001 21 forward FW 1 0 150 1 1

The cell below will check your solution for initialize_player_states (exercise 5).

These 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.
    • Any key:value pair in original_input_vars should also exist in 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.

Note: Unlike the inline demo in the cell where you defined your solution, this check cell is intended to be a complete test of your solution.

  • There is a hidden test cell below which executes the same check for grading.
  • Alert the course staff if the grading fails but the check passes. This is unintended behavior.
In [ ]:
### Check Cell - Exercise 5  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Check
test_name = 'initialize_player_states_sampler_0'
input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=initialize_player_states
)
if err:
    raise err


print('Check passed! Please submit.')
In [ ]:
### BEGIN HIDDEN TESTS
### Test Cell - Exercise 5  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Test
test_name = 'initialize_player_states_sampler_0'

input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=initialize_player_states)
if err:
    raise err


input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'AB-o6StwV2l-SAkN0Tzvd3jABeBcpGhEwNg8qsA8rtc=',
    func=initialize_player_states,
    hidden=True)
if err:
    raise err

print('Passed! Please submit.')
### END HIDDEN TESTS

The substitutions table isn't quite what you need yet. It has one row each time a player comes onto the field or leaves it, and teams often change several players at once. Instead, we will consolidate the substitution records by combining rows so that each moment of change gets one row.

This will make it easier for us to use our substitution records to turn a match into stints.

In [ ]:
### Run Me
filled_substitutions = utils.serialization_utils.load_serialized_object('filled_substitutions', 'preload')

Exercise 6: consolidate_substitutions (3 points)¶

Your task: define consolidate_substitutions as follows:

Create a table with one row for each moment in a match when players were substituted.

Args:

  • substitutions_df (pd.DataFrame): One row per substitution record. It includes the tournament_id, match_id, minute_regulation, and score_margin columns from the previous exercises.

Returns:

  • unique_substitutions_df (pd.DataFrame): One row for each unique substitution moment, as described below.

Requirements:

  • Don't modify substitutions_df. Return a new DataFrame.
  • Each row must match a unique combination of tournament_id, match_id, minute_regulation, and score_margin. We provide substitution_keys in the starter code.
    • substitutions_df has several rows for each combination, because it records each player who leaves the field and each player who comes on separately. Combine them into one row.
  • Rename minute_regulation to end_minute. Each substitution ends one stint.
  • Add a stint_id column. It counts the substitutions in each match, starting at 1. For example, the second substitution in a match has a stint_id of 2.
  • Return only these columns, in this order: tournament_id, match_id, stint_id, end_minute, score_margin.
  • Sort the result by tournament_id, match_id, and end_minute.

Hints:

  1. drop_duplicates() removes repeated rows.
  2. Sort the rows before you number them. The stints must be in the order they happened.
  3. Number the rows within each match, starting at 1 for the first substitution.
In [ ]:
### Solution - Exercise 6  
def consolidate_substitutions(substitutions_df):
    substitution_keys = ['tournament_id', 'match_id', 'minute_regulation', 'score_margin']

    ### BEGIN SOLUTION
    unique_substitutions = (
        substitutions_df[substitution_keys]
        .drop_duplicates()
        .sort_values(substitution_keys)
        .reset_index(drop=True)
    )
    unique_substitutions['stint_id'] = unique_substitutions.groupby(['tournament_id', 'match_id']).cumcount() + 1
    unique_substitutions = unique_substitutions.rename(columns={'minute_regulation': 'end_minute'})

    return unique_substitutions[['tournament_id', 'match_id', 'stint_id', 'end_minute', 'score_margin']]
    ### END SOLUTION


### Exercise Demo
demo_substitutions = filled_substitutions[filled_substitutions['match_id'] == 'M-1970-04']
result = consolidate_substitutions(demo_substitutions)
display(result.head(10))  

The demo should display this output:


Demo Function Call

tournament_id match_id stint_id end_minute score_margin
0 WC-1970 M-1970-04 1 29 -1
1 WC-1970 M-1970-04 2 51 -1
2 WC-1970 M-1970-04 3 59 0
3 WC-1970 M-1970-04 4 73 1

The cell below will check your solution for consolidate_substitutions (exercise 6).

These 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.
    • Any key:value pair in original_input_vars should also exist in 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.

Note: Unlike the inline demo in the cell where you defined your solution, this check cell is intended to be a complete test of your solution.

  • There is a hidden test cell below which executes the same check for grading.
  • Alert the course staff if the grading fails but the check passes. This is unintended behavior.
In [ ]:
### Check Cell - Exercise 6  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Check
test_name = 'consolidate_substitutions_sampler_0'
input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=consolidate_substitutions
)
if err:
    raise err


print('Check passed! Please submit.')
In [ ]:
### BEGIN HIDDEN TESTS
### Test Cell - Exercise 6  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Test
test_name = 'consolidate_substitutions_sampler_0'

input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=consolidate_substitutions)
if err:
    raise err


input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'AB-o6StwV2l-SAkN0Tzvd3jABeBcpGhEwNg8qsA8rtc=',
    func=consolidate_substitutions,
    hidden=True)
if err:
    raise err

print('Passed! Please submit.')
### END HIDDEN TESTS

At this point, you have the data you need to build the stints. We will complete this in section 3.

In [ ]:
### Run Me
consolidated_substitutions_df = utils.serialization_utils.load_serialized_object('consolidated_substitutions_df', 'preload')

Section 3: Engineering The Stints¶

A stint is a period of play between two substitutions. Each row in the substitution table now marks the end of a stint.

In this section, you will:

  1. Turn the substitution records into a table of stints (Exercise 7).
  2. Track which players are on the field during each stint (Exercise 8).
  3. Put everything together into the final stint table.

Next, you will work out when each stint started.

Exercise 7: DEBUG add_stint_times (2 points)¶

Your task: define add_stint_times as follows:

Add the start time and the duration of each stint.

A stint ends at the minute of a substitution, and the next stint starts at that same minute. This means you can find the start of each stint by looking at the stint before it.

Args:

  • stints_df (pd.DataFrame): The output of Exercise 6. It has one row for each stint, with the columns tournament_id, match_id, stint_id, end_minute, and score_margin.

Returns:

  • A copy of stints_df with two new columns, start_minute and duration_minutes.

Requirements:

  • Don't modify stints_df.
  • start_minute is the end_minute of the previous stint in the same match. The first stint of each match starts at minute 0.
  • duration_minutes is end_minute minus start_minute.
  • Keep all of the original columns, and keep their original data types.
  • start_minute and duration_minutes must be floating-point numbers, like in the demo output.
  • Sort the result by tournament_id, match_id, and stint_id.

Hints:

  1. Sort the rows first, so that each stint comes right after the previous stint in the same match.
  2. The first stint of each match has no stint before it, so it has no start minute yet.
In [ ]:
### Solution - Exercise 7  
def add_stint_times(stints_df):
    ### BEGIN SOLUTION
    result = stints_df.sort_values(['tournament_id', 'match_id', 'stint_id']).reset_index(drop=True)
    result['start_minute'] = (
        result
        .groupby(['tournament_id', 'match_id'])['end_minute']
        .shift()
        .fillna(0)
    )
    result['duration_minutes'] = result['end_minute'] - result['start_minute']
    return result
    ### END SOLUTION


### Exercise Demo
stint_times_demo_df = add_stint_times(
    consolidated_substitutions_df[consolidated_substitutions_df['match_id'] == 'M-1970-04']
)
display(stint_times_demo_df)  

The demo should display this output:


Demo Function Call

tournament_id match_id stint_id end_minute score_margin start_minute duration_minutes
0 WC-1970 M-1970-04 1 29 -1 0.0 29.0
1 WC-1970 M-1970-04 2 51 -1 29.0 22.0
2 WC-1970 M-1970-04 3 59 0 51.0 8.0
3 WC-1970 M-1970-04 4 73 1 59.0 14.0

The cell below will check your solution for add_stint_times (exercise 7).

These 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.
    • Any key:value pair in original_input_vars should also exist in 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.

Note: Unlike the inline demo in the cell where you defined your solution, this check cell is intended to be a complete test of your solution.

  • There is a hidden test cell below which executes the same check for grading.
  • Alert the course staff if the grading fails but the check passes. This is unintended behavior.
In [ ]:
### Check Cell - Exercise 7  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Check
test_name = 'add_stint_times_sampler_0'
input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=add_stint_times
)
if err:
    raise err


print('Check passed! Please submit.')
In [ ]:
### BEGIN HIDDEN TESTS
### Test Cell - Exercise 7  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Test
test_name = 'add_stint_times_sampler_0'

input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=add_stint_times)
if err:
    raise err


input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'AB-o6StwV2l-SAkN0Tzvd3jABeBcpGhEwNg8qsA8rtc=',
    func=add_stint_times,
    hidden=True)
if err:
    raise err

print('Passed! Please submit.')
### END HIDDEN TESTS
In [ ]:
### Run Me
stint_times_df = utils.serialization_utils.load_serialized_object('stint_times_df', 'preload')
match_df = utils.serialization_utils.load_serialized_object('match_df', 'preload')
player_states_df = utils.serialization_utils.load_serialized_object('player_states_df', 'preload')
substitutions_df = utils.serialization_utils.load_serialized_object('substitutions_df', 'preload')

Every stint so far ends with a substitution. But the last stint of a match ends at the final whistle, so it doesn't have a row yet. Matches with no substitutions don't have any rows at all.

You don't need to write any code for this step. Read the code in the next cell, and then run it. It adds the last stint of every match:

  • The last stint starts at the minute of the last substitution. If the match had no substitutions, it starts at minute 0.
  • The last stint ends at minute 90. If the last substitution happened after minute 90, the stint ends at that minute instead.
  • The score margin comes from match_df, which has the final score margin of every match.
  • The stint_id is one more than the previous stint.

The cell shows the stints of two matches so you can see the result.

In [ ]:
### Provided code: add the last stint of every match
last_stints = (
    stint_times_df
    .sort_values('stint_id')
    .groupby(['tournament_id', 'match_id'])
    .tail(1)
    [['tournament_id', 'match_id', 'stint_id', 'end_minute']]
    .rename(columns={'stint_id': 'last_stint_id', 'end_minute': 'start_minute'})
)

final_stints = match_df.merge(last_stints, on=['tournament_id', 'match_id'], how='left')
final_stints['stint_id'] = final_stints['last_stint_id'].fillna(0).astype(int) + 1
final_stints['start_minute'] = final_stints['start_minute'].fillna(0)
final_stints['end_minute'] = final_stints['start_minute'].clip(lower=90)
final_stints['duration_minutes'] = final_stints['end_minute'] - final_stints['start_minute']

stints_df = (
    pd.concat([stint_times_df, final_stints[stint_times_df.columns]], ignore_index=True)
    .sort_values(['tournament_id', 'match_id', 'stint_id'], ignore_index=True)
)

display(stints_df[stints_df['match_id'].isin(['M-1970-04', 'M-1974-01'])])

You now have a table with every stint of every match. Next, you need to know who is on the field during each stint.

Remember the field state from Exercise 5:

  • 1 means the player is on the field for the home team.
  • -1 means the player is on the field for the away team.
  • 0 means the player is not on the field.

Exercise 5 gave you the field state at the start of a match. Every time a substitution happens, the field state changes. Players who leave the field change to 0. Players who come onto the field change to 1 or -1, depending on their team.

In Exercise 8, you write the function that makes these changes for one substitution moment. After the exercise, we use your function to go through every stint.

Exercise 8: apply_substitutions (2 points)¶

Your task: define apply_substitutions as follows:

Update the field state of the players in a match after one substitution moment.

Args:

  • player_states_df (pd.DataFrame): The players of one match, with the team_flag and field_state columns from Exercise 5. field_state shows who is on the field before the substitutions.
  • substitutions_df (pd.DataFrame): The substitution records for one moment of the match. This DataFrame can be empty.

Returns:

  • A copy of player_states_df with the updated field_state values.

Requirements:

  • Don't modify the inputs.
  • If a player has going_off equal to 1 in substitutions_df, set their field_state to 0.
  • If a player has coming_on equal to 1 in substitutions_df, set their field_state to their team_flag.
  • Don't change any other players, rows, or columns.
  • If substitutions_df is empty, return an unchanged copy of player_states_df.

Hints:

  1. player_states_df['player_id'].isin(some_ids) is True for each player whose ID is in some_ids.
  2. df.loc[mask, 'field_state'] = value changes field_state only in the rows where mask is True. The value can be a Series. Pandas matches it to the rows by index.
In [ ]:
### Solution - Exercise 8  
def apply_substitutions(player_states_df, substitutions_df):
    ### BEGIN SOLUTION
    result = player_states_df.copy()
    going_off = result['player_id'].isin(substitutions_df.loc[substitutions_df['going_off'] == 1, 'player_id'])
    coming_on = result['player_id'].isin(substitutions_df.loc[substitutions_df['coming_on'] == 1, 'player_id'])
    result.loc[going_off, 'field_state'] = 0
    result.loc[coming_on, 'field_state'] = result.loc[coming_on, 'team_flag']
    return result
    ### END SOLUTION


### Exercise Demo
demo_players = player_states_df[player_states_df['match_id'] == 'M-1970-04']
demo_substitutions = substitutions_df[substitutions_df['match_id'] == 'M-1970-04']
first_minute = demo_substitutions['minute_regulation'].min()
demo_substitutions = demo_substitutions[demo_substitutions['minute_regulation'] == first_minute]

demo_result_df = apply_substitutions(demo_players, demo_substitutions)
demo_result_df = demo_result_df[demo_result_df['player_id'].isin(demo_substitutions['player_id'])]
demo_result_df = demo_result_df.reindex(columns=['player_id', 'team_flag', 'starter', 'field_state'])
display(demo_result_df)  

The demo should display this output:


Demo Function Call

player_id team_flag starter field_state
91 P-30953 1 1 0
93 P-38889 1 0 1

The cell below will check your solution for apply_substitutions (exercise 8).

These 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.
    • Any key:value pair in original_input_vars should also exist in 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.

Note: Unlike the inline demo in the cell where you defined your solution, this check cell is intended to be a complete test of your solution.

  • There is a hidden test cell below which executes the same check for grading.
  • Alert the course staff if the grading fails but the check passes. This is unintended behavior.
In [ ]:
### Check Cell - Exercise 8  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Check
test_name = 'apply_substitutions_sampler_0'
input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=apply_substitutions
)
if err:
    raise err


print('Check passed! Please submit.')
In [ ]:
### BEGIN HIDDEN TESTS
### Test Cell - Exercise 8  
from cse6040_devkit.grading.test_cell import execute_test

# Execute Test
test_name = 'apply_substitutions_sampler_0'

input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'C-fQMa8FaNt8rWp2c6VwedVELHWXxBuxJeskUz2wVUk=',
    func=apply_substitutions)
if err:
    raise err


input_vars, original_input_vars, returned_output_vars, true_output_vars, err = \
execute_test(
    test_name=test_name,
    key=b'AB-o6StwV2l-SAkN0Tzvd3jABeBcpGhEwNg8qsA8rtc=',
    func=apply_substitutions,
    hidden=True)
if err:
    raise err

print('Passed! Please submit.')
### END HIDDEN TESTS

Putting it all together¶

You now have every piece you need. You don't need to write any more code. The cells below use your apply_substitutions function to build the final data.

For each match, the code goes through the stints in order. For each stint, it applies the substitutions that happened when the stint started. Then it records who is on the field. The result is a table with one row for each match, stint, and player.

In [ ]:
### Provided code: record who is on the field in every stint
panels = []

for match_id, match_stints in stints_df.groupby('match_id'):
    player_states = player_states_df[player_states_df['match_id'] == match_id]
    match_substitutions = substitutions_df[substitutions_df['match_id'] == match_id]

    for stint in match_stints.sort_values('stint_id').itertuples():
        moment = match_substitutions[match_substitutions['minute_regulation'] == stint.start_minute]
        player_states = apply_substitutions(player_states, moment)

        panel = player_states[['player_id', 'field_state']].copy()
        panel.insert(0, 'stint_id', stint.stint_id)
        panel.insert(0, 'match_id', match_id)
        panels.append(panel)

player_panel_df = pd.concat(panels, ignore_index=True)
display(player_panel_df.head(10))

Finally, we reshape this table into the stint table from the start of the exam. Each row is a stint. The columns hold the stint data, followed by one column for every player.

The full table is very large, because it has a column for every player in World Cup history. The next cell builds the table for the 2022 tournament only.

In [ ]:
### Provided code: the stint table for one tournament
tournament_stints = stints_df[stints_df['tournament_id'] == 'WC-2022']
tournament_panel = player_panel_df[player_panel_df['match_id'].isin(tournament_stints['match_id'])]

stint_data = (
    tournament_stints
    .set_index(['match_id', 'stint_id'])
    [['start_minute', 'end_minute', 'score_margin']]
)

player_indicators = (
    tournament_panel
    .pivot(index=['match_id', 'stint_id'], columns='player_id', values='field_state')
    .fillna(0)
    .astype(int)
)

stint_table = pd.concat({'data': stint_data, 'player_indicators': player_indicators}, axis=1)
display(stint_table.head(10))

This is the stint table you set out to build. Every row shows who was on the field, and what the score margin was, during one stint of a match. With enough matches, a RAPM model can use this table to estimate how much each player affects the score.

Fin¶

Congratulations! You have completed the exam! Don't forget to submit!

"What now?"¶

You started with several tables of raw match records. You ended with one table that has a row for each stint, with its start, end, score margin, and which players were on the field.

This is the table a RAPM model needs. Each stint is one observation. The score margin is the outcome, and the duration tells the model how much weight a stint should get. Each player is a column: 1 for the home team, -1 for the away team, and 0 for not on the field. When you fit the model, each player gets a number that estimates how the score margin changes while they play. The "regularized" part keeps these numbers stable, because there are thousands of player columns and most are 0 in any one stint.

Most analysis work starts the way this exam did: getting records out of a database and reshaping them into a table a model can use. You practiced that with SQL and Pandas. You also practiced reading code you didn't write and fixing it, which comes up often in real projects.

If you want to keep going, try feeding the stint table into a RAPM model to estimate how much each player affects the score margin. You will learn how to fit models like this later in the course.