app.py

import pulp
import numpy as np
import pandas as pd
import streamlit as st
import gspread
from itertools import combinations

scope = ['https://www.googleapis.com/auth/spreadsheets',
          "https://www.googleapis.com/auth/drive"]

credentials = {
  "type": "service_account",
  "project_id": "sheets-api-connect-378620",
  "private_key_id": "1005124050c80d085e2c5b344345715978dd9cc9",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCtKa01beXwc88R\nnPZVQTNPVQuBnbwoOfc66gW3547ja/UEyIGAF112dt/VqHprRafkKGmlg55jqJNt\na4zceLKV+wTm7vBu7lDISTJfGzCf2TrxQYNqwMKE2LOjI69dBM8u4Dcb4k0wcp9v\ntW1ZzLVVuwTvmrg7JBHjiSaB+x5wxm/r3FOiJDXdlAgFlytzqgcyeZMJVKKBQHyJ\njEGg/1720A0numuOCt71w/2G0bDmijuj1e6tH32MwRWcvRNZ19K9ssyDz2S9p68s\nYDhIxX69OWxwScTIHLY6J2t8txf/XMivL/636fPlDADvBEVTdlT606n8CcKUVQeq\npUVdG+lfAgMBAAECggEAP38SUA7B69eTfRpo658ycOs3Amr0JW4H/bb1rNeAul0K\nZhwd/HnU4E07y81xQmey5kN5ZeNrD5EvqkZvSyMJHV0EEahZStwhjCfnDB/cxyix\nZ+kFhv4y9eK+kFpUAhBy5nX6T0O+2T6WvzAwbmbVsZ+X8kJyPuF9m8ldcPlD0sce\ntj8NwVq1ys52eosqs7zi2vjt+eMcaY393l4ls+vNq8Yf27cfyFw45W45CH/97/Nu\n5AmuzlCOAfFF+z4OC5g4rei4E/Qgpxa7/uom+BVfv9G0DIGW/tU6Sne0+37uoGKt\nW6DzhgtebUtoYkG7ZJ05BTXGp2lwgVcNRoPwnKJDxQKBgQDT5wYPUBDW+FHbvZSp\nd1m1UQuXyerqOTA9smFaM8sr/UraeH85DJPEIEk8qsntMBVMhvD3Pw8uIUeFNMYj\naLmZFObsL+WctepXrVo5NB6RtLB/jZYxiKMatMLUJIYtcKIp+2z/YtKiWcLnwotB\nWdCjVnPTxpkurmF2fWP/eewZ+wKBgQDRMtJg7etjvKyjYNQ5fARnCc+XsI3gkBe1\nX9oeXfhyfZFeBXWnZzN1ITgFHplDznmBdxAyYGiQdbbkdKQSghviUQ0igBvoDMYy\n1rWcy+a17Mj98uyNEfmb3X2cC6WpvOZaGHwg9+GY67BThwI3FqHIbyk6Ko09WlTX\nQpRQjMzU7QKBgAfi1iflu+q0LR+3a3vvFCiaToskmZiD7latd9AKk2ocsBd3Woy9\n+hXXecJHPOKV4oUJlJgvAZqe5HGBqEoTEK0wyPNLSQlO/9ypd+0fEnArwFHO7CMF\nycQprAKHJXM1eOOFFuZeQCaInqdPZy1UcV5Szla4UmUZWkk1m24blHzXAoGBAMcA\nyH4qdbxX9AYrC1dvsSRvgcnzytMvX05LU0uF6tzGtG0zVlub4ahvpEHCfNuy44UT\nxRWW/oFFaWjjyFxO5sWggpUqNuHEnRopg3QXx22SRRTGbN45li/+QAocTkgsiRh1\nqEcYZsO4mPCsQqAy6E2p6RcK+Xa+omxvSnVhq0x1AoGAKr8GdkCl4CF6rieLMAQ7\nLNBuuoYGaHoh8l5E2uOQpzwxVy/nMBcAv+2+KqHEzHryUv1owOi6pMLv7A9mTFoS\n18B0QRLuz5fSOsVnmldfC9fpUc6H8cH1SINZpzajqQA74bPwELJjnzrCnH79TnHG\nJuElxA33rFEjbgbzdyrE768=\n-----END PRIVATE KEY-----\n",
  "client_email": "gspread-connection@sheets-api-connect-378620.iam.gserviceaccount.com",
  "client_id": "106625872877651920064",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/gspread-connection%40sheets-api-connect-378620.iam.gserviceaccount.com"
}

gc = gspread.service_account_from_dict(credentials)

st.set_page_config(layout="wide")

american_format = {'OwnAvg': '{:.2%}'}
stacks_format = {'Total Own': '{:.2%}'}

@st.cache_resource(ttl = 600)
def init_baselines():
    sh = gc.open_by_url("https://docs.google.com/spreadsheets/d/17OAf4OAfW92-loMNUFvIubNmgF9111dsObybo6xhtYY/edit?gid=1468336051#gid=1468336051")
    worksheet = sh.worksheet('QB')
    all_values = worksheet.get_all_values()
    cell_vals = [row[0:11] for row in all_values[2:500]]
    frame_hold = pd.DataFrame(cell_vals, columns=['Player', 'Team', 'Salary', 'OwnAvg', 'PointsAvg', 'Points per $', 'blank', 'drop', 'drop2', 'drop3', 'GPP Rank'])
    frame_hold['PointsAvg'] = frame_hold['PointsAvg'].astype(float)
    frame_hold['OwnAvg'] = frame_hold['OwnAvg'].str.replace('%', '').astype(float)/100
    frame_hold['Floor'] = frame_hold['PointsAvg'] * .15
    frame_hold['Ceiling'] = frame_hold['PointsAvg'] * 1.85
    qb_frame = frame_hold[['Player', 'Team', 'Salary', 'OwnAvg', 'Floor', 'PointsAvg', 'Ceiling', 'Points per $', 'GPP Rank']]
    string_cols = ['Team']
    qb_frame = qb_frame.drop_duplicates(subset='Player')
    qb_frame = qb_frame.set_index('Player')
    
    for col in qb_frame.columns:
        if col not in string_cols:
            try:
                qb_frame[col] = pd.to_numeric(qb_frame[col], errors='coerce')
            except ValueError:
                pass  # Ignore columns that cannot be converted
                
    qb_frame = qb_frame.sort_values(by='GPP Rank', ascending=False)
    
    worksheet = sh.worksheet('RB')
    all_values = worksheet.get_all_values()
    cell_vals = [row[0:11] for row in all_values[2:500]]
    frame_hold = pd.DataFrame(cell_vals, columns=['Player', 'Team', 'Salary', 'OwnAvg', 'PointsAvg', 'Points per $', 'blank', 'drop', 'drop2', 'drop3', 'GPP Rank'])
    frame_hold['PointsAvg'] = frame_hold['PointsAvg'].astype(float)
    frame_hold['OwnAvg'] = frame_hold['OwnAvg'].str.replace('%', '').astype(float)/100
    frame_hold['Floor'] = frame_hold['PointsAvg'] * .15
    frame_hold['Ceiling'] = frame_hold['PointsAvg'] * 1.85
    rb_frame = frame_hold[['Player', 'Team', 'Salary', 'OwnAvg', 'Floor', 'PointsAvg', 'Ceiling', 'Points per $', 'GPP Rank']]
    string_cols = ['Team']
    rb_frame = rb_frame.drop_duplicates(subset='Player')
    rb_frame = rb_frame.set_index('Player')
    
    for col in rb_frame.columns:
        if col not in string_cols:
            try:
                rb_frame[col] = pd.to_numeric(rb_frame[col], errors='coerce')
            except ValueError:
                pass  # Ignore columns that cannot be converted
                
    rb_frame = rb_frame.sort_values(by='GPP Rank', ascending=False)
    
    worksheet = sh.worksheet('WR')
    all_values = worksheet.get_all_values()
    cell_vals = [row[0:11] for row in all_values[2:500]]
    frame_hold = pd.DataFrame(cell_vals, columns=['Player', 'Team', 'Salary', 'OwnAvg', 'PointsAvg', 'Points per $', 'blank', 'drop', 'drop2', 'drop3', 'GPP Rank'])
    frame_hold['PointsAvg'] = frame_hold['PointsAvg'].astype(float)
    frame_hold['OwnAvg'] = frame_hold['OwnAvg'].str.replace('%', '').astype(float)/100
    frame_hold['Floor'] = frame_hold['PointsAvg'] * .15
    frame_hold['Ceiling'] = frame_hold['PointsAvg'] * 1.85
    wr_frame = frame_hold[['Player', 'Team', 'Salary', 'OwnAvg', 'Floor', 'PointsAvg', 'Ceiling', 'Points per $', 'GPP Rank']]
    string_cols = ['Team']
    wr_frame = wr_frame.drop_duplicates(subset='Player')
    wr_frame = wr_frame.set_index('Player')
    
    for col in wr_frame.columns:
        if col not in string_cols:
            try:
                wr_frame[col] = pd.to_numeric(wr_frame[col], errors='coerce')
            except ValueError:
                pass  # Ignore columns that cannot be converted
                
    wr_frame = wr_frame.sort_values(by='GPP Rank', ascending=False)
    
    worksheet = sh.worksheet('Flex')
    all_values = worksheet.get_all_values()
    cell_vals = [row[0:11] for row in all_values[2:500]]
    frame_hold = pd.DataFrame(cell_vals, columns=['Player', 'Team', 'Salary', 'OwnAvg', 'PointsAvg', 'Points per $', 'blank', 'drop', 'drop2', 'drop3', 'GPP Rank'])
    frame_hold['PointsAvg'] = frame_hold['PointsAvg'].astype(float)
    frame_hold['OwnAvg'] = frame_hold['OwnAvg'].str.replace('%', '').astype(float)/100
    frame_hold['Floor'] = frame_hold['PointsAvg'] * .15
    frame_hold['Ceiling'] = frame_hold['PointsAvg'] * 1.85
    flex_frame = frame_hold[['Player', 'Team', 'Salary', 'OwnAvg', 'Floor', 'PointsAvg', 'Ceiling', 'Points per $', 'GPP Rank']]
    string_cols = ['Team']
    flex_frame = flex_frame.drop_duplicates(subset='Player')
    flex_frame = flex_frame.set_index('Player')
    
    for col in flex_frame.columns:
        if col not in string_cols:
            try:
                flex_frame[col] = pd.to_numeric(flex_frame[col], errors='coerce')
            except ValueError:
                pass  # Ignore columns that cannot be converted
                
    flex_frame = flex_frame.sort_values(by='GPP Rank', ascending=False)
    
    worksheet = sh.worksheet('Stacks')
    all_values = worksheet.get_all_values()
    cell_vals = [row[0:29] for row in all_values[1:500]]
    frame_hold = pd.DataFrame(cell_vals, columns=['Team', 'Opp', 'd1', 'd2', 'Game Stack', 'd3', 'd4', 'd5', 'd6', 'd7', 'd8', 'd9', 'd10', 'd11', 'd12', 'd13', 'd14', 'Team Stack',
                                                  '15', '16', '17', '18', '19', '20', '21', 'Total Stack Cost', 'Total Own', 'Total Points', 'Points/$'])
    frame_hold = frame_hold[frame_hold['Total Own'] != ""]
    frame_hold['Total Own'] = frame_hold['Total Own'].str.replace('%', '').astype(float)/100
    frame_hold['Total Stack Cost'] = frame_hold['Total Stack Cost'].str.replace(',', '').astype(float)
    stack_frame = frame_hold[['Team', 'Opp', 'Game Stack', 'Team Stack', 'Total Stack Cost', 'Total Own', 'Total Points', 'Points/$']]
    
    string_cols = ['Team', 'Opp']
    stack_frame = stack_frame.drop_duplicates(subset='Team')
    stack_frame = stack_frame.set_index('Team')
    
    for col in stack_frame.columns:
        if col not in string_cols:
            try:
                stack_frame[col] = pd.to_numeric(stack_frame[col], errors='coerce')
            except ValueError:
                pass  # Ignore columns that cannot be converted
                
    stack_frame = stack_frame.sort_values(by='Team Stack', ascending=False)
    
    return qb_frame, rb_frame, wr_frame, flex_frame, stack_frame

@st.cache_resource()
def convert_df_to_csv(df):
    return df.to_csv().encode('utf-8')

qb_frame, rb_frame, wr_frame, flex_frame, stack_frame = init_baselines()

tab1, tab2, tab3, tab4, tab5 = st.tabs(['Stacks data', 'QB data', 'RB data', 'WR data', 'Flex data'])

with tab1:
    with st.container():
              st.dataframe(stack_frame.style.background_gradient(axis=0).background_gradient(cmap='RdYlGn').format(stacks_format, precision=2), height = 1000, use_container_width = True)
              st.download_button(
                      label="Export Tables",
                      data=convert_df_to_csv(stack_frame),
                      file_name='NCAAF_Stacks_model_export.csv',
                      mime='text/csv',
              )

with tab2:
    with st.container():
              st.dataframe(qb_frame.style.background_gradient(axis=0).background_gradient(cmap='RdYlGn').format(american_format, precision=2), height = 1000, use_container_width = True)
              st.download_button(
                      label="Export Tables",
                      data=convert_df_to_csv(qb_frame),
                      file_name='NCAAF_QB_model_export.csv',
                      mime='text/csv',
              )
with tab3:
    with st.container():
              st.dataframe(rb_frame.style.background_gradient(axis=0).background_gradient(cmap='RdYlGn').format(american_format, precision=2), height = 1000, use_container_width = True)
              st.download_button(
                      label="Export Tables",
                      data=convert_df_to_csv(rb_frame),
                      file_name='NCAAF_RB_model_export.csv',
                      mime='text/csv',
              )
with tab4:
    with st.container():
              st.dataframe(wr_frame.style.background_gradient(axis=0).background_gradient(cmap='RdYlGn').format(american_format, precision=2), height = 1000, use_container_width = True)
              st.download_button(
                      label="Export Tables",
                      data=convert_df_to_csv(wr_frame),
                      file_name='NCAAF_WR_model_export.csv',
                      mime='text/csv',
              )
with tab5:
    with st.container():
              st.dataframe(flex_frame.style.background_gradient(axis=0).background_gradient(cmap='RdYlGn').format(american_format, precision=2), height = 1000, use_container_width = True)
              st.download_button(
                      label="Export Tables",
                      data=convert_df_to_csv(flex_frame),
                      file_name='NCAAF_Flex_model_export.csv',
                      mime='text/csv',
              )