MT2 TA Testing solutions¶
Ex1¶
In [ ]:
most_substitutions_query = """
SELECT TOURNAMENT_ID, TEAM_ID, COUNT(DISTINCT MATCH_ID) AS UNIQUE_MATCHES,
SUM(CASE WHEN COMING_ON == 1 THEN 1 ELSE 0 END) AS TOTAL_SUBSTITUTIONS,
(CAST(SUM(CASE WHEN COMING_ON == 1 THEN 1 ELSE 0 END) AS FLOAT)/(COUNT(DISTINCT MATCH_ID)))*1.0 AS AVERAGE_SUBSTITUTIONS_PER_MATCH
FROM substitutions
GROUP BY TOURNAMENT_ID, TEAM_ID
ORDER BY AVERAGE_SUBSTITUTIONS_PER_MATCH DESC, TOTAL_SUBSTITUTIONS DESC, TEAM_ID DESC;
"""
In [ ]:
most_substitutions_query = """
select tournament_id,
team_id,
COUNT(DISTINCT match_id) as UNIQUE_MATCHES,
COUNT(*) as TOTAL_SUBSTITUTIONS,
cast(COUNT(*) as float) / 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
"""
In [ ]:
most_substitutions_query = """
SELECT tournament_id,
team_id,
COUNT(DISTINCT match_id) AS UNIQUE_MATCHES,
SUM(coming_on) AS TOTAL_SUBSTITUTIONS,
SUM(coming_on)/(1.0 * 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
"""
In [ ]:
most_substitutions_query = """
-- FIXME
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
"""
In [ ]:
most_substitutions_query = """
-- FIXME
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
"""
In [ ]:
### Solution - Exercise 1
most_substitutions_query = """
-- FIXME
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
"""
In [ ]:
most_substitutions_query = """
SELECT
tournament_id,
team_id,
COUNT(DISTINCT match_id) AS unique_matches,
COUNT(DISTINCT substitution_id) AS total_substitutions,
1.0 * COUNT(DISTINCT 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
"""
Ex2¶
In [ ]:
player_appearances_query = """
SELECT *
FROM player_appearances
WHERE start_date > 0
"""
In [ ]:
player_appearances_query = """
select *
from player_appearances
where start_date > 0
"""
In [ ]:
player_appearances_query = """
SELECT *
FROM player_appearances
WHERE start_date > 0
"""
In [ ]:
player_appearances_query = """
SELECT * FROM player_appearances
WHERE start_date > strftime('%s', '1970-01-01')
"""
In [ ]:
select * from player_appearances where start_date >0
In [ ]:
### Solution - Exercise 2
player_appearances_query = """
SELECT * FROM player_appearances
WHERE start_date > 0
"""
In [ ]:
player_appearances_query = """
SELECT *
FROM player_appearances
WHERE start_date > 0
"""
Ex3¶
In [ ]:
running_goal_margin_query = """
SELECT TOURNAMENT_ID , MATCH_ID, MINUTE_REGULATION, MINUTE_STOPPAGE, GOAL_ID,
SUM(CASE
WHEN HOME_TEAM == 1 AND OWN_GOAL == 0 THEN 1
WHEN HOME_TEAM == 1 AND OWN_GOAL == 1 THEN -1
WHEN HOME_TEAM == 0 AND OWN_GOAL == 0 THEN -1
WHEN HOME_TEAM == 0 AND OWN_GOAL == 1 THEN 1 END)
OVER(PARTITION BY MATCH_ID ORDER BY MINUTE_REGULATION, MINUTE_STOPPAGE, GOAL_ID) AS SCORE_MARGIN
FROM goals
ORDER BY TOURNAMENT_ID, MATCH_ID, MINUTE_REGULATION, MINUTE_STOPPAGE, GOAL_ID
"""
In [ ]:
running_goal_margin_query = """
select tournament_id,
match_id,
minute_regulation,
minute_stoppage,
goal_id,
sum(
CASE
when own_goal = 1 then
case when home_team = 1 then -1 else 1 end
else
case when home_team = 1 then 1 else -1 end
end
) over (partition by match_id
order by goal_id) as score_margin
from goals
order by tournament_id,match_id,minute_regulation, minute_stoppage,goal_id
"""
In [ ]:
running_goal_margin_query = """
WITH goal_effect AS (SELECT goal_id,
tournament_id,
match_id,
minute_regulation,
minute_stoppage,
CASE WHEN (own_goal == 1 AND away_team == 1) THEN 1
WHEN (home_team == 1 AND own_goal == 0) THEN 1
ELSE -1 END AS goal_impact
FROM goals)
SELECT tournament_id,
match_id,
minute_regulation,
minute_stoppage,
goal_id,
SUM(goal_impact) OVER (PARTITION BY match_id ORDER BY goal_id) AS score_margin
FROM goal_effect
"""
In [ ]:
running_goal_margin_query = """
SELECT
tournament_id , match_id , minute_regulation , minute_stoppage , goal_id , SUM(
CASE
WHEN (home_team = 1 AND own_goal = 0)
OR (home_team = 0 AND own_goal = 1) THEN 1
ELSE -1
END
) OVER (
PARTITION BY tournament_id, match_id
ORDER BY minute_regulation, minute_stoppage, goal_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS score_margin
FROM goals
ORDER BY tournament_id , match_id , minute_regulation , minute_stoppage , goal_id
"""
In [ ]:
running_goal_margin_query = """
Select
tournament_id,
match_id,
minute_regulation,
minute_stoppage,
goal_id,
Sum(
Case
When home_team = 1 and own_goal=0 then 1
When home_team = 1 and own_goal=1 then -1
when away_team = 1 and own_goal=0 then -1
when away_team = 1 and own_goal=1 then 1
Else 0
End
)over (
partition by match_id
order by minute_regulation,minute_stoppage,goal_id
) as score_margin
from
goals
Order by
tournament_id, match_id, minute_regulation, minute_stoppage, goal_id
"""
In [ ]:
### Solution - Exercise 3
running_goal_margin_query = """
SELECT tournament_id,
match_id,
minute_regulation,
minute_stoppage,
goal_id,
SUM(
CASE WHEN own_goal = 1 THEN -COALESCE(home_team, 0) + COALESCE(away_team, 0)
ELSE COALESCE(home_team, 0) - COALESCE(away_team, 0)
END
) OVER(PARTITION BY tournament_id, match_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS score_margin
FROM goals
"""
In [ ]:
running_goal_margin_query = """
SELECT
tournament_id,
match_id,
minute_regulation,
minute_stoppage,
goal_id,
SUM(
(CASE WHEN home_team = 1 THEN 1 ELSE -1 END)
* (CASE WHEN own_goal = 1 THEN -1 ELSE 1 END)
) OVER (
PARTITION BY tournament_id, match_id
ORDER BY minute_regulation, minute_stoppage, goal_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS score_margin
FROM goals
ORDER BY tournament_id, match_id, minute_regulation, minute_stoppage, goal_id
"""
Ex4¶
In [ ]:
substitution_score_margin_query = """
-- FIXME
WITH collated_substitutions AS (
SELECT
s.*,
gm.MINUTE_REGULATION AS most_recent_goal_minute,
ROW_NUMBER() OVER (PARTITION BY s.substitution_id ORDER BY gm.minute_regulation desc, gm.goal_id desc) AS goal_priority,
COALESCE(gm.score_margin, 0) AS score_margin,
gm.home_team AS home_team_goal
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,
score_margin
FROM collated_substitutions
WHERE goal_priority = 1
ORDER BY substitution_id asc
"""
In [ ]:
substitution_score_margin_query = """
-- FIXME
WITH collated_substitutions AS (
SELECT
s.*,
gm.MINUTE_REGULATION AS most_recent_goal_minute,
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 margin,
gm.home_team as home_team_goal,
coalesce(gm.score_margin,0) 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,
score_margin
FROM collated_substitutions
WHERE goal_priority = 1
"""
In [ ]:
substitution_score_margin_query = """
-- FIXME
WITH collated_substitutions AS (
SELECT
s.*,
gm.minute_regulation AS most_recent_goal_minute,
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,
gm.home_team AS home_team_goal
FROM substitutions AS s
LEFT JOIN goal_margins AS 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,
CASE WHEN score_margin IS NULL THEN 0 ELSE score_margin END AS score_margin
FROM collated_substitutions
WHERE goal_priority = 1
"""
In [ ]:
substitution_score_margin_query = """
-- FIXME
WITH collated_substitutions AS (
SELECT
s.*,
gm.home_team AS home_team_goal,
gm.MINUTE_REGULATION AS most_recent_goal_minute,
ROW_NUMBER() OVER (PARTITION BY s.substitution_id ORDER BY gm.minute_regulation DESC, gm.goal_id DESC) AS goal_priority,
COALESCE(gm.score_margin , 0) 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 gm.minute_regulation <= s.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,
score_margin
FROM collated_substitutions
WHERE goal_priority = 1
"""
In [ ]:
-- FIXME
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 gm.minute_regulation <=s.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
"""
In [ ]:
### Solution - Exercise 4
substitution_score_margin_query = """
-- FIXME
WITH collated_substitutions AS (
SELECT
s.*,
gm.MINUTE_REGULATION AS most_recent_goal_minute,
gm.home_team AS home_team_goal,
gm.score_margin AS score_margin,
gm.goal_id as goal_id,
PERCENT_RANK() OVER (PARTITION BY s.substitution_id ORDER BY gm.MINUTE_REGULATION, gm.goal_id) AS goal_priority,
gm.score_margin AS margin
FROM substitutions s
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
),
collated_substitutions_2 AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY substitution_id
ORDER BY minute_regulation DESC,
goal_id DESC) AS goal_priority,
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
FROM collated_substitutions
)
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,
score_margin
FROM collated_substitutions_2
WHERE goal_priority = 1
"""
In [ ]:
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,
gm.score_margin AS margin,
ROW_NUMBER() OVER (
PARTITION BY s.substitution_id
ORDER BY gm.minute_regulation DESC, gm.goal_id DESC
) AS goal_priority
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,
COALESCE(margin, 0) AS score_margin
FROM collated_substitutions
WHERE goal_priority = 1
ORDER BY substitution_id
"""
Ex5¶
In [ ]:
def initialize_player_states(player_appearances_df, match_id):
df = player_appearances_df.copy()
df = df[df['match_id'] == match_id]
df['team_flag'] = [-1+2*i for i in df['home_team']]
df['field_state'] = [i if j == 1 else 0 for i, j in zip(df['team_flag'], df['starter'])]
df = df.sort_values(by='player_id')
return df
In [ ]:
def initialize_player_states(player_appearances_df, match_id):
pad = player_appearances_df[player_appearances_df['match_id']==match_id].copy()
pad['team_flag'] = np.where(pad['home_team']==1,1,-1)
pad['field_state'] = pad['team_flag'] * pad['starter']
return pad
In [ ]:
def initialize_player_states(player_appearances_df, match_id):
###
### YOUR CODE HERE
###
appearances_df_copy = player_appearances_df.copy()
appearances_df_copy = appearances_df_copy[appearances_df_copy['match_id'] == match_id]
appearances_df_copy["team_flag"] = np.where(appearances_df_copy["home_team"] == 1, 1, -1)
appearances_df_copy["field_state"] = np.where(appearances_df_copy["starter"] == 1, appearances_df_copy["team_flag"], 0)
return appearances_df_copy.sort_values(by='player_id', ascending=True)
In [ ]:
def initialize_player_states(player_appearances_df, match_id):
df_copy = player_appearances_df.copy()
df_copy = df_copy[df_copy.match_id == match_id]
df_copy['team_flag'] = df_copy['home_team'] - df_copy['away_team']
df_copy['field_state'] = df_copy['team_flag'] * df_copy['starter']
return df_copy.sort_values(by = 'player_id')
In [ ]:
def initialize_player_states(player_appearances_df, match_id):
player_appearances_df_new = player_appearances_df.copy()
player_appearances_df_new = player_appearances_df_new [player_appearances_df ['match_id']==match_id]
player_appearances_df_new['team_flag'] = player_appearances_df_new['home_team'] -player_appearances_df_new['away_team']
player_appearances_df_new['field_state'] = player_appearances_df_new['team_flag']*player_appearances_df_new['starter']
return player_appearances_df_new
In [ ]:
def initialize_player_states(player_appearances_df, match_id):
###
### YOUR CODE HERE
###
df = player_appearances_df.copy()
df = df[df['match_id'] == match_id]
df['team_flag'] = np.where(df['home_team'] == 1, 1, -1)
df['field_state'] = df['team_flag'] * df['starter']
df = df.sort_values(by='player_id', ascending=True)
return df
In [ ]:
def initialize_player_states(player_appearances_df, match_id):
result = player_appearances_df[player_appearances_df['match_id'] == match_id].copy()
result['team_flag'] = np.where(result['home_team'] == 1, 1, -1)
result['field_state'] = result['team_flag'] * result['starter']
return result.sort_values('player_id').reset_index(drop=True)
Ex6¶
In [ ]:
def consolidate_substitutions(substitutions_df):
substitution_keys = ['tournament_id', 'match_id', 'minute_regulation', 'score_margin']
df = substitutions_df.copy()
df = df.drop_duplicates(subset=substitution_keys)
df = df.sort_values(by='minute_regulation')
df['stint_id'] = [1 + i for i in df.groupby(['tournament_id', 'match_id']).cumcount()]
df = df.rename(columns={"minute_regulation":"end_minute"})
df = df[["tournament_id", "match_id", "stint_id", "end_minute", "score_margin"]]
df = df.sort_values(by=["tournament_id", "match_id", "end_minute"])
df = df.astype({"stint_id": "int64"})
return df
In [ ]:
def consolidate_substitutions(substitutions_df):
substitution_keys = ['tournament_id', 'match_id', 'minute_regulation', 'score_margin']
sd = substitutions_df.copy()
sd = sd.drop_duplicates(subset=substitution_keys)
sd = sd.rename(columns={'minute_regulation':'end_minute'})
sd['stint_id'] = sd.groupby('match_id').cumcount()+1
sd = sd.sort_values(['tournament_id', 'match_id', 'end_minute'])
return sd[['tournament_id', 'match_id', 'stint_id', 'end_minute', 'score_margin']]
In [ ]:
def consolidate_substitutions(substitutions_df):
substitution_keys = ['tournament_id', 'match_id', 'minute_regulation', 'score_margin']
###
### YOUR CODE HERE
###
df_copy = substitutions_df.copy()
df_copy = df_copy.drop_duplicates(subset=substitution_keys)
df_copy = df_copy.rename(columns={'minute_regulation': 'end_minute'})
df_copy = df_copy.sort_values(by = ["tournament_id", "match_id", "end_minute"])
df_copy["stint_id"] = df_copy.groupby(['tournament_id', 'match_id']).cumcount() + 1
return df_copy[['tournament_id', 'match_id', 'stint_id', 'end_minute', 'score_margin']].reset_index(drop = True)
In [ ]:
def consolidate_substitutions(substitutions_df):
substitution_keys = ['tournament_id', 'match_id', 'minute_regulation', 'score_margin']
df = substitutions_df[substitution_keys].drop_duplicates().copy()
df.sort_values(by = ['tournament_id', 'match_id', 'minute_regulation'] , inplace = True)
df.rename(columns = {'minute_regulation': 'end_minute'} , inplace = True)
df = df.reset_index(drop = True)
df['stint_id'] = df.groupby(['tournament_id' , 'match_id']).cumcount() + 1
return df[['tournament_id', 'match_id', 'stint_id', 'end_minute', 'score_margin']]
In [ ]:
def consolidate_substitutions(substitutions_df):
substitution_keys = ['tournament_id', 'match_id', 'end_minute', 'score_margin']
substitutions_df_new = substitutions_df.copy()
substitutions_df_group = substitutions_df_new.rename(columns = {"minute_regulation":"end_minute"}).sort_values(by = ["tournament_id", "match_id", "end_minute"])
substitutions_df_group = substitutions_df_group.drop_duplicates(subset=substitution_keys)
substitutions_df_group['stint_id']= substitutions_df_group.groupby(['tournament_id','match_id']).cumcount()+1
return substitutions_df_group[['tournament_id', 'match_id','stint_id', 'end_minute', 'score_margin']]
In [ ]:
### Solution - Exercise 6
def consolidate_substitutions(substitutions_df):
substitution_keys = ['tournament_id', 'match_id', 'minute_regulation', 'score_margin']
###
### YOUR CODE HERE
###
df = substitutions_df.copy()
df = df.drop_duplicates(subset=substitution_keys)
df = df.rename(columns={"minute_regulation": "end_minute"})
df = df.sort_values(by=['tournament_id', 'match_id', 'end_minute'], ascending=[True, True, True])
df['stint_id'] = df.groupby('match_id').cumcount() + 1
df = df[['tournament_id', 'match_id', 'stint_id', 'end_minute', 'score_margin']]
return df
In [ ]:
def consolidate_substitutions(substitutions_df):
substitution_keys = ['tournament_id', 'match_id', 'minute_regulation', 'score_margin']
result = (
substitutions_df[substitution_keys]
.drop_duplicates()
.sort_values(['tournament_id', 'match_id', 'minute_regulation'])
.rename(columns={'minute_regulation': 'end_minute'})
.reset_index(drop=True)
)
result['stint_id'] = result.groupby(['tournament_id', 'match_id']).cumcount() + 1
return result[['tournament_id', 'match_id', 'stint_id', 'end_minute', 'score_margin']]
Ex7¶
In [ ]:
def add_stint_times(stints_df):
df = stints_df.copy()
df = df.sort_values(by=["tournament_id", "match_id", "stint_id"])
df['start_minute'] = df.groupby(["tournament_id", "match_id"])['end_minute'].shift(fill_value=0)
df['duration_minutes'] = df['end_minute'] - df['start_minute']
df = df.sort_values(by=["tournament_id", "match_id", "stint_id"])
df = df.astype({"start_minute": "float", "duration_minutes": "float"})
return df
In [ ]:
def add_stint_times(stints_df):
sd = stints_df.copy()
sd['start_minute'] = sd.groupby('match_id')['end_minute'].shift(1).fillna(0)
sd['duration_minutes'] = sd['end_minute'] - sd['start_minute']
sd = sd.sort_values(['tournament_id','match_id','stint_id'])
return sd
In [ ]:
def add_stint_times(stints_df):
###
### YOUR CODE HERE
###
df_copy = stints_df.copy()
df_copy['start_minute'] = df_copy.groupby(['tournament_id', 'match_id'])['end_minute'].shift().fillna(0)
df_copy["duration_minutes"] = df_copy["end_minute"] - df_copy["start_minute"]
return df_copy
In [ ]:
def add_stint_times(stints_df):
df = stints_df.copy()
result = df.sort_values(['tournament_id', 'match_id', 'stint_id'], ascending=True).reset_index(drop=True)
result['start_minute'] = result.groupby('match_id')['end_minute'].shift(1)
result['start_minute'] = result.start_minute.fillna(0.0)
result['duration_minutes'] = result['end_minute'] - result['start_minute']
return result.sort_values(by = ['tournament_id' , 'match_id' , 'stint_id'])
In [ ]:
def add_stint_times(stints_df):
# FIXME
stints_df_new = stints_df.copy()
result = stints_df_new.sort_values(['tournament_id', 'match_id', 'stint_id'], ascending=True)
result['start_minute'] = result.groupby('match_id')['end_minute'].shift(1).fillna(0)
result['duration_minutes'] = result['end_minute'] - result['start_minute']
result['duration_minutes'] = (result['duration_minutes'].fillna(0))
return result
In [ ]:
### Solution - Exercise 7
def add_stint_times(stints_df):
# FIXME
result = stints_df.copy()
result = result.sort_values(['tournament_id', 'match_id', 'stint_id'], ascending=True).reset_index(drop=True)
result['start_minute'] = result['end_minute'].shift()
result['start_minute'] = np.where(result['stint_id'] == 1, 0.0, result['start_minute'])
result['duration_minutes'] = result['end_minute'] - result['start_minute']
return result
In [ ]:
def add_stint_times(stints_df):
result = stints_df.sort_values(['tournament_id', 'match_id', 'stint_id']).reset_index(drop=True)
previous_end = result.groupby(['tournament_id', 'match_id'])['end_minute'].shift()
result['start_minute'] = previous_end.fillna(0).astype(float)
result['duration_minutes'] = (result['end_minute'] - result['start_minute']).astype(float)
return result
Ex8¶
In [ ]:
def apply_substitutions(player_states_df, substitutions_df):
if substitutions_df.empty:
return player_states_df
else:
df = player_states_df.copy()
sdf = substitutions_df.copy()
off = sdf.loc[sdf['going_off'] == 1, 'player_id']
onn = sdf.loc[sdf['coming_on'] == 1, 'player_id']
df.loc[df['player_id'].isin(off), 'field_state'] = 0
df.loc[df['player_id'].isin(onn), 'field_state'] = df.loc[df['player_id'].isin(onn),'team_flag']
return df
In [ ]:
def apply_substitutions(player_states_df, substitutions_df):
psd = player_states_df.copy()
sd = substitutions_df.copy()
sd['side'] = sd['going_off']-sd['coming_on']
psd = psd.merge(sd[['player_id','side']],on='player_id',how='left')
psd['field_state'] = psd['field_state'].where(psd['side'].isna(), 0)
psd['field_state'] = psd['field_state'].where(psd['side'] != -1, psd['team_flag'])
psd.drop(columns=['side'],inplace=True)
return psd
In [ ]:
def apply_substitutions(player_states_df, substitutions_df):
###
### YOUR CODE HERE
###
if substitutions_df.empty:
return player_states_df
else:
sub_df_copy = substitutions_df.copy()
play_df = player_states_df.copy()
players_off = sub_df_copy.loc[sub_df_copy['going_off'] == 1, "player_id"].tolist()
players_on = sub_df_copy.loc[sub_df_copy['coming_on'] == 1, "player_id"].tolist()
mask_1 = play_df['player_id'].isin(players_off)
play_df.loc[mask_1, 'field_state'] = 0
mask_2 = play_df['player_id'].isin(players_on)
play_df.loc[mask_2, 'field_state'] = play_df.loc[mask_2, "team_flag"]
return play_df
In [ ]:
def apply_substitutions(player_states_df, substitutions_df):
df = player_states_df.copy()
if substitutions_df.empty:
return df
off_ids = substitutions_df.loc[substitutions_df['going_off'] == 1, 'player_id']
on_ids = substitutions_df.loc[substitutions_df['coming_on'] == 1, 'player_id']
df.loc[df['player_id'].isin(off_ids), 'field_state'] = 0
on_mask = df['player_id'].isin(on_ids)
df.loc[on_mask, 'field_state'] = df.loc[on_mask, 'team_flag']
return df
In [ ]:
def apply_substitutions(player_states_df, substitutions_df):
player_states_df_new = player_states_df.copy()
player_states_df_new = player_states_df_new.merge(substitutions_df[['tournament_id','match_id','team_id','going_off','coming_on','player_id']],on =['tournament_id','match_id','team_id','player_id'],how = 'left')
player_states_df_new.loc[player_states_df_new['going_off']==1,'field_state' ]=0
player_states_df_new.loc[player_states_df_new['coming_on']==1,'field_state' ]= player_states_df_new['team_flag']
player_states_df_new=player_states_df_new.drop(columns = ['going_off','coming_on'])
return player_states_df_new
In [ ]:
### Solution - Exercise 8
def apply_substitutions(player_states_df, substitutions_df):
###
### YOUR CODE HERE
###
df = player_states_df.copy()
df2 = substitutions_df.copy()
if df2.empty:
return df
some_ids = df2['player_id'].to_list()
df = df[df['player_id'].isin(some_ids)]
df_with_index = df.reset_index()
df_merged = pd.merge(
df_with_index,
df2[['player_id', 'going_off', 'coming_on']],
on='player_id',
how='left'
)
df_merged = df_merged.set_index('index')
mask = df_merged['going_off'] == 1
df_merged.loc[mask, 'field_state'] = 0
mask2 = df_merged['coming_on'] == 1
df_merged.loc[mask2, 'field_state'] = df_merged['team_flag']
df_merged = df_merged[['player_id', 'team_flag', 'starter', 'field_state']]
return df_merged
In [ ]:
def apply_substitutions(player_states_df, substitutions_df):
result = player_states_df.copy()
if substitutions_df.empty:
return result
going_off = substitutions_df.loc[substitutions_df['going_off'] == 1, 'player_id']
coming_on = substitutions_df.loc[substitutions_df['coming_on'] == 1, 'player_id']
result.loc[result['player_id'].isin(going_off), 'field_state'] = 0
on_mask = result['player_id'].isin(coming_on)
result.loc[on_mask, 'field_state'] = result.loc[on_mask, 'team_flag']
return result