cief-dashboard/apps/upload_data.py

from app import *
from apps.data import get_data
import base64
import io
import pgeocode
from zipfile import ZipFile 

def get_covid_derivative(df, n=32_500_000):
    df['Total active case']=df['Total case']-df['Total recovered']-df['Total death']
    for i, v in enumerate(df['Date']):
        if i > 0:
            df.loc[i, 'New case']=df.loc[i, 'Total case']-df.loc[i-1, 'Total case']
            df.loc[i, 'New recovered']=df.loc[i, 'Total recovered']-df.loc[i-1, 'Total recovered']
            df.loc[i, 'New death']=df.loc[i, 'Total death']-df.loc[i-1, 'Total death']
            df.loc[i, 'New active case']=df.loc[i, 'Total active case']-df.loc[i-1, 'Total active case']
            
            df.loc[i, 'Recovery increase rate']=df.loc[i, 'New recovered']/df.loc[i-1, 'Total recovered']
            df.loc[i, 'Death increase rate']=df.loc[i, 'New death']/df.loc[i-1, 'Total death']
            df.loc[i, 'Active case increase rate']=df.loc[i, 'Total active case']/df.loc[i-1, 'Total active case']
            df.loc[i, 'New case increase rate']=df.loc[i, 'New case']/df.loc[i-1, 'Total case']
            
            df.loc[i, 'Recovery percentage']=df.loc[i, 'Total recovered']/df.loc[i-1, 'Total case']
            df.loc[i, 'Death percentage']=df.loc[i, 'Total death']/df.loc[i-1, 'Total case']
            df.loc[i, 'Active case percentage']=df.loc[i, 'Total active case']/df.loc[i-1, 'Total case']
            
    df['S']=n-df['Total case']
    for i, v in enumerate(df['Date']):
        if i > 0:
            df.loc[i, 'Gamma']=(df.loc[i, 'New recovered'])/df.loc[i-1, 'Total active case']
            df.loc[i, 'Alpha']=(df.loc[i, 'New death'])/df.loc[i-1, 'Total active case']
            df.loc[i, 'Beta']=(df.loc[i, 'New active case']+df.loc[i, 'New recovered']+df.loc[i, 'New death'])/df.loc[i-1, 'Total active case']*n/df.loc[i, 'S']
    df['R0']=df['Beta']/df['Gamma']
    for i, v in enumerate(df['Date']):
        if i > 2:
            df.loc[i, 'R0 in 3 days']=(df.loc[i, 'R0']+df.loc[i-1, 'R0']+df.loc[i-2, 'R0'])/3
    for i, v in enumerate(df['Date']):
        if i > 6:
            df.loc[i, 'R0 in 7 days']=(df.loc[i, 'R0']+df.loc[i-1, 'R0']+df.loc[i-2, 'R0']+df.loc[i-3, 'R0']+df.loc[i-4, 'R0']+df.loc[i-5, 'R0']+df.loc[i-6, 'R0'])/7

def preprocess_covid(input_csv, output_csv):
    malaysia=pd.read_csv(input_csv[0])
    malaysia.where(malaysia['Country']=='Malaysia', inplace=True)
    malaysia=malaysia.dropna().reset_index(drop=True)
    malaysia=malaysia.iloc[55:,[0,2,3,4]].reset_index(drop=True)
    malaysia.columns=['Date', 'Total case', 'Total recovered', 'Total death']
    get_covid_derivative(malaysia)
    malaysia.to_csv(output_csv[0],index=False)

    world_wide=pd.read_csv(input_csv[1])
    world_wide.rename(columns={'Confirmed':'Total case', 'Recovered':'Total recovered', 'Deaths':'Total death', 'Increase rate':'New case increase rate'}, inplace=True)
    get_covid_derivative(world_wide, n=7_700_000_000)
    world_wide.to_csv(output_csv[1],index=False)

def preprocess_warehouse(input_csv, output_csv):
    df=pd.read_csv(input_csv)
    df=df.iloc[:, [0,1,5,6,7,8,10,11,15,16,17,18,21,22]]
    df.columns=['Date', 'Destination port', 'ETA date', 'Marking', 'Description', 'CTN', 'Postcode', 'Delivery date', 'Unstuffing date', 'Delivery status', 'Release date', 'Remark', 'CBM', 'Real ETA date']

    # clean marking aside marking
    df['Cleaned marking']=df['Marking'].copy()
    # correct string errors, prepare to extract marking
    marking_replace={
            "(?i)cief":"CIEF/", "(?i)ceif":"CIEF/", "(?i)cie/":"CIEF/",
            "\n":"/", "\(.*\)":"/", "-":"/", " +":"/", "\.":"/", "/+":"/", "$":"/"
            }
    df.loc[:,'Cleaned marking'].replace(to_replace=marking_replace,regex=True,inplace=True)
    # extract markeing regardless of prefix and postfix
    df.loc[:,'Cleaned marking']=df['Cleaned marking'].str.extract("(CIEF/\w+)/",expand=False)

    # dropna
    df.dropna(subset=['Cleaned marking'],inplace=True)

    # convert date format
    for column_name in ['Date', 'ETA date', 'Delivery date', 'Unstuffing date', 'Release date', 'Real ETA date']:
        df.loc[:,column_name]=pd.to_datetime(df[column_name], errors='coerce')
    # convert several dates to relative periods from the ordering date
    df['ETA']=(df['ETA date']-df['Date']).dt.days
    df['Delivery duration']=(df['Delivery date']-df['Date']).dt.days
    df['Unstuffing duration']=(df['Unstuffing date']-df['Date']).dt.days
    df['Release duration']=(df['Release date']-df['Date']).dt.days
    df['Real ETA']=(df['Real ETA date']-df['Date']).dt.days
    # correct outliers
    for i in ['ETA', 'Delivery duration', 'Unstuffing duration', 'Release duration', 'Real ETA']:
        correct_outlier(df.loc[:,i])
        
    # correct ctn number
    df.loc[:,'CTN']=df['CTN'].astype('str').str.extract("(\d+)", expand=False)
    df.loc[:,'CTN']=pd.to_numeric(df['CTN'], errors='coerce').fillna(0)

    # extract postcode from address
    df.loc[:,'Postcode']=df['Postcode'].str.extract("\D(\d{5})\D", expand=False).fillna('Unkown')
    # pad empty string
    df.loc[:,'Postcode']=df['Postcode'].str.pad(width=5, side='left', fillchar='0')

    # clean remark
    remark_replace={
            "^.*HOLD \(PIA\)":"Hold (PIA)", "(?i)^.*self.*$":"Self collection",
            "(?i)^.*delay.*$":"Delay",
            "(?i)^.*custom.*$|^.*check.*$":"Custom Check",
            "^.*IN.*$|(?i)^.*day.*$|(?i)^.*urgent.*$":"Urgent delivery"
            }
    df.loc[:,'Remark'].replace(to_replace=remark_replace, regex=True, inplace=True)
    df.loc[:,'Remark']=df['Remark'].str.extract("(Urgent delivery|Custom Check|Self collection|Hold \(PIA\)|Delay)", expand=False)
    df.loc[:,'Remark'].fillna('No remark', inplace=True)

    # convert postcode to geographical information
    nominating = pgeocode.Nominatim('my')
    postcodes=df['Postcode'].unique()
    location_df=nominating.query_postal_code(postcodes)
    location_df=location_df.iloc[:,[0,2,3,9,10]]
    location_df.columns=['Postcode','Place name','State name','Latitude','Longitude']
    location_df.loc[:,'Place name'].fillna(value='NA', inplace=True)
    location_df.loc[:,'State name'].fillna(value='NA', inplace=True)
    df=pd.merge(df, location_df, how='left', on='Postcode')

    # order
    df=df.loc[:, ['Date', 'Cleaned marking', 'Marking', 'CBM', 'CTN', 'Description', 'Delivery status', 'Remark', 'Destination port', 'ETA', 'Delivery duration', 'Unstuffing duration', 'Release duration', 'Real ETA', 'Place name', 'State name', 'Latitude', 'Longitude']]

    # check is CBM in date format 
    # if yes, it needs manual correct
    q1=df['CBM'].quantile(0.25)

    # save
    df.to_csv(output_csv,index=False)

def upload_data(contents, saved_csv):
    content_type, content_string = contents.split(',')
    decoded = base64.b64decode(content_string)
    content=io.BytesIO(decoded)
    try:
        # Assume that the user uploaded an excel file
        if 'openxml' in content_type:
            if saved_csv=='data/warehouse0.csv':
                df = pd.read_excel(content,sheet_name='Monthly',header=1)
                df.to_csv(saved_csv, index=False)
                # prprocess
                preprocess_warehouse(saved_csv, 'data/warehouse1.csv')
                return(dbc.Alert("File loaded successfully", color="success",  
                    dismissable=True, is_open=True))

        # Assume that the user uploaded a zip file
        elif 'zip' in content_type:
            if saved_csv=='data/covid_19':
                malysia_file_name = "data/countries-aggregated.csv"
                world_wide_file_name = "data/worldwide-aggregate.csv"
                malaysia_csv = "data/covid_19/countries-aggregated.csv"
                world_wide_csv = "data/covid_19/worldwide-aggregate.csv"
                # read zip
                with ZipFile(content, 'r') as folder: 
                    malaysia_df = pd.read_csv(folder.open(malysia_file_name))
                    malaysia_df.to_csv(malaysia_csv, index=False)
                    world_wide_df = pd.read_csv(folder.open(world_wide_file_name))
                    world_wide_df.to_csv(world_wide_csv, index=False)
                # prprocess
                input_csv=[malaysia_csv, world_wide_csv]
                output_csv=['data/covid_19/malaysia1.csv', 'data/covid_19/worldwide1.csv']
                preprocess_covid(input_csv, output_csv)
                return(dbc.Alert("File loaded successfully", color="success",  
                    dismissable=True, is_open=True))
    except Exception as e:
        print(e)
        return(dbc.Alert("There was an error processing this file.", color="warning",  
            dismissable=True, is_open=True))

# layout
upload_style={
        'width': '100%',
        'height': '60px',
        'lineHeight': '60px',
        'borderWidth': '1px',
        'borderStyle': 'dashed',
        'borderRadius': '5px',
        'textAlign': 'center',
        'margin': '10px'}

layout = html.Div([
    # upload warehouse
    html.H1('Upload warehouse summery data'),
    dcc.Upload(
        html.A('Drag or Select Files'),
        id='input-warehouse_excel', style=upload_style),

    # upload covid 19
    html.H1('Upload Covid-19 data'),
    dcc.Upload(
        html.A('Drag or Select Files'),
        id='input-covid_zip', style=upload_style),

    html.Div(id='output-upload_result'),
    ])

# upload data
@app.callback(
        Output('output-upload_result', 'children'),
        [Input('input-warehouse_excel', 'contents'),
            Input('input-covid_zip', 'contents')])
def update_output(warehouse_data, covid_data):
    if warehouse_data is not None:
        print('warehouse data')
        result=upload_data(warehouse_data, 'data/warehouse0.csv')
        return(result)
    elif covid_data is not None:
        print('covid data')
        result=upload_data(covid_data,'data/covid_19')
        return(result)
    else:
        raise(PreventUpdate)

# update data
@app.callback(
        Output('input-interval', 'n_intervals'),
        Input('output-upload_result', 'children'),
        State('input-interval', 'n_intervals'))
def reload_data(content, n_intervals):
    if content is None:
        raise(PreventUpdate)
    if content['props']['color']=='success':
        n_intervals=n_intervals+1
        print('reloaded')
        return(n_intervals)
    else:
        raise(PreventUpdate)