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