456 lines
14 KiB
Python
456 lines
14 KiB
Python
from pathlib import Path
|
|
from typing import Optional
|
|
import tbaapiv3client
|
|
from tbaapiv3client.rest import ApiException
|
|
import statbotics
|
|
from pprint import pprint
|
|
from datetime import datetime as dt
|
|
|
|
from openpyxl import Workbook, worksheet, load_workbook
|
|
from openpyxl.worksheet.table import Table, TableStyleInfo
|
|
|
|
from csv import DictWriter
|
|
|
|
# Setup Config
|
|
config = tbaapiv3client.Configuration(
|
|
host='https://www.thebluealliance.com/api/v3',
|
|
api_key={
|
|
'X-TBA-Auth-Key': '7SvEHOaVwj3HXwnSyxLatIDWBMN3QPPvhJxzQ7m9DnuD94OzLUu0yp56YguLa1KF'
|
|
})
|
|
|
|
config.verify_ssl = False
|
|
|
|
district = 'fim'
|
|
|
|
|
|
def get_rankings(api_client: tbaapiv3client.ApiClient, seasons: list[int]) -> dict[dict]:
|
|
# Create an instance of the API class
|
|
api_instance = tbaapiv3client.DistrictApi(api_client)
|
|
|
|
rankings = {}
|
|
|
|
for season in seasons:
|
|
year = int(season[:4])
|
|
try:
|
|
results = api_instance.get_district_rankings(season)
|
|
except ApiException as e:
|
|
print("Exception when calling DistrictApi->get_district_rankings: %s\n" % e)
|
|
else:
|
|
|
|
epa_list:dict[int] = {}
|
|
|
|
try:
|
|
sb = statbotics.Statbotics()
|
|
sb.session.verify = False
|
|
sb_results = sb.get_team_years(year=year,district=district, limit=1000, fields=['team', 'epa_end'])
|
|
except Exception as e:
|
|
print("Exception when calling Statbotics DistrictApi->get_district_rankings: %s\n" % e)
|
|
else:
|
|
for sb_result in sb_results:
|
|
epa_list[sb_result['team']] = sb_result['epa_end']
|
|
|
|
for result in results:
|
|
team = result.team_key
|
|
team_num = int(team[3:])
|
|
|
|
if team not in rankings:
|
|
rankings[team] = {}
|
|
|
|
|
|
epa = ''
|
|
|
|
if team_num in epa_list:
|
|
epa = epa_list[team_num]
|
|
|
|
|
|
points_event1 = None
|
|
points_event1_na = None
|
|
points_event2 = None
|
|
points_event2_na = None
|
|
|
|
if len(result.event_points) > 0:
|
|
event = result.event_points[0]
|
|
points_event1 = event.total
|
|
points_event1_na = event.qual_points + event.elim_points + event.alliance_points
|
|
|
|
if len(result.event_points) > 1:
|
|
event = result.event_points[1]
|
|
points_event2 = event.total
|
|
points_event2_na = event.qual_points + event.elim_points + event.alliance_points
|
|
|
|
rankings[team][season] = {
|
|
"rank": result.rank,
|
|
"point_total": result.point_total,
|
|
"epa_end": epa,
|
|
"points_event1": points_event1,
|
|
"points_event2": points_event2,
|
|
"points_event1_na": points_event1_na,
|
|
"points_event2_na": points_event2_na,
|
|
|
|
}
|
|
|
|
|
|
return rankings
|
|
|
|
|
|
def get_event_teams(api_client: tbaapiv3client.ApiClient, season: str) -> dict[dict]:
|
|
|
|
api_district = tbaapiv3client.DistrictApi(api_client)
|
|
api_event = tbaapiv3client.EventApi(api_client)
|
|
|
|
try:
|
|
district_results = api_district.get_district_events(season)
|
|
|
|
except ApiException as e:
|
|
print("Exception when calling EventApi->get_district_events: %s\n" % e)
|
|
else:
|
|
event_teams: dict = {}
|
|
|
|
for district_result in district_results:
|
|
key = district_result.key
|
|
name = district_result.name
|
|
if 'cmp' not in key:
|
|
try:
|
|
team_results = api_event.get_event_teams_keys(key)
|
|
|
|
except ApiException as e:
|
|
print(
|
|
"Exception when calling EventApi->get_event_teams_keys: %s\n" % e)
|
|
else:
|
|
event_teams[key] = {
|
|
'key': key,
|
|
'name': name,
|
|
'teams': team_results,
|
|
'start date': district_result.start_date,
|
|
'week': district_result.start_date.isocalendar()[1] - 8
|
|
}
|
|
|
|
return event_teams
|
|
|
|
|
|
def write_power_rank(path: str | Path, sheet_name: str, rankings: list[dict], seasons: list[str]) -> None:
|
|
# Create annual rank CSV
|
|
results_list: list[dict] = []
|
|
|
|
for team, ranks in rankings.items():
|
|
result = {
|
|
'team': team
|
|
}
|
|
|
|
rank_sum = 0
|
|
point_total_sum = 0
|
|
epa_sum = 0
|
|
point_district_sum = 0
|
|
point_district_na_sum = 0
|
|
|
|
entry_count = 0
|
|
|
|
for season in seasons:
|
|
rank = ''
|
|
point_total = ''
|
|
epa = ''
|
|
point_district = ''
|
|
point_district_na = ''
|
|
|
|
if season in ranks:
|
|
rank = ranks[season]['rank']
|
|
point_total = ranks[season]['point_total']
|
|
epa = ranks[season]['epa_end']
|
|
|
|
point_district = 0
|
|
|
|
if ranks[season]['points_event1'] is not None:
|
|
point_district += ranks[season]['points_event1']
|
|
|
|
if ranks[season]['points_event2'] is not None:
|
|
point_district += ranks[season]['points_event2']
|
|
|
|
point_district_na = 0
|
|
|
|
if ranks[season]['points_event1_na'] is not None:
|
|
point_district_na += ranks[season]['points_event1_na']
|
|
|
|
if ranks[season]['points_event2_na'] is not None:
|
|
point_district_na += ranks[season]['points_event2_na']
|
|
|
|
rank_sum += rank
|
|
point_total_sum += point_total
|
|
point_district_sum += point_district
|
|
point_district_na_sum += point_district_na
|
|
|
|
if epa != '':
|
|
epa_sum += epa
|
|
|
|
entry_count += 1
|
|
|
|
result[f'{season} Rank'] = rank
|
|
result[f'{season} Point Total'] = point_total
|
|
result[f'{season} EPA'] = epa
|
|
result[f'{season} Points District'] = point_district
|
|
result[f'{season} Points District No Awards'] = point_district_na
|
|
|
|
rank_avg = ''
|
|
point_total_avg = ''
|
|
epa_avg = ''
|
|
point_district_avg = ''
|
|
point_district_na_avg = ''
|
|
|
|
if entry_count > 0:
|
|
rank_avg = rank_sum / entry_count
|
|
point_total_avg = point_total_sum / entry_count
|
|
epa_avg = epa_sum / entry_count
|
|
point_district_avg = point_district_sum / entry_count
|
|
point_district_na_avg = point_district_na_sum / entry_count
|
|
|
|
result[f'Avg Rank'] = rank_avg
|
|
result[f'Avg Point Total'] = point_total_avg
|
|
result[f'Avg EPA'] = epa_avg
|
|
result[f'Avg Points District'] = point_district_avg
|
|
result[f'Avg Points District No Awards'] = point_district_na_avg
|
|
|
|
results_list.append(result)
|
|
|
|
headers = ['team']
|
|
prefix_list = seasons.copy()
|
|
prefix_list.append('Avg')
|
|
suffix_list = ['Rank', 'Point Total', 'EPA',
|
|
'Points District', 'Points District No Awards']
|
|
|
|
for suffix in suffix_list:
|
|
for prefix in prefix_list:
|
|
headers.append(f'{prefix} {suffix}')
|
|
|
|
write_result(path, results_list, sheet_name, headers=headers)
|
|
|
|
|
|
def write_event_summary(path: str | Path, seasons: list[str], event_teams: dict[dict], rankings: dict[dict]) -> None:
|
|
event_summary: list[dict] = []
|
|
|
|
for key, event in event_teams.items():
|
|
rank_sum = 0
|
|
point_total_sum = 0
|
|
epa_sum = 0
|
|
point_district_sum = 0
|
|
point_district_na_sum = 0
|
|
|
|
entry_count = 0
|
|
|
|
event_rankings: dict[dict] = {}
|
|
|
|
for team in event['teams']:
|
|
if team in rankings:
|
|
event_rankings[team] = rankings[team]
|
|
else:
|
|
event_rankings[team] = {}
|
|
|
|
if team in rankings:
|
|
for season in seasons:
|
|
if season in rankings[team]:
|
|
team_season = rankings[team][season]
|
|
|
|
rank_sum += team_season['rank']
|
|
point_total_sum += team_season['point_total']
|
|
|
|
if team_season['epa_end'] != '':
|
|
epa_sum += team_season['epa_end']
|
|
|
|
if team_season['points_event1'] is not None:
|
|
point_district_sum += team_season['points_event1']
|
|
|
|
if team_season['points_event2'] is not None:
|
|
point_district_sum += team_season['points_event2']
|
|
|
|
if team_season['points_event1_na'] is not None:
|
|
point_district_na_sum += team_season['points_event1_na']
|
|
|
|
if team_season['points_event2_na'] is not None:
|
|
point_district_na_sum += team_season['points_event2_na']
|
|
|
|
entry_count += 1
|
|
|
|
write_power_rank(path, event['key'], event_rankings, seasons)
|
|
|
|
# Generate Event Summary
|
|
rank_avg = ''
|
|
point_total_avg = ''
|
|
epa_avg = ''
|
|
point_district_avg = ''
|
|
point_district_na_avg = ''
|
|
|
|
if entry_count > 0:
|
|
rank_avg = rank_sum / entry_count
|
|
point_total_avg = point_total_sum / entry_count
|
|
epa_avg = epa_sum / entry_count
|
|
point_district_avg = point_district_sum / entry_count
|
|
point_district_na_avg = point_district_na_sum / entry_count
|
|
|
|
event_summary.append({
|
|
'key': key,
|
|
'name': event['name'],
|
|
'start date': event['start date'],
|
|
'week': event['week'],
|
|
'average rank': rank_avg,
|
|
'average point total': point_total_avg,
|
|
'average EPA': epa_avg,
|
|
'average point district': point_district_avg,
|
|
'average point district no awards': point_district_na_avg,
|
|
'entries': entry_count,
|
|
'team count': len(event['teams'])
|
|
})
|
|
|
|
# Write Event Summary
|
|
write_result(path, event_summary, "Summary", 0)
|
|
|
|
|
|
def write_rank(path: str | Path, rankings: list[dict], seasons: list[str]) -> None:
|
|
# Create annual rank CSV
|
|
results_list: list[dict] = []
|
|
|
|
for team, ranks in rankings.items():
|
|
result = {
|
|
'team': team
|
|
}
|
|
|
|
for season in seasons:
|
|
value = ''
|
|
if season in ranks:
|
|
value = ranks[season]['rank']
|
|
|
|
result[season] = value
|
|
|
|
results_list.append(result)
|
|
|
|
write_result(path, results_list)
|
|
|
|
|
|
def write_point_total(path: str | Path, rankings: list[dict], seasons: list[str]) -> None:
|
|
# Create annual rank CSV
|
|
results_list: list[dict] = []
|
|
|
|
for team, ranks in rankings.items():
|
|
result = {
|
|
'team': team
|
|
}
|
|
|
|
for season in seasons:
|
|
value = ''
|
|
if season in ranks:
|
|
value = ranks[season]['point_total']
|
|
|
|
result[season] = value
|
|
|
|
results_list.append(result)
|
|
|
|
write_result(path, results_list)
|
|
|
|
|
|
def write_points_events(path: str | Path, rankings: list[dict], seasons: list[str]) -> None:
|
|
# Create annual rank CSV
|
|
results_list: list[dict] = []
|
|
|
|
for team, ranks in rankings.items():
|
|
result = {
|
|
'team': team
|
|
}
|
|
|
|
for season in seasons:
|
|
value = ''
|
|
if season in ranks:
|
|
value = 0
|
|
|
|
if ranks[season]['points_event1'] is not None:
|
|
value += ranks[season]['points_event1']
|
|
|
|
if ranks[season]['points_event2'] is not None:
|
|
value += ranks[season]['points_event2']
|
|
|
|
result[season] = value
|
|
|
|
results_list.append(result)
|
|
|
|
write_result(path, results_list)
|
|
|
|
|
|
def write_result(path: str | Path, results_list: list[dict], sheet_name: Optional[str] = None, sheet_index: Optional[int] = None, headers: Optional[list[str]] = None) -> None:
|
|
# Convert path to Path type if it isn't already
|
|
if not isinstance(path, Path):
|
|
path = Path(path)
|
|
|
|
# Get headers
|
|
if headers is None:
|
|
headers = list(results_list[0].keys())
|
|
|
|
# Open workbook
|
|
wb: Workbook = None
|
|
ws = None
|
|
if path.exists():
|
|
wb = load_workbook(path)
|
|
|
|
if sheet_name is None or sheet_name in wb.sheetnames:
|
|
wb.remove(wb[sheet_name])
|
|
|
|
ws = wb.create_sheet(sheet_name, sheet_index)
|
|
else:
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
|
|
if sheet_name is not None:
|
|
ws.title = sheet_name
|
|
|
|
# Write Header
|
|
ws.append(headers)
|
|
|
|
# Write Results
|
|
|
|
for row in results_list:
|
|
values = []
|
|
|
|
for header in headers:
|
|
if header in row:
|
|
values.append(row[header])
|
|
else:
|
|
values.append(None)
|
|
|
|
ws.append(values)
|
|
|
|
# Enable Filter
|
|
ws.auto_filter.ref = ws.dimensions
|
|
|
|
# Save Workbook
|
|
wb.save(path)
|
|
|
|
|
|
def main():
|
|
# Initialize system
|
|
run_time = dt.now()
|
|
run_time_str = run_time.strftime('%Y%m%d_%H%M')
|
|
|
|
skip_years = [2020, 2021]
|
|
|
|
seasons = [f'{year}{district}' for year in range(
|
|
2022, 2025) if year not in skip_years]
|
|
|
|
root_dir = Path('results')
|
|
|
|
# Get Statistics
|
|
with tbaapiv3client.ApiClient(config) as api_client:
|
|
print('Getting Team Rankings')
|
|
rankings = get_rankings(api_client, seasons)
|
|
|
|
print('Getting event team lists')
|
|
event_teams = get_event_teams(api_client, '2025fim')
|
|
|
|
# Write Event Summary
|
|
print('Writing Event Summary')
|
|
write_event_summary(
|
|
root_dir / f'{run_time_str} - Event Details.xlsx', seasons, event_teams, rankings)
|
|
|
|
# Write Power Rankings
|
|
print('Writing Power Ranking')
|
|
write_power_rank(
|
|
root_dir / f"Power Ranking.xlsx", 'Power Ranking', rankings, seasons)
|
|
|
|
|
|
if __name__ == '__main__':
|
|
main()
|