Hi I am trying to update a data table with date range picker for some DF and its throwing me an error “key error”
import os
import math
# dash libs
import dash
from dash.dependencies import Input, Output
from dash import dcc, html, dash_table
import plotly.figure_factory as ff
import plotly.graph_objs as go
from plotly.subplots import make_subplots
import datetime as dt
import dash_daq as daq
from statsmodels.tsa.seasonal import seasonal_decompose
import dash_bootstrap_components as dbc
from decimal import *
# pydata stack
import pandas as pd
from sqlalchemy import create_engine
# set params
# conn = create_engine(os.environ['DB_URI'])
###########################
# Data Manipulation / Model
###########################
df = pd.read_excel('/Users/sdabi/Documents/sample_data.xlsx', sheet_name='Sheet1')
# print(df.dtypes)
# exit()
# df['Baseline']=pd.to_numeric(df['Baseline'])
# print(df.head())
decompose_result_mult = seasonal_decompose(df[['Sales']], model="additive",period = 12)
trend = decompose_result_mult.trend
seasonal = decompose_result_mult.seasonal
residual = decompose_result_mult.resid
# metric_list = ['Sales','CustomForecast','Both']
curve_list = ['Annual','Weekly']
df['Annual_split']= df['Sales']/df['Sales'].sum()
def get_cagr(constant, cagr= 0, adjuster= False):
#calculate daily cagr
constant_list =[]
CustomForecast =[]
inc_forecast =[]
reduction_factor =[]
daily_factor =[1]
annual_split = df['Annual_split'].tolist()
constant_list = [constant*1.0]*len(df)
if cagr == 0 and not adjuster:
CustomForecast = [x/len(constant_list) for x in constant_list]
inc_forecast = [0]*len(df)
reduction_factor= [0]*len(df)
daily_factor = [1]*len(df)
elif cagr == 0 and adjuster:
CustomForecast = [a*b for a,b in zip(constant_list,annual_split)]
inc_forecast = [0]*len(df)
reduction_factor= [0]*len(df)
daily_factor = [1]*len(df)
else:
daily_cagr = ((1.0+cagr)**(1.0/len(df)))-1.0
daily_factor = [1]
for i in range(1,len(df)):
prev_val = daily_factor[i-1]
daily_factor.append((daily_cagr+1)*prev_val)
if not adjuster:
inc_forecast_temp = [x/len(constant_list) for x in constant_list]
inc_forecast = [a*b for a,b in zip(inc_forecast_temp,daily_factor)]
reduction_factor = [x/sum(inc_forecast) for x in constant_list]
CustomForecast = [a*b for a,b in zip(inc_forecast,reduction_factor)]
else:
inc_forecast = [a*b*c for a,b,c in zip(constant_list,annual_split,daily_factor)]
reduction_factor = [x/sum(inc_forecast) for x in constant_list]
CustomForecast = [a*b*c for a,b,c in zip(inc_forecast,reduction_factor,annual_split)]
result_dict = {'Date': df['Date'].to_list(),
'constant': constant_list,
'inc_forecast': inc_forecast,
'reduction_factor': reduction_factor,
'daily_factor':daily_factor,
'annual_split':annual_split,
'CustomForecast':CustomForecast
}
return result_dict
def get_metric_results(start_date,end_date,cagr_dict):
df_new = pd.DataFrame.from_dict(cagr_dict)
df_new = df_new.loc[(df_new['Date'] >= start_date) & (df_new['Date'] <= end_date)]
metric_results = df_new[['Date','CustomForecast','constant','daily_factor','inc_forecast','reduction_factor']]
# metric_results['Date']= metric_results['Date'].dt.strftime('%m-%d-%Y')
return metric_results
# get_cagr(40000000,0.05,False)
# x = get_metric_results('2021-01-01','2021-01-10',get_cagr(40000000,0.05,False))
# print(x.dtypes)
# exit()
#########################
# Dashboard Layout / View
#########################
# Set up Dashboard and create layout
app = dash.Dash(__name__, external_stylesheets=[dbc.themes.BOOTSTRAP])
app.layout = html.Div([
dbc.Row(dbc.Col(html.H3("Forecast Generator"),
width={'size': 6},
),
),
dbc.Row(dbc.Col(html.Div('Select Metric/Filter to adjust data'),
width=4
)
),
dbc.Row(
[
dbc.Col(dcc.DatePickerRange(
id='my-date-picker-range', # ID to be used for callback
calendar_orientation='horizontal', # vertical or horizontal
day_size=30, # size of calendar image. Default is 39
end_date_placeholder_text="Return", # text that appears when no end date chosen
reopen_calendar_on_clear=True,
clearable=True, # whether or not the user can clear the dropdown
number_of_months_shown=1, # number of months shown when calendar is open
min_date_allowed=dt.datetime(2021, 1, 1), # minimum date allowed on the DatePickerRange component
max_date_allowed=dt.datetime(2021, 12, 31), # maximum date allowed on the DatePickerRange component
initial_visible_month=dt.datetime(2021, 1, 1), # the month initially presented when the user opens the calendar
start_date=dt.datetime(2021, 1, 1).date(),
end_date=dt.datetime(2021, 12, 31).date(),
display_format='MM-DD-YYYY', # how selected dates are displayed in the DatePickerRange component.
minimum_nights=2, # minimum number of days between start and end date
persistence=True,
persistence_type='session', # session, local, or memory. Default is 'local'
updatemode='bothdates' # singledate or bothdates. Determines when callback is triggered
),
width={'size': 3, 'order': 2}
),
dbc.Col(dcc.Input(
id='starting_point_baseline',
placeholder='enter a number to start generating forecast',
type='number',
value=400000000,
name='Baseline Units'),
width={'size': 2, 'order': 3}
),
],
),
dbc.Row([dbc.Col(dash_table.DataTable(id="metric-results",
editable=False
),
width=4, lg={'size': 2, "offset": 0, 'order': 'last'}
)
])
])
#############################################
# Interaction Between Components / Controller
#############################################
@app.callback(
[Output(component_id='metric-results',component_property='data'),
Output(component_id='metric-results',component_property='columns')],
[
# Input(component_id='metric-selector', component_property='value'),
Input(component_id='my-date-picker-range', component_property='start_date'),
Input(component_id='my-date-picker-range', component_property='end_date'),
Input(component_id='starting_point_baseline', component_property='value'),
Input(component_id='btn-adjuster-1', component_property='on'),
Input(component_id='cagr-slider-1',component_property='value')
]
)
def load_metric_results(start_date,end_date,constant,adjuster=None,cagr=0):
cagr_results = get_cagr(constant,adjuster,cagr)
start_date_x = dt.datetime.strptime(start_date,'%Y-%m-%d')
start_date_a = dt.datetime.strftime(start_date_x,'%Y-%m-%d')
end_date_x = dt.datetime.strptime(end_date,'%Y-%m-%d')
end_date_a = dt.datetime.strftime(end_date_x,'%Y-%m-%d')
results = get_metric_results(start_date_a,end_date_a,cagr_results)
print(results.dtypes)
if len(results)>0:
data = results.to_dict('rows'),
columns = {c: i for i, c in enumerate(results.columns)}
return [
results.to_dict("records"),[{'name': x, 'id': x} for x in results.columns]
]
# start Flask server
if __name__ == '__main__':
app.run_server(
debug=True,
# host='0.0.0.0',
port=8050
)