Spaces:
Sleeping
Sleeping
File size: 12,411 Bytes
a660599 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 |
import streamlit as st
from numerize.numerize import numerize
import io
import pandas as pd
from utilities import (format_numbers,decimal_formater,
channel_name_formating,
load_local_css,set_header,
initialize_data,
load_authenticator)
from openpyxl import Workbook
from openpyxl.styles import Alignment,Font,PatternFill
import pickle
import streamlit_authenticator as stauth
import yaml
from yaml import SafeLoader
from classes import class_from_dict
st.set_page_config(layout='wide')
load_local_css('styles.css')
set_header()
# for k, v in st.session_state.items():
# if k not in ['logout', 'login','config'] and not k.startswith('FormSubmitter'):
# st.session_state[k] = v
def create_scenario_summary(scenario_dict):
summary_rows = []
for channel_dict in scenario_dict['channels']:
name_mod = channel_name_formating(channel_dict['name'])
summary_rows.append([name_mod,
channel_dict.get('actual_total_spends') * channel_dict.get('conversion_rate'),
channel_dict.get('modified_total_spends') * channel_dict.get('conversion_rate'),
channel_dict.get('actual_total_sales') ,
channel_dict.get('modified_total_sales'),
channel_dict.get('actual_total_sales') / (channel_dict.get('actual_total_spends') * channel_dict.get('conversion_rate')),
channel_dict.get('modified_total_sales') / (channel_dict.get('modified_total_spends') * channel_dict.get('conversion_rate')),
channel_dict.get('actual_mroi'),
channel_dict.get('modified_mroi'),
channel_dict.get('actual_total_spends') * channel_dict.get('conversion_rate') / channel_dict.get('actual_total_sales'),
channel_dict.get('modified_total_spends') * channel_dict.get('conversion_rate') / channel_dict.get('modified_total_sales')])
summary_rows.append(['Total',
scenario_dict.get('actual_total_spends'),
scenario_dict.get('modified_total_spends'),
scenario_dict.get('actual_total_sales'),
scenario_dict.get('modified_total_sales'),
scenario_dict.get('actual_total_sales') / scenario_dict.get('actual_total_spends'),
scenario_dict.get('modified_total_sales') / scenario_dict.get('modified_total_spends'),
'-',
'-',
scenario_dict.get('actual_total_spends') / scenario_dict.get('actual_total_sales'),
scenario_dict.get('modified_total_spends') / scenario_dict.get('modified_total_sales')])
columns_index = pd.MultiIndex.from_product([[''],['Channel']], names=["first", "second"])
columns_index = columns_index.append(pd.MultiIndex.from_product([['Spends','NRPU','ROI','MROI','Spend per NRPU'],['Actual','Simulated']], names=["first", "second"]))
return pd.DataFrame(summary_rows, columns=columns_index)
def summary_df_to_worksheet(df, ws):
heading_fill = PatternFill(fill_type='solid',start_color='FF11B6BD',end_color='FF11B6BD')
for j,header in enumerate(df.columns.values):
col = j + 1
for i in range(1,3):
ws.cell(row=i, column=j + 1, value=header[i - 1]).font = Font(bold=True, color='FF11B6BD')
ws.cell(row=i,column=j+1).fill = heading_fill
if col > 1 and (col - 6)%5==0:
ws.merge_cells(start_row=1, end_row=1, start_column = col-3, end_column=col)
ws.cell(row=1,column=col).alignment = Alignment(horizontal='center')
for i,row in enumerate(df.itertuples()):
for j,value in enumerate(row):
if j == 0:
continue
elif (j-2)%4 == 0 or (j-3)%4 == 0:
ws.cell(row=i+3, column = j, value=value).number_format = '$#,##0.0'
else:
ws.cell(row=i+3, column = j, value=value)
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, PatternFill
import logging
def scenario_df_to_worksheet(df, ws):
heading_fill = PatternFill(start_color='FF11B6BD', end_color='FF11B6BD', fill_type='solid')
for j, header in enumerate(df.columns.values):
cell = ws.cell(row=1, column=j + 1, value=header)
cell.font = Font(bold=True, color='FF11B6BD')
cell.fill = heading_fill
for i, row in enumerate(df.itertuples()):
for j, value in enumerate(row[1:], start=1): # Start from index 1 to skip the index column
try:
cell = ws.cell(row=i + 2, column=j, value=value)
if isinstance(value, (int, float)):
cell.number_format = '$#,##0.0'
elif isinstance(value, str):
cell.value = value[:32767]
else:
cell.value = str(value)
except ValueError as e:
logging.error(f"Error assigning value '{value}' to cell {get_column_letter(j)}{i+2}: {e}")
cell.value = None # Assign None to the cell where the error occurred
return ws
def download_scenarios():
"""
Makes a excel with all saved scenarios and saves it locally
"""
## create summary page
if len(scenarios_to_download) == 0:
return
wb = Workbook()
wb.iso_dates = True
wb.remove(wb.active)
st.session_state['xlsx_buffer'] = io.BytesIO()
summary_df = None
#print(scenarios_to_download)
for scenario_name in scenarios_to_download:
scenario_dict = st.session_state['saved_scenarios'][scenario_name]
_spends = []
column_names = ['Date']
_sales = None
dates = None
summary_rows = []
for channel in scenario_dict['channels']:
if dates is None:
dates = channel.get('dates')
_spends.append(dates)
if _sales is None:
_sales = channel.get('modified_sales')
else:
_sales += channel.get('modified_sales')
_spends.append(channel.get('modified_spends') * channel.get('conversion_rate'))
column_names.append(channel.get('name'))
name_mod = channel_name_formating(channel['name'])
summary_rows.append([name_mod,
channel.get('modified_total_spends') * channel.get('conversion_rate') ,
channel.get('modified_total_sales'),
channel.get('modified_total_sales') / channel.get('modified_total_spends') * channel.get('conversion_rate'),
channel.get('modified_mroi'),
channel.get('modified_total_sales') / channel.get('modified_total_spends') * channel.get('conversion_rate')])
_spends.append(_sales)
column_names.append('NRPU')
scenario_df = pd.DataFrame(_spends).T
scenario_df.columns = column_names
## write to sheet
ws = wb.create_sheet(scenario_name)
scenario_df_to_worksheet(scenario_df, ws)
summary_rows.append(['Total',
scenario_dict.get('modified_total_spends') ,
scenario_dict.get('modified_total_sales'),
scenario_dict.get('modified_total_sales') / scenario_dict.get('modified_total_spends'),
'-',
scenario_dict.get('modified_total_spends') / scenario_dict.get('modified_total_sales')])
columns_index = pd.MultiIndex.from_product([[''],['Channel']], names=["first", "second"])
columns_index = columns_index.append(pd.MultiIndex.from_product([[scenario_name],['Spends','NRPU','ROI','MROI','Spends per NRPU']], names=["first", "second"]))
if summary_df is None:
summary_df = pd.DataFrame(summary_rows, columns = columns_index)
summary_df = summary_df.set_index(('','Channel'))
else:
_df = pd.DataFrame(summary_rows, columns = columns_index)
_df = _df.set_index(('','Channel'))
summary_df = summary_df.merge(_df, left_index=True, right_index=True)
ws = wb.create_sheet('Summary',0)
summary_df_to_worksheet(summary_df.reset_index(), ws)
wb.save(st.session_state['xlsx_buffer'])
st.session_state['disable_download_button'] = False
def disable_download_button():
st.session_state['disable_download_button'] =True
def transform(x):
if x.name == ("",'Channel'):
return x
elif x.name[0] == 'ROI' or x.name[0] == 'MROI':
return x.apply(lambda y : y if isinstance(y,str) else decimal_formater(format_numbers(y,include_indicator=False,n_decimals=4),n_decimals=4))
else:
return x.apply(lambda y : y if isinstance(y,str) else format_numbers(y))
def delete_scenario():
if selected_scenario in st.session_state['saved_scenarios']:
del st.session_state['saved_scenarios'][selected_scenario]
with open('../saved_scenarios.pkl', 'wb') as f:
pickle.dump(st.session_state['saved_scenarios'],f)
def load_scenario():
if selected_scenario in st.session_state['saved_scenarios']:
st.session_state['scenario'] = class_from_dict(selected_scenario_details)
authenticator = st.session_state.get('authenticator')
if authenticator is None:
authenticator = load_authenticator()
name, authentication_status, username = authenticator.login('Login', 'main')
auth_status = st.session_state.get('authentication_status')
if auth_status == True:
is_state_initiaized = st.session_state.get('initialized',False)
if not is_state_initiaized:
#print("Scenario page state reloaded")
initialize_data()
saved_scenarios = st.session_state['saved_scenarios']
if len(saved_scenarios) ==0:
st.header('No saved scenarios')
else:
with st.sidebar:
selected_scenario = st.radio(
'Pick a scenario to view details',
list(saved_scenarios.keys())
)
st.markdown("""<hr>""", unsafe_allow_html=True)
scenarios_to_download = st.multiselect('Select scenarios to download',
list(saved_scenarios.keys()))
st.button('Prepare download',on_click=download_scenarios)
st.download_button(
label="Download Scenarios",
data=st.session_state['xlsx_buffer'].getvalue(),
file_name="scenarios.xlsx",
mime="application/vnd.ms-excel",
disabled= st.session_state['disable_download_button'],
on_click= disable_download_button
)
column_1, column_2,column_3 = st.columns((6,1,1))
with column_1:
st.header(selected_scenario)
with column_2:
st.button('Delete scenarios', on_click=delete_scenario)
with column_3:
st.button('Load Scenario', on_click=load_scenario)
selected_scenario_details = saved_scenarios[selected_scenario]
pd.set_option('display.max_colwidth', 100)
st.markdown(create_scenario_summary(selected_scenario_details).transform(transform).style.set_table_styles(
[{
'selector': 'th',
'props': [('background-color', '#11B6BD')]
},
{
'selector' : 'tr:nth-child(even)',
'props' : [('background-color', '#11B6BD')]
}
]).to_html(),unsafe_allow_html=True)
elif auth_status == False:
st.error('Username/Password is incorrect')
if auth_status != True:
try:
username_forgot_pw, email_forgot_password, random_password = authenticator.forgot_password('Forgot password')
if username_forgot_pw:
st.success('New password sent securely')
# Random password to be transferred to user securely
elif username_forgot_pw == False:
st.error('Username not found')
except Exception as e:
st.error(e)
|