<jupyter_start><jupyter_text>Pre season analysis<jupyter_code>df_teams.sort_values('2018/19 pts', ascending=False).plot.bar(y=['Cost', '2018/19 pts'], figsize=(10,8), grid=True)
df_teams.sort_values('Team Cost per Point 2018/19', ascending=False)['Team Cost per Point 2018/19']<jupyter_output><empty_output><jupyter_text>This means we should favour players from the bottom of this list to maximise the points per money. 3 LIV yplayers, then WOL, CRY, MCI and EVE<jupyter_code># 38 fixtures per season. Which team had most players playing > 60 minutes per game
df_mins = df[df['2018/19 mins'] > (60 * 38)]
df_mins.groupby('Team').count()['Player']<jupyter_output><empty_output><jupyter_text>This means teams like SOU have a high rotation risk. Should focus on WOL, LIV, MCI, LEI, WAT, CRY, EVE, BUR  Use Integer Linear Programming to pick an optimal team based on last year's performance<jupyter_code># ILP team, inspiration from 
# https://github.com/kangeugine/fantasy-football/blob/master/%5BPart%202%5D%20Knapsack%20Problem.ipynb
from pulp import *
# only consider players who have data from last season
# what to do about all the new players in SHU and NOR?

def build_lp_vars(dfo):  
    lp_vars = {}
    
    lp_vars['player'] = [str(i) for i in dfo.index.tolist()]
    lp_vars['point'] = {str(i): int(dfo.loc[i]['2018/19 pts']) for i in dfo.index.tolist() }
    lp_vars['cost'] = {str(i): int(dfo.loc[i]['Cost']) for i in dfo.index.tolist()} 
    lp_vars['gk'] = {str(i): 1 if str(dfo.loc[i]['Position']) == '1' else 0 for i in dfo.index.tolist()} 
    lp_vars['defe'] = {str(i): 1 if str(dfo.loc[i]['Position']) == '2' else 0 for i in dfo.index.tolist()}
    lp_vars['mid'] = {str(i): 1 if str(dfo.loc[i]['Position']) == '3' else 0 for i in dfo.index.tolist()}
    lp_vars['stri'] = {str(i): 1 if str(dfo.loc[i]['Position']) == '4' else 0 for i in dfo.index.tolist()}

    # constrain on max 3 per team ... there may be a prettier way to do this .. 
    lp_vars['ars'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'ARS' else 0 for i in dfo.index.tolist()}
    lp_vars['bha'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'BHA' else 0 for i in dfo.index.tolist()}
    lp_vars['bou'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'BOU' else 0 for i in dfo.index.tolist()}
    lp_vars['bur'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'BUR' else 0 for i in dfo.index.tolist()}
    lp_vars['che'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'CHE' else 0 for i in dfo.index.tolist()}
    lp_vars['cry'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'CRY' else 0 for i in dfo.index.tolist()}
    lp_vars['eve'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'EVE' else 0 for i in dfo.index.tolist()}
    lp_vars['lei'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'LEI' else 0 for i in dfo.index.tolist()}
    lp_vars['liv'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'LIV' else 0 for i in dfo.index.tolist()}
    lp_vars['mci'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'MCI' else 0 for i in dfo.index.tolist()}
    lp_vars['mun'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'MUN' else 0 for i in dfo.index.tolist()}
    lp_vars['new'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'NEW' else 0 for i in dfo.index.tolist()}
    lp_vars['sou'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'SOU' else 0 for i in dfo.index.tolist()}
    lp_vars['tot'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'TOT' else 0 for i in dfo.index.tolist()}
    lp_vars['wat'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'WAT' else 0 for i in dfo.index.tolist()}
    lp_vars['whu'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'WHU' else 0 for i in dfo.index.tolist()}
    lp_vars['wol'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'WOL' else 0 for i in dfo.index.tolist()}
    lp_vars['nor'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'NOR' else 0 for i in dfo.index.tolist()}
    lp_vars['shu'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'SHU' else 0 for i in dfo.index.tolist()}
    lp_vars['avl'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'AVL' else 0 for i in dfo.index.tolist()}
    lp_vars['xi'] = {str(i): 1 for i in dfo.index.tolist()}
    
    return lp_vars
    
df_optimise = df[pd.notnull(df['2018/19 pts'])]
LP_VAR = build_lp_vars(df_optimise)
TEAMS = ['ars', 'bha', 'bou', 'bur', 'che', 'cry', 'eve', 'lei', 'liv', 'mci', 'mun',
         'new', 'sou', 'tot', 'wat', 'whu', 'wol', 'nor', 'shu', 'avl']

def build_lp_team(n_gk = 1, n_defe = 4, n_mid = 4, n_fwd = 2, tot_cost = 830, tot_players = 11, 
                tgt = LP_VAR['point'], lp_vars = LP_VAR):

    # objective function, maximise total points
    prob = LpProblem("Fantasy Football",LpMaximize)
    player = lp_vars['player']
    
    player_vars = LpVariable.dicts("Players",player,0,1,LpBinary)
    prob += lpSum([tgt[i]*player_vars[i] for i in player]), "Total Points"

    # Team configuration (adjust for 4-4-2, 4-3-3, etc.)
    prob += lpSum([player_vars[i] for i in player]) == tot_players, tot_players
    prob += lpSum([lp_vars['cost'][i] * player_vars[i] for i in player]) == tot_cost, "Total Cost Max Playing"
    prob += lpSum([lp_vars['gk'][i] * player_vars[i] for i in player]) == n_gk, "1 GK"
    prob += lpSum([lp_vars['defe'][i] * player_vars[i] for i in player]) == n_defe, "4 DEF"
    prob += lpSum([lp_vars['mid'][i] * player_vars[i] for i in player]) == n_mid, "4 MID"
    prob += lpSum([lp_vars['stri'][i] * player_vars[i] for i in player]) == n_fwd, "2 STR"

    
    for t in TEAMS:
        prob += lpSum([lp_vars[t][i] * player_vars[i] for i in player]) <= 3, t

    
    return prob
# solve
prob = build_lp_team()
status = prob.solve()
print("Status:", LpStatus[prob.status])
# Extract which players were picked by the solver and mark in our dataframe
def extract_xi(dfo, prob = prob):  

    # can't write to slice
    dfo = dfo.copy()
    
    selection = {}
    for v in prob.variables():
        index = int(v.name.split("_")[1])
        selection[index] = v.varValue

    dfo['integer_programming'] = 0.0
    for i in selection:
        dfo.loc[i, 'integer_programming'] = selection[i]
    
    return dfo[dfo['integer_programming'] == 1.0]
# stats from the selected 11
def print_summary_xi(df_optimise, prob, points = '2018/19 pts', cost = 'Cost'):
    xi = extract_xi(df_optimise, prob)
    t_points = xi[points].sum()
    t_cost = xi[cost].sum()/10
    t_players = xi.shape[0]
    print("Total points:{}, cost:£{}, and with players:{}\n".format(t_points, t_cost, t_players))
    
    return xi
# permute team formations

# 442
prob = build_lp_team(n_gk = 1, n_defe = 4, n_mid = 4, n_fwd = 2)
status = prob.solve()
print("\n")
print("Status:", LpStatus[prob.status])
print("442 formation")
xi442 = print_summary_xi(df_optimise, prob)
display(xi442[['Player','Position', 'Team','2018/19 pts','Cost', 'Selected']]
     .sort_values(['Position'])
     .reset_index(drop=True))


# 433
prob = build_lp_team(n_gk = 1, n_defe = 4, n_mid = 3, n_fwd = 3)
status = prob.solve()

print("\n")
print("Status:", LpStatus[prob.status])
print("432 formation")
xi433 = print_summary_xi(df_optimise, prob)
display(xi433[['Player','Position', 'Team','2018/19 pts','Cost', 'Selected']]
     .sort_values(['Position'])
     .reset_index(drop=True))


# 451
prob = build_lp_team(n_gk = 1, n_defe = 4, n_mid = 5, n_fwd = 1)
status = prob.solve()
print("\n")
print("Status:", LpStatus[prob.status])
print("451 formation")
xi451 = print_summary_xi(df_optimise, prob)
display(xi451[['Player','Position', 'Team','2018/19 pts','Cost', 'Selected']]
     .sort_values(['Position'])
     .reset_index(drop=True))

# 352
prob = build_lp_team(n_gk = 1, n_defe = 3, n_mid = 5, n_fwd = 2)
status = prob.solve()
print("\n")
print("Status:", LpStatus[prob.status])
print("352 formation")
xi352 = print_summary_xi(df_optimise, prob)
display(xi352[['Player','Position', 'Team','2018/19 pts','Cost', 'Selected']]
     .sort_values(['Position'])
     .reset_index(drop=True))<jupyter_output>Status: Optimal
442 formation
Total points:2091.0, cost:£83.0, and with players:11<jupyter_text>ILP, pick a bench for 17M<jupyter_code># Typical bench
prob = build_lp_team(n_gk = 1, n_defe = 1, n_mid = 1, n_fwd = 1, tot_players = 4, tot_cost = 170)
status = prob.solve()
print("\n")
print("Status:", LpStatus[prob.status])
print("1111 bench")
iv1111 = print_summary_xi(df_optimise, prob)
display(iv1111[['Player','Position', 'Team','2018/19 pts','Cost', 'Selected']]
     .sort_values(['Position'])
     .reset_index(drop=True))

# Typical bench
prob = build_lp_team(n_gk = 1, n_defe = 2, n_mid = 0, n_fwd = 1, tot_players = 4, tot_cost = 170)
status = prob.solve()
print("\n")
print("Status:", LpStatus[prob.status])
print("1201 bench")
iv1201 = print_summary_xi(df_optimise, prob)
display(iv1201[['Player','Position', 'Team','2018/19 pts','Cost', 'Selected']]
     .sort_values(['Position'])
     .reset_index(drop=True))<jupyter_output>Status: Optimal
1111 bench
Total points:243.0, cost:£17.0, and with players:4<jupyter_text>2019 Current Season<jupyter_code>gw = 17
last = gw - 1

# reload players from local storage
df = pd.read_excel(r'Players_2019_2020.xlsx', sheet_name='Sheet1')
df.set_index('PlayerId', inplace=True)

base_cols = ['Position', 'Cost', 'Status', 'Team', 'Selected', '2018/19 pts']
pts_cols = [str(i) + ' pts' for i in range(1,gw)]
mins_cols = [str(i) + ' mins' for i in range(1,gw)]

# next 5 fixture difficulties
fds5_cols = [str(i) + ' fds' for i in range(0,5)]
ppg5_cols = [str(i) + ' pts' for i in range(gw-6,gw)]

val_col = str(last) + 'val'
df.columns.values
# compute total points 2019, total minutes 2019, 

df['COST'] = df['Cost']/10
df['PTS'] = df[pts_cols].sum(axis=1)
df['MINS'] = df[mins_cols].sum(axis=1)
df['FDS5'] = df[fds5_cols].sum(axis=1)
df['PPG5'] = df[ppg5_cols].sum(axis=1)/5

df['FDS5'].describe()
# compute points/game 2019/20, minutes/game 2019/20, points/million 2019/20
df['PPG'] = df['PTS']/last
df['MPG'] = df['MINS']/last
df['PPM'] = df['PTS']/df['COST']

# this is a fiddle to fix
df['ADJPPG5'] = df['PPG']/df['FDS5']*df['FDS5'].mean()

summary_cols = ['Player', 'Position', 'COST', 'Status', 'Team', 'Selected', 'PTS', 'MINS', 'PPG', 'PPG5', 'MPG', 'PPM', 'FDS5', 'ADJPPG5' ]
df[summary_cols].sort_values('ADJPPG5', ascending=False).head(10)
df_teams = df.groupby(['Team']).sum()
df_teams['Team Cost per Point 2019/20'] = df_teams['COST']/df_teams['PTS']
df_teams.sort_values('PTS', ascending=False).plot.bar(y=['COST', 'PTS'], figsize=(10,8), grid=True)
df_teams.sort_values('Team Cost per Point 2019/20', ascending=False)['Team Cost per Point 2019/20']
df_teams.sort_values('ADJPPG5', ascending=False).plot.bar(y=['PPG5', 'ADJPPG5'], figsize=(10,8), grid=True)
df[['PPG5','ADJPPG5']].describe()
# only consider players who have data from last season

def build_lp_vars_curr(dfo):  
    lp_vars = {}
    
    lp_vars['player'] = [str(i) for i in dfo.index.tolist()]
    lp_vars['point'] = {str(i): int(dfo.loc[i]['PTS']) for i in dfo.index.tolist() }
    lp_vars['cost'] = {str(i): int(dfo.loc[i]['Cost']) for i in dfo.index.tolist()} 
    lp_vars['mins'] = {str(i): int(dfo.loc[i]['MINS']) for i in dfo.index.tolist() }
    lp_vars['ppg'] = {str(i): int(dfo.loc[i]['PPG']) for i in dfo.index.tolist() }
    lp_vars['fds5'] = {str(i): int(dfo.loc[i]['FDS5']) for i in dfo.index.tolist() }
    lp_vars['adjppg5'] = {str(i): int(dfo.loc[i]['ADJPPG5']) for i in dfo.index.tolist() }
    lp_vars['ppg5'] = {str(i): int(dfo.loc[i]['PPG5']) for i in dfo.index.tolist() }

    lp_vars['gk'] = {str(i): 1 if str(dfo.loc[i]['Position']) == '1' else 0 for i in dfo.index.tolist()} 
    lp_vars['defe'] = {str(i): 1 if str(dfo.loc[i]['Position']) == '2' else 0 for i in dfo.index.tolist()}
    lp_vars['mid'] = {str(i): 1 if str(dfo.loc[i]['Position']) == '3' else 0 for i in dfo.index.tolist()}
    lp_vars['stri'] = {str(i): 1 if str(dfo.loc[i]['Position']) == '4' else 0 for i in dfo.index.tolist()}

    # constrain on max 3 per team ... there may be a prettier way to do this .. 
    lp_vars['ars'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'ARS' else 0 for i in dfo.index.tolist()}
    lp_vars['bha'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'BHA' else 0 for i in dfo.index.tolist()}
    lp_vars['bou'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'BOU' else 0 for i in dfo.index.tolist()}
    lp_vars['bur'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'BUR' else 0 for i in dfo.index.tolist()}
    lp_vars['che'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'CHE' else 0 for i in dfo.index.tolist()}
    lp_vars['cry'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'CRY' else 0 for i in dfo.index.tolist()}
    lp_vars['eve'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'EVE' else 0 for i in dfo.index.tolist()}
    lp_vars['lei'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'LEI' else 0 for i in dfo.index.tolist()}
    lp_vars['liv'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'LIV' else 0 for i in dfo.index.tolist()}
    lp_vars['mci'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'MCI' else 0 for i in dfo.index.tolist()}
    lp_vars['mun'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'MUN' else 0 for i in dfo.index.tolist()}
    lp_vars['new'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'NEW' else 0 for i in dfo.index.tolist()}
    lp_vars['sou'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'SOU' else 0 for i in dfo.index.tolist()}
    lp_vars['tot'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'TOT' else 0 for i in dfo.index.tolist()}
    lp_vars['wat'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'WAT' else 0 for i in dfo.index.tolist()}
    lp_vars['whu'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'WHU' else 0 for i in dfo.index.tolist()}
    lp_vars['wol'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'WOL' else 0 for i in dfo.index.tolist()}
    lp_vars['nor'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'NOR' else 0 for i in dfo.index.tolist()}
    lp_vars['shu'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'SHU' else 0 for i in dfo.index.tolist()}
    lp_vars['avl'] = {str(i): 1 if str(dfo.loc[i]['Team']) == 'AVL' else 0 for i in dfo.index.tolist()}
    lp_vars['xi'] = {str(i): 1 for i in dfo.index.tolist()}
    
    return lp_vars
    
# Players must have points score and be available for selection
df_optimise_curr = df[pd.notnull(df['PTS'])]
df_optimise_curr = df_optimise_curr[df_optimise_curr['Status'] == 'a']

LP_VAR_CURR = build_lp_vars_curr(df_optimise_curr)
# def build_lp_team(n_gk = 1, n_defe = 4, n_mid = 4, n_fwd = 2, tot_cost = 830, tot_players = 11, 
#               tgt = LP_VAR['point'], lp_vars = LP_VAR):

# permute team formations

cols = ['Player','Position', 'Team','PTS','COST', 'PPM', 'MPG', 'PPG', 'Selected', 'FDS5']
t = LP_VAR_CURR['adjppg5']
v = LP_VAR_CURR
tc = 840

# 442
prob = build_lp_team(n_gk = 1, n_defe = 4, n_mid = 4, n_fwd = 2, tgt = t, tot_cost = tc, lp_vars = LP_VAR_CURR)
status = prob.solve()
print("\n")
print("Status:", LpStatus[prob.status])
print("442 formation")
xi442 = print_summary_xi(df_optimise_curr, prob, points = 'PTS')
display(xi442[cols].sort_values(['Position']).reset_index(drop=True))


# 433
prob = build_lp_team(n_gk = 1, n_defe = 4, n_mid = 3, n_fwd = 3, tgt = t, tot_cost = tc, lp_vars = v)
status = prob.solve()

print("\n")
print("Status:", LpStatus[prob.status])
print("433 formation")
xi433 = print_summary_xi(df_optimise_curr, prob, points='PTS')
display(xi433[cols].sort_values(['Position']).reset_index(drop=True))


# 451
prob = build_lp_team(n_gk = 1, n_defe = 4, n_mid = 5, n_fwd = 1, tgt = t, tot_cost = tc, lp_vars = v)
status = prob.solve()
print("\n")
print("Status:", LpStatus[prob.status])
print("451 formation")
xi451 = print_summary_xi(df_optimise_curr, prob, points='PTS')
display(xi451[cols].sort_values(['Position']).reset_index(drop=True))

# 352
prob = build_lp_team(n_gk = 1, n_defe = 3, n_mid = 5, n_fwd = 2, tgt = t, tot_cost = tc, lp_vars = v)
status = prob.solve()
print("\n")
print("Status:", LpStatus[prob.status])
print("352 formation")
xi352 = print_summary_xi(df_optimise_curr, prob, points = 'PTS')
display(xi352[cols].sort_values(['Position']).reset_index(drop=True))
# Typical bench
prob = build_lp_team(n_gk = 1, n_defe = 1, n_mid = 1, n_fwd = 1, tot_players = 4, tot_cost = 196, tgt = LP_VAR_CURR['adjppg5'], lp_vars = LP_VAR_CURR)
status = prob.solve()
print("\n")
print("Status:", LpStatus[prob.status])
print("1111 bench")
iv1111 = print_summary_xi(df_optimise_curr, prob)
display(iv1111[cols].sort_values(['Position']).reset_index(drop=True))

# Typical bench
prob = build_lp_team(n_gk = 1, n_defe = 2, n_mid = 0, n_fwd = 1, tot_players = 4, tot_cost = 196, tgt = LP_VAR_CURR['adjppg5'], lp_vars = LP_VAR_CURR)
status = prob.solve()
print("\n")
print("Status:", LpStatus[prob.status])
print("1201 bench")
iv1201 = print_summary_xi(df_optimise_curr, prob)
display(iv1201[cols].sort_values(['Position']).reset_index(drop=True))

# Typical bench
prob = build_lp_team(n_gk = 1, n_defe = 1, n_mid = 2, n_fwd = 0, tot_players = 4, tot_cost = 196, tgt = LP_VAR_CURR['adjppg5'], lp_vars = LP_VAR_CURR)
status = prob.solve()
print("\n")
print("Status:", LpStatus[prob.status])
print("1120 bench")
iv1120 = print_summary_xi(df_optimise_curr, prob)
display(iv1120[cols].sort_values(['Position']).reset_index(drop=True))
# Quick analysis of my team

picks = ['Henderson', 
         'Kelly', 'Söyüncü', 'Lundstram', 'Baldock',
         '<NAME>', 'Mané', 'Maddison',
         'Rashford', 'Ings', 'Vardy']

df_pcks = df[df['Player'].isin(picks)]
df_pcks.head(11)
df_pcks[['PTS','PPG', 'MINS', 'PPG5', 'ADJPPG5']].describe()
df_pcks[['PPG', 'PPG5', 'ADJPPG5']].sum()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.2045532157085942, 'fraction_numerical': 0.03614114968696642, 'mean_word_length': 4.682729624838292, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 22, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '599439', 'license': 'MIT', 'n_tokens_mistral': 7649, 'n_tokens_neox': 7172, 'n_words': 1690, 'path': 'FPL Data 2019.ipynb', 'repo_name': 'linkmic/fantasy', 'size': '17573'}