def get_sources():
sources = {}
pref = '/Users/lavanyasingh/Desktop/GSC2O19internet_archive/data/cleaned'
paths = os.listdir(pref)
paths.remove('.DS_Store')
for path in paths:
total = 0
with open('data/cleaned/' + path, 'r') as inf:
reader = csv.reader(inf, delimiter=',')
next(reader)
for item in reader:
if item[1].find('subject=') != -1:
None
else:
total += 1
url = helpers.truncate(item[1])
item[1] = url
val = check_sources(list(sources.keys()), url, path)
if val != -1:
for i in range(len(item)):
if not helpers.is_bad(item[i]):
sources[val][i] = item[i]
if len(url) < len(val): sources[val][1] = url
else:
item += ['' for i in range(10)]
if (item[0] == "United States"
or item[0].lower() == "us"
or item[0].lower() == "usa"):
item[0] == "United States of America"
if path != 'sheet_cleaned.csv':
item[7] = get_meta(path)
sources.update({url: item})
if total % 2500 == 0: print(path, total)
print(path, total)
return sources
def get_sources_sheet():
raw, total = helpers.get_sources(spreadsheet_id, service)
sources = []
for item in raw:
for url in item:
sources.append(helpers.truncate(item[url][2]))
return sources
def write_to_csv():
sources = get_sources()
with open(
'/Users/lavanyasingh/Desktop/GSC2O19internet_archive/data/newsgrabber_sources.csv',
'w',
errors='ignore') as outf:
w = csv.writer(outf,
delimiter=',',
quotechar='"',
quoting=csv.QUOTE_MINIMAL)
w.writerow(['country', 'source url', 'title', 'language', 'type'])
for source in sources:
print(helpers.truncate(source))
w.writerow([
'none' if i != 2 else helpers.truncate(source)
for i in range(5)
])
def read_cc():
with open("data/common_crawl.txt", "r") as f:
sources = set()
reader = csv.reader(f, delimiter='\t')
for line in reader:
sources.add(helpers.truncate(line[1]))
print("CC", len(sources))
return sources
def read_all():
sources = []
with open("data/raw/all_raw_cleaned3.csv", "r") as f:
reader = csv.reader(f, delimiter=',')
for line in reader:
sources.append(helpers.truncate(line[1]))
print(len(sources))
return sources
def read_in(path):
sources = []
total, uq = 0, 0
with open(path, 'r', errors='ignore') as f:
reader = csv.reader(f, delimiter=',')
for line in reader:
total += 1
row = [
'',
helpers.truncate(line[0]), '', '', '', '', '', '', '', '', '',
''
]
if row not in sources:
uq += 1
sources.append(row)
if total % 1000 == 0: print(helpers.truncate(line[0]))
print(path, total, uq)
return sources
def decode(cipher, key):
r = ''
c, k = truncate(cipher, key)
for i, j in zip(c, k):
if j == 0:
r+="_"
continue
r += chr(i ^ j)
return r
def make_all_data(self, infile):
rows = {}
with open('data/' + infile, 'r') as inf:
reader = csv.reader(inf, delimiter=',')
for line in reader:
# clean URL
url = helpers.truncate(line[1])
line[1] = url
if len(rows) % 5000 == 0: print(url)
# get path ('/money' or '/index' for example)
o = urllib.parse.urlparse(self.prep_url(line[1]))
path = o.path
# add metasource
metasource = self.clean_meta(line[7])
line[7] = [metasource]
# check for various spellings of USA
if (line[0] == "United States" or line[0].lower() == "us"
or line[0].lower() == "usa"):
line[0] == "United States of America"
# extend row
line += ['', '']
# if unique url, add path
if url not in rows:
if self.url_is_good(url):
if self.path_is_good(path):
line[13] = [path]
rows.update({url: line})
else:
# add metasource if necessary
if metasource not in rows[url][7]:
rows[url][7].append(metasource)
# update any broken metadata to new value
for i in range(len(rows[url]) - 1):
if helpers.is_bad(rows[url][i]):
try:
rows[url][i] = line[i]
except:
pass
# add path if good
if self.path_is_good(path):
rows[url][13].append(path)
return rows
def pitching():
rows = db.execute(
"SELECT player, SUM(ip), SUM(hp), SUM(rp), SUM(er), SUM(bbp), SUM(kp), SUM(w), SUM(l), SUM(s), SUM(so), AVG(er), AVG(ip) FROM playerStats GROUP BY player"
)
if not rows:
return apology("Unable to load data", 400)
for x in rows:
x['ip'] = float(x['SUM(ip)'])
x['kp'] = int(x['SUM(kp)'])
if x['ip'] > 0:
era = truncate((9 * x['AVG(er)']) / (x['AVG(ip)']), 2)
x['era'] = era
else:
x['era'] = 'N/A'
x['ip'] = "{:.2f}".format(float(x['SUM(ip)']))
rowsort = sorted(rows, key=itemgetter('SUM(ip)'), reverse=True)
totals = db.execute(
"SELECT player, SUM(ip), SUM(hp), SUM(rp), SUM(er), SUM(bbp), SUM(kp), SUM(w), SUM(l), SUM(s), SUM(so), AVG(er), AVG(ip) FROM playerStats"
)
if not totals:
return apology("Unable to load data", 400)
for x in totals:
x['ip'] = float(x['SUM(ip)'])
x['kp'] = int(x['SUM(kp)'])
if x['ip'] > 0:
era = truncate((9 * x['AVG(er)']) / (x['AVG(ip)']), 2)
x['era'] = era
else:
x['era'] = 'N/A'
if x['ip'] > 0:
oppsba = truncate(
int(x['SUM(hp)']) / ((x['ip'] * 3) + int(x['SUM(hp)'])), 3)
whip = truncate(
(int(x['SUM(bbp)']) + int(x['SUM(hp)'])) / (x['ip']), 3)
else:
oppsba = "N/A"
whip = "N/A"
if x['ip'] > 0:
oppsba = truncate(
int(x['SUM(hp)']) / ((x['ip'] * 3) + int(x['SUM(hp)'])), 3)
whip = truncate(
(int(x['SUM(bbp)']) + int(x['SUM(hp)'])) / (x['ip']), 3)
else:
oppsba = "N/A"
whip = "N/A"
x['ip'] = "{:.2f}".format(float(x['SUM(ip)']))
return render_template("pitching.html",
rowsort=rowsort,
totals=totals,
oppsba=oppsba,
whip=whip)
def read_in(path):
sources = []
total, uq = 0, 0
with open(path, 'r', errors = 'ignore') as f:
reader = csv.reader(f, delimiter=',')
next(reader)
for line in reader:
total += 1
row = ['United States', helpers.truncate(line[3]), line[2], 'English', 'Newspaper', line[4],
line[0], line[1], line[7]]
if row not in sources:
uq += 1
sources.append(row)
print ('total', total)
print ('unique', uq)
return sources
def read_us():
sources, urls = [], []
total, uq = 0, 0
with open('data/us_news.csv', 'r') as f:
reader = csv.reader(f, delimiter=',')
for line in reader:
total += 1
url = helpers.truncate(line[2])
print(url)
if url not in urls:
uq += 1
urls.append(url)
sources.append([
'United States', url, line[1], 'English', line[3], line[1],
'', 'original', line[0], '', '', '', ''
])
print(total, uq)
return sources
def read_lion():
sources, urls = [], []
total, uq = 0, 0
with open('data/lion.csv', 'r') as f:
reader = csv.reader(f, delimiter=',')
for line in reader:
total += 1
url = helpers.truncate(line[1])
print(url)
if url not in urls:
uq += 1
urls.append(url)
sources.append([
'United States', url, line[0], 'English', 'Newspaper',
line[0], '', 'lion', line[5], line[4], '', '', ''
])
print(total, uq)
return sources
def recent_comments():
comments_q = meta.Session.query(model.Comment).filter(model.Comment.approved==True)
comments_q = comments_q.order_by(model.comments_table.c.created_on.desc())
recent_comments = comments_q.join(model.Post).limit(4)
if recent_comments is None:
return ''
else:
comments= []
template = """
<div id="wurdig-recent-comments" class="wurdig-sidebar-list">
<h2>Newest Comments</h2>
<ul>
%s
</ul>
</div>
"""
for comment in recent_comments:
i = """
<li class="%s">
<span class="lone">%s shared: </span>
<span>%s</span>
<span>Shared in: %s</span>
</li>
"""
name = comment.name
if comment.url is not None:
name = h.link_to(comment.name, comment.url)
content = h.truncate(h.strip_tags(comment.content), 80)
link = h.link_to(
comment.posts.title,
h.url_for(
controller='post',
action='view',
year=comment.posts.posted_on.strftime('%Y'),
month=comment.posts.posted_on.strftime('%m'),
slug=comment.posts.slug,
anchor=u"comment-" + str(comment.id)
)
)
comments.append(i % (comment.id, name, content, link))
return template % '\n'.join(comments)
def read_usnpl():
sources, urls = [], []
total, uq = 0, 0
with open('data/usnpl_wiki_list.csv', 'r') as f:
reader = csv.reader(f, delimiter=',')
for line in reader:
total += 1
url = helpers.truncate(line[3])
if total % 1000 == 0: print(url)
if url not in urls:
uq += 1
urls.append(url)
# additional entries: [state, town, wikipedia name, redirects?(Y/N), wikipedia link]
sources.append([
'United States', url, line[2], 'English', 'Newspaper',
line[2], '', 'USNPL', line[0], line[1], line[4], line[6],
line[7]
])
print(total, uq)
return sources
def get_sources():
sources = []
pref = '/Users/lavanyasingh/Desktop/GSC2O19internet_archive/data/cleaned'
paths = os.listdir(pref)
total = 0
paths.remove('.DS_Store')
paths.remove('all.csv')
for path in paths:
with open('data/cleaned/' + path, 'r') as inf:
reader = csv.reader(inf, delimiter=',')
next(reader)
for item in reader:
total += 1
url = helpers.truncate(item[1])
if path != 'sheet_cleaned.csv':
meta = get_meta(path)
else:
meta = item[7]
sources.append([url, meta])
print("total", total)
return sources
def home():
## Figure out if it's baseball season yet, if not display last year's info
now = datetime.now()
if now.month < 5:
year = int(now.year) - 1
else:
year = int(now.year)
ranks = db.execute('SELECT * FROM playerStats where year = :year',
year=year)
if not ranks:
return apology("Unable to load data", 400)
ranks = prep(ranks)
## Tabulate current highest ranking players
rows = db.execute(
'SELECT * FROM standings WHERE year = :year AND team = "Padres"',
year=year)
## Determine number of games played
for row in rows:
games = (int(row['win']) + int(row['loss']) + int(row['tie'])) * 2.1
## Determine if min is hit to be included in current rankings
avgs = db.execute(
'SELECT player, h, ab FROM playerStats where year = :year AND ab > :games',
year=year,
games=games)
for aver in avgs:
if aver['ab'] > 0:
avg = truncate(aver['h'] / aver['ab'], 3)
aver['avg'] = avg
## Calculate current rankings
avgsort = sorted(avgs, key=itemgetter('avg'), reverse=True)[:5]
for x in avgsort:
x['avg'] = "{:.3f}".format(float(x['avg']))
hsort = sorted(ranks, key=itemgetter('h'), reverse=True)[:5]
rsort = sorted(ranks, key=itemgetter('r'), reverse=True)[:5]
rbisort = sorted(ranks, key=itemgetter('rbi'), reverse=True)[:5]
sbsort = sorted(ranks, key=itemgetter('sb'), reverse=True)[:5]
tbsort = sorted(ranks, key=itemgetter('tb'), reverse=True)[:5]
wsort = sorted(ranks, key=itemgetter('w'), reverse=True)[:2]
kpsort = sorted(ranks, key=itemgetter('kp'), reverse=True)[:2]
ipsort = sorted(ranks, key=itemgetter('ip'), reverse=True)[:2]
for x in ipsort:
x['ip'] = "{:.2f}".format(float(x['ip']))
## Determine if min is hit to be included in current rankings
eras = db.execute(
'SELECT player, er, ip FROM playerStats where year = :year AND ip > 10',
year=year)
for eraa in eras:
if eraa['ip'] > 0:
era = (9 * eraa['er']) / (eraa['ip'])
eraa['era'] = era
else:
eraa['era'] = " "
erasort = sorted(eras, key=itemgetter('era'), reverse=False)[:2]
for x in erasort:
if x['era'] == 100000000:
x['era'] = 'N/A'
else:
x['era'] = "{:.2f}".format(float(x['era']))
## Tabulate current standings across league
rows = db.execute('SELECT * FROM standings WHERE year = :year', year=year)
if not rows:
return apology("Unable to load data", 400)
## Pull recap data
res = db.execute('SELECT * FROM recap')
recapsort = sorted(res, key=itemgetter('date'), reverse=True)[:5]
return render_template("home.html",
recapsort=recapsort,
rows=rows,
ranks=ranks,
avgsort=avgsort,
hsort=hsort,
rsort=rsort,
rbisort=rbisort,
sbsort=sbsort,
tbsort=tbsort,
wsort=wsort,
kpsort=kpsort,
ipsort=ipsort,
erasort=erasort,
year=year)
def hitting():
rows = db.execute(
"SELECT player, SUM(ab), SUM(r), SUM(h), SUM(rbi), SUM(twob), SUM(threeb), SUM(hr), SUM(sac), SUM(sb), SUM(hbp), SUM(bb), SUM(k), AVG(h), AVG(hbp), AVG(bb), AVG(ab), AVG(twob), AVG(threeb), AVG(hr) FROM playerStats GROUP BY player"
)
if not rows:
return apology("Unable to load data", 400)
for x in rows:
oneb = int(x['SUM(h)']) - (int(x['SUM(twob)']) +
int(x['SUM(threeb)']) + int(x['SUM(hr)']))
x['oneb'] = oneb
x['tb'] = int(x['oneb'] +
((int(x['SUM(twob)']) * 2) +
(int(x['SUM(threeb)']) * 3) + (int(x['SUM(hr)']) * 4)))
if int(x['SUM(ab)']) > 0:
avg = truncate(x['AVG(h)'] / x['AVG(ab)'], 3)
x['avg'] = avg
slg = truncate((x['tb'] / int(x['SUM(ab)'])), 3)
x['slg'] = slg
else:
x['avg'] = 'N/A'
x['slg'] = 'N/A'
if int(x['SUM(ab)']) + x['SUM(hbp)'] + int(x['SUM(bb)']) > 0:
obp = truncate((x['AVG(h)'] + x['AVG(hbp)'] + x['AVG(bb)']) /
(x['AVG(ab)'] + x['AVG(hbp)'] + x['AVG(bb)']), 3)
x['obp'] = obp
else:
x['obp'] = 'N/A'
rows = sorted(rows, key=itemgetter('SUM(ab)'), reverse=True)
totals = db.execute(
"SELECT player, SUM(ab), SUM(r), SUM(h), SUM(rbi), SUM(twob), SUM(threeb), SUM(hr), SUM(sac), SUM(sb), SUM(hbp), SUM(bb), SUM(k), AVG(h), AVG(hbp), AVG(bb), AVG(ab), AVG(twob), AVG(threeb), AVG(hr) FROM playerStats"
)
if not totals:
return apology("Unable to load data", 400)
for x in totals:
oneb = int(x['SUM(h)']) - (int(x['SUM(twob)']) +
int(x['SUM(threeb)']) + int(x['SUM(hr)']))
x['oneb'] = oneb
x['tb'] = int(x['oneb'] +
((int(x['SUM(twob)']) * 2) +
(int(x['SUM(threeb)']) * 3) + (int(x['SUM(hr)']) * 4)))
if int(x['SUM(ab)']) > 0:
avg = truncate(x['AVG(h)'] / x['AVG(ab)'], 3)
x['avg'] = avg
slg = truncate((x['tb'] / int(x['SUM(ab)'])), 3)
x['slg'] = slg
else:
x['avg'] = 'N/A'
x['slg'] = 'N/A'
if int(x['SUM(ab)']) + x['SUM(hbp)'] + int(x['SUM(bb)']) > 0:
obp = truncate((x['AVG(h)'] + x['AVG(hbp)'] + x['AVG(bb)']) /
(x['AVG(ab)'] + x['AVG(hbp)'] + x['AVG(bb)']), 3)
x['obp'] = obp
else:
x['obp'] = 'N/A'
return render_template("hitting.html", rows=rows, totals=totals)
def year():
if request.method == "POST":
## Pull data from database for players
rows = db.execute(
"SELECT * FROM playerStats WHERE year = :year ORDER BY player",
year=request.form.get('years'))
if not rows:
return apology("Unable to load data", 400)
rows = prep(rows)
rows = sorted(rows, key=itemgetter('avg'), reverse=True)
erasorts = sorted(rows, key=itemgetter('era'), reverse=False)
for erasort in erasorts:
if erasort['era'] == 100000000:
erasort['era'] = 'N/A'
else:
erasort['era'] = "{:.2f}".format(float(erasort['era']))
totals = db.execute(
"SELECT SUM(ab), SUM(r), SUM(h), SUM(rbi), SUM(twob), SUM(threeb), SUM(hr), SUM(sac), SUM(sb), SUM(hbp), SUM(bb), SUM(k), SUM(ip), SUM(hp), SUM(rp), SUM(er), SUM(bbp), SUM(kp), SUM(w), SUM(l), SUM(s), SUM(so), AVG(er), AVG(ip), AVG(h), AVG(hbp), AVG(bb), AVG(ab), AVG(twob), AVG(threeb), AVG(hr) FROM playerStats WHERE year = :year",
year=request.form.get('years'))
if not totals:
return apology("Unable to load data", 400)
for x in totals:
oneb = int(x['SUM(h)']) - (int(x['SUM(twob)']) + int(
x['SUM(threeb)']) + int(x['SUM(hr)']))
x['oneb'] = oneb
x['tb'] = int(x['oneb'] + ((int(x['SUM(twob)']) * 2) +
(int(x['SUM(threeb)']) * 3) +
(int(x['SUM(hr)']) * 4)))
x['ip'] = float(x['SUM(ip)'])
x['kp'] = int(x['SUM(kp)'])
if int(x['SUM(ab)']) > 0:
avg = truncate(x['AVG(h)'] / x['AVG(ab)'], 3)
x['avg'] = avg
slg = truncate((x['tb'] / int(x['SUM(ab)'])), 3)
x['slg'] = slg
else:
x['avg'] = 'N/A'
x['slg'] = 'N/A'
if int(x['SUM(ab)']) + x['SUM(hbp)'] + int(x['SUM(bb)']) > 0:
obp = truncate((x['AVG(h)'] + x['AVG(hbp)'] + x['AVG(bb)']) /
(x['AVG(ab)'] + x['AVG(hbp)'] + x['AVG(bb)']), 3)
x['obp'] = obp
else:
x['obp'] = 'N/A'
if x['ip'] > 0:
era = truncate((9 * x['AVG(er)']) / (x['AVG(ip)']), 2)
x['era'] = era
else:
x['era'] = 'N/A'
if x['ip'] > 0:
oppsba = truncate(
int(x['SUM(hp)']) / ((x['ip'] * 3) + int(x['SUM(hp)'])), 3)
whip = truncate(
(int(x['SUM(bbp)']) + int(x['SUM(hp)'])) / (x['ip']), 3)
else:
oppsba = "N/A"
whip = "N/A"
x['ip'] = "{:.2f}".format(float(x['SUM(ip)']))
return render_template("selectedyear.html",
rows=rows,
totals=totals,
oppsba=oppsba,
whip=whip,
year=request.form.get('years'),
erasorts=erasorts)
if request.method == "GET":
## Pull data from database for players
rows = db.execute(
"SELECT DISTINCT year FROM playerStats ORDER BY year")
if not rows:
return apology("Unable to load data", 400)
return render_template("year.html", rows=rows)
def legacy():
## When the form is completed
if request.method == 'POST':
rows = db.execute(
"SELECT * FROM playerStats WHERE player = :player ORDER BY year",
player=request.form.get("legplayers"))
player = request.form.get('legplayers')
if not rows:
return apology("Unable to load data", 400)
rows = prep(rows)
for row in rows:
if row['era'] == 100000000:
row['era'] = 'N/A'
else:
row['era'] = "{:.2f}".format(row['era'])
## Find total for all player data
totals = db.execute(
"SELECT SUM(ab), SUM(r), SUM(h), SUM(rbi), SUM(twob), SUM(threeb), SUM(hr), SUM(sac), SUM(sb), SUM(hbp), SUM(bb), SUM(k), SUM(ip), SUM(hp), SUM(rp), SUM(er), SUM(bbp), SUM(kp), SUM(w), SUM(l), SUM(s), SUM(so), AVG(er), AVG(ip), AVG(h), AVG(hbp), AVG(bb), AVG(ab), AVG(twob), AVG(threeb), AVG(hr) FROM playerStats WHERE player = :play",
play=request.form.get('legplayers'))
if not totals:
return apology("Unable to load data", 400)
for x in totals:
oneb = int(x['SUM(h)']) - (int(x['SUM(twob)']) + int(
x['SUM(threeb)']) + int(x['SUM(hr)']))
x['oneb'] = oneb
x['tb'] = int(x['oneb'] + ((int(x['SUM(twob)']) * 2) +
(int(x['SUM(threeb)']) * 3) +
(int(x['SUM(hr)']) * 4)))
x['ip'] = float(x['SUM(ip)'])
x['kp'] = int(x['SUM(kp)'])
if int(x['SUM(ab)']) > 0:
avg = truncate(x['AVG(h)'] / x['AVG(ab)'], 3)
x['avg'] = avg
slg = truncate((x['tb'] / int(x['SUM(ab)'])), 3)
x['slg'] = slg
else:
x['avg'] = 'N/A'
x['slg'] = 'N/A'
if int(x['SUM(ab)']) + x['SUM(hbp)'] + int(x['SUM(bb)']) > 0:
obp = truncate((x['AVG(h)'] + x['AVG(hbp)'] + x['AVG(bb)']) /
(x['AVG(ab)'] + x['AVG(hbp)'] + x['AVG(bb)']), 3)
x['obp'] = obp
else:
x['obp'] = 'N/A'
if x['ip'] > 0:
era = truncate((9 * x['AVG(er)']) / (x['AVG(ip)']), 2)
x['era'] = era
else:
x['era'] = 'N/A'
if x['ip'] > 0:
oppsba = truncate(
int(x['SUM(hp)']) / ((x['ip'] * 3) + int(x['SUM(hp)'])), 3)
whip = truncate(
(int(x['SUM(bbp)']) + int(x['SUM(hp)'])) / (x['ip']), 3)
else:
oppsba = "N/A"
whip = "N/A"
x['ip'] = "{:.2f}".format(float(x['SUM(ip)']))
return render_template("selectedlegacy.html",
rows=rows,
totals=totals,
oppsba=oppsba,
whip=whip,
player=player)
else:
## Pull data from database for players
rows = db.execute(
"SELECT name FROM players WHERE status = 'legacy' ORDER BY name")
if not rows:
return apology("Unable to load data", 400)
return render_template("legacy.html", rows=rows)
def records():
ranks = db.execute(
"SELECT player, SUM(ab), SUM(r), SUM(h), SUM(rbi), SUM(twob), SUM(threeb), SUM(hr), SUM(sac), SUM(sb), SUM(hbp), SUM(bb), SUM(k), SUM(ip), SUM(hp), SUM(rp), SUM(er), SUM(bbp), SUM(kp), SUM(w), SUM(l), SUM(s), SUM(so), AVG(er), AVG(ip), AVG(h), AVG(hbp), AVG(bb), AVG(ab), AVG(twob), AVG(threeb), AVG(hr) FROM playerStats GROUP BY player"
)
if not ranks:
return apology("Unable to load data", 400)
for x in ranks:
oneb = int(x['SUM(h)']) - (int(x['SUM(twob)']) +
int(x['SUM(threeb)']) + int(x['SUM(hr)']))
x['oneb'] = oneb
x['tb'] = int(x['oneb'] +
((int(x['SUM(twob)']) * 2) +
(int(x['SUM(threeb)']) * 3) + (int(x['SUM(hr)']) * 4)))
x['ip'] = float(x['SUM(ip)'])
x['kp'] = int(x['SUM(kp)'])
if int(x['SUM(ab)']) > 0:
avg = truncate(x['AVG(h)'] / x['AVG(ab)'], 3)
x['avg'] = avg
slg = truncate((x['tb'] / int(x['SUM(ab)'])), 3)
x['slg'] = slg
else:
x['avg'] = 'N/A'
x['slg'] = 'N/A'
if int(x['SUM(ab)']) + x['SUM(hbp)'] + int(x['SUM(bb)']) > 0:
obp = truncate((x['AVG(h)'] + x['AVG(hbp)'] + x['AVG(bb)']) /
(x['AVG(ab)'] + x['AVG(hbp)'] + x['AVG(bb)']), 3)
x['obp'] = obp
else:
x['obp'] = 'N/A'
if x['ip'] > 0:
era = truncate((9 * x['AVG(er)']) / (x['AVG(ip)']), 2)
x['era'] = era
else:
x['era'] = 'N/A'
if x['ip'] > 0:
oppsba = truncate(
int(x['SUM(hp)']) / ((x['ip'] * 3) + int(x['SUM(hp)'])), 3)
whip = truncate(
(int(x['SUM(bbp)']) + int(x['SUM(hp)'])) / (x['ip']), 3)
else:
oppsba = "N/A"
whip = "N/A"
x['ip'] = "{:.2f}".format(float(x['SUM(ip)']))
if int(x['SUM(ab)']) > 200:
avg = truncate(int(x['SUM(h)']) / int(x['SUM(ab)']), 3)
x['avg'] = float(avg)
else:
x['avg'] = 0
## Calculate current rankings
avgsort = sorted(ranks, key=itemgetter('avg'), reverse=True)[:5]
for x in avgsort:
x['avg'] = "{:.3f}".format(float(x['avg']))
hsort = sorted(ranks, key=itemgetter('SUM(h)'), reverse=True)[:5]
rsort = sorted(ranks, key=itemgetter('SUM(r)'), reverse=True)[:5]
dsort = sorted(ranks, key=itemgetter('SUM(twob)'), reverse=True)[:5]
tsort = sorted(ranks, key=itemgetter('SUM(threeb)'), reverse=True)[:5]
hrsort = sorted(ranks, key=itemgetter('SUM(hr)'), reverse=True)[:5]
rbisort = sorted(ranks, key=itemgetter('SUM(rbi)'), reverse=True)[:5]
bbsort = sorted(ranks, key=itemgetter('SUM(bb)'), reverse=True)[:5]
hbpsort = sorted(ranks, key=itemgetter('SUM(hbp)'), reverse=True)[:5]
sbsort = sorted(ranks, key=itemgetter('SUM(sb)'), reverse=True)[:5]
tbsort = sorted(ranks, key=itemgetter('tb'), reverse=True)[:5]
wsort = sorted(ranks, key=itemgetter('SUM(w)'), reverse=True)[:5]
kpsort = sorted(ranks, key=itemgetter('SUM(kp)'), reverse=True)[:5]
ipsort = sorted(ranks, key=itemgetter('SUM(ip)'), reverse=True)[:5]
for x in ipsort:
x['ip'] = "{:.2f}".format(float(x['ip']))
## Determine if min is hit to be included in current rankings ##
eras = db.execute(
'SELECT player, SUM(er), SUM(ip), SUM(s), SUM(so) FROM playerStats WHERE ip > 1 GROUP BY player'
)
for x in eras:
if x['SUM(ip)'] > 74:
era = (9 * (x['SUM(er)'])) / (x['SUM(ip)'])
x['era'] = era
else:
x['era'] = 1000000
erasort = sorted(eras, key=itemgetter('era'), reverse=False)[:5]
for x in erasort:
x['era'] = "{:.2f}".format(float(x['era']))
ssort = sorted(ranks, key=itemgetter('SUM(s)'), reverse=True)[:5]
sosort = sorted(ranks, key=itemgetter('SUM(so)'), reverse=True)[:5]
return render_template("records.html",
hrsort=hrsort,
sosort=sosort,
ssort=ssort,
hbpsort=hbpsort,
bbsort=bbsort,
ranks=ranks,
dsort=dsort,
tsort=tsort,
avgsort=avgsort,
hsort=hsort,
rsort=rsort,
rbisort=rbisort,
sbsort=sbsort,
tbsort=tbsort,
wsort=wsort,
kpsort=kpsort,
ipsort=ipsort,
erasort=erasort)