# - Wetterturnier specific modules
from pywetterturnier import utils, database
# - Evaluating input arguments
inputs = utils.inputcheck('ComputePoints')
# - Read configuration file
config = utils.readconfig('config.conf',inputs)
# - Initializing class and open database connection
db = database.database(config)
# - Loading tdate (day since 1970-01-01) for the tournament.
# Normaly Friday-Tornament (tdate is then Friday) while
# the bet-dates are for Saturday and Sunday.
if config['input_tdate'] == None:
tdates = [db.current_tournament()]
print ' * Current tournament is %s' % utils.tdate2string( tdates[0] )
else:
tdates = [config['input_tdate']]
# - If input_user was given as string we have to find the
# corresponding userID first!
if type(config['input_user']) == type(str()):
config['input_user'] = db.get_user_id( config['input_user'] )
if not config['input_user']:
utils.exit('SORRY could not convert your input -u/--user to corresponding userID. Check name.')
# - Loading all parameters
params = db.get_parameter_names(False)
# ----------------------------------------------------------------
# Final variable. If set to False we would not allow to merge.
# If 'True' merging would be allowed.
merging_allowed = True
cur = db.cursor()
for tdate in tdates:
# Initialize
counts = [None] * len(users)
for u in range(0, len(users)):
cur.execute(sql % (userids[u], tdate))
counts[u] = cur.fetchone()[0]
# If no bets for this tournament at all: skip
if np.max(counts) == 0: continue
# Show frontend
print " %10s %6d " % (utils.tdate2string(tdate), tdate),
for u in range(0, len(users)):
print " %10d " % counts[u],
if len(np.where(np.array(counts) > 0)[0]) == 1:
print " merge ok"
else:
merging_allowed = False
print " [ERROR] MERGING NOT ALLOWED (OVERLAP)"
if not merging_allowed:
print "\n\n !!!! MERGING NOT ALLOWED !!!!!\n\n"
db.close()
sys.exit(9)
# ----------------------------------------------------------------
# Else merge user
# - Wetterturnier specific modules
from pywetterturnier import utils, database
# - Evaluating input arguments
inputs = utils.inputcheck('ComputePoints')
# - Read configuration file
config = utils.readconfig('config.conf', inputs)
# - Initializing class and open database connection
db = database.database(config)
# - Loading tdate (day since 1970-01-01) for the tournament.
# Normaly Friday-Tornament (tdate is then Friday) while
# the bet-dates are for Saturday and Sunday.
if config['input_tdate'] == None:
tdates = [db.current_tournament()]
print(' * Current tournament is %s' % utils.tdate2string(tdates[0]))
else:
tdates = [config['input_tdate']]
# - If input_user was given as string we have to find the
# corresponding userID first!
if type(config['input_user']) == type(str()):
config['input_user'] = db.get_user_id(config['input_user'])
if not config['input_user']:
utils.exit(
'SORRY could not convert your input -u/--user to corresponding userID. Check name.'
)
# - Loading all parameters
params = db.get_parameter_names(False)
inputs = utils.inputcheck('PlotStats')
# - Read configuration file
config = utils.readconfig('config.conf',inputs)
# - Initializing class and open database connection
db = database.database(config)
# ----------------------------------------------------------------
# - Loading all different cities (active cities)
cities = db.get_cities()
#TODO: only accepted input should be city!
# - If input city set, then drop all other cities.
if not config['input_city'] == None:
tmp = []
for i in cities:
if i['name'] == config['input_city']: tmp.append( i )
cities = tmp
if config['input_tdate'] == None:
tdate = db.current_tournament()
print(' * Current tournament is %s' % utils.tdate2string( tdate ))
else:
tdate = config['input_tdate']
# - Calling the function now
plot(db, cities, tdate)
db.commit()
db.close()
def print_moses(db, config, cities, tdates):
path = "moses/input/"
#fixed strings and headers:
file_head = "Berliner Wetterprognoseturnier %s\n\nEingetroffene Werte und abgegebene Prognosen:\n"
table_head = "Name N Sd dd ff fx Wv Wn PPP TTm TTn TTd RR"
day_heads = ["Samstag:", "Sonntag:"]
ranking_str = "Wertung der Prognose vom %s:\n"
ranking_head = "Pl. Name Punkte Tag1 Tag2\n_________________________________________________"
info_str = "\nDie durchschnittliche Punktzahl beträgt: %5.1f Punkte.\nWertung für nicht teilnehmende Mitspieler: %5.1f Punkte."
season_str = "Die aktuelle Jahreszeitenwertung (%s):\n(basierend auf den Prognosen vom %s bis zum %s)"
season_head = "Platz Name Punkte\n___________________________________________________"
weeks_str = "Das aktuelle Gesamtranking:\n(basierend auf den Prognosen vom %s bis zum %s)"
weeks_head = "Platz Name Punkte Durchschnitt Teiln.\n_____________________________________________________________________________"
footer = "_____________________________________\nBerliner Wetterprognoseturnier\nhttp://www.wetterturnier.de/\[email protected]"
params = db.get_parameter_names(sort=True)
printf = lambda *args: print(*args, file=f)
def print_rows(args, file):
row_format = "{name:<25.25s} {n:>1} {sd:>3} {dd:>3} {ff:>2} {fx:>2} {wv:>2} {wn:>2} {ppp:>6.6} {tn:>5.5} {tx:>5.5} {td:>5.5} {rr:>5.5}"
for i in range(1, 8):
if type(args[i]) != str: args[i] = int(args[i])
print(row_format.format(name=args[0],
n=args[1],
sd=args[2],
dd=args[3],
ff=args[4],
fx=args[5],
wv=args[6],
wn=args[7],
ppp=args[8],
tn=args[9],
tx=args[10],
td=args[11],
rr=args[12]),
file=f)
for city in cities:
cityID = city['ID']
if config['input_alldates']:
tdates = db.all_tournament_dates(city['ID'])
current = db.current_tournament()
if current in tdates:
tdates.remove(current)
tdates = [i for i in tdates if i > 12027]
for tdate in tdates:
missing_bets = db.find_missing_bets(cityID, tdate)
missing_obs = db.find_missing_obs(cityID, tdate)
#if *missing_bets returns False, it's a bool, missing obs returns either True or False
if type(missing_bets) != bool or missing_obs:
print("To many missing obs or parameters!")
continue
stations = db.get_stations_for_city(cityID,
active=False,
tdate=tdate)
#print output to file, first get prober filename
filename = path + utils.tdate2string(
tdate, moses=True) + "." + city['name'].lower()[0] + "pw"
f = open(filename, 'w')
tdate_str = utils.tdate2string(tdate)
printf(file_head % tdate_str)
users = db.get_participants_in_city(cityID,
tdate,
sort=True,
what="user_login")
for day in range(1, 3):
printf(day_heads[day - 1])
printf(table_head)
printf(78 * "_")
for station in stations:
obs = [station.name]
for param in params:
paramID = db.get_parameter_id(param)
value = db.get_obs_data(cityID, paramID, tdate, day,
station.wmo)
if value is False or value is None: obs.append("n")
else:
obs.append(float(value) / 10)
print_rows(obs, f)
#printf(78*"_")
printf("")
for userID in users:
bet = [
db.get_username_by_id(userID,
which="user_login").replace(
"GRP_", "")
]
for param in params:
paramID = db.get_parameter_id(param)
value = db.get_bet_data("user", userID, cityID,
paramID, tdate, day)
if value is False or value is None: bet.append("n")
else:
bet.append(float(value) / 10)
print_rows(bet, f)
if day == 1: printf("\f")
else: printf("\n")
#weekend ranking
printf(ranking_str % tdate_str)
printf(ranking_head)
sleepyID = db.get_user_id("Sleepy")
sql = "SELECT bs.rank, wu.user_login, bs.points, bs.points_d1, bs.points_d2, REPLACE(wu.user_login, 'GRP_', '') "
sql += "FROM wp_wetterturnier_betstat bs JOIN wp_users wu ON userID = wu.ID WHERE tdate=%d AND cityID=%d AND userID != %d ORDER BY bs.rank"
cur = db.cursor()
cur.execute(sql % (tdate, cityID, sleepyID))
data = cur.fetchall()
for i in data:
printf("{:2d}. {:<25.25s} {:5.1f} ({:5.1f}/{:5.1f})".format(
i[0], i[1].replace("GRP_", ""), i[2], i[3], i[4]))
sql = "SELECT mean FROM wp_wetterturnier_tdatestats WHERE tdate = %d AND cityID = %d"
cur.execute(sql % (tdate, cityID))
mean = cur.fetchone()[0]
do, fr = db.get_user_id("Donnerstag"), db.get_user_id("Freitag")
sleepy = db.get_sleepy_points(cityID, tdate, [do, fr])
printf(info_str % (mean, sleepy))
printf("\n")
#TODO: season ranking (fake?)
#TODO: 15 weeks ranking (only parts?)
printf(footer)
def print_moses(db, config, cities, tdates):
path = "moses/input/"
table_head = "Spielername N SD DD FF FX Wv Wn PPP TX TN TD RR"
day_heads = [
" Samstag",
" Sonntag"
]
params = db.get_parameter_names(sort=True)
def print_rows(args, file):
row_format = "{name:<21.21s} {n:>5} {sd:>3} {dd:>4} {ff:>2} {fx:>2} {wv:>2} {wn:>1} {ppp:>7.7} {tn:>6.6} {tx:>5.5} {td:>5.5} {rr:>5.5}"
for i in range(1, 8):
if type(args[i]) != str: args[i] = int(args[i])
print(row_format.format(name=args[0],
n=args[1],
sd=args[2],
dd=args[3],
ff=args[4],
fx=args[5],
wv=args[6],
wn=args[7],
ppp=args[8],
tn=args[9],
tx=args[10],
td=args[11],
rr=args[12]),
file=f)
for city in cities:
cityID = city['ID']
if config['input_alldates']:
tdates = db.all_tournament_dates(city['ID'])
current = db.current_tournament()
if current in tdates:
tdates.remove(current)
tdates = [i for i in tdates if i > 12027]
for tdate in tdates:
missing_bets = db.find_missing_bets(cityID, tdate)
missing_obs = db.find_missing_obs(cityID, tdate)
#if *missing_bets returns False, it's a bool, missing obs returns either True or False
if type(missing_bets) != bool or missing_obs:
print("To many missing obs or parameters!")
continue
stations = db.get_stations_for_city(cityID,
active=False,
tdate=tdate)
#print output to file, first get prober filename
filename = path + utils.tdate2string(
tdate, moses=True) + "." + city['name'].lower()[0] + "pw"
f = open(filename, 'w')
users = db.get_participants_in_city(cityID,
tdate,
sort=True,
what="user_login")
print(users)
for day in range(1, 3):
print(day_heads[day - 1], file=f)
print("", file=f)
for i in range(4):
print("", file=f)
print(table_head, file=f)
print(80 * "-", file=f)
for station in stations:
obs = [station.name]
for param in params:
paramID = db.get_parameter_id(param)
value = db.get_obs_data(cityID, paramID, tdate, day,
station.wmo)
if value is False or value is None: obs.append("n")
else:
obs.append(float(value) / 10)
print_rows(obs, f)
print(80 * "-", file=f)
for userID in users:
bet = [
db.get_username_by_id(userID,
which="user_login").replace(
"GRP_", "")
]
for param in params:
paramID = db.get_parameter_id(param)
value = db.get_bet_data("user", userID, cityID,
paramID, tdate, day)
if value is False or value is None: bet.append("n")
else:
bet.append(float(value) / 10)
print_rows(bet, f)
if day == 1: print("\f", file=f)
else: print("", file=f)
def compute_stats(self,
cityID,
measures,
userID=False,
tdate=False,
day=0,
last_tdate=None,
referenz=True,
mitteltips=True,
aliases=None,
pout=25,
pmid=100,
midyear=2010,
span=None,
dates=None,
verbose=False):
"""
Computes all kinds of statistics e.g. the points for the eternal list plus min/max/median/mean of points for each user, city, tdate and so on.
"""
res = {
} #results will be saved here with measures as keys, tdate as subkeys
import numpy as np
cur = self.db.cursor()
day_strs = ["", "_d1", "_d2"]
day_str = day_strs[0]
if day != 0:
day_str = day_strs[day]
if last_tdate: last_tdate_str = " AND tdate<=" + str(last_tdate)
else: last_tdate_str = ""
sql = "SELECT points" + day_str + " FROM %swetterturnier_betstat WHERE "
if tdate or (not tdate and not userID and cityID):
# We don't want Sleepy in our tdatestats!
#exclude = [self.get_user_id("Sleepy")]
exclude = []
if not referenz:
groupID = self.get_group_id("Referenztipps")
for j in self.get_participants_in_group(groupID,
cityID,
tdate,
playing=False):
exclude.append(j)
if not mitteltips:
#include no groups
sql2 = "SELECT ID FROM %susers WHERE user_login LIKE \"%s\""
cur.execute(sql2 % (self.prefix, "GRP_%"))
data2 = cur.fetchall()
for j in data2:
exclude.append(int(j[0]))
if tdate: #tdatestats
#only include users who really played on tdate (no sleepy points!)
#played = self.sql_tuple( self.get_participants_in_city( cityID, tdate ) )
sql += "cityID=%d AND tdate=%d AND userID NOT IN%s" + last_tdate_str
cur.execute(sql %
(self.prefix, cityID, tdate, self.sql_tuple(exclude)))
elif cityID: #citystats
sql2 = "SELECT part FROM %swetterturnier_tdatestats WHERE cityID=%d" + last_tdate_str
cur.execute(sql2 % (self.prefix, cityID))
data = cur.fetchall()
for i in measures:
parts = []
for j in data:
parts.append(int(j[0]))
if i == "mean_part": res[i] = np.mean(parts)
elif i == "max_part": res[i] = np.max(parts)
elif i == "min_part": res[i] = np.min(parts)
elif i == "tdates": res[i] = len(parts)
sql += "cityID=%d AND userID NOT IN%s" + last_tdate_str
cur.execute(sql % (self.prefix, cityID, self.sql_tuple(exclude)))
elif userID: #userstats
userIDs = [userID]
#if we are using an alias dict we merge all aliases of a user with his/her other identities
if aliases:
username = self.get_username_by_id(userID)
if username in aliases.keys() or username in sum(
aliases.values(), []):
if verbose: print(username)
for j in aliases.keys():
if username == j:
for k in aliases[j]:
userIDs.append(self.get_user_id(k))
break
else:
if username in aliases[j]:
userIDs.append(self.get_user_id(j))
for k in aliases[j]:
userID = self.get_user_id(k)
if userID not in userIDs:
userIDs.append(userID)
if verbose: print userIDs
sql += "userID IN%s"
if cityID != 0:
sql += " AND cityID=%d" + last_tdate_str
cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), cityID))
else:
sql += last_tdate_str
cur.execute(sql % (self.prefix, self.sql_tuple(userIDs)))
else:
utils.exit("Wrong usage of compute_stats!")
data = cur.fetchall()
points = []
for i in data:
#sleepy has NULL points on d1 and d2, skip him!
if i[0] == None: continue
else: points.append(float(i[0]))
if not points: points.append(0)
if cityID == 0:
"""
first calculate sd_ind for all tournaments played. Pretend as cities were one
so if a user participated in multiple cities on the same day
it counts like he played multiple tournaments &
for each of these tournaments we look up the sd_upp and calculate a mean (sd_ind)
"""
sql = "SELECT cityID, tdate, points FROM %swetterturnier_betstat WHERE userID IN%s AND tdate <= %d"
cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), last_tdate))
points = {}
for j in cur.fetchall():
if j[0] not in points:
points[j[0]] = {}
points[j[0]][j[1]] = j[2]
sd_upp = []
for cityID in points.keys():
for tdate in points[cityID].keys():
sd_upp.append(
self.get_stats(cityID, measure="sd_upp", tdate=tdate))
if not sd_upp: return {"points_adj": 0}
#remove None values
sd_upp = [x for x in sd_upp if x]
sd_ind = np.nanmean(sd_upp)
res["sd_ind"] = sd_ind
#now we get the individual points for each tournament played
points_adj = []
parts = {}
for cityID in points.keys():
parts[cityID] = 0
for cityID in points.keys():
for tdate in points[cityID].keys():
median = self.get_stats(cityID, measure="median", tdate=tdate)
points_adj.append(points[cityID][tdate] - median)
parts[cityID] += 1
#get mean participations for every city a user ever played
parts_all = np.sum(parts.values())
#actually parts_mean, we will use this variable in calculation
parts = parts_all / len(parts.values())
res["part"] = parts_all
sql = "SELECT points FROM wp_wetterturnier_betstat WHERE userID IN%s"
cur.execute(sql % self.sql_tuple(userIDs))
points_all = [j[0] for j in cur.fetchall() if j[0]]
if points_all:
res["max"] = np.max(points_all)
res["mean"] = np.mean(points_all)
#norm by sd_ind, scale by mean participations in all cities
if parts < pout:
f = 0
elif parts < pmid:
f = np.sqrt(float(parts) / pmid)
else:
f = 1
#average points above median
res["points_med"] = np.mean(points_adj)
#final adjusted points
res["points_adj"] = f * (res["points_med"] / sd_ind) * 1000
if np.isnan(res["points_adj"]):
res["points_adj"] = 0
sql = """
SELECT rank, count(rank) AS count FROM %swetterturnier_betstat
WHERE userID IN%s AND rank <= 3 %s
GROUP BY rank ORDER BY rank
"""
cur.execute(sql %
(self.prefix, self.sql_tuple(userIDs), last_tdate_str))
ranks = {1: "0", 2: "0", 3: "0"}
for j in cur.fetchall():
ranks[j[0]] = str(j[1])
res["ranks_weekend"] = ",".join(ranks.values())
return res
elif cityID == 6:
#weighted variant sum(p_adj_i * part_i) / sum(part_i)
#which cities did the user play? would be better to call userstat but then we need to get rid of zero/null rows...
sql = "SELECT cityID FROM wp_wetterturnier_betstat WHERE userID IN%s"
cur.execute(sql % self.sql_tuple(userIDs))
cityIDs = []
for i in cur.fetchall():
if i[0] not in cityIDs: cityIDs.append(i[0])
#get points_adj from userstats
points_adj, parts, sd_ind = ([] for _ in range(3))
for cityID in cityIDs:
measures = self.get_stats(
cityID=cityID,
userID=userIDs[0],
measures=["points_adj", "part", "sd_ind"])
if measures:
points_adj.append(measures[0])
parts.append(measures[1])
sd_ind.append(measures[2])
if not points_adj or not parts or not sd_ind or None in points_adj:
res["points_adj"] = 0
res["part"] = 0
res["sd_ind"] = 0
return res
else:
sql = "SELECT points FROM wp_wetterturnier_betstat WHERE userID IN%s"
cur.execute(sql % self.sql_tuple(userIDs))
points_all = [j[0] for j in cur.fetchall() if j[0]]
if points_all:
res["max"] = np.max(points_all)
res["mean"] = np.mean(points_all)
sql = """
SELECT rank, count(rank) AS count FROM %swetterturnier_betstat
WHERE userID IN%s AND rank <= 3 %s
GROUP BY rank ORDER BY rank
"""
cur.execute(sql %
(self.prefix, self.sql_tuple(userIDs), last_tdate_str))
ranks = {1: "0", 2: "0", 3: "0"}
for j in cur.fetchall():
ranks[j[0]] = str(j[1])
res["ranks_weekend"] = ",".join(ranks.values())
parts_all = np.sum(parts)
res["part"] = parts_all
res["points_adj"] = np.dot(points_adj, parts) / parts_all
res["sd_ind"] = np.dot(sd_ind, parts) / parts_all
return res
for i in measures:
i += day_str
if i == "points" + day_str:
res[i] = sum(points)
elif "sd_ind" in i and "_d" not in i:
#get tdates where the user participated
sql = "SELECT tdate FROM %swetterturnier_betstat WHERE userID IN%s AND cityID=%d"
if "1" in i or "2" in i or "X" in i:
spanstr = i[-1]
if midyear:
middle_tdate = str(
utils.string2tdate(str(midyear) + "-01-01"))
#print "Middle of tournament (tdate): %s" % middle_tdate
if "1" in i:
sql += " AND tdate<=" + middle_tdate
elif "2" in i:
sql += " AND tdate>" + middle_tdate
elif span:
if verbose: print span
sql += " AND tdate BETWEEN " + str(
span[0]) + " AND " + str(span[1])
cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), cityID))
tdates = [j[0] for j in cur.fetchall()]
sql = "SELECT sd_upp from %swetterturnier_tdatestats WHERE cityID=%d AND tdate IN%s"
cur.execute(sql % (self.prefix, cityID, self.sql_tuple(tdates)))
#print sql % (self.prefix, cityID, self.sql_tuple(tdates) )
sd_upp = [j[0] for j in cur.fetchall()]
res[i] = np.mean(sd_upp)
#print(res[i])
if res[i] == None or np.isnan(res[i]): res[i] = 0
elif "points_adj" in i and "_d" not in i:
if verbose:
print self.get_username_by_id(userIDs[0])
print i, "\n"
#skip Sleepy
#if self.get_user_id("Sleepy") in userIDs: continue
parts = len(points)
if not parts: continue
"""
find all dates where the user actually played
for each date calculate:
(points-points) / (ymax-median*)
sum up and divide by number of tdates
* daily median and median fitted by PlotStats
should be calculated earlier in ComputeStats with other citystats!
"""
tdates = {}
sql = "SELECT tdate, points FROM %swetterturnier_betstat WHERE userID IN%s AND cityID=%d"
if "1" in i or "2" in i or "X" in i:
if midyear:
middle_tdate = str(
utils.string2tdate(str(midyear) + "-01-01"))
if verbose:
print "Middle of tournament (tdate): %s" % middle_tdate
if "1" in i:
sql += " AND tdate<=" + middle_tdate
elif "2" in i:
sql += " AND tdate>" + middle_tdate
elif span:
if verbose: print span
sql += " AND tdate BETWEEN " + str(
span[0]) + " AND " + str(span[1])
#print sql
cur.execute(sql % (self.prefix, self.sql_tuple(userIDs), cityID))
data = cur.fetchall()
for j in data:
tdates[j[0]] = {}
tdates[j[0]]["points"] = j[1]
#get the actual median for each tdate
sql = "SELECT tdate, median from %swetterturnier_tdatestats WHERE cityID=%d AND tdate IN%s"
cur.execute(sql %
(self.prefix, cityID, self.sql_tuple(tdates.keys())))
data = cur.fetchall()
for j in data:
tdates[j[0]]["median"] = j[1]
if "1" in i: timestr = "1"
elif "2" in i: timestr = "2"
elif "X" in i: timestr = "X"
else: timestr = ""
sd_ind = self.get_stats(cityID,
measure="sd_ind" + timestr,
userID=userIDs[0])
if sd_ind in [0, None] or np.isnan(sd_ind): continue
if verbose:
print "tdate points median sd points_adj"
points_adj = []
for t in sorted(tdates.keys()):
if {"points", "median"} <= set(tdates[t]):
perc = tdates[t]["points"] - tdates[t]["median"]
if verbose:
print utils.tdate2string(t), str(
tdates[t]["points"]).ljust(6), str(
tdates[t]["median"]).ljust(6), str(
round(sd_ind,
2)).ljust(7), str(round(perc,
2)).ljust(10)
else:
continue
points_adj.append(perc)
if not points_adj: points_adj.append(0)
if verbose:
print ""
#if someone has less than "pout" parts, just kick him out!
if parts < pout:
f = 0
#in between pout and pmid the points get adjusted by the sqrt() function
elif parts < pmid:
f = np.sqrt(float(parts) / pmid)
else: #if parts >= pmid
f = 1
#average points above median
res["points_med"] = np.mean(points_adj)
#final adjusted points
res["points_adj"] = f * (res["points_med"] / sd_ind) * 1000
if np.isnan(res["points_adj"]):
res["points_adj"] = 0
elif i == "mean" + day_str:
res[i] = round(np.mean(points), 1)
elif i == "median" + day_str:
res[i] = np.median(points)
elif i == "Qlow" + day_str:
res[i] = np.percentile(points, 25, interpolation="midpoint")
elif i == "Qupp" + day_str:
res[i] = np.percentile(points, 75, interpolation="midpoint")
elif i == "max" + day_str:
res[i] = max(points)
elif i == "min" + day_str:
res[i] = min(points)
elif i == "sd" + day_str: #standard deviation
sd = np.std(points)
if np.isnan(sd): res[i] = 0
else: res[i] = sd
elif i == "part":
#important for part count, otherwise could be 1 if a player/date actually has 0 part
if len(points) == 1 and points[0] == 0: res[i] = 0
else: res[i] = len(points)
elif i == "sd_upp" + day_str:
median = self.get_stats(tdate=tdate,
cityID=cityID,
measure="median" + day_str)
if not median: median = res["median" + day_str]
if not median: continue
sql = "SELECT points" + day_str + " from %swetterturnier_betstat WHERE tdate=%d AND cityID=%d AND points" + day_str + " > %f"
#print sql % (self.prefix, tdate, cityID, median)
cur.execute(sql % (self.prefix, tdate, cityID, median))
sd = np.mean([j[0] - median for j in cur.fetchall()])
if np.isnan(sd): res[i] = 0
else: res[i] = sd
elif i == "ranks_weekend":
sql = """
SELECT rank, count(rank) AS count FROM %swetterturnier_betstat
WHERE cityID = %d AND userID IN%s AND rank <= 3 %s
GROUP BY rank ORDER BY rank
"""
cur.execute(
sql %
(self.prefix, cityID, self.sql_tuple(userIDs), last_tdate_str))
ranks = {1: "0", 2: "0", 3: "0"}
for j in cur.fetchall():
ranks[j[0]] = str(j[1])
res[i] = ",".join(ranks.values())
elif i == "ranks_season":
pass
else:
continue
if not res: utils.exit("No results!")
return res
inputs = utils.inputcheck('ComputePersistenzen')
# - Read configuration file
config = utils.readconfig('config.conf', inputs)
# - Initializing class and open database connection
db = database.database(config)
# - Loading tdate (day since 1970-01-01) for the tournament.
# Normaly Friday-Tornament (tdate is then Friday) while
# the bet-dates are for Saturday and Sunday if there was
# no input tournament date -t/--tdate.
if config['input_tdate'] == None:
tdates = [db.current_tournament()]
else:
tdates = [config['input_tdate']]
print "Current tournament is %s" % utils.tdate2string(tdates[0])
# - Loading all different cities (active cities)
cities = db.get_cities()
# - If input city set, then drop all other cities.
if not config['input_city'] == None:
tmp = []
for elem in cities:
if elem['name'] == config['input_city']: tmp.append(elem)
cities = tmp
# - Reading parameter list
params = db.get_parameter_names(False)
for i, j in zip(["Donnerstag", "Freitag"], [1, 0]):
print i, j
# Final variable. If set to False we would not allow to merge.
# If 'True' merging would be allowed.
merging_allowed = True
cur = db.cursor()
for tdate in tdates:
# Initialize
counts = [None] * len(users)
for u in range(0, len(users)):
cur.execute(sql % (userids[u], tdate))
counts[u] = cur.fetchone()[0]
# If no bets for this tournament at all: skip
if np.max(counts) == 0: continue
# Show frontend
print(" %10s %6d " % (utils.tdate2string(tdate), tdate), end=' ')
for u in range(0, len(users)):
print(" %10d " % counts[u], end=' ')
if len(np.where(np.array(counts) > 0)[0]) == 1:
print(" merge ok")
else:
merging_allowed = False
print(" [ERROR] MERGING NOT ALLOWED (OVERLAP)")
if not merging_allowed:
print("\n\n !!!! MERGING NOT ALLOWED !!!!!\n\n")
db.close()
sys.exit(9)
# ----------------------------------------------------------------
# Else merge user
def print_moses(db, config, cities, tdates):
path = "moses/input/"
file_head = "Berliner Wetterprognoseturnier %s\n\nEingetroffene Werte und abgegebene Prognosen:\n"
table_head = "Name N Sd dd ff fx Wv Wn PPP TTm TTn TTd RR"
day_heads = ["Samstag:", "Sonntag:"]
ranking_str = "Wertung der Prognose vom %s:\n"
ranking_head = "Pl. Name Punkte Tag1 Tag2\n_________________________________________________"
info_str = "\nDie durchschnittliche Punktzahl beträgt: %5.1f Punkte.\nWertung für nicht teilnehmende Mitspieler: %5.1f Punkte."
params = db.get_parameter_names(sort=True)
def print_rows(args, file):
row_format = "{name:<21.21s} {n:>5} {sd:>3} {dd:>3} {ff:>2} {fx:>2} {wv:>2} {wn:>2} {ppp:>6.6} {tn:>5.5} {tx:>5.5} {td:>5.5} {rr:>5.5}"
for i in range(1, 8):
if type(args[i]) != str: args[i] = int(args[i])
print(row_format.format(name=args[0],
n=args[1],
sd=args[2],
dd=args[3],
ff=args[4],
fx=args[5],
wv=args[6],
wn=args[7],
ppp=args[8],
tn=args[9],
tx=args[10],
td=args[11],
rr=args[12]),
file=f)
for city in cities:
cityID = city['ID']
if config['input_alldates']:
tdates = db.all_tournament_dates(city['ID'])
current = db.current_tournament()
if current in tdates:
tdates.remove(current)
tdates = [i for i in tdates if i > 12027]
for tdate in tdates:
missing_bets = db.find_missing_bets(cityID, tdate)
missing_obs = db.find_missing_obs(cityID, tdate)
#if *missing_bets returns False, it's a bool, missing obs returns either True or False
if type(missing_bets) != bool or missing_obs:
print("To many missing obs or parameters!")
continue
stations = db.get_stations_for_city(cityID,
active=False,
tdate=tdate)
#print output to file, first get prober filename
filename = path + utils.tdate2string(
tdate, moses=True) + "." + city['name'].lower()[0] + "pw"
f = open(filename, 'w')
tdate_str = utils.tdate2string(tdate)
print(file_head % tdate_str, file=f)
users = db.get_participants_in_city(cityID, tdate, sort=True)
for day in range(1, 3):
print(day_heads[day - 1], file=f)
print(table_head, file=f)
print(80 * "_", file=f)
for station in stations:
obs = [station.name]
for param in params:
paramID = db.get_parameter_id(param)
value = db.get_obs_data(cityID, paramID, tdate, day,
station.wmo)
if value is False or value is None: obs.append("n")
else:
obs.append(float(value) / 10)
print_rows(obs, f)
#print(78*"_", file=f)
print("", file=f)
for userID in users:
bet = [db.get_username_by_id(userID, which="display_name")]
for param in params:
paramID = db.get_parameter_id(param)
value = db.get_bet_data("user", userID, cityID,
paramID, tdate, day)
if value is False or value is None: bet.append("n")
else:
bet.append(float(value) / 10)
print_rows(bet, f)
if day == 1: print("\f", file=f)
else: print("\n", file=f)
print(ranking_str % tdate_str, file=f)
print(ranking_head, file=f)
#TODO: ranking
sleepyID = db.get_user_id("Sleepy")
sql = "SELECT bs.rank, wu.display_name, bs.points, bs.points_d1, bs.points_d2 FROM wp_wetterturnier_betstat bs JOIN wp_users wu ON userID = wu.ID WHERE tdate=%d AND cityID=%d AND userID != %d ORDER BY bs.rank"
cur = db.cursor()
cur.execute(sql % (tdate, cityID, sleepyID))
data = cur.fetchall()
for i in data:
print("{:2d}. {:26s}{:5.1f} ({:5.1f}/{:5.1f})".format(
i[0], i[1], i[2], i[3], i[4]),
file=f)
sql = "SELECT mean FROM wp_wetterturnier_tdatestats WHERE tdate = %d AND cityID = %d"
cur.execute(sql % (tdate, cityID))
mean = cur.fetchone()[0]
do, fr = db.get_user_id("Donnerstag"), db.get_user_id("Freitag")
sleepy = db.get_sleepy_points(cityID, tdate, [do, fr])
print(sleepy)
print(info_str % (mean, sleepy), file=f)
def CSP(db,config,cities,tdates):
import sys, os
import numpy as np
from pywetterturnier import utils
print('\n * Compute sum points to fill betstat table')
# ----------------------------------------------------------------
# - Now going over the cities and compute the points.
# ----------------------------------------------------------------
for city in cities:
# -------------------------------------------------------------
# - If aldates, take all tdates from database
# -------------------------------------------------------------
if config['input_alldates']:
tdates = db.all_tournament_dates( city['ID'] )
# -------------------------------------------------------------
# - Looping trough dates
# -------------------------------------------------------------
for tdate in tdates:
# ----------------------------------------------------------------
# - Check if we are allowed to perform the computation of the
# mean bets on this date
# ----------------------------------------------------------------
check = utils.datelock(config,tdate)
if check:
print(' Date is \'locked\' (datelock). Dont execute, skip.')
continue
# ----------------------------------------------------------
# - Which judgingclass do we have to take?
# It is possible that the scoring system changed.
# ----------------------------------------------------------
# Take the latest judgingclass changed in 2002-12-06
if tdate < 12027:
if config['input_ignore']:
print('[!] Judginglcass not defined - but started in ignore mode. Skip.')
continue
else:
utils.exit('I dont know which judgingclass I should use for this date. Stop.')
print(' For %s tournament is %s' % (city['name'], utils.tdate2string( tdate )))
# - If config['input_user'] is an integer value this
# is a userID. Compute the sum points for this user
# only. This is for some replay purposes.
if type(config['input_user']) == type(int()):
extra = ' AND userID = %d ' % config['input_user']
else:
extra = ''
sqlP = 'SELECT userID, cityID, tdate, ' + \
'round(sum(points),1) AS points, max(placed) AS submitted ' + \
'FROM %swetterturnier_bets ' + \
'WHERE cityID = %d AND tdate = %d %s ' + \
'GROUP BY userID, cityID, tdate '
sqlP = sqlP % (db.prefix,city['ID'], tdate, extra)
sqlX = 'SELECT userID, cityID, tdate, sum(points) AS points ' + \
'FROM %swetterturnier_bets ' + \
'WHERE cityID = %d AND tdate = %d ' + \
'AND betdate = %d %s ' + \
'GROUP BY userID, cityID, tdate'
sql1 = sqlX % (db.prefix,city['ID'], tdate, tdate+1, extra)
sql2 = sqlX % (db.prefix,city['ID'], tdate, tdate+2, extra)
sql_full = 'SELECT p.points AS points, d1.points AS points_d1, d2.points AS points_d2, ' + \
'p.userID, p.cityID, p.tdate ' + \
'FROM ('+sqlP+') AS p ' + \
'LEFT OUTER JOIN ' + \
'('+sql1+') AS d1 ON p.userID=d1.userID AND p.tdate=d1.tdate ' + \
'AND p.cityID = d1.cityID ' + \
'LEFT OUTER JOIN ' + \
'('+sql2+') AS d2 ON p.userID=d2.userID AND p.tdate=d2.tdate ' + \
'AND p.cityID = d2.cityID ' + \
''
print(' - Reading data from database')
cur = db.cursor()
cur.execute( sql_full )
desc = cur.description
data = cur.fetchall()
# Now compute
if len(data) == 0:
print(' - Sorry, got no data to compute sum points')
else:
# Else: we have data, update database
print(' - Upserting database (%d lines)' % len(data))
# Require the index of the "points" variable
points_idx = None
for di in range(0,len(desc)):
if str(desc[di][0]) == 'points':
points_idx = di
break
if points_idx is None:
sys.exit("ERROR: could not find variable \"points\" in data. Stop.")
# - Prepare the data
sql = 'INSERT INTO '+db.prefix+'wetterturnier_betstat ' + \
'SET rank=%s, points=%s, points_d1=%s, points_d2=%s, ' + \
'userID=%s, cityID=%s, tdate=%s ON DUPLICATE KEY UPDATE points=VALUES(points), ' + \
'points_d1 = VALUES(points_d1), points_d2 = VALUES(points_d2)'
#for d in data:
# if not int(d[0]) == 1130: continue
# print d
# Compute rank
points = []
for pi in range(0,len(data)):
points.append(data[pi][points_idx])
print(points)
try:
points = np.sort(points)[::-1]
except:
print("ERROR: error while computing ranks")
continue
#sys.exit(0)
rank = []
data = list(data)
for pi in range(0,len(data)):
this = data[pi][points_idx]
# if points is empty
# Append empty rank (IN FRONT) and skip
if not this:
data[pi] = (None,) + data[pi]
continue
# else search rank
# and append rank IN FRONT (check sql command)
rank = np.where(points == this)[0]
if not len(rank):
data[pi] = (None,) + data[pi]
else:
data[pi] = (rank[0] + 1,) + data[pi]
cur.executemany( sql , data )
db.commit()
def CSP(db,config,cities,tdates):
import sys, os
import numpy as np
from pywetterturnier import utils
print '\n * Compute sum points to fill betstat table'
# ----------------------------------------------------------------
# - Now going over the cities and compute the points.
# ----------------------------------------------------------------
for city in cities:
# -------------------------------------------------------------
# - If aldates, take all tdates from database
# -------------------------------------------------------------
if config['input_alldates']:
tdates = db.all_tournament_dates( city['ID'] )
# -------------------------------------------------------------
# - Looping trough dates
# -------------------------------------------------------------
for tdate in tdates:
print ' For %s tournament is %s' % (city['name'], utils.tdate2string( tdate ))
# - If config['input_user'] is an integer value this
# is a userID. Compute the sum points for this user
# only. This is for some replay purposes.
if type(config['input_user']) == type(int()):
extra = ' AND userID = %d ' % config['input_user']
else:
extra = ''
sqlP = 'SELECT userID, cityID, tdate, ' + \
'round(sum(points),1) AS points, max(placed) AS submitted ' + \
'FROM %swetterturnier_bets ' + \
'WHERE cityID = %d AND tdate = %d %s ' + \
'GROUP BY userID, cityID, tdate '
sqlP = sqlP % (db.prefix,city['ID'], tdate, extra)
sqlX = 'SELECT userID, cityID, tdate, sum(points) AS points ' + \
'FROM %swetterturnier_bets ' + \
'WHERE cityID = %d AND tdate = %d ' + \
'AND betdate = %d %s ' + \
'GROUP BY userID, cityID, tdate'
sql1 = sqlX % (db.prefix,city['ID'], tdate, tdate+1, extra)
sql2 = sqlX % (db.prefix,city['ID'], tdate, tdate+2, extra)
sql_full = 'SELECT p.points AS points, d1.points AS points_d1, d2.points AS points_d2, ' + \
'p.userID, p.cityID, p.tdate ' + \
'FROM ('+sqlP+') AS p ' + \
'LEFT OUTER JOIN ' + \
'('+sql1+') AS d1 ON p.userID=d1.userID AND p.tdate=d1.tdate ' + \
'AND p.cityID = d1.cityID ' + \
'LEFT OUTER JOIN ' + \
'('+sql2+') AS d2 ON p.userID=d2.userID AND p.tdate=d2.tdate ' + \
'AND p.cityID = d2.cityID ' + \
''
print ' - Reading data from database'
cur = db.cursor()
cur.execute( sql_full )
desc = cur.description
data = cur.fetchall()
# Now compute
if len(data) == 0:
print ' - Sorry, got no data to compute sum points'
else:
# Else: we have data, update database
print ' - Upserting database (%d lines)' % len(data)
# Require the index of the "points" variable
points_idx = None
for di in range(0,len(desc)):
if str(desc[di][0]) == 'points':
points_idx = di
break
if points_idx is None:
sys.exit("ERROR: could not find variable \"points\" in data. Stop.")
# - Prepare the data
sql = 'UPDATE IGNORE '+db.prefix+'wetterturnier_betstat ' + \
'SET rank=%s, points=%s, points_d1=%s, points_d2=%s ' + \
'WHERE userID=%s AND cityID=%s AND tdate=%s'
#for d in data:
# if not int(d[0]) == 1130: continue
# print d
# Compute rank
points = []
for pi in range(0,len(data)): points.append(data[pi][points_idx])
points = np.sort(points)[::-1]
rank = []
data = list(data)
for pi in range(0,len(data)):
this = data[pi][points_idx]
# if points is empty
# Append empty rank (IN FRONT) and skip
if not this:
data[pi] = (None,) + data[pi]
continue
# else search rank
# and append rank IN FRONT (check sql command)
rank = np.where(points == this)[0]
if not len(rank):
data[pi] = (None,) + data[pi]
else:
data[pi] = (rank[0] + 1,) + data[pi]
cur.executemany( sql , data )
db.commit()
