def format_gs_hitting(league, ls):
import gspread
import gspread_formatting as gsfmt
gc = gspread.service_account(filename='./bb-2021-2b810d2e3d25.json')
bb2021 = gc.open("BB 2021 " + league)
hitter_proj = bb2021.worksheet('Hitter Projections')
rate = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.000'))
cardinal = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0'))
value = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.0'))
headers = hitter_proj.row_values(1)
for header in headers:
column = chr(65 + headers.index(header))
print(header + ' is column ' + column)
if header in ls.hitting_counting_stats:
gsfmt.format_cell_range(hitter_proj, column + ':' + column,
cardinal)
elif header in ls.hitting_rate_stats:
gsfmt.format_cell_range(hitter_proj, column + ':' + column, rate)
elif header in ['zar', 'value']:
gsfmt.format_cell_range(hitter_proj, column + ':' + column, value)
def format_gsheet(sheet):
import gspread
import gspread_formatting as gsfmt
headers = sheet.row_values(1)
hitting_rate_stats = ['avg', 'obp', 'ops', 'slg']
hitting_rate_format = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.000'))
pitching_rate_stats = [
'era', 'whip', 'DRA', 'xxxFIP', 'gmli', 'wpa', 'kwera', 'xfip'
]
pitching_rate_format = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.00'))
counting_stats = [
'hr', 'r', 'rbi', 'sb', 'pa', 'ab', 'qs', 'w', 'so', 'sv', 'hld',
'svhld', 'ip', 'cFIP'
]
counting_format = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0'))
value_format = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.0'))
for header in headers:
colnum = chr(headers.index(header) + 97)
if header in hitting_rate_stats:
gsfmt.format_cell_range(sheet, colnum + ':' + colnum,
hitting_rate_format)
if header in pitching_rate_stats:
gsfmt.format_cell_range(sheet, colnum + ':' + colnum,
pitching_rate_format)
if header in counting_stats:
gsfmt.format_cell_range(sheet, colnum + ':' + colnum,
counting_format)
if header[0:4] == 'zar_' or header[0:6] == 'value_':
gsfmt.format_cell_range(sheet, colnum + ':' + colnum, value_format)
def format_gs_all(league, ls, type):
import gspread
import gspread_formatting as gsfmt
gc = gspread.service_account(filename='./bb-2021-2b810d2e3d25.json')
#bb2021 = gc.open("BB 2021 " + league)
bb2021 = gc.open("BB 2021 InSeason")
if type.lower() in ['hitter', 'hitters', 'hitting', 'batting']:
sh_proj = bb2021.worksheet('Hitter Projections - ' + ls.league_name)
elif type.lower() in ['pitcher', 'pitchers', 'pitching']:
sh_proj = bb2021.worksheet('Pitcher Projections - ' + ls.league_name)
rate2 = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.00'))
rate3 = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.000'))
cardinal = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0'))
value = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.0'))
headers = sh_proj.row_values(1)
for header in headers:
column = chr(65 + headers.index(header))
#print(header + ' is column ' + column)
if header in ls.hitting_counting_stats or header in ls.pitching_counting_stats or header in [
'pa', 'ip', 'g', 'gs'
]:
gsfmt.format_cell_range(sh_proj, column + ':' + column, cardinal)
elif header in ls.hitting_rate_stats:
gsfmt.format_cell_range(sh_proj, column + ':' + column, rate3)
elif header in ls.pitching_rate_stats:
gsfmt.format_cell_range(sh_proj, column + ':' + column, rate2)
elif header in ['zar', 'value']:
gsfmt.format_cell_range(sh_proj, column + ':' + column, value)
def compare(f_price):
prices = worksheet.col_values(2)[1:]
rows = 1
o_prices = list(map(lambda x: float(x.replace(",", ".")), prices))
for a, b in zip(f_price, o_prices):
rows += 1
if a < b:
worksheet.update_cell(rows, 2, a)
fmt = gsf.CellFormat(backgroundColor=gsf.Color(0, 1, 0),
textFormat=gsf.TextFormat(
bold=True,
foregroundColor=gsf.Color(0, 0, 0),
fontSize=10))
gsf.format_cell_range(worksheet, 'B' + str(rows) + '', fmt)
print('Product is now cheaper')
elif a == b:
print('Product price did not change')
else:
worksheet.update_cell(rows, 2, a)
fmt = gsf.CellFormat(backgroundColor=gsf.Color(1, 0, 0),
textFormat=gsf.TextFormat(
bold=True,
foregroundColor=gsf.Color(0, 0, 0),
fontSize=10))
gsf.format_cell_range(worksheet, 'B' + str(rows) + '', fmt)
print('Price has increased')
def sheet_formatter(self, sheetname):
working_sheet = self.sh.worksheet(sheetname)
sheetId = working_sheet.id
body = {
"requests": [{
"autoResizeDimensions": {
"dimensions": {
"sheetId": sheetId,
"dimension": 'COLUMNS',
"startIndex": 1,
"endIndex": 5
}
}
}]
}
self.sh.batch_update(body)
header_fmt = f.CellFormat(
backgroundColor=f.Color(0.043137256, 0.3254902, 0.5803922),
textFormat=f.TextFormat(bold=True,
foregroundColor=f.Color(1, 1, 1)),
horizontalAlignment='CENTER',
verticalAlignment='MIDDLE')
f.set_frozen(
working_sheet, rows=1,
cols=2) # freeze the headers and hotel_id and status column
f.format_cell_range(working_sheet, '1', header_fmt)
def format_gs_pitching():
import gspread
import gspread_formatting as gsfmt
gc = gspread.service_account(filename='./bb-2021-2b810d2e3d25.json')
bb2021 = gc.open("BB 2021 SoS")
rate = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.00'))
cardinal = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0'))
value = gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.0'))
gsfmt.format_cell_range(bb2021.worksheet('Pitcher Projections'), 'C:G',
cardinal)
gsfmt.format_cell_range(bb2021.worksheet('Pitcher Projections'), 'H:I',
rate)
gsfmt.format_cell_range(bb2021.worksheet('Pitcher Projections'), 'J:K',
value)
def create_combined_valuations(league):
import pandas as pd
import gspread
import gspread_dataframe as gsdf
import gspread_formatting as gsfmt
from general import postgres
from munging import player_names
from general import gs
assert league.league_name in ['SoS', 'Legacy']
#league = 'SoS'
#league = 'Legacy'
bbdb = postgres.connect_to_bbdb()
names = player_names.get_player_names()
gc = gspread.service_account(filename='./bb-2021-2b810d2e3d25.json')
#bb2021 = gc.open("BB 2021 " + league.league_name)
bb2021 = gc.open("BB 2021 InSeason")
combined_hitters = create_combined_hitter_valuations(league)
combined_pitchers = create_combined_pitcher_valuations(league)
hitter_projections = bb2021.worksheet('Hitter Projections - ' +
league.league_name)
bb2021.values_clear(hitter_projections.title + "!A:Z")
gsdf.set_with_dataframe(hitter_projections, combined_hitters)
hitter_projections.update
format_gs.format_gs_all(league=league, ls=league, type='hitting')
pitcher_projections = bb2021.worksheet('Pitcher Projections - ' +
league.league_name)
bb2021.values_clear(pitcher_projections.title + "!A:Z")
gsdf.set_with_dataframe(pitcher_projections, combined_pitchers)
pitcher_projections.update
format_gs.format_gs_all(league=league.league_name,
ls=league,
type='pitching')
combined = pd.concat([
combined_hitters[['name', 'fg_id', 'type', 'zar', 'value']],
combined_pitchers[['name', 'fg_id', 'type', 'zar', 'value']]
])
combined = combined.sort_values(by='value', ascending=False)
gs_combined = bb2021.worksheet('Combined Z')
gsdf.set_with_dataframe(gs_combined, combined)
gs_combined.update
gsfmt.format_cell_range(
gs_combined, 'D:E',
gsfmt.CellFormat(
numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.0')))
im = Image.open("pic.png").convert('RGB')
gf.set_row_height(ws, '1:' + str(height), 10)
gf.set_column_width(ws, 'A:' + i2s(width), 10)
formats = []
h_scale = im.height // height
w_scale = im.width // width
for i in range(height):
for j in range(width):
r_s, g_s, b_s = 0, 0, 0
cnt = 0
for x in range(i * h_scale, (i + 1) * h_scale):
for y in range(j * w_scale, (j + 1) * w_scale):
r, g, b = im.getpixel((x, y))
r_s += r
g_s += g
b_s += b
cnt += 1
cnt *= 255
fmt = gf.CellFormat(backgroundColor=gf.Color(r_s / cnt, g_s /
cnt, b_s / cnt))
formats.append((c2s(j + 1, i + 1) + ':' + c2s(j + 1, i + 1), fmt))
gf.format_cell_ranges(ws, formats)
