-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathCompPickValueDetermination.py
195 lines (160 loc) · 8.13 KB
/
CompPickValueDetermination.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
import pandas as pd
import numpy as np
# Your File Paths
file_path = 'Files/Madden24/IE/Season7/Player.xlsx'
all_pros_path = 'Files/Madden24/IE/Season7/AllPros.xlsx'
all_xlsm_path = 'Files/Madden24/IE/Season7/All.xlsm'
# Specify the current season year
current_season_year = 4 ####### Change this to the correct value #######
def calculate_number_value_ranking(file_path, all_pros_path, all_xlsm_path):
"""
Calculate number value rankings for players based on conditions.
Args:
- file_path (str): The path to the "Player.xlsx" file.
- all_pros_path (str): The path to the "AllPros.xlsx" file.
- all_xlsm_path (str): The path to the "All.xlsm" file.
Returns:
- pd.DataFrame: The DataFrame with updated "APY," "AwardedPoints," "TotalPoints," and "DOWNSPLAYED" columns.
"""
# Read player data from the Excel file
df = pd.read_excel(file_path)
# Read All Pros data from the Excel file
all_pros_df = pd.read_excel(all_pros_path)
# Read All.xlsm and extract the necessary information
all_xlsm = pd.read_excel(all_xlsm_path, sheet_name=None)
def calculate_downs_played(row):
position = row['Position']
first_name = row['FirstName']
last_name = row['LastName']
# Determine the sheet name based on the player's position
if position in ['QB', 'RB', 'HB', 'FB', 'WR', 'TE']:
sheet_name = 'Offensive Stats'
elif position in ['LE', 'RE', 'DT', 'LOLB', 'ROLB', 'MLB', 'CB', 'FS', 'SS']:
sheet_name = 'Defensive Stats'
elif position in ['LT', 'LG', 'C', 'RG', 'RT']:
sheet_name = 'OLine Stats'
else:
return None # Return None for unsupported positions
# Check if the SEAS_YEAR column matches the current season year
if sheet_name in all_xlsm and 'SEAS_YEAR' in all_xlsm[sheet_name]:
season_year = all_xlsm[sheet_name]['SEAS_YEAR'].max()
if season_year == current_season_year:
# Search for the player in the corresponding sheet of All.xlsm
matching_player = all_xlsm[sheet_name][(all_xlsm[sheet_name]['FirstName'] == first_name) & (all_xlsm[sheet_name]['LastName'] == last_name) & (all_xlsm[sheet_name]['Position'] == position)]
# Ensure the player is from the correct season year
matching_player = matching_player[matching_player['SEAS_YEAR'] == current_season_year]
if not matching_player.empty:
downs_played = matching_player[matching_player['SEAS_YEAR'] == current_season_year]['DOWNSPLAYED'].max()
if pd.isna(downs_played):
downs_played = 0
return downs_played
else:
return 0
return None # Return None if player not found or SEAS_YEAR doesn't match
def calculate_snap_points(row):
position = row['Position']
downs_played = row['DOWNSPLAYED']
# Define the position-dependent snap values
position_values = {
'QB': 1100,
'LE': 1100,
'RE': 1100,
'LOLB': 1100,
'ROLB': 1100,
'RB': 1100,
'HB': 1100,
'DT': 1100,
'WR': 1100,
'LT': 1100,
'LG': 1100,
'RG': 1100,
'C': 1100,
'RT': 1100,
'MLB': 1100,
'CB': 1100,
'TE': 1100,
'FS': 1100,
'SS': 1100,
}
if position in position_values and pd.notna(downs_played): # Check if downs_played is not NaN:
position_value = position_values[position]
snap_points = round((downs_played * 100) / position_value)
if snap_points > 100:
snap_points = 100
elif snap_points < 25:
snap_points = 0
elif downs_played > position_value:
snap_points = 100 # Update SnapPoints to 100 if DOWNSPLAYED > position_value
return snap_points
return 0 # Default to 0 SnapPoints if conditions are not met
# Calculate DownsPlayed and add it as a new column
df['DOWNSPLAYED'] = df.apply(calculate_downs_played, axis=1)
# Calculate SnapPoints and add it as a new column
df['SnapPoints'] = df.apply(calculate_snap_points, axis=1)
# Define conditions for awarding number value rankings
def calculate_rank(row):
if row['ContractStatus'] in ['Signed', 'Expiring']:
# Sum up contract salaries and bonuses and divide by contract length
total_contract_value = (
row['ContractSalary0'] + row['ContractBonus0'] +
row['ContractSalary1'] + row['ContractBonus1'] +
row['ContractSalary2'] + row['ContractBonus2'] +
row['ContractSalary3'] + row['ContractBonus3'] +
row['ContractSalary4'] + row['ContractBonus4'] +
row['ContractSalary5'] + row['ContractBonus5'] +
row['ContractSalary6'] + row['ContractBonus6'] +
row['ContractSalary7'] + row['ContractBonus7']
)
contract_length = min(row['ContractLength'], sum(1 for salary in range(8) if row[f'ContractSalary{salary}'] != 0))
if contract_length > 0:
ranking = total_contract_value / contract_length
else:
ranking = 0 # To avoid division by zero
else:
ranking = None # Players with other contract statuses get a None value
return ranking
# Create a new column 'AAV' using apply function
df['AAV'] = df.apply(lambda row: calculate_rank(row), axis=1)
# Calculate number value rankings for each player
df['NumberValueRanking'] = df.apply(calculate_rank, axis=1)
# Filter out players with None values (other contract statuses)
df = df.dropna(subset=['NumberValueRanking'])
# Perform min-max scaling to get rankings between 1 and the number of players
min_rank = 1
max_rank = len(df)
df['NumberValueRanking'] = (df['NumberValueRanking'] - df['NumberValueRanking'].min()) / (df['NumberValueRanking'].max() - df['NumberValueRanking'].min()) * (max_rank - min_rank) + min_rank
# Rank the DataFrame based on NumberValueRanking
df['APY'] = df['NumberValueRanking'].rank(method='first')
# Function to update the "APY" and add "AwardedPoints" column
def update_rank(row):
awarded_points = 0
matching_player = all_pros_df[(all_pros_df['FirstName'] == row['FirstName']) & (all_pros_df['LastName'] == row['LastName']) & (all_pros_df['Position'] == row['Position'])]
if not matching_player.empty:
if matching_player['1st Team'].values[0] == 'Y':
awarded_points = 20
elif matching_player['2nd Team'].values[0] == 'Y':
awarded_points = 5
row['AwardedPoints'] = awarded_points
return row
# Update the "APY" and add "AwardedPoints" column
df = df.apply(update_rank, axis=1)
# Calculate the "TotalPoints" as the sum of "APY" and "AwardedPoints"
df['TotalPoints'] = df['APY'] + df['AwardedPoints'] + df['SnapPoints']
# Calculate percentiles for "TotalPoints" to determine "CompPickValue"
percentiles = df['TotalPoints'].rank(pct=True)
df['CompPickValue'] = pd.cut(
percentiles,
bins=[0.0, 0.65, 0.75, 0.85, 0.90, 0.95, 1.0],
labels=['None', 7, 6, 5, 4, 3]
)
# Sort the DataFrame based on NumberValueRanking, LastName, and FirstName
df = df.sort_values(by=['TotalPoints'], ascending=[False])
return df
# Calculate number value rankings for players and update columns
result_df = calculate_number_value_ranking(file_path, all_pros_path, all_xlsm_path)
# After sorting the DataFrame based on "TotalPoints," add the following code to calculate "CompRank":
# Calculate "CompRank" based on "TotalPoints" in descending order (higher TotalPoints get lower ranks)
result_df['CompRank'] = result_df['TotalPoints'].rank(method='min', ascending=False).astype(int)
# Export the resulting DataFrame to an Excel document
output_filename = 'CompPickPlayerValue.xlsx'
result_df.to_excel('Files/Madden24/IE/Season7/CompPickPlayerValue.xlsx', index=False)