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:
Final reminders:
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.
You will do the following:
Section 0: Data Exploration (Exercises 0-1)
Section 1: Data Querying (Exercises 2-4)
Section 2: Data Cleaning (Exercises 5-6)
Section 3: Engineering The Stints (Exercises 7-8)
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.
### 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
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.
### Run Me
database_schema_dfs = utils.serialization_utils.load_serialized_object('database_schema_dfs', 'preload')
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.
### 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())
### Check Cell - Exercise 0
### 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
### 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.
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:
tournament_id: A tournament ID contained in substitutionsteam_id: A team ID contained in substitutionsunique_matches: A count of total distinct matches played by the team in the tournamenttotal_substitutions: A count of total substitutions by the team in the tournamentaverage_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.coming_on column to do this. A value of 1 means the player is coming onto the field.DESCending order, using the following columns:average_substitutions_per_matchtotal_substitutionsteam_idHints:
GROUP BY clause and then COUNT the appropriate values.### 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 |
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.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.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.
### 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.')
### 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.
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 indicates that a player is on the field for the home team.0 indicates that a player is not on the field.-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:
player_appearances)substitutions)goals)To start, we need to know which players appeared in each match.
### Run Me
player_appearances_data = utils.serialization_utils.load_serialized_object('player_appearances_data', 'preload')
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:
start_date column to filter the table.Hints:
start_date is stored as a number of days since the Unix epoch (which is, conveniently, January 1, 1970).### 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 |
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.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.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.
### 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.')
### 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.
### Run Me
running_goal_margin_data_dict = utils.serialization_utils.load_serialized_object('running_goal_margin_data_dict', 'preload')
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:
tournament_id: The tournament in which the goal was scored.match_id: The match in which the goal was scored.minute_regulation: The minute in regulation time at which the goal was scored.minute_stoppage: The minute in "overtime" at which the goal was scored.goal_id: A unique identifier for the goal.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.tournament_id, match_id, minute_regulation, minute_stoppage, and goal_id.Hints:
score_margin.SUM(...) OVER (...) AS score_margin can be used to calculate the running total.+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.own_goal to determine the direction (a value of 1 for own_goal means a player scored against their own team).JOIN and GROUP BY logic to calculate the solution.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?)### 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 |
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.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.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.
### 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.')
### 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.
### Run Me
substitution_score_margin_data_dict = utils.serialization_utils.load_serialized_object('substitution_score_margin_data_dict', 'preload')
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:
substitutions except for shirt_number. Also include the following:most_recent_goal_minute: The minute of the most recent goal at or before the substitution.home_team_goal: The home_team column from goal_margins for that goal.score_margin: The cumulative score margin at the time of that goal.0.Hints: We recommend the following approach:
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.ROW_NUMBER window function to rank the matched goals by recency, then keep only the most recent one.substitution_id so that ranking is independent per substitution.minute_regulation and goal_id, in descending order, to break ties.goal_priority field (a priority of 1 means it's most recent!)### 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 |
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.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.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.
### 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.')
### 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.
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:
1 indicates that a player is on the field for the home team.0 indicates that a player is not on the field.-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.
### Run Me
eligible_appearances = utils.serialization_utils.load_serialized_object('eligible_appearances', 'preload')
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:
player_appearances_df as defined below.Requirements:
player_appearances_df to only rows matching match_id.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.player_id.Hints:
home_team and away_team are binary columns. Only one of them will be 1 for a given player.starter is also binary. Multiplying team_flag by starter is a compact way to zero out players who are not yet on the field.### 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 |
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.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.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.
### 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.')
### 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.
### Run Me
filled_substitutions = utils.serialization_utils.load_serialized_object('filled_substitutions', 'preload')
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:
substitutions_df. Return a new DataFrame.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.minute_regulation to end_minute. Each substitution ends one stint.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.tournament_id, match_id, stint_id, end_minute, score_margin.tournament_id, match_id, and end_minute.Hints:
drop_duplicates() removes repeated rows.### 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 |
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.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.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.
### 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.')
### 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.
### Run Me
consolidated_substitutions_df = utils.serialization_utils.load_serialized_object('consolidated_substitutions_df', 'preload')
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:
Next, you will work out when each stint started.
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:
stints_df with two new columns, start_minute and duration_minutes.Requirements:
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.start_minute and duration_minutes must be floating-point numbers, like in the demo output.tournament_id, match_id, and stint_id.Hints:
### 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 |
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.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.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.
### 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.')
### 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
### 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:
0.90. If the last substitution happened after minute 90, the stint ends at that minute instead.match_df, which has the final score margin of every match.stint_id is one more than the previous stint.The cell shows the stints of two matches so you can see the result.
### 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.
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:
player_states_df with the updated field_state values.Requirements:
going_off equal to 1 in substitutions_df, set their field_state to 0.coming_on equal to 1 in substitutions_df, set their field_state to their team_flag.substitutions_df is empty, return an unchanged copy of player_states_df.Hints:
player_states_df['player_id'].isin(some_ids) is True for each player whose ID is in some_ids.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.### 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 |
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.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.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.
### 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.')
### 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
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.
### 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.
### 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.
Congratulations! You have completed the exam! Don't forget to submit!
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.