from app import * def get_data(): warehouse=pd.read_csv('data/warehouse1.csv', parse_dates=['Date']) billing=pd.read_csv('data/billing1.csv', parse_dates=['Date']) marketing=pd.read_csv('data/marketing1.csv', parse_dates=['Date']) covid_malaysia=pd.read_csv('data/covid_19/malaysia1.csv', parse_dates=['Date']) covid_world_wide=pd.read_csv('data/covid_19/worldwide1.csv', parse_dates=['Date']) # sql amount, booking_rate, exchange_marking=get_sql() data={'warehouse':warehouse, 'billing':billing, 'marketing':marketing, 'covid_malaysia':covid_malaysia, 'covid_world_wide':covid_world_wide, 'amount':amount, 'booking_rate':booking_rate, 'exchange_marking':exchange_marking} to_json(data) print('got data') return(data) def get_sql(): # transfer amount amount = pd.read_sql("select bookings.created_at, user_id, transfer_amount, term, status, is_approve, is_reject, is_cancel from user_bank_slips left join bookings on user_bank_slips.booking_id=bookings.id;", con=db_connection) amount.columns=['Date', 'User id', 'Transfer amount', 'Term', 'Status', 'Approved', 'Rejected', 'Canceled'] # remove testing user amount=amount.mask(amount['User id']==1).dropna() amount=amount.mask(amount['Status']<=2).dropna() amount=amount.where(amount['Approved']==1).dropna() amount=amount.where(amount['Rejected']==0).dropna() amount=amount.where(amount['Canceled']==0).dropna() amount=amount.loc[:, ['Date','Transfer amount', 'Term']] # booking rate booking_rate = pd.read_sql("select created_at, rate from bookings", con=db_connection) booking_rate.columns=['Date','Booking rate'] correct_outlier(booking_rate['Booking rate']) booking_rate['Date']=booking_rate['Date'].dt.strftime('%Y-%m-%d') booking_rate=booking_rate['Booking rate'].groupby(booking_rate['Date']).mean() booking_rate=pd.DataFrame(booking_rate).reset_index() # exchange marking exchange_marking = pd.read_sql("select bookings.created_at, marking from bookings left join users on bookings.user_id=users.id;", con=db_connection) exchange_marking.columns=['Date', 'Marking'] exchange_marking['Date']=pd.to_datetime(exchange_marking['Date'].dt.strftime('%Y-%m-%d')) exchange_marking=exchange_marking.where(exchange_marking['Marking'].str.contains("/")).dropna() marking_replace={"$":"/"} exchange_marking['Marking'].replace(to_replace=marking_replace,regex=True,inplace=True) exchange_marking['Marking']=exchange_marking['Marking'].str.extract("(CIEF/\w+)/",expand=False) return(amount, booking_rate, exchange_marking) def get_periods(df, frequency='day'): df=df.copy() # convert to different period if frequency=='day': df['Date']=df['Date'].dt.strftime("%Y-%m-%d") elif frequency=='month': df['Date']=df['Date'].dt.strftime("%Y-%m") elif frequency=='year': df['Date']=df['Date'].dt.strftime("%Y") elif frequency=='quarter': df['Year']=df['Date'].dt.year df['Quarter']=df['Date'].dt.quarter df['Date']=df['Year'].astype(str)+'-quarter'+df['Quarter'].astype(str) df.drop(['Year', 'Quarter'], axis=1, inplace=True) elif frequency=='week': df['Date']=df['Date'].dt.strftime("%Y-week%W") elif frequency=='weekday': # encode df['Date']=df['Date'].dt.strftime("%Y-weekday%w") df=df.groupby(df['Date']).sum() df=pd.DataFrame(df).reset_index() # decode if frequency=='weekday': replace={"weekday0":"Sunday", "weekday1":"Monday", "weekday2":"Tuesday", "weekday3":"Wednesday", "weekday4":"Thursday", "weekday5":"Friday", "weekday6":"Saturday"} df['Date'].replace(to_replace=replace, regex=True, inplace=True) return(df) def get_conversion(register_count, register_marking, converted_marking, week): conversion=register_count.copy() for index, start_time in enumerate(conversion['Date']): # set counting date end_time=start_time+pd.Timedelta(week, unit='W') filtered_register_marking=register_marking[register_marking['Date'] == start_time] filtered_converted_marking=converted_marking[(converted_marking['Date'] < end_time) & (start_time <= converted_marking['Date'])] conversion.loc[index, 'Converted count']=filtered_register_marking['Marking'].isin(filtered_converted_marking['Marking']).sum() conversion=conversion.groupby(conversion['Date'].dt.strftime('%Y-%m')).sum() # calculate conversion rate conversion['Conversion rate']=conversion['Converted count']/conversion['Register count'] conversion.reset_index(inplace=True) conversion['Number of week based']=week return(conversion) def get_conversion_rate_animation(register_count, register_marking, transport_marking, exchange_marking, n_week=16): exchange_and_transport_marking=pd.concat([exchange_marking, transport_marking]) animation_conversion=pd.DataFrame() for week in range(1,n_week+1): conversion=get_conversion(register_count, register_marking, transport_marking, week) conversion['Type']='Transport' # append animation_conversion=pd.concat([animation_conversion, conversion]) conversion=get_conversion(register_count, register_marking, exchange_marking, week) conversion['Type']='Exchange' # append animation_conversion=pd.concat([animation_conversion, conversion]) conversion=get_conversion(register_count, register_marking, exchange_and_transport_marking, week) conversion['Type']='Exchange or Transport' # append animation_conversion=pd.concat([animation_conversion, conversion]) animation_conversion.reset_index(drop=True, inplace=True) return(animation_conversion) # layout layout = html.Div([ dcc.Store(id='data', data=get_data()), dcc.Store(id='data-summery'), dcc.Store(id='data-machine_learning'), dcc.Store(id='data-monthly'), dcc.Store(id='data-conversion_rate'), ]) @app.callback( Output('data-summery', 'data'), Input('data', 'modified_timestamp'), State('data', 'data'), prevent_initial_call=True) def get_summery_data(modified_timestamp, data): to_df(data) # proprocess cbm warehouse=data['warehouse'] cbm=warehouse.loc[:,['Date', 'CBM']] correct_outlier(cbm['CBM']) cbms=[] cbms.append(get_periods(cbm)) cbms.append(get_periods(cbm, frequency='month')) cbms.append(get_periods(cbm, frequency='year')) cbms.append(get_periods(cbm, frequency='quarter')) cbms.append(get_periods(cbm, frequency='week')) cbms.append(get_periods(cbm, frequency='weekday')) to_json(cbms) # proprocess X1 X2 amount amount=data['amount'] x1=amount.where(amount['Term'].str.contains('x1')).drop('Term', axis=1).dropna().rename(columns={'Transfer amount':'X1 amount'}) x2=amount.where(amount['Term'].str.contains('x2')).drop('Term', axis=1).dropna().rename(columns={'Transfer amount':'X2 amount'}) x1_x2=pd.merge(x1, x2, how="outer", on='Date') correct_outlier(x1_x2['X1 amount']) correct_outlier(x1_x2['X2 amount']) x1_x2s=[] x1_x2s.append(get_periods(x1_x2)) x1_x2s.append(get_periods(x1_x2, frequency='month')) x1_x2s.append(get_periods(x1_x2, frequency='year')) x1_x2s.append(get_periods(x1_x2, frequency='quarter')) x1_x2s.append(get_periods(x1_x2, frequency='week')) x1_x2s.append(get_periods(x1_x2, frequency='weekday')) for i in range(len(x1_x2s)): x1_x2s[i]=pd.melt(x1_x2s[i], 'Date') to_json(x1_x2s) # preprocess place data place_cbm=warehouse['CBM'].groupby(warehouse['Place name']).sum() place_cbm=pd.DataFrame({'CBM by place':place_cbm}).reset_index() place=pd.merge(warehouse.loc[:,['Place name','State name','Latitude','Longitude']], place_cbm,how="left", on='Place name') place.dropna(subset=['State name'],inplace=True) # preprocess number of monthly transfer n_transfer=amount['Date'].dt.strftime("%Y-%m") n_transfer=n_transfer.groupby(n_transfer).count() n_transfer=pd.DataFrame({'Number of transfer':n_transfer}).reset_index() # preprocess ctn ctn=warehouse.loc[:,['Date', 'CTN']] ctn['Date']=ctn['Date'].dt.strftime("%Y-%m") ctn=ctn.groupby(ctn['Date']).sum() ctn=pd.DataFrame(ctn).reset_index() # delivery delivery=warehouse.loc[:,['Date', 'ETA', 'Delivery duration', 'Unstuffing duration', 'Release duration', 'Real ETA']] delivery=delivery.groupby(delivery['Date']).mean() delivery=delivery.reset_index() delivery.sort_values(['Date'],inplace=True) delivery=pd.melt(delivery, 'Date') booking_rate=data['booking_rate'] # save data={'place':place, 'booking_rate':booking_rate, 'n_transfer':n_transfer, 'ctn':ctn, 'delivery':delivery} to_json(data) data['cbms']=cbms data['x1_x2s']=x1_x2s print('got summery') return(data) @app.callback( Output('data-machine_learning', 'data'), Input('data', 'modified_timestamp'), State('data', 'data'), prevent_initial_call=True) def get_machine_learning_data(modified_timestamp, data): to_df(data) warehouse=data['warehouse'] cbm_ctn=warehouse.loc[:,['Date', 'CBM', 'CTN']] cbm_ctn=cbm_ctn.groupby(cbm_ctn['Date']).sum() cbm_ctn=pd.DataFrame(cbm_ctn).reset_index() correct_outlier(cbm_ctn['CBM']) correct_outlier(cbm_ctn['CTN']) cbm_ctn.columns=['Date', 'Daily CBM', 'Daily CTN'] covid_world_wide=data['covid_world_wide'] covid_world_wide_fatal_rate=covid_world_wide.loc[100:,['Date', 'Death percentage', 'New case']].reset_index(drop=True) covid_world_wide_fatal_rate.fillna(method='backfill',inplace=True) covid_world_wide_fatal_rate.fillna(method='pad',inplace=True) data={'covid_world_wide_fatal_rate':covid_world_wide_fatal_rate, 'cbm_ctn':cbm_ctn} to_json(data) print('got machine learning data') return(data) @app.callback( Output('data-monthly', 'data'), Input('data', 'modified_timestamp'), State('data', 'data'), prevent_initial_call=True) def get_monthly_data(modified_timestamp, data): to_df(data) warehouse=data['warehouse'] marketing=data['marketing'] # preprocess marketing marketing['Selling platform'].fillna('Other', inplace=True) # proprocess accumulated cbm daily_cbm=warehouse['CBM'].groupby(warehouse['Date']).sum() daily_cbm=pd.DataFrame({'CBM':daily_cbm}).reset_index() # proprocess cbm cbm=warehouse.loc[:, ['Date', 'CBM', 'CTN', 'Marking']] daily_ctn=warehouse['CTN'].groupby(warehouse['Date']).sum() daily_ctn=pd.DataFrame({'CTN':daily_ctn}).reset_index() cbm = pd.merge(cbm, daily_cbm.rename(columns={'CBM':'Daily CBM'}), how='left', on='Date') cbm = pd.merge(cbm, daily_ctn.rename(columns={'CTN':'Daily CTN'}), how='left', on='Date') # preprocess customer cbm customer_history=warehouse.loc[:,['CBM', 'CTN', 'Cleaned marking']] customer_cbm=customer_history.groupby(customer_history['Cleaned marking']).sum() customer_cbm.columns=['Customer CBM', 'Customer CTN'] customer_cbm=pd.DataFrame(customer_cbm).reset_index() customer_cbm=pd.merge(customer_history, customer_cbm, how='left', on='Cleaned marking') customer_cbm['Date']=warehouse['Date'] # destination destination=warehouse.loc[:,['Date', 'Destination port']] # remark remark=warehouse.loc[:,['Date', 'Remark']] # save data={'marketing':marketing, 'daily_cbm':daily_cbm, 'customer_cbm':customer_cbm, 'cbm':cbm, 'destination':destination, 'remark':remark} to_json(data) print('got monthly data') return(data) @app.callback( Output('data-conversion_rate', 'data'), Input('data', 'modified_timestamp'), State('data', 'data'), prevent_initial_call=True) def get_monthly_data(modified_timestamp, data): to_df(data) # conversion rate billing=data['billing'] register_marking=billing.loc[:,['Date', 'Marking']] register_count=register_marking['Marking'].groupby(register_marking['Date']).count() register_count=pd.DataFrame({'Register count':register_count}).reset_index() warehouse=data['warehouse'] transport_marking=warehouse.loc[:,['Date', 'Cleaned marking']] transport_marking.rename(columns={'Cleaned marking':'Marking'}, inplace=True) exchange_marking=data['exchange_marking'] conversion_rate_animation=get_conversion_rate_animation(register_count, register_marking, transport_marking, exchange_marking) data={'conversion_rate_animation':conversion_rate_animation} to_json(data) print('got conversion rate data') return(data)