897 lines
27 KiB
Python
897 lines
27 KiB
Python
# %%
|
|
|
|
from pulp import (
|
|
LpVariable,
|
|
LpProblem,
|
|
LpMinimize,
|
|
lpSum,
|
|
LpStatus,
|
|
value,
|
|
LpInteger,
|
|
LpContinuous,
|
|
XPRESS,
|
|
)
|
|
|
|
import googlemaps
|
|
|
|
from gmplot import GoogleMapPlotter
|
|
import json
|
|
import pandas as pd
|
|
import ast
|
|
import random
|
|
import itertools
|
|
import time
|
|
|
|
|
|
# %%
|
|
def convert_xlsx_to_json():
|
|
gmaps = googlemaps.Client(key="AIzaSyB76EhR4OqjdXHQUiTkHZC0Svx_7cPGqyU")
|
|
|
|
staffel = None
|
|
|
|
dresden = pd.read_excel("data/beispiel_daten_sachsen.xlsx")
|
|
# staffeln = dresden["STAFFEL"].unique()
|
|
|
|
unique_staffeln = list(
|
|
dresden[["MS_ART", "SP_KLASSE", "STAFFEL"]]
|
|
.drop_duplicates()
|
|
.itertuples(index=False, name=None)
|
|
)
|
|
competitions_dict = {s: {} for s in unique_staffeln}
|
|
teams_in_competition = {}
|
|
staffel_type = None
|
|
for art, klasse, staffel in unique_staffeln:
|
|
find_duplicates = []
|
|
teams_in_competition[(art, klasse, staffel)] = []
|
|
teams = dresden[
|
|
(dresden["MS_ART"] == art)
|
|
& (dresden["SP_KLASSE"] == klasse)
|
|
& (dresden["STAFFEL"] == staffel)
|
|
][
|
|
[
|
|
"GEB_VEREIN",
|
|
"GEB_MS",
|
|
"MANNSCHAFT",
|
|
"MS_KEY",
|
|
"MS_ART",
|
|
"SP_KLASSE",
|
|
"STAFFEL",
|
|
"SCHLUESSEL_ZAHL",
|
|
"WUNSCH_WOCHENTAG",
|
|
"WUNSCH_TAG",
|
|
"WUNSCH_ZEIT",
|
|
"SPIELSTAETTE",
|
|
]
|
|
].to_dict(
|
|
orient="records"
|
|
)
|
|
|
|
for t in teams:
|
|
if t["MANNSCHAFT"] not in find_duplicates:
|
|
teams_in_competition[(art, klasse, staffel)].append(t)
|
|
find_duplicates.append(t["MANNSCHAFT"])
|
|
geocode_result = gmaps.geocode(
|
|
f"{t['GEB_VEREIN']} {t['MANNSCHAFT']} {t['SPIELSTAETTE']}"
|
|
)
|
|
latitude = 0
|
|
longitude = 0
|
|
if len(geocode_result) > 0:
|
|
location = geocode_result[0]["geometry"]["location"]
|
|
latitude = location["lat"]
|
|
longitude = location["lng"]
|
|
|
|
t["LATITUDE"] = latitude
|
|
t["LONGITUDE"] = longitude
|
|
|
|
competitions_dict[(art, klasse, staffel)]["teams"] = teams_in_competition[
|
|
(art, klasse, staffel)
|
|
]
|
|
competitions_dict[(art, klasse, staffel)]["nTeams"] = len(
|
|
teams_in_competition[(art, klasse, staffel)]
|
|
)
|
|
competitions_dict[(art, klasse, staffel)]["art"] = art
|
|
competitions_dict[(art, klasse, staffel)]["klasse"] = klasse
|
|
competitions_dict[(art, klasse, staffel)]["staffel"] = staffel
|
|
|
|
competitions_dict_list_keys = {str(k): v for k, v in competitions_dict.items()}
|
|
|
|
with open("data/sachsen.json", "w", encoding="utf-8") as f:
|
|
json.dump(
|
|
competitions_dict_list_keys, f, ensure_ascii=False, indent=4, default=str
|
|
)
|
|
|
|
|
|
# %%
|
|
|
|
|
|
from math import sqrt, sin, cos, atan2, pi
|
|
|
|
|
|
def degreesToRadians(degrees):
|
|
"""Convert degrees to radians"""
|
|
return degrees * pi / 180
|
|
|
|
|
|
def distanceInKmByGPS(lat1, lon1, lat2, lon2):
|
|
"""Calculate the distance between two points in km"""
|
|
earthRadiusKm = 6371
|
|
dLat = degreesToRadians(lat2 - lat1)
|
|
dLon = degreesToRadians(lon2 - lon1)
|
|
lat1 = degreesToRadians(lat1)
|
|
lat2 = degreesToRadians(lat2)
|
|
a = sin(dLat / 2) * sin(dLat / 2) + sin(dLon / 2) * sin(dLon / 2) * cos(lat1) * cos(
|
|
lat2
|
|
)
|
|
c = 2 * atan2(sqrt(a), sqrt(1 - a))
|
|
return int(earthRadiusKm * c)
|
|
|
|
|
|
for metric in ["road_distance", "road_duration", "flight_distance"]:
|
|
print("\n\n#######################################################")
|
|
print(f"Calculating {metric}")
|
|
# %%
|
|
""" read csv and skip first row """
|
|
distance_dict = {}
|
|
with open("data/distances.csv", "r", encoding="utf-8") as f:
|
|
csv_distances = f.readlines()
|
|
for i, row in enumerate(csv_distances):
|
|
if i == 0:
|
|
continue
|
|
_, _, team1, team2, road_distance, road_duration, flight_distance = (
|
|
row.split(",")
|
|
)
|
|
distance_dict[(team1, team2)] = {
|
|
"road_distance": float(road_distance),
|
|
"road_duration": float(road_duration),
|
|
"flight_distance": float(flight_distance),
|
|
}
|
|
|
|
# %%
|
|
|
|
with open("data/sachsen.json", "r", encoding="utf-8") as f:
|
|
competitions = json.load(f)
|
|
|
|
competitions = {ast.literal_eval(k): v for k, v in competitions.items()}
|
|
|
|
# region
|
|
# STAFFELN PRO ART UND KLASSE
|
|
# ('Herren', 'Landesliga') 1
|
|
# ('Herren', 'Landesklasse') 3
|
|
# ('Frauen', 'Landesliga') 1
|
|
# ('Frauen', 'Landesklasse') 3
|
|
# ('A-Junioren', 'Landesliga') 1
|
|
# ('A-Junioren', 'Landesklasse') 4
|
|
# ('Herren', 'Kreisoberliga') 13
|
|
# ('Herren', '1.Kreisliga (A)') 19
|
|
# ('Herren', '2.Kreisliga (B)') 8
|
|
# ('Herren', '3.Kreisliga (C)') 1
|
|
# ('Herren', '1.Kreisklasse') 21
|
|
# ('Herren', '2.Kreisklasse') 9
|
|
# ('A-Junioren', 'Kreisoberliga') 10
|
|
# ('A-Junioren', '1.Kreisliga (A)') 6
|
|
# ('Frauen', 'Kreisoberliga') 4
|
|
# ('Frauen', '1.Kreisliga (A)') 1
|
|
# ('Frauen', '1.Kreisklasse') 3
|
|
# ('B-Junioren', 'Landesliga') 1
|
|
# ('B-Junioren', 'Landesklasse') 4
|
|
# ('B-Junioren', 'Kreisoberliga') 13
|
|
# ('B-Junioren', '1.Kreisliga (A)') 13
|
|
# ('B-Junioren', '1.Kreisklasse') 1
|
|
# ('C-Junioren', 'Landesliga') 1
|
|
# ('C-Junioren', 'Landesklasse') 4
|
|
# ('C-Junioren', 'Kreisoberliga') 16
|
|
# ('C-Junioren', '1.Kreisliga (A)') 15
|
|
# ('C-Junioren', '1.Kreisklasse') 9
|
|
# ('D-Junioren', 'Landesliga') 1
|
|
# ('D-Junioren', 'Landesklasse') 6
|
|
# ('D-Junioren', 'Kreisoberliga') 16
|
|
# ('D-Junioren', '1.Kreisliga (A)') 24
|
|
# ('D-Junioren', '2.Kreisliga (B)') 8
|
|
# ('D-Junioren', '3.Kreisliga (C)') 2
|
|
# ('D-Junioren', '1.Kreisklasse') 33
|
|
# ('D-Junioren', '2.Kreisklasse') 10
|
|
# ('B-Juniorinnen', 'Landesliga') 1
|
|
# ('B-Juniorinnen', 'Landesklasse') 2
|
|
# ('C-Juniorinnen', 'Landesklasse') 3
|
|
# ('D-Juniorinnen', 'Kreisoberliga') 1
|
|
# ('Herren Ü35', 'Kreisoberliga') 4
|
|
# ('Herren Ü35', '1.Kreisliga (A)') 3
|
|
# ('Herren Ü35', '1.Kreisklasse') 3
|
|
# ('Herren Ü35', '2.Kreisklasse') 1
|
|
# ('Herren Ü40', '1.Kreisliga (A)') 5
|
|
# ('Herren Ü40', '1.Kreisklasse') 1
|
|
# ('Herren Ü50', '1.Kreisliga (A)') 1
|
|
# ('Herren Ü50', '1.Kreisklasse') 1
|
|
# ('Freizeitsport', '1.Kreisliga (A)') 3
|
|
# ('Freizeitsport', '1.Kreisklasse') 2
|
|
# endregion
|
|
|
|
some_colors = [
|
|
"red",
|
|
"blue",
|
|
"green",
|
|
"yellow",
|
|
"purple",
|
|
"orange",
|
|
"pink",
|
|
"brown",
|
|
"black",
|
|
"white",
|
|
"gray",
|
|
"cyan",
|
|
"magenta",
|
|
"lime",
|
|
"indigo",
|
|
"violet",
|
|
"turquoise",
|
|
"gold",
|
|
"silver",
|
|
"beige",
|
|
"maroon",
|
|
"olive",
|
|
"navy",
|
|
"teal",
|
|
"coral",
|
|
"lavender",
|
|
"salmon",
|
|
"chocolate",
|
|
"crimson",
|
|
"aqua",
|
|
"ivory",
|
|
"khaki",
|
|
"plum",
|
|
"orchid",
|
|
"peru",
|
|
"tan",
|
|
"tomato",
|
|
"wheat",
|
|
"azure",
|
|
"mint",
|
|
"apricot",
|
|
"chartreuse",
|
|
"amber",
|
|
"fuchsia",
|
|
"jade",
|
|
"ruby",
|
|
"amethyst",
|
|
"rose",
|
|
"sapphire",
|
|
"cerulean",
|
|
"moss",
|
|
"denim",
|
|
"copper",
|
|
"peach",
|
|
"sand",
|
|
"pearl",
|
|
"mulberry",
|
|
"lemon",
|
|
"cream",
|
|
"ocher",
|
|
"brass",
|
|
"eggplant",
|
|
"cinnamon",
|
|
"mustard",
|
|
"rust",
|
|
"sienna",
|
|
"sepia",
|
|
"umber",
|
|
"limegreen",
|
|
"seagreen",
|
|
"forestgreen",
|
|
"dodgerblue",
|
|
"mediumslateblue",
|
|
"royalblue",
|
|
"firebrick",
|
|
"darkolivegreen",
|
|
"midnightblue",
|
|
"darkturquoise",
|
|
"lightcoral",
|
|
"palevioletred",
|
|
"hotpink",
|
|
"deeppink",
|
|
"darkkhaki",
|
|
"lightseagreen",
|
|
"darkslategray",
|
|
"slategray",
|
|
"lightsteelblue",
|
|
"skyblue",
|
|
"lightblue",
|
|
"powderblue",
|
|
"darkorange",
|
|
"lightsalmon",
|
|
"indianred",
|
|
"thistle",
|
|
"burlywood",
|
|
"mediumaquamarine",
|
|
"mediumorchid",
|
|
"mediumvioletred",
|
|
"papayawhip",
|
|
"moccasin",
|
|
"bisque",
|
|
"blanchedalmond",
|
|
"antiquewhite",
|
|
"mistyrose",
|
|
"lavenderblush",
|
|
"linen",
|
|
"snow",
|
|
"honeydew",
|
|
"palegreen",
|
|
"lightcyan",
|
|
"aliceblue",
|
|
"ghostwhite",
|
|
"whitesmoke",
|
|
"gainsboro",
|
|
]
|
|
|
|
latitude = 51.18292980165227
|
|
longitude = 13.11435805600463
|
|
gmap = GoogleMapPlotter(
|
|
latitude, longitude, 8, apikey="AIzaSyAPzFyMk3ZA0kL9TUlJ_kpV_IY56uBwdrc"
|
|
)
|
|
|
|
def random_color():
|
|
return "#{:06x}".format(random.randint(0, 0xFFFFFF))
|
|
|
|
previous_statistics = {}
|
|
|
|
competition_details = {}
|
|
color = None
|
|
for staffel, attr in competitions.items():
|
|
if (staffel[0], staffel[1]) != ("Herren", "Kreisoberliga"):
|
|
# if (staffel[0], staffel[1]) != ('Herren', '1.Kreisklasse'):
|
|
continue
|
|
competitions[staffel]["distance"] = []
|
|
|
|
if (staffel[0], staffel[1]) not in competition_details:
|
|
competition_details[(staffel[0], staffel[1])] = {
|
|
"nStaffeln": 1,
|
|
"nTeams": 0,
|
|
"previous_distances": [],
|
|
"teams": [],
|
|
}
|
|
color = some_colors.pop(0)
|
|
else:
|
|
competition_details[(staffel[0], staffel[1])]["nStaffeln"] += 1
|
|
color = some_colors.pop(0)
|
|
|
|
latitudes = []
|
|
longitudes = []
|
|
markers_text = []
|
|
|
|
distance_for_team = {}
|
|
for team1 in attr["teams"]:
|
|
competition_details[(staffel[0], staffel[1])]["nTeams"] += 1
|
|
competition_details[(staffel[0], staffel[1])]["teams"].append(team1)
|
|
distance_for_team[team1["MANNSCHAFT"]] = []
|
|
for team2 in attr["teams"]:
|
|
distance = 0
|
|
if team1["MANNSCHAFT"] != team2["MANNSCHAFT"]:
|
|
distance = distance_dict[
|
|
(team1["MANNSCHAFT"], team2["MANNSCHAFT"])
|
|
][metric]
|
|
competition_details[(staffel[0], staffel[1])][
|
|
"previous_distances"
|
|
].append(distance)
|
|
competitions[staffel]["distance"].append(distance)
|
|
distance_for_team[team1["MANNSCHAFT"]].append(distance)
|
|
latitudes.append(team1["LATITUDE"])
|
|
longitudes.append(team1["LONGITUDE"])
|
|
markers_text.append(f"{team1['MANNSCHAFT']} @{team1['SPIELSTAETTE']}")
|
|
|
|
# Plot the points on the map
|
|
gmap.scatter(latitudes, longitudes, color=color, size=40, marker=False)
|
|
for (lat1, lon1), (lat2, lon2) in itertools.combinations(
|
|
zip(latitudes, longitudes), 2
|
|
):
|
|
gmap.plot([lat1, lat2], [lon1, lon2], color=color, edge_width=2)
|
|
for lat, lon, text in zip(latitudes, longitudes, markers_text):
|
|
gmap.marker(lat, lon, title=text.replace('"', ""), color=color)
|
|
|
|
print(color, staffel, attr["nTeams"], sum(attr["distance"]))
|
|
|
|
previous_statistics[staffel] = {
|
|
"nTeams": attr["nTeams"],
|
|
"total_distance": sum(attr["distance"]),
|
|
"average_distance": sum(attr["distance"]) / attr["nTeams"],
|
|
"max_distance": max(attr["distance"]),
|
|
"min_distance": min(attr["distance"]),
|
|
"max_team": max(distance_for_team, key=lambda x: sum(distance_for_team[x])),
|
|
"max_team_distance": max(
|
|
[sum(distance_for_team[x]) for x in distance_for_team]
|
|
),
|
|
"min_team": min(distance_for_team, key=lambda x: sum(distance_for_team[x])),
|
|
"min_team_distance": min(
|
|
[sum(distance_for_team[x]) for x in distance_for_team]
|
|
),
|
|
}
|
|
|
|
""" GATHER SOME PREVIOUS STATISTICS """
|
|
|
|
for key, val in previous_statistics.items():
|
|
print(key, val)
|
|
|
|
""" add overall statistics """
|
|
for competition, details in competition_details.items():
|
|
print(
|
|
competition,
|
|
details["nStaffeln"],
|
|
details["nTeams"],
|
|
sum(details["previous_distances"]),
|
|
)
|
|
previous_statistics["overall"] = {
|
|
"nStaffeln": sum(
|
|
[details["nStaffeln"] for details in competition_details.values()]
|
|
),
|
|
"nTeams": sum(
|
|
[details["nTeams"] for details in competition_details.values()]
|
|
),
|
|
"total_distance": sum(
|
|
[
|
|
sum(details["previous_distances"])
|
|
for details in competition_details.values()
|
|
]
|
|
),
|
|
"average_distance": sum(
|
|
[
|
|
sum(details["previous_distances"])
|
|
for details in competition_details.values()
|
|
]
|
|
)
|
|
/ sum([details["nTeams"] for details in competition_details.values()]),
|
|
"max_distance": max(
|
|
[
|
|
max(details["previous_distances"])
|
|
for details in competition_details.values()
|
|
]
|
|
),
|
|
"min_distance": min(
|
|
[
|
|
min(details["previous_distances"])
|
|
for details in competition_details.values()
|
|
]
|
|
),
|
|
"average_group_distance": sum(
|
|
[
|
|
sum(details["previous_distances"])
|
|
for details in competition_details.values()
|
|
]
|
|
)
|
|
/ sum([details["nStaffeln"] for details in competition_details.values()]),
|
|
}
|
|
|
|
previous_statistics_str_keys = {str(k): v for k, v in previous_statistics.items()}
|
|
|
|
with open(f"data/previous_stats_{metric}.json", "w", encoding="utf-8") as f:
|
|
json.dump(
|
|
previous_statistics_str_keys, f, ensure_ascii=False, indent=4, default=str
|
|
)
|
|
|
|
# Optionally, draw a line path connecting the points
|
|
# gmap.plot(latitudes, longitudes, color='blue', edge_width=2.5)
|
|
|
|
# Save the map to an HTML file
|
|
gmap.draw(f"map_previous_{metric}.html")
|
|
# %%
|
|
# for key, value in competition_details.items():
|
|
# print(key,value['nStaffeln'])
|
|
|
|
# %%
|
|
|
|
"""" GENERATE ALL DISTANCES BETWEEN TEAMS """
|
|
|
|
distance_between_teams = {}
|
|
for competition, details in competition_details.items():
|
|
print(f"Calculating distances for {competition}")
|
|
for team1 in details["teams"]:
|
|
distance_between_teams[team1["MANNSCHAFT"]] = {}
|
|
for team2 in details["teams"]:
|
|
distance = 0
|
|
if team1["MANNSCHAFT"] != team2["MANNSCHAFT"]:
|
|
distance = distance_dict[
|
|
(team1["MANNSCHAFT"], team2["MANNSCHAFT"])
|
|
][metric]
|
|
distance_between_teams[team1["MANNSCHAFT"]][
|
|
team2["MANNSCHAFT"]
|
|
] = distance
|
|
|
|
for comp, attr in competition_details.items():
|
|
teams = attr["teams"]
|
|
|
|
"""" RECLUSTERING THE COMPETITION INTO DIVISIONS """
|
|
|
|
model = LpProblem("Cluster", LpMinimize)
|
|
|
|
""" x = 1 if team i is in same division as j, 0 otherwise """
|
|
x = {}
|
|
|
|
for team1 in teams:
|
|
for team2 in teams:
|
|
x[(team1["MANNSCHAFT"], team2["MANNSCHAFT"])] = LpVariable(
|
|
f"team_{team1['MANNSCHAFT']}_{team2['MANNSCHAFT']}",
|
|
lowBound=0,
|
|
upBound=1,
|
|
cat=LpInteger,
|
|
)
|
|
|
|
""" g = 1 if team i is i group j, 0 otherwise """
|
|
groups = range(1, 14)
|
|
|
|
g = {}
|
|
for team in teams:
|
|
for group in groups:
|
|
g[(team["MANNSCHAFT"], group)] = LpVariable(
|
|
f"team_{team['MANNSCHAFT']}_{group}",
|
|
lowBound=0,
|
|
upBound=1,
|
|
cat=LpInteger,
|
|
)
|
|
|
|
""" Each team is in exactly one division """
|
|
for team in teams:
|
|
model += lpSum(g[(team["MANNSCHAFT"], group)] for group in groups) == 1
|
|
|
|
""" Each team is in same divisin as itself """
|
|
for team in teams:
|
|
model += x[(team["MANNSCHAFT"], team["MANNSCHAFT"])] == 1
|
|
|
|
""" Each team is in same division with at least 14 and at most 16 other teams"""
|
|
|
|
for team1 in teams:
|
|
model += (
|
|
lpSum(x[(team1["MANNSCHAFT"], team2["MANNSCHAFT"])] for team2 in teams)
|
|
>= 14
|
|
)
|
|
model += (
|
|
lpSum(x[(team1["MANNSCHAFT"], team2["MANNSCHAFT"])] for team2 in teams)
|
|
<= 16
|
|
)
|
|
|
|
if False:
|
|
""" no more than 16 teams in a division """
|
|
for group in groups:
|
|
model += lpSum(g[(team["MANNSCHAFT"], group)] for team in teams) <= 16
|
|
|
|
""" use each group / at least one team per group """
|
|
for group in groups:
|
|
model += lpSum(g[(team["MANNSCHAFT"], group)] for team in teams) >= 1
|
|
|
|
model += lpSum(g[(team["MANNSCHAFT"], 1)] for team in teams) == 14
|
|
model += lpSum(g[(team["MANNSCHAFT"], 2)] for team in teams) == 14
|
|
model += lpSum(g[(team["MANNSCHAFT"], 3)] for team in teams) == 16
|
|
model += lpSum(g[(team["MANNSCHAFT"], 4)] for team in teams) == 14
|
|
model += lpSum(g[(team["MANNSCHAFT"], 5)] for team in teams) == 14
|
|
model += lpSum(g[(team["MANNSCHAFT"], 6)] for team in teams) == 14
|
|
model += lpSum(g[(team["MANNSCHAFT"], 7)] for team in teams) == 16
|
|
model += lpSum(g[(team["MANNSCHAFT"], 8)] for team in teams) == 16
|
|
model += lpSum(g[(team["MANNSCHAFT"], 9)] for team in teams) == 14
|
|
model += lpSum(g[(team["MANNSCHAFT"], 10)] for team in teams) == 14
|
|
model += lpSum(g[(team["MANNSCHAFT"], 11)] for team in teams) == 15
|
|
model += lpSum(g[(team["MANNSCHAFT"], 12)] for team in teams) == 16
|
|
model += lpSum(g[(team["MANNSCHAFT"], 13)] for team in teams) == 14
|
|
|
|
|
|
""" if team1 and team2 are paired, than they are in the same division """
|
|
for group in groups:
|
|
for team1 in teams:
|
|
for team2 in teams:
|
|
if team1["MANNSCHAFT"] != team2["MANNSCHAFT"]:
|
|
model += (
|
|
x[(team1["MANNSCHAFT"], team2["MANNSCHAFT"])]
|
|
+ g[(team1["MANNSCHAFT"], group)]
|
|
<= 1 + g[(team2["MANNSCHAFT"], group)]
|
|
)
|
|
model += (
|
|
x[(team1["MANNSCHAFT"], team2["MANNSCHAFT"])]
|
|
+ g[(team2["MANNSCHAFT"], group)]
|
|
<= 1 + g[(team1["MANNSCHAFT"], group)]
|
|
)
|
|
|
|
""" symmetry constraint """
|
|
for t1, t2 in x.keys():
|
|
model += x[(t1, t2)] == x[(t2, t1)]
|
|
|
|
""" MINIMIZE THE TRAVEL DISTANCE """
|
|
model += lpSum(
|
|
distance_between_teams[team1["MANNSCHAFT"]][team2["MANNSCHAFT"]]
|
|
* x[(team1["MANNSCHAFT"], team2["MANNSCHAFT"])]
|
|
for team1 in teams
|
|
for team2 in teams
|
|
)
|
|
|
|
model.solve(XPRESS(msg=1, gapRel=0.01, timeLimit=1))
|
|
|
|
localsearch_time = 3600*2
|
|
|
|
start_time = time.time()
|
|
|
|
while time.time() - start_time < localsearch_time:
|
|
|
|
used_groups = [
|
|
group
|
|
for group in groups
|
|
if sum(value(g[(team["MANNSCHAFT"], group)]) for team in teams) > 0.9
|
|
]
|
|
|
|
group_size = random.randint(2, 6)
|
|
opt_groups = random.sample(used_groups, group_size)
|
|
opt_stalltime = group_size * 25
|
|
|
|
print(f"Time: {time.time() - start_time}")
|
|
print("Optimizing groups", opt_groups)
|
|
|
|
for key in x.keys():
|
|
x[key].setInitialValue(value(x[key]))
|
|
x[key].lowBound = value(x[key])
|
|
|
|
for key in g.keys():
|
|
g[key].setInitialValue(value(g[key]))
|
|
if key[1] in opt_groups:
|
|
g[key].lowBound = 0
|
|
for (t1,t2) in x.keys():
|
|
if t1 == key[0] or t2 == key[0]:
|
|
x[(t1,t2)].lowBound = 0
|
|
else:
|
|
g[key].lowBound = value(g[key])
|
|
|
|
model.solve(
|
|
XPRESS(
|
|
msg=1,
|
|
gapRel=0.01,
|
|
warmStart=True,
|
|
options=[f"MAXSTALLTIME={opt_stalltime}"],
|
|
)
|
|
)
|
|
|
|
some_colors = [
|
|
"red",
|
|
"blue",
|
|
"green",
|
|
"yellow",
|
|
"purple",
|
|
"orange",
|
|
"pink",
|
|
"brown",
|
|
"black",
|
|
"white",
|
|
"gray",
|
|
"cyan",
|
|
"magenta",
|
|
"lime",
|
|
"indigo",
|
|
"violet",
|
|
"turquoise",
|
|
"gold",
|
|
"silver",
|
|
"beige",
|
|
"maroon",
|
|
"olive",
|
|
"navy",
|
|
"teal",
|
|
"coral",
|
|
"lavender",
|
|
"salmon",
|
|
"chocolate",
|
|
"crimson",
|
|
"aqua",
|
|
"ivory",
|
|
"khaki",
|
|
"plum",
|
|
"orchid",
|
|
"peru",
|
|
"tan",
|
|
"tomato",
|
|
"wheat",
|
|
"azure",
|
|
"mint",
|
|
"apricot",
|
|
"chartreuse",
|
|
"amber",
|
|
"fuchsia",
|
|
"jade",
|
|
"ruby",
|
|
"amethyst",
|
|
"rose",
|
|
"sapphire",
|
|
"cerulean",
|
|
"moss",
|
|
"denim",
|
|
"copper",
|
|
"peach",
|
|
"sand",
|
|
"pearl",
|
|
"mulberry",
|
|
"lemon",
|
|
"cream",
|
|
"ocher",
|
|
"brass",
|
|
"eggplant",
|
|
"cinnamon",
|
|
"mustard",
|
|
"rust",
|
|
"sienna",
|
|
"sepia",
|
|
"umber",
|
|
"limegreen",
|
|
"seagreen",
|
|
"forestgreen",
|
|
"dodgerblue",
|
|
"mediumslateblue",
|
|
"royalblue",
|
|
"firebrick",
|
|
"darkolivegreen",
|
|
"midnightblue",
|
|
"darkturquoise",
|
|
"lightcoral",
|
|
"palevioletred",
|
|
"hotpink",
|
|
"deeppink",
|
|
"darkkhaki",
|
|
"lightseagreen",
|
|
"darkslategray",
|
|
"slategray",
|
|
"lightsteelblue",
|
|
"skyblue",
|
|
"lightblue",
|
|
"powderblue",
|
|
"darkorange",
|
|
"lightsalmon",
|
|
"indianred",
|
|
"thistle",
|
|
"burlywood",
|
|
"mediumaquamarine",
|
|
"mediumorchid",
|
|
"mediumvioletred",
|
|
"papayawhip",
|
|
"moccasin",
|
|
"bisque",
|
|
"blanchedalmond",
|
|
"antiquewhite",
|
|
"mistyrose",
|
|
"lavenderblush",
|
|
"linen",
|
|
"snow",
|
|
"honeydew",
|
|
"palegreen",
|
|
"lightcyan",
|
|
"aliceblue",
|
|
"ghostwhite",
|
|
"whitesmoke",
|
|
"gainsboro",
|
|
]
|
|
|
|
latitude = 51.18292980165227
|
|
longitude = 13.11435805600463
|
|
gmap = GoogleMapPlotter(
|
|
latitude, longitude, 8, apikey="AIzaSyAPzFyMk3ZA0kL9TUlJ_kpV_IY56uBwdrc"
|
|
)
|
|
|
|
new_statistics = {}
|
|
|
|
f = open(f"data/new_solution_{metric}.csv", "w")
|
|
|
|
for group in groups:
|
|
print(f"GROUP {group}")
|
|
new_statistics[group] = {
|
|
"nTeams": 0,
|
|
"total_distance": 0,
|
|
"average_distance": 0,
|
|
"max_distance": 0,
|
|
"min_distance": 0,
|
|
"max_team": "",
|
|
"max_team_distance": 0,
|
|
"min_team": "",
|
|
"min_team_distance": 0,
|
|
}
|
|
|
|
latitudes = []
|
|
longitudes = []
|
|
markers_text = []
|
|
|
|
color = some_colors.pop(0)
|
|
|
|
for team in teams:
|
|
if value(g[(team["MANNSCHAFT"], group)]) > 0.9:
|
|
print(f"TEAM {team['MANNSCHAFT']} - {group}")
|
|
f.write(f"{team['MANNSCHAFT']},{group}\n")
|
|
|
|
latitudes.append(team["LATITUDE"])
|
|
longitudes.append(team["LONGITUDE"])
|
|
markers_text.append(f"{team['MANNSCHAFT']} @{team['SPIELSTAETTE']}")
|
|
new_statistics[group]["nTeams"] += 1
|
|
|
|
for t1, t2 in x.keys():
|
|
if t1 == team["MANNSCHAFT"] and value(x[(t1, t2)]) > 0.9:
|
|
new_statistics[group][
|
|
"total_distance"
|
|
] += distance_between_teams[t1][t2]
|
|
|
|
new_statistics[group]["average_distance"] = new_statistics[group][
|
|
"total_distance"
|
|
] / (max(new_statistics[group]["nTeams"], 1))
|
|
|
|
teams_in_group = [
|
|
team["MANNSCHAFT"]
|
|
for team in teams
|
|
if value(g[(team["MANNSCHAFT"], group)]) > 0.9
|
|
]
|
|
new_statistics[group]["max_distance"] = max(
|
|
[
|
|
distance_between_teams[t1][t2]
|
|
for t1 in teams_in_group
|
|
for t2 in teams_in_group
|
|
],
|
|
default=0,
|
|
)
|
|
new_statistics[group]["min_distance"] = min(
|
|
[
|
|
distance_between_teams[t1][t2]
|
|
for t1 in teams_in_group
|
|
for t2 in teams_in_group
|
|
],
|
|
default=0,
|
|
)
|
|
new_statistics[group]["max_team"] = max(
|
|
teams_in_group,
|
|
key=lambda x: sum([distance_between_teams[x][t2] for t2 in teams_in_group]),
|
|
default=0,
|
|
)
|
|
new_statistics[group]["max_team_distance"] = sum(
|
|
[
|
|
distance_between_teams[new_statistics[group]["max_team"]][t2]
|
|
for t2 in teams_in_group
|
|
]
|
|
)
|
|
new_statistics[group]["min_team"] = min(
|
|
teams_in_group,
|
|
key=lambda x: sum([distance_between_teams[x][t2] for t2 in teams_in_group]),
|
|
default=0,
|
|
)
|
|
new_statistics[group]["min_team_distance"] = sum(
|
|
[
|
|
distance_between_teams[new_statistics[group]["min_team"]][t2]
|
|
for t2 in teams_in_group
|
|
]
|
|
)
|
|
|
|
# Plot the points on the map
|
|
gmap.scatter(latitudes, longitudes, color=color, size=40, marker=False)
|
|
for (lat1, lon1), (lat2, lon2) in itertools.combinations(
|
|
zip(latitudes, longitudes), 2
|
|
):
|
|
gmap.plot([lat1, lat2], [lon1, lon2], color=color, edge_width=2)
|
|
for lat, lon, text in zip(latitudes, longitudes, markers_text):
|
|
gmap.marker(lat, lon, title=text.replace('"', ""), color=color)
|
|
|
|
f.close()
|
|
|
|
gmap.draw(f"map_new_{metric}.html")
|
|
|
|
new_statistics["overall"] = {
|
|
"nGroups": len(
|
|
[group for group in groups if new_statistics[group]["nTeams"] > 0]
|
|
),
|
|
"nTeams": sum([new_statistics[group]["nTeams"] for group in groups]),
|
|
"total_distance": sum(
|
|
[new_statistics[group]["total_distance"] for group in groups]
|
|
),
|
|
"average_distance": sum(
|
|
[new_statistics[group]["total_distance"] for group in groups]
|
|
)
|
|
/ sum([new_statistics[group]["nTeams"] for group in groups]),
|
|
"max_distance": max(
|
|
[new_statistics[group]["max_distance"] for group in groups]
|
|
),
|
|
"min_distance": min(
|
|
[new_statistics[group]["min_distance"] for group in groups]
|
|
),
|
|
}
|
|
new_statistics["overall"]["average_group_distance"] = (
|
|
new_statistics["overall"]["total_distance"]
|
|
/ new_statistics["overall"]["nGroups"]
|
|
)
|
|
|
|
new_statistics_str_keys = {str(k): v for k, v in new_statistics.items()}
|
|
|
|
with open(f"data/new_stats_{metric}.json", "w", encoding="utf-8") as f:
|
|
json.dump(new_statistics_str_keys, f, ensure_ascii=False, indent=4, default=str)
|