cief-dashboard/apps/data.py

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)