!mkdir data
!wget -P data/ http://www.football-data.co.uk/mmz4281/1617/E0.csv

import math
import numpy as np
import pandas as pd

results = pd.read_csv(
    'data/E0.csv', 
    usecols=[x for x in range(11)],
    parse_dates=['Date'],
    dayfirst=True)

results.head()

## converting each matchup into 2 rows
## one where each team is 'current_team' and opponent is identfied
results['H'] = results['HomeTeam']
results['A'] = results['AwayTeam']
cols_to_keep = ['Div', 'Date', 'HomeTeam', 'AwayTeam', 'FTHG',
                'FTAG', 'FTR', 'HTHG', 'HTAG', 'HTR', 'Referee']

team_results = pd.melt(
    results, 
    id_vars=cols_to_keep, 
    value_vars=['H', 'A'],
    var_name='Home/Away',
    value_name='Team')

team_results['Opponent'] = np.where(team_results['Team'] == team_results['HomeTeam'],
                                    team_results['AwayTeam'],
                                    team_results['HomeTeam'])

team_results.head(2)

points_map = {
    'W': 3,
    'D': 1,
    'L': 0
}

def get_result(score, score_opp):
    if score == score_opp:
        return 'D'
    elif score > score_opp:
        return 'W'
    else:
        return 'L'

# full time goals
team_results['Goals'] = np.where(team_results['Team'] == team_results['HomeTeam'],
                                 team_results['FTHG'],
                                 team_results['FTAG'])
team_results['Goals_Opp'] = np.where(team_results['Team'] != team_results['HomeTeam'],
                                     team_results['FTHG'],
                                     team_results['FTAG'])
team_results['Result'] = np.vectorize(get_result)(team_results['Goals'], team_results['Goals_Opp'])
team_results['Points'] = team_results['Result'].map(points_map)

# 1st half goals
team_results['1H_Goals'] = np.where(team_results['Team'] == team_results['HomeTeam'],
                                    team_results['HTHG'],
                                    team_results['HTAG'])
team_results['1H_Goals_Opp'] = np.where(team_results['Team'] != team_results['HomeTeam'],
                                        team_results['HTHG'],
                                        team_results['HTAG'])
team_results['1H_Result'] = np.vectorize(get_result)(team_results['1H_Goals'], team_results['1H_Goals_Opp'])
team_results['1H_Points'] = team_results['1H_Result'].map(points_map)

# 2nd half goals
team_results['2H_Goals'] = team_results['Goals'] - team_results['1H_Goals']
team_results['2H_Goals_Opp'] = team_results['Goals_Opp'] - team_results['1H_Goals_Opp']
team_results['2H_Result'] = np.vectorize(get_result)(team_results['2H_Goals'], team_results['2H_Goals_Opp'])
team_results['2H_Points'] = team_results['2H_Result'].map(points_map)

# Drop unnecessary columns and sort by date
cols_to_drop = ['HomeTeam', 'AwayTeam', 'FTHG', 'FTAG', 'FTR', 'HTHG', 'HTAG', 'HTR']
team_results = (team_results
                    .drop(cols_to_drop, axis=1)
                    .sort_values(by=['Date', 'Referee']))

team_results.head()

# Testing the standings function against real world data
(team_results
     .groupby('Team')
     .sum()['Points']
     .sort_values(ascending=False))

def standings(frame, result_col, goals_col, goals_opp_col, points_col):
    """This function takes in a DataFrame and strings identifying fields
    to calculate the league table.
    
    Making it generalized will allow us to calculate league tables for
    First Half Goals only. Second Half Goals only.
    """
    record = {}
    
    record['Played'] = np.size(frame[result_col])
    record['Won'] = np.sum(frame[result_col] == 'W')
    record['Drawn'] = np.sum(frame[result_col] == 'D')
    record['Lost'] = np.sum(frame[result_col] == 'L')
    record['GF'] = np.sum(frame[goals_col])
    record['GA'] = np.sum(frame[goals_opp_col])
    record['GD'] = record['GF'] - record['GA']
    record['Points'] = np.sum(frame[points_col])
    
    return pd.Series(record,
                     index=['Played', 'Won', 'Drawn', 'Lost', 'GF', 'GA', 'GD', "Points"])

# Get League Table
results_byteam = team_results.groupby(['Team'])

(results_byteam 
     .apply(standings,
            result_col='Result',
            goals_col='Goals',
            goals_opp_col='Goals_Opp',
            points_col='Points')
     .sort_values('Points', ascending=False))

# Get League Table for First Half Goals only
(results_byteam
     .apply(standings,
            result_col='Result',
            goals_col='Goals',
            goals_opp_col='Goals_Opp',
            points_col='Points')
     .sort_values('Points', ascending=False))

# Get League Table for Second Half Goals only
(results_byteam
     .apply(standings,
            result_col='Result',
            goals_col='Goals',
            goals_opp_col='Goals_Opp',
            points_col='Points')
     .sort_values('Points', ascending=False))

# Rank Teams in Standings
league_table = (results_byteam
                    .apply(standings,
                           result_col='Result',
                           goals_col='Goals',
                           goals_opp_col='Goals_Opp',
                           points_col='Points')
                    .sort_values(by=['Points', 'GD', 'GF'], ascending=False))

league_table['rank'] = (league_table
                            .apply(lambda row: (row['Points'], row['GD'], row['GF']), axis=1)
                            .rank(method='min', ascending=False)
                            .astype(int))
league_table

def rank_teams(league_table, team_list):
    """Return a Series of ranked teams, including those who have yet to play
    
    Args:
        * league_table - League Table DataFrame
        * team_list - List of all teams in league
    """
    
    # sort by tiebraker and rank
    team_rank = (league_table
                     .apply(lambda row: (row['Points'], row['GD'], row['GF']), axis=1)
                     .rank(method='min', ascending=False)
                     .astype(int))
    
    # if not all teams are ranked (i.e. some of them might have not have played yet)
    if team_rank.size < len(team_list):
        # get all teams that need to be added to the table
        ranked_teams = team_rank.index.values
        teams_to_add = {team for team in team_list if team not in ranked_teams}  
        
        # position to rank remaining teams
        rank_to_assign = team_rank.size + 1
        
        # add teams that haven't played a game to rankings
        team_pos = {}
        for team in teams_to_add:
            team_pos[team] = rank_to_assign
        team_rank = team_rank.append(pd.Series(data=team_pos))
    
    return team_rank

# Let's test our function to make sure it works
all_teams = np.sort(team_results['Team'].unique())
rank_teams(league_table, team_list=all_teams)

# get list of days
rank_history = []
all_dates = team_results['Date'].unique()

# calculate ranks after each day there is a game
for day in all_dates:
    # get results up to current day
    dailyresults_byteam = (team_results[team_results['Date'] <= day]
                               .groupby(['Team']))
    
    # create league table with ranking
    # premier league ranking goes: Points, GD, GF
    league_table = (dailyresults_byteam
                        .apply(standings,
                               result_col='Result',
                               goals_col='Goals',
                               goals_opp_col='Goals_Opp',
                               points_col='Points')
                        .sort_values(by=['Points', 'GD', 'GF'], ascending=False))
    team_rank = rank_teams(league_table, team_list=all_teams)
    
    rank_history.append(team_rank)
    
# create historical ranking dataframe from list of ranks
rank_history_df = (pd.DataFrame
                    .from_records(rank_history, index=all_dates))

# Reindex and include all dates
idx = pd.date_range(start=rank_history_df.index.min(), end=rank_history_df.index.max())
rank_history_df = rank_history_df.reindex(idx, method='ffill')

rank_history_df.head()

rank_history_df.tail()

ordinal = lambda n: "%d%s" % (n,"tsnrhtdd"[(math.floor(n/10)%10!=1)*(n%10<4)*n%10::4])

# for each team in the league, get the length of the longest streak
for team in all_teams:
    rank_history_team = rank_history_df[team].to_frame()
    rank_history_team.columns = ['A']
    rank_history_team['block'] = ((rank_history_team.A.shift(1) != rank_history_team.A)
                                      .astype(int)
                                      .cumsum())

    streak_lengths = (rank_history_team
                          .reset_index()
                          .groupby(['A','block'])['index']
                          .apply(np.size))
    
    pos = streak_lengths.argmax()[0]
    max_length = streak_lengths.max()
    print(f'{team} was {ordinal(pos)} for {max_length} days')  # f-string! =)