def unpack_game_values(nfl_data, week):
"""
:param nfl_data:
:param week:
:return:
"""
nfl_data = nfl_data[[0, 4, 'Time', 'Date']]
nfl_data.columns = ['Teams', 'Spread', 'Time', 'Date']
nfl_data['week'] = week
nfl_data.reset_index(drop=True, inplace=True)
nfl_data['DateTime'] = nfl_data['Date'] + ' ' + nfl_data['Time'].str.strip(
)
nfl_data['DateTime'] = pd.to_datetime(nfl_data['DateTime'],
format='%A %b %d, %Y %I:%M %p')
nfl_data['time'] = nfl_data['DateTime'].apply(
lambda x: convert_tz(x, est_to_utc=True))
nfl_data.drop(['DateTime', 'Date', 'Time'], axis=1, inplace=True)
nfl_data.reset_index(inplace=True)
nfl_data['group_col'] = nfl_data['index'].apply(lambda i: i + 2
if i % 2 == 0 else i + 1)
nfl_data['Spread'] = nfl_data['Spread'].str.replace('PK', '0')
nfl_data['Spread'] = nfl_data['Spread'].astype(float)
nfl_data['Spread'] = nfl_data['Spread'].apply(lambda x: x
if x <= 0 else np.nan)
nfl_data = nfl_data.groupby('group_col').apply(fill_in_all_spreads)
nfl_data.Spread.fillna(nfl_data.reverse_spread, inplace=True)
nfl_data['Teams'] = nfl_data['Teams'].str.replace(' «', '')
return nfl_data.drop(['index', 'reverse_spread'], axis=1)
def update_games_with_score(weekly_scores_df, current_week, prod_str):
"""
:param weekly_scores_df:
:return:
"""
exist_games_q = """
select id, home_team_id, away_team_id, home_spread from games
where week = {} and game_status not in ('Final', 'Final (OT)') or game_status is NULL
and '{}' >= time;""".format(
current_week,
convert_tz(dt.now(), est_to_utc=True).strftime('%Y-%m-%d %H:%M:%S'))
exist_games_df = pd.read_sql(exist_games_q, prod_str)
weekly_scores_df = pd.merge(weekly_scores_df,
exist_games_df,
how='left',
on=['home_team_id', 'away_team_id'])
weekly_scores_df = weekly_scores_df[
(weekly_scores_df['home_team_score'].notnull())
& (weekly_scores_df['away_team_score'].notnull()) &
(weekly_scores_df['home_spread'].notnull())]
weekly_scores_df = weekly_scores_df.apply(calc_spread_winner, axis=1)
final_games = weekly_scores_df[weekly_scores_df['game_live'] == 0]
live_game = weekly_scores_df[(weekly_scores_df['game_live'] == 1)]
if len(final_games) > 0:
for i, row in final_games.iterrows():
update_q = """
UPDATE games SET
spread_winner_id = {0}, moneyline_winner_id = {1}, push = {2},
home_team_score = {3}, away_team_score = {4}, game_status = '{5}'
WHERE
id = {6}""".format(
row['spread_winner_id'] if pd.notnull(row['spread_winner_id'])
else "NULL", row['moneyline_winner_id'], row['push'],
row['home_team_score'], row['away_team_score'],
row['game_status'], row['id'])
for x in range(0, 2):
while True:
try:
update(prod_str, update_q)
except DatabaseError:
continue
break
if not live_game.empty:
for i, row in live_game.iterrows():
update_q = """
UPDATE games SET
home_team_score = {0}, away_team_score = {1}, game_status = '{2}'
WHERE
id = {3}""".format(row['home_team_score'],
row['away_team_score'], row['game_status'],
row['id'])
for x in range(0, 2):
while True:
try:
update(prod_str, update_q)
except DatabaseError:
continue
break
def is_there_game_on(current_week, prod_str):
"""
:return:
"""
games = pd.read_sql(
'select time from games where week = {} order by time ASC;'.format(
current_week), prod_str)
games['start_time'] = games['time'].apply(convert_tz)
games['end_time'] = games['start_time'] + td(hours=4)
now = convert_tz(dt.now())
games['game_happening'] = games.apply(
lambda row: 1 if row['start_time'] <= now <= row['end_time'] else 0,
axis=1)
return 1 if any(games['game_happening']) else 0