forecastApp_17Feb23-Copy5_incl_OTD.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ca3eb5f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sklearn\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.metrics import mean_squared_error\n",
    "import math\n",
    "from scipy import stats\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "from datetime import datetime\n",
    "from dateutil.relativedelta import relativedelta\n",
    "import re\n",
    "import openpyxl\n",
    "from matplotlib.backends.backend_pdf import PdfPages\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9b21dfc7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# location of files\n",
    "output_data = r\"C:\\Users\\stephenl\\OneDrive - GC International AG\\ML\\forecast function demo\\templates\\output_data\\\\\"\n",
    "sell_out_data_path = r'C:\\Users\\stephenl\\OneDrive - GC International AG\\ML\\forecast function demo\\templates\\selloutdata\\\\'\n",
    "inventory_path = r'C:\\Users\\stephenl\\OneDrive - GC International AG\\ML\\forecast function demo\\templates\\inventory\\\\'\n",
    "path = r'C:\\Users\\stephenl\\OneDrive - GC International AG\\ML\\forecast function demo\\templates\\input_data\\\\' #top 20 products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fe626d52",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('July-2022', datetime.datetime(2023, 2, 21, 10, 14, 2, 353471))"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def past6mths(current_date = datetime.today()): #----------------------------------\n",
    "    n = 7\n",
    "    past_date = current_date - relativedelta(months=n)\n",
    "    return past_date.strftime('%B-%Y')\n",
    "\n",
    "past6mths(), datetime.today()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47988765",
   "metadata": {},
   "source": [
    "# Create function (6 months OTD data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4d1cd60f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def forecast6(num):\n",
    "\n",
    "\n",
    "    func = lambda x: 0 if x < 0 else x\n",
    "    func1 = lambda x: x if x <0 else 0\n",
    "\n",
    "\n",
    "    def past5mths(current_date = datetime.today()): #----------------------------------\n",
    "        n = 4\n",
    "        past_date = current_date - relativedelta(months=n)\n",
    "        return past_date.strftime('%B-%Y')\n",
    "\n",
    "    def past6mths(current_date = datetime.today()): #----------------------------------\n",
    "        n = 6\n",
    "        past_date = current_date - relativedelta(months=n)\n",
    "        return past_date.strftime('%B-%Y')\n",
    "\n",
    "    def future10mths(current_date = datetime.today()): #----------------------------------\n",
    "        n = 10\n",
    "        future_date = current_date + relativedelta(months=n)\n",
    "        return future_date.strftime('%B-%Y')\n",
    "\n",
    "    def today(current_date = datetime.today()):\n",
    "        return current_date.strftime('%B-%Y')\n",
    "\n",
    "    def nextmth(current_date = datetime.today()): #----------------------------------\n",
    "        n = 1\n",
    "        future_date = current_date + relativedelta(months=n)\n",
    "        return future_date.strftime('%B-%Y')\n",
    "    \n",
    "    def lastmonth():\n",
    "        n=-1\n",
    "        a =  datetime.today()+relativedelta(months=n)\n",
    "        return a.strftime('%B-%Y')\n",
    "    \n",
    "    def future6mths(current_date = datetime.today()): #----------------------------------\n",
    "        n = 5\n",
    "        future_date = current_date + relativedelta(months=n)\n",
    "        return future_date.strftime('%B-%Y')\n",
    "\n",
    "\n",
    "    start = past6mths()\n",
    "    end = future10mths()\n",
    "    this_mth = today()\n",
    "    next_mth = nextmth()\n",
    "    last_mth = lastmonth()\n",
    "    end_of_forecast = future6mths()\n",
    "\n",
    "\n",
    "    def startfunction(a_=0,b_=0,c_=0, d_=0, e_=0):\n",
    "\n",
    "        global df\n",
    "\n",
    "        # create empty dataframe and date range\n",
    "\n",
    "        d=pd.date_range(start=past6mths(), end=future10mths(), freq='MS')\n",
    "        df=pd.DataFrame(columns=['date','cls_stk_dpp','open_ord','total','fcast_open_stk','fcast_gds_rec','otd','otd_fcast','recom_topup',\n",
    "                                'recom_safe_stk','fcast_cls_stk','recom_ord','rssl_less_stk',\n",
    "                                'next_mth_cls_stk'])\n",
    "\n",
    "        # fill the dates with date range\n",
    "\n",
    "        df.date=d\n",
    "        df = df.fillna(0)\n",
    "\n",
    "        # system input order for 'cls_stk_dpp' & 'open_ord' and 'total'\n",
    "\n",
    "        df.loc[6,'cls_stk_dpp']=cls_stk_dpp\n",
    "\n",
    "        df.loc[6,'open_ord']=open_ord_dpp\n",
    "\n",
    "        df.total=df.cls_stk_dpp + df.open_ord\n",
    "\n",
    "        # input otd data from row 0 to row 5\n",
    "\n",
    "        y=[first,second,third,fouth,fifth, sixth]\n",
    "        df.loc[:5,'otd']=y\n",
    "\n",
    "        # convert dates to ordinal values\n",
    "\n",
    "        df['ord_date'] = [i.toordinal() for i in df.date]\n",
    "\n",
    "        # fill column with ordinal dates as independent training data for linear regression\n",
    "\n",
    "        otd_date = df.loc[0:5,'ord_date']\n",
    "\n",
    "        # run linera regression to produce m and b\n",
    "\n",
    "        m, b, rv, p, se = stats.linregress(otd_date,y)\n",
    "\n",
    "        # prepare ordinal dates for prediction and convert to list\n",
    "\n",
    "        fcast_date = df.loc[6:11,'ord_date']\n",
    "        fcast_date = fcast_date.tolist()\n",
    "\n",
    "        # forecast prediction using ordinal dates\n",
    "\n",
    "        fcast = [i*m+b for i in fcast_date]\n",
    "\n",
    "        fcast = [i.round(0) for i in fcast]\n",
    "\n",
    "        fcast = [func(i) for i in fcast]\n",
    "\n",
    "        # fill column with predicted values in 'otd_fcast'\n",
    "\n",
    "        df.loc[6:11,'otd_fcast']=fcast\n",
    "\n",
    "        # recommended top up qty\n",
    "\n",
    "        df.loc[6,'recom_topup']=topup\n",
    "\n",
    "\n",
    "\n",
    "        # recommended safety stocks\n",
    "\n",
    "        safe_stks = round(df.loc[0:5,'otd'].mean()*leadtime,0)\n",
    "        df.loc[6,'recom_safe_stk']=safe_stks\n",
    "\n",
    "\n",
    "        def fillrows(h):\n",
    "        # forecasted closing stock (O)\n",
    "\n",
    "            s = 1\n",
    "\n",
    "            if h >= 7:\n",
    "\n",
    "                df.loc[h,'fcast_cls_stk'] = df.loc[h,'fcast_open_stk'] + df.loc[h,'fcast_gds_rec'] - df.loc[h,'otd_fcast'] + \\\n",
    "                func1(df.loc[h-1,'fcast_cls_stk'])-df.loc[h-1,'recom_topup']\n",
    "\n",
    "            else:\n",
    "\n",
    "                df.loc[h,'fcast_cls_stk']= df.loc[h,'cls_stk_dpp']-df.loc[h,'otd_fcast']\n",
    "\n",
    "\n",
    "            # forecasted opening stock (G)\n",
    "\n",
    "            df.loc[h+1,'fcast_open_stk']=func(df.loc[h,'fcast_cls_stk'])\n",
    "\n",
    "\n",
    "            # recommended order qty (p)\n",
    "\n",
    "            if h>= 7:\n",
    "\n",
    "                b = df.loc[h,'otd_fcast'] + df.loc[h,'recom_topup'] + df.loc[h,'recom_safe_stk'] - df.loc[h,'fcast_open_stk']\n",
    "                c = func(b)\n",
    "                df.loc[h,'recom_ord'] = c + df.loc[h+s,'rssl_less_stk'] # change to s -------------------------------------------------\n",
    "                \n",
    "                \n",
    "            else:\n",
    "\n",
    "                b = df.loc[h,'otd_fcast'] + df.loc[h,'recom_topup'] + df.loc[h,'recom_safe_stk'] - df.loc[h,'total']\n",
    "                c = func(b)\n",
    "                df.loc[h,'recom_ord'] = c + df.loc[h+s,'rssl_less_stk'] # change to s -------------------------------------------------\n",
    "\n",
    "\n",
    "            # forecasted goods received (H)\n",
    "\n",
    "            df.loc[h+s,'fcast_gds_rec'] = df.loc[h,'recom_ord'] + df.loc[h,'open_ord'] # change to s -----------------------------------------\n",
    "            \n",
    "\n",
    "\n",
    "#             df.loc[h+s,'fcast_gds_rec'] = df.loc[h,'recom_ord']\n",
    "            # next month closing stock (S)\n",
    "\n",
    "            df.loc[h,'next_mth_cls_stk']=  safe_stks - df.loc[h,'fcast_cls_stk']\n",
    "\n",
    "            return df\n",
    "\n",
    "        # first to run\n",
    "\n",
    "        # insert \"next month closing stock less saftety stock\" into 'rssl_less_stk'\n",
    "\n",
    "        df.loc[7:11,'rssl_less_stk'] = [a_,b_,c_,d_,e_]\n",
    "\n",
    "\n",
    "\n",
    "        h=6\n",
    "        df = fillrows(h)\n",
    "\n",
    "        h=7\n",
    "        df = fillrows(h)\n",
    "\n",
    "        h=8\n",
    "        df = fillrows(h)\n",
    "\n",
    "        h=9\n",
    "        df = fillrows(h)\n",
    "\n",
    "        h=10\n",
    "        df = fillrows(h)\n",
    "\n",
    "        h=11\n",
    "        df = fillrows(h)\n",
    "\n",
    "        return df\n",
    "        \n",
    "    \n",
    "    total_df = pd.DataFrame() # summary\n",
    "    total_df1 = pd.DataFrame() # detail\n",
    "    total_with_OTD = pd.DataFrame() # summary with OTD forecast\n",
    "\n",
    "    # OTD inputs from dealers\n",
    "    \n",
    "    with PdfPages(output_data+'output_plots_'+str(num)+'_'+str(curr_mth())+'.pdf') as pdf:\n",
    "#     with PdfPages(output_data+'output_plots_'+num+'_'+str(curr_mth())+'.pdf') as pdf:\n",
    "        \n",
    "        for i in range(inp.shape[0]):\n",
    "            first=inp.iloc[i,0]\n",
    "            second=inp.iloc[i,1]\n",
    "            third=inp.iloc[i,2]\n",
    "            fouth=inp.iloc[i,3]\n",
    "            fifth=inp.iloc[i,4]\n",
    "            sixth=inp.iloc[i,5]\n",
    "\n",
    "            leadtime=inp.iloc[i,6] # extract from DPP (pre-determined)\n",
    "\n",
    "            cls_stk_dpp=inp.iloc[i,7] # extract from DPP\n",
    "            open_ord_dpp=inp.iloc[i,8] # extract from DPP\n",
    "            topup = inp.iloc[i,9] # input from CM/dealer\n",
    "            product_name=inp.iloc[i,11] # extract from DPP\n",
    "            product_code =inp.iloc[i,10] #--------------------------------------------------------------------\n",
    "            dealer_name = inp.iloc[i,12] # extract from DPP\n",
    "            wsp=inp.iloc[i,13] # extract from DPP (pre-determined)\n",
    "            dlr = inp.iloc[i,12] # extract from DPP (pre-determined)\n",
    "\n",
    "            # create df dataframe\n",
    "            df= startfunction()\n",
    "\n",
    "            a_ = func(df.loc[7,'next_mth_cls_stk'])\n",
    "            df = startfunction(a_)\n",
    "\n",
    "            b_ = func(df.loc[8,'next_mth_cls_stk'])\n",
    "            df = startfunction(a_,b_)\n",
    "\n",
    "            c_ = func(df.loc[9,'next_mth_cls_stk'])\n",
    "            df = startfunction(a_,b_,c_)\n",
    "\n",
    "            d_ = func(df.loc[10, 'next_mth_cls_stk'])\n",
    "            df = startfunction(a_,b_,c_,d_)\n",
    "\n",
    "            e_ = func(df.loc[11, 'next_mth_cls_stk'])\n",
    "            df = startfunction(a_,b_,c_,d_,e_)\n",
    "\n",
    "\n",
    "            # replace nan with zeros\n",
    "\n",
    "            df.otd.replace(0,np.nan, inplace=True)\n",
    "            df.loc[:5,'otd_fcast'].replace(0,np.nan, inplace=True) \n",
    "            df.loc[:5,'recom_ord'].replace(0, np.nan, inplace=True)#-----------------------------------------------\n",
    "            df.fcast_cls_stk.replace(0, np.nan, inplace=True)\n",
    "\n",
    "            # set negative values to zero\n",
    "\n",
    "            df.recom_ord = [func(iiii) for iiii in df.recom_ord]\n",
    "\n",
    "            # adding new columns\n",
    "\n",
    "        #     df['recom_ord']=df['recom_ord'].fillna(0) #-----------------------------------------------\n",
    "            df['total_recom_ord']=df['recom_ord']+df['recom_topup']\n",
    "#             df['total_recom_ord_val']=df['total_recom_ord']*wsp\n",
    "            df['product_name']=product_name\n",
    "            df['product_code']=product_code\n",
    "            df['dealer_name']=dlr\n",
    "\n",
    "            df2=df.loc[6,['product_code','product_name','recom_safe_stk','fcast_cls_stk','recom_ord','recom_topup',\\\n",
    "                          'total_recom_ord','dealer_name']]\n",
    "            \n",
    "            \n",
    "            df3=df.loc[6,['date','product_code','product_name','recom_safe_stk','fcast_cls_stk','recom_ord','recom_topup',\\\n",
    "                          'total_recom_ord','otd_fcast','dealer_name']]\n",
    "\n",
    "\n",
    "            df=df.drop(['rssl_less_stk','next_mth_cls_stk','ord_date'], axis=1)\n",
    "\n",
    "            df=df.iloc[0:12,:]\n",
    "\n",
    "            df=df.round(0)\n",
    "\n",
    "\n",
    "            total_df=total_df.append(df2)\n",
    "            total_with_OTD = total_with_OTD.append(df3)\n",
    "\n",
    "            total_df=total_df.reset_index(drop=True)\n",
    "            total_with_OTD=total_with_OTD.reset_index(drop=True)\n",
    "\n",
    "            total_df1=total_df1.append(df)\n",
    "\n",
    "\n",
    "            date_ = list(df.loc[6:10,'date'])\n",
    "            rec_ = list(df.loc[6:10,'total_recom_ord']) #------------------------------------\n",
    "\n",
    "                        \n",
    "            with open(output_data+'stocks_log_'+num+'_'+str(curr_mth())+'.txt', \"a\") as f:\n",
    "                for d, iii in zip(df.date, df.fcast_cls_stk):\n",
    "                    if iii <= 0:\n",
    "#                         print(f\"Insufficient stocks on {this_mth} for {product_name} in {dealer_name}\")\n",
    "#                         f.write(f\"Insufficient stocks in {this_mth} for {product_name} in {dealer_name}\"+ \"\\n\")\n",
    "                        f.write(f\"Insufficient stocks in {this_mth} for {product_name}\"+ \"\\n\")\n",
    "\n",
    "\n",
    "            plt.figure(figsize=(12,8))\n",
    "\n",
    "            axs=sns.lineplot(data=df, x='date', y='otd')\n",
    "            axs=sns.lineplot(data=df, x='date', y='otd_fcast')\n",
    "            axs=sns.lineplot(data=df, x='date', y='total_recom_ord') #------------------------------\n",
    "            axs=sns.lineplot(data=df, x='date', y='fcast_cls_stk')\n",
    "            plt.legend(['sell-out (6 months)','sell-out forecast (6 months)','recommended order','forecasted closing stocks'])\n",
    "            plt.title(f\"Forecast Of {product_name} For {dealer_name}\", fontsize=16, fontweight='heavy')\n",
    "            plt.xlabel('Date', fontsize=14)\n",
    "            plt.ylabel('Quantity', fontsize=14)\n",
    "\n",
    "            for f, e in enumerate(rec_):\n",
    "                plt.text(date_[f], rec_[f],e)\n",
    "#             plt.savefig(\"forecast_{i}.png\".format(i=i))\n",
    "\n",
    "            pdf.savefig()\n",
    "            plt.close()\n",
    "               \n",
    "\n",
    "\n",
    "    total_df = total_df.rename(columns={'product_code':'Product Code','product_name':'Product Name','recom_safe_stk':'Recommended Safety Stocks',\n",
    "                                 'fcast_cls_stk':'Forecasted Closing Stocks','recom_ord':'Recommended Order',\n",
    "                                 'recom_topup':'Recommended Top-up','total_recom_ord':'Total Recommended Order',\n",
    "                                 'dealer_name':'Dealer Name'})\n",
    "\n",
    "    total_df1 = total_df1.rename(columns={'cls_stk_dpp':'Closing stocks DPP','open_ord':'Open orders DPP','fcast_open_stk':\n",
    "                                         'Forecasted opening stocks','fcast_gds_rec':'Forecasted goods received',\n",
    "                                         'otd_fcast':'OTD Forecast','recom_topup':'Recommended Top-up','recom_safe_stk':\n",
    "                                         'Recommended Safety Stocks','fcast_cls_stk':'Forecasted Closing Stocks',\n",
    "                                         'recom_ord':'Recommended Order Qty','total_recom_ord':'Total Recommended Order Qty',\n",
    "                                         'product_name':'Product Name',\n",
    "                                         'dealer_name':'Dealer Name','product_code':'Product Code'})\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    print()\n",
    "    print(f\"Processed {inp.shape[0]} items for {num}\")\n",
    "    print(f\"Historical OTD data : {start} to {last_mth}\")\n",
    "    print(f\"Forecast OTD data : {this_mth} to {end_of_forecast}\")\n",
    "    print(f\"Processing completed for {num}\")\n",
    "    print()\n",
    "    \n",
    "    return total_df, total_df1, total_with_OTD\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "154d70b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_score1(dlr):\n",
    "    df3 = pd.DataFrame(columns = ['Product Name','Error Rate','Dealer Name'])\n",
    "    df2 = pd.DataFrame(columns = ['Product Name','Error Rate','Dealer Name'])\n",
    "    df=b[b['Dealer Name']==dlr]\n",
    "    pnlist = df['Product Name'].unique()\n",
    "    pnlist = list(pnlist)\n",
    "    for pn in pnlist:\n",
    "        df1=df[df['Product Name']==pn]\n",
    "        otd = df1.iloc[0:6,6].to_list()\n",
    "        fcast = df1.iloc[6:12,7].to_list()\n",
    "        mse = math.sqrt(mean_squared_error(otd,fcast))\n",
    "        dic = {'Product Name':pn,'Error Rate':mse, 'Dealer Name':dlr}\n",
    "        df2 = pd.DataFrame(dic, index=[0])\n",
    "        df3 = pd.concat([df3,df2], axis=0)\n",
    "    return df3\n",
    "\n",
    "\n",
    "def curr_mth():\n",
    "    return datetime.now().strftime('%b%y')\n",
    "        \n",
    "\n",
    "# df4 = pd.DataFrame(columns = ['Product Name','Error Rate','Dealer Name'])\n",
    "# for dlr in total_df1['Dealer Name'].unique():\n",
    "#     df4 = pd.concat([df4,check_score(dlr)])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12387964",
   "metadata": {},
   "source": [
    "# Top 20 products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a9b18183",
   "metadata": {},
   "outputs": [],
   "source": [
    "# load forecast raw data to identify top 20 products per country\n",
    "\n",
    "path = r'C:\\Users\\stephenl\\OneDrive - GC International AG\\ML\\forecast function demo\\templates\\input_data\\\\'\n",
    "\n",
    "fd = pd.read_excel(path+'forecast_data.xlsx',header=1)\n",
    "del fd['Unnamed: 0']\n",
    "\n",
    "# forward fill country and dealer fields\n",
    "fd=fd.fillna(method='ffill')\n",
    "\n",
    "fd.Dealer = [i.replace('/','_') for i in fd.Dealer]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1d68aece",
   "metadata": {},
   "outputs": [],
   "source": [
    "# simplify column names\n",
    "\n",
    "fd.rename(columns={'COUNTRY_SEA':'country','Dealer':'dealer','PRODUCT CODE':'productcode',\n",
    "                   'PRODUCT PACKAGING':'product_packaging','Sales_YTD as per Oct':'ytd_sales',\n",
    "                  '% of Dealer Sales':'percentage_of_sales'},inplace=True)\n",
    "\n",
    "# change to lower cases\n",
    "fd.dealer = [i.lower() for i in fd.dealer]\n",
    "\n",
    "# convert apl promed to promed (dealer)\n",
    "promed_name = lambda x: 'promed' if x=='apl promed' else x\n",
    "\n",
    "fd.dealer = [promed_name(i) for i in fd.dealer]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1216bb3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# shortlist top 20 products for Singapore\n",
    "\n",
    "def top20():\n",
    "    rev_share_dic={}\n",
    "    final = pd.DataFrame()\n",
    "    dlr_list = fd.dealer.unique()\n",
    "    for c in dlr_list:\n",
    "        y = fd[fd.dealer==c]\n",
    "        z = y.sort_values('percentage_of_sales',ascending=False).reset_index(drop=True)\n",
    "        z['rev_share']=z.percentage_of_sales.cumsum()\n",
    "        z = z[z.rev_share<=0.8]\n",
    "        final=pd.concat([final,z])\n",
    "        \n",
    "        # index of last row\n",
    "        try:\n",
    "            idx = z.index[-1]\n",
    "            # extract rev share\n",
    "            rev_share1 = z.loc[idx,'rev_share']\n",
    "            rev_share_dic[c]=rev_share1\n",
    "        except:\n",
    "            continue\n",
    "    return final[['country','dealer','productcode','product_packaging']], rev_share_dic\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "67e62818",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove certain dealers\n",
    "\n",
    "x,r = top20()\n",
    "\n",
    "req = ((x.dealer!='vtech')&(x.dealer!='health promotion board')&(x.dealer!='singhealth')&\n",
    "      (x.dealer!='quantumleap')&(x.dealer!='bumi')&(x.dealer!='gc ind'))\n",
    "\n",
    "top20 = x[req]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3af9f096",
   "metadata": {},
   "outputs": [],
   "source": [
    "# top 20 products of 6 major countries\n",
    "\n",
    "# create a dict of dealer and productcode\n",
    "top20dict={}\n",
    "for d,p in zip(top20.dealer,top20.productcode):\n",
    "    if d in top20dict:\n",
    "        top20dict[d].append(p)\n",
    "    else:\n",
    "        top20dict[d]=[p]\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b81fb451",
   "metadata": {},
   "source": [
    "# Actual sell out data from DPP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c57c0b62",
   "metadata": {},
   "outputs": [],
   "source": [
    "# load sell out data from DPP\n",
    "\n",
    "sell_out_data_path = r'C:\\Users\\stephenl\\OneDrive - GC International AG\\ML\\forecast function demo\\templates\\selloutdata\\\\'\n",
    "\n",
    "# sod = pd.read_csv(sell_out_data_path+'DPP Sellout_Cleaned07122022.csv')\n",
    "# sod = pd.read_excel(sell_out_data_path+'DPP Sellout_Cleaned07122022.xlsx', sheet_name='DPP Sellout_Cleaned07122022')\n",
    "sod = pd.read_excel(sell_out_data_path+'Sellout as at 17 Feb 2023.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "9d487479",
   "metadata": {},
   "outputs": [],
   "source": [
    "# trim columns\n",
    "\n",
    "sod = sod[['Create Date','Product Code','Product Name','Qty','Created By']]\n",
    "sod.columns = sod.columns.str.lower()\n",
    "sod.columns = [i.replace(' ','_') for i in sod.columns]\n",
    "\n",
    "# change to lower cases\n",
    "sod.created_by = [i.lower() for i in sod.created_by]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c15545c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert to datetime format\n",
    "sod.create_date=pd.to_datetime(sod.create_date,format='%d/%m/%Y')\n",
    "\n",
    "# create new column 'month' to extract month from date\n",
    "sod['month']=[t.strftime('%Y-%m') for t in sod.create_date]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "92ee8a7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove current in forecast calculation\n",
    "this_month = datetime.now().strftime('%Y-%m')\n",
    "\n",
    "req = (sod.month!=this_month)\n",
    "sod = sod[req]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "9bca90df",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>create_date</th>\n",
       "      <th>product_code</th>\n",
       "      <th>product_name</th>\n",
       "      <th>qty</th>\n",
       "      <th>created_by</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>0137F097-0020</td>\n",
       "      <td>SOLARE Sculpt Syringe A2 1.9g(1mL)</td>\n",
       "      <td>100</td>\n",
       "      <td>sales a: th (achs)</td>\n",
       "      <td>2021-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>0138F119-0130</td>\n",
       "      <td>G-aenial Universal Injectable 1.7g JE</td>\n",
       "      <td>50</td>\n",
       "      <td>sales a: th (achs)</td>\n",
       "      <td>2021-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>0137F097-0030</td>\n",
       "      <td>SOLARE Sculpt Syringe A3 1.9g(1mL)</td>\n",
       "      <td>20</td>\n",
       "      <td>sales a: th (achs)</td>\n",
       "      <td>2021-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>0130F129-0010C</td>\n",
       "      <td>Fuji IX 1-1 Package New</td>\n",
       "      <td>10</td>\n",
       "      <td>sales a: th (achs)</td>\n",
       "      <td>2021-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>341001</td>\n",
       "      <td>Coe Comfort Professional Package</td>\n",
       "      <td>2</td>\n",
       "      <td>th: accord hs - pennapa (for sellout)</td>\n",
       "      <td>2022-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11525</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>0131F163-0020</td>\n",
       "      <td>GC Gold Label 9 1-1 Extra A3</td>\n",
       "      <td>480</td>\n",
       "      <td>vn: tuyet hai</td>\n",
       "      <td>2023-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11526</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>0123F100-0000</td>\n",
       "      <td>FujiCEM Refill Pack, 13.3g Cartridge</td>\n",
       "      <td>190</td>\n",
       "      <td>vn: tuyet hai</td>\n",
       "      <td>2023-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11527</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>0101B888-0000</td>\n",
       "      <td>Fuji Plus 15g Powder</td>\n",
       "      <td>1680</td>\n",
       "      <td>vn: tuyet hai</td>\n",
       "      <td>2023-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11528</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>0101B887-0000</td>\n",
       "      <td>Fuji Plus 1-1 Pkg with Conditioner</td>\n",
       "      <td>2520</td>\n",
       "      <td>vn: tuyet hai</td>\n",
       "      <td>2023-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11529</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>0126F051-0000</td>\n",
       "      <td>GC Gold Label 1 1-1 Package</td>\n",
       "      <td>6200</td>\n",
       "      <td>vn: tuyet hai</td>\n",
       "      <td>2023-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11509 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      create_date    product_code                           product_name  \\\n",
       "0      2021-08-01   0137F097-0020     SOLARE Sculpt Syringe A2 1.9g(1mL)   \n",
       "1      2021-08-01   0138F119-0130  G-aenial Universal Injectable 1.7g JE   \n",
       "2      2021-08-01   0137F097-0030     SOLARE Sculpt Syringe A3 1.9g(1mL)   \n",
       "3      2021-08-01  0130F129-0010C                Fuji IX 1-1 Package New   \n",
       "4      2022-04-01          341001       Coe Comfort Professional Package   \n",
       "...           ...             ...                                    ...   \n",
       "11525  2023-01-01   0131F163-0020           GC Gold Label 9 1-1 Extra A3   \n",
       "11526  2023-01-01   0123F100-0000   FujiCEM Refill Pack, 13.3g Cartridge   \n",
       "11527  2023-01-01   0101B888-0000                   Fuji Plus 15g Powder   \n",
       "11528  2023-01-01   0101B887-0000     Fuji Plus 1-1 Pkg with Conditioner   \n",
       "11529  2023-01-01   0126F051-0000            GC Gold Label 1 1-1 Package   \n",
       "\n",
       "        qty                             created_by    month  \n",
       "0       100                     sales a: th (achs)  2021-08  \n",
       "1        50                     sales a: th (achs)  2021-08  \n",
       "2        20                     sales a: th (achs)  2021-08  \n",
       "3        10                     sales a: th (achs)  2021-08  \n",
       "4         2  th: accord hs - pennapa (for sellout)  2022-04  \n",
       "...     ...                                    ...      ...  \n",
       "11525   480                          vn: tuyet hai  2023-01  \n",
       "11526   190                          vn: tuyet hai  2023-01  \n",
       "11527  1680                          vn: tuyet hai  2023-01  \n",
       "11528  2520                          vn: tuyet hai  2023-01  \n",
       "11529  6200                          vn: tuyet hai  2023-01  \n",
       "\n",
       "[11509 rows x 6 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sod"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "5d1617f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# shortlist NK Luck and Pharmaforte\n",
    "\n",
    "sod_dealer = pd.DataFrame(columns=sod.columns)\n",
    "c = pd.DataFrame(columns=sod.columns)\n",
    "c['dealer']=np.nan\n",
    "c['country']=np.nan\n",
    "\n",
    "for idx, d in zip(sod.index, sod.created_by):\n",
    "    if re.search(r'nk luck',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='nk luck'\n",
    "        c.loc[idx,'country']='singapore'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'pharmaforte',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='pharmaforte'\n",
    "        c.loc[idx,'country']='singapore'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'phamaforte - ruth dealer',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='pharmaforte'\n",
    "        c.loc[idx,'country']='singapore'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'cobra',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='cobra'\n",
    "        c.loc[idx,'country']='indonesia'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'promed',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='promed'\n",
    "        c.loc[idx,'country']='indonesia'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'ntc',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='ntc'\n",
    "        c.loc[idx,'country']='malaysia'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'accord',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='accord'\n",
    "        c.loc[idx,'country']='thailand'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'sales a',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='accord'\n",
    "        c.loc[idx,'country']='thailand'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'eea',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='eea'\n",
    "        c.loc[idx,'country']='philippines'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'dental domain',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='dental domain'\n",
    "        c.loc[idx,'country']='philippines'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'denmedico',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='denmedico'\n",
    "        c.loc[idx,'country']='vietnam'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'minh phuong',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='minh phuong'\n",
    "        c.loc[idx,'country']='vietnam'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'edi',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='edi'\n",
    "        c.loc[idx,'country']='malaysia'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'lien ha',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='lien nha'\n",
    "        c.loc[idx,'country']='vietnam'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'p2 dental',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='kaldera _ p2 dental'\n",
    "        c.loc[idx,'country']='malaysia'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    elif re.search(r'tuyet hai',d):\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']='tuyet hai'\n",
    "        c.loc[idx,'country']='vietnam'\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "    else:\n",
    "        c = sod.loc[[idx]]\n",
    "        c.loc[idx,'dealer']=np.nan\n",
    "        c.loc[idx,'country']=np.nan\n",
    "        sod_dealer= pd.concat([sod_dealer,c])\n",
    "        \n",
    "sod_dealer.dropna(inplace=True)\n",
    "\n",
    "# list of dealers\n",
    "dlr_list = sod_dealer.dealer.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73b745e3",
   "metadata": {},
   "source": [
    "# Create function to determine the name of months needed for forecast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "bcd8b885",
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate the specific months needed for forecast\n",
    "def current_month():\n",
    "    return int(datetime.now().strftime('%m'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "367423e4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['2022-08', '2022-09', '2022-10', '2022-11', '2022-12', '2023-01']\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "\n",
    "now = datetime.now()\n",
    "end_date = (now - pd.DateOffset(months=1)).replace(day=1)\n",
    "start_date = (end_date - pd.DateOffset(months=5)).replace(day=1)\n",
    "dates = pd.date_range(start=start_date, end=end_date, freq='MS').strftime('%Y-%m').tolist()\n",
    "\n",
    "print(dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "1723a7e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create function to determine the number of months whether it qualifies for 6 months of historical data\n",
    "def num_of_months(dlr,pc):\n",
    "    \n",
    "    x = sod_dealer[sod_dealer.dealer==dlr]\n",
    "    y = x[x.product_code==pc]\n",
    "    z = y.month.unique()\n",
    "\n",
    "    num_of_months = len(set(z)&set(dates))\n",
    "    months_item = set(z)&set(dates)\n",
    "\n",
    "    return num_of_months"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41ff56c4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "3a7c7d2b",
   "metadata": {},
   "source": [
    "# Load inventory from DPP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "49832a24",
   "metadata": {},
   "outputs": [],
   "source": [
    "inventory_path = r'C:\\Users\\stephenl\\OneDrive - GC International AG\\ML\\forecast function demo\\templates\\inventory\\\\'\n",
    "\n",
    "inv_folder = os.listdir(inventory_path)\n",
    "excel_files = [invi for invi in inv_folder if invi.endswith(\".csv\")]\n",
    "excel_files = sorted(excel_files, key=lambda x: os.path.getmtime(os.path.join(inventory_path, x)), reverse=True)\n",
    "latest_file_path = os.path.join(inventory_path, excel_files[0])\n",
    "\n",
    "if re.search(r'inventory', latest_file_path):\n",
    "    inv = pd.read_csv(latest_file_path)\n",
    "    inv.dealer = inv.dealer.str.lower()\n",
    "else:\n",
    "    print(\"File not found.\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "b5fce303",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create new column 'dealer1'\n",
    "\n",
    "# shortlist NK Luck and Pharmaforte\n",
    "\n",
    "# sod_dealer = pd.DataFrame(columns=sod.columns)\n",
    "# c = pd.DataFrame(columns=sod.columns)\n",
    "inv['dealer1']=np.nan\n",
    "inv['country']=np.nan\n",
    "\n",
    "for idx, d in zip(inv.index, inv.dealer):\n",
    "    if re.search(r'nk luck',d):\n",
    "        inv.loc[idx,'dealer1']='nk luck'\n",
    "        inv.loc[idx,'country']='singapore'\n",
    "\n",
    "    elif re.search(r'pharmaforte',d):\n",
    "        inv.loc[idx,'dealer1']='pharmaforte'\n",
    "        inv.loc[idx,'country']='singapore'\n",
    "\n",
    "    elif re.search(r'cobra',d):\n",
    "        inv.loc[idx,'dealer1']='cobra'\n",
    "        inv.loc[idx,'country']='indonesia'\n",
    "\n",
    "    elif re.search(r'promed',d):\n",
    "        inv.loc[idx,'dealer1']='promed'\n",
    "        inv.loc[idx,'country']='indonesia'\n",
    "\n",
    "    elif re.search(r'ntc',d):\n",
    "        inv.loc[idx,'dealer1']='ntc'\n",
    "        inv.loc[idx,'country']='malaysia'\n",
    "\n",
    "    elif re.search(r'accord',d):\n",
    "        inv.loc[idx,'dealer1']='accord'\n",
    "        inv.loc[idx,'country']='thailand'\n",
    "\n",
    "    elif re.search(r'eea',d):\n",
    "        inv.loc[idx,'dealer1']='eea'\n",
    "        inv.loc[idx,'country']='philippines'\n",
    "\n",
    "    elif re.search(r'dental domain',d):\n",
    "        inv.loc[idx,'dealer1']='dental domain'\n",
    "        inv.loc[idx,'country']='philippines'\n",
    "\n",
    "    elif re.search(r'denmedico',d):\n",
    "        inv.loc[idx,'dealer1']='denmedico'\n",
    "        inv.loc[idx,'country']='vietnam'\n",
    "\n",
    "    elif re.search(r'minh phuong',d):\n",
    "        inv.loc[idx,'dealer1']='minh phuong'\n",
    "        inv.loc[idx,'country']='vietnam'\n",
    "\n",
    "    elif re.search(r'edi',d):\n",
    "        inv.loc[idx,'dealer1']='edi'\n",
    "        inv.loc[idx,'country']='malaysia'\n",
    "\n",
    "    elif re.search(r'lien',d):\n",
    "        inv.loc[idx,'dealer1']='lien nha'\n",
    "        inv.loc[idx,'country']='vietnam'\n",
    "\n",
    "    elif re.search(r'p2 dental',d) or re.search(r'kaldera',d):\n",
    "        inv.loc[idx,'dealer1']='kaldera _ p2 dental'\n",
    "        inv.loc[idx,'country']='malaysia'\n",
    "\n",
    "    elif re.search(r'tuyet hai',d):\n",
    "        inv.loc[idx,'dealer1']='tuyet hai'\n",
    "        inv.loc[idx,'country']='vietnam'\n",
    "\n",
    "    else:\n",
    "        inv.loc[idx,'dealer1']=np.nan\n",
    "        inv.loc[idx,'country']=np.nan\n",
    "\n",
    "        \n",
    "inv.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "bd5aa98d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# consolidate inventory status using groupby\n",
    "\n",
    "inv1 = inv.groupby(['dealer1','code','packaging']).agg(total_openorder=('open_order','sum'),total_available=('available','sum')).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43a6c747",
   "metadata": {},
   "source": [
    "# Create function to convert array to spot value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "f501ecd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert array to integer or string\n",
    "def spot(x):\n",
    "    for i in x:\n",
    "        return i"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2fd458b2",
   "metadata": {},
   "source": [
    "# Input data into input template"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "5304f857",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>create_date</th>\n",
       "      <th>product_code</th>\n",
       "      <th>product_name</th>\n",
       "      <th>qty</th>\n",
       "      <th>created_by</th>\n",
       "      <th>month</th>\n",
       "      <th>dealer</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>0137F097-0020</td>\n",
       "      <td>SOLARE Sculpt Syringe A2 1.9g(1mL)</td>\n",
       "      <td>100</td>\n",
       "      <td>sales a: th (achs)</td>\n",
       "      <td>2021-08</td>\n",
       "      <td>accord</td>\n",
       "      <td>thailand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>0138F119-0130</td>\n",
       "      <td>G-aenial Universal Injectable 1.7g JE</td>\n",
       "      <td>50</td>\n",
       "      <td>sales a: th (achs)</td>\n",
       "      <td>2021-08</td>\n",
       "      <td>accord</td>\n",
       "      <td>thailand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>0137F097-0030</td>\n",
       "      <td>SOLARE Sculpt Syringe A3 1.9g(1mL)</td>\n",
       "      <td>20</td>\n",
       "      <td>sales a: th (achs)</td>\n",
       "      <td>2021-08</td>\n",
       "      <td>accord</td>\n",
       "      <td>thailand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>0130F129-0010C</td>\n",
       "      <td>Fuji IX 1-1 Package New</td>\n",
       "      <td>10</td>\n",
       "      <td>sales a: th (achs)</td>\n",
       "      <td>2021-08</td>\n",
       "      <td>accord</td>\n",
       "      <td>thailand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>341001</td>\n",
       "      <td>Coe Comfort Professional Package</td>\n",
       "      <td>2</td>\n",
       "      <td>th: accord hs - pennapa (for sellout)</td>\n",
       "      <td>2022-04</td>\n",
       "      <td>accord</td>\n",
       "      <td>thailand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11525</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>0131F163-0020</td>\n",
       "      <td>GC Gold Label 9 1-1 Extra A3</td>\n",
       "      <td>480</td>\n",
       "      <td>vn: tuyet hai</td>\n",
       "      <td>2023-01</td>\n",
       "      <td>tuyet hai</td>\n",
       "      <td>vietnam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11526</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>0123F100-0000</td>\n",
       "      <td>FujiCEM Refill Pack, 13.3g Cartridge</td>\n",
       "      <td>190</td>\n",
       "      <td>vn: tuyet hai</td>\n",
       "      <td>2023-01</td>\n",
       "      <td>tuyet hai</td>\n",
       "      <td>vietnam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11527</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>0101B888-0000</td>\n",
       "      <td>Fuji Plus 15g Powder</td>\n",
       "      <td>1680</td>\n",
       "      <td>vn: tuyet hai</td>\n",
       "      <td>2023-01</td>\n",
       "      <td>tuyet hai</td>\n",
       "      <td>vietnam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11528</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>0101B887-0000</td>\n",
       "      <td>Fuji Plus 1-1 Pkg with Conditioner</td>\n",
       "      <td>2520</td>\n",
       "      <td>vn: tuyet hai</td>\n",
       "      <td>2023-01</td>\n",
       "      <td>tuyet hai</td>\n",
       "      <td>vietnam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11529</th>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>0126F051-0000</td>\n",
       "      <td>GC Gold Label 1 1-1 Package</td>\n",
       "      <td>6200</td>\n",
       "      <td>vn: tuyet hai</td>\n",
       "      <td>2023-01</td>\n",
       "      <td>tuyet hai</td>\n",
       "      <td>vietnam</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11509 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      create_date    product_code                           product_name  \\\n",
       "0      2021-08-01   0137F097-0020     SOLARE Sculpt Syringe A2 1.9g(1mL)   \n",
       "1      2021-08-01   0138F119-0130  G-aenial Universal Injectable 1.7g JE   \n",
       "2      2021-08-01   0137F097-0030     SOLARE Sculpt Syringe A3 1.9g(1mL)   \n",
       "3      2021-08-01  0130F129-0010C                Fuji IX 1-1 Package New   \n",
       "4      2022-04-01          341001       Coe Comfort Professional Package   \n",
       "...           ...             ...                                    ...   \n",
       "11525  2023-01-01   0131F163-0020           GC Gold Label 9 1-1 Extra A3   \n",
       "11526  2023-01-01   0123F100-0000   FujiCEM Refill Pack, 13.3g Cartridge   \n",
       "11527  2023-01-01   0101B888-0000                   Fuji Plus 15g Powder   \n",
       "11528  2023-01-01   0101B887-0000     Fuji Plus 1-1 Pkg with Conditioner   \n",
       "11529  2023-01-01   0126F051-0000            GC Gold Label 1 1-1 Package   \n",
       "\n",
       "        qty                             created_by    month     dealer  \\\n",
       "0       100                     sales a: th (achs)  2021-08     accord   \n",
       "1        50                     sales a: th (achs)  2021-08     accord   \n",
       "2        20                     sales a: th (achs)  2021-08     accord   \n",
       "3        10                     sales a: th (achs)  2021-08     accord   \n",
       "4         2  th: accord hs - pennapa (for sellout)  2022-04     accord   \n",
       "...     ...                                    ...      ...        ...   \n",
       "11525   480                          vn: tuyet hai  2023-01  tuyet hai   \n",
       "11526   190                          vn: tuyet hai  2023-01  tuyet hai   \n",
       "11527  1680                          vn: tuyet hai  2023-01  tuyet hai   \n",
       "11528  2520                          vn: tuyet hai  2023-01  tuyet hai   \n",
       "11529  6200                          vn: tuyet hai  2023-01  tuyet hai   \n",
       "\n",
       "        country  \n",
       "0      thailand  \n",
       "1      thailand  \n",
       "2      thailand  \n",
       "3      thailand  \n",
       "4      thailand  \n",
       "...         ...  \n",
       "11525   vietnam  \n",
       "11526   vietnam  \n",
       "11527   vietnam  \n",
       "11528   vietnam  \n",
       "11529   vietnam  \n",
       "\n",
       "[11509 rows x 8 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sod_dealer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "01b223f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# extract productcodes from top20 products\n",
    "\n",
    "\n",
    "\n",
    "input_temp = pd.DataFrame(columns=['first','second','third','fourth','fifth','sixth','leadtime','closing_stks_dpp',\n",
    "                                  'open_order_dpp','topup','product_code','product_name','dealer_name','wholesale_px'])\n",
    "# start row counter\n",
    "n=0\n",
    "\n",
    "# append qualified dealer\n",
    "dlr_qualified=[]\n",
    "for dlr in dlr_list:\n",
    "    for pc in top20dict[dlr]:\n",
    "\n",
    "        if num_of_months(dlr,pc)==6:\n",
    "            x = sod_dealer[sod_dealer.dealer==dlr]\n",
    "            \n",
    "            # append dealers\n",
    "            dlr_qualified.append(dlr)\n",
    "            \n",
    "            p = x[x.product_code==pc]\n",
    "            g = p.groupby('month').agg(total_qty=('qty','sum'))\n",
    "            s = g.sort_index(ascending=False).head(6)\n",
    "            input_temp.loc[n,'first']=s.iloc[5,0]\n",
    "            input_temp.loc[n,'second']=s.iloc[4,0]\n",
    "            input_temp.loc[n,'third']=s.iloc[3,0]\n",
    "            input_temp.loc[n,'fourth']=s.iloc[2,0]\n",
    "            input_temp.loc[n,'fifth']=s.iloc[1,0]\n",
    "            input_temp.loc[n,'sixth']=s.iloc[0,0]\n",
    "            input_temp.loc[n,'leadtime']=2\n",
    "            inv1 = (inv.groupby(['dealer1','code','packaging']).agg(total_openorder=('open_order','sum'),\n",
    "                                                        total_available=('available','sum')).reset_index())\n",
    "            inv2 = inv1[inv1.dealer1==dlr]\n",
    "            inv3 = spot(inv2[inv2.code==pc].total_available)\n",
    "            input_temp.loc[n,'closing_stks_dpp']=inv3\n",
    "            inv4 = spot(inv2[inv2.code==pc].total_openorder)\n",
    "            input_temp.loc[n,'open_order_dpp']=inv4\n",
    "            input_temp.loc[n,'topup']=0\n",
    "            input_temp.loc[n,'product_code']=pc\n",
    "            inv5 = spot(inv2[inv2.code==pc].packaging)\n",
    "            input_temp.loc[n,'product_name']=inv5\n",
    "            input_temp.loc[n,'dealer_name']=dlr\n",
    "            input_temp.loc[n,'wholesale_px']=0\n",
    "            n+=1\n",
    "            \n",
    "            \n",
    "            \n",
    "#             print(f\"dealer: {dlr}, productcode: {pc}, qualify: {True}\")\n",
    "unique_dlr_list=[]         \n",
    "for i in dlr_qualified:\n",
    "    if i not in unique_dlr_list:\n",
    "        unique_dlr_list.append(i)\n",
    "        \n",
    "unique_dlr_list=[i.upper() for i in unique_dlr_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "b57e3169",
   "metadata": {},
   "outputs": [],
   "source": [
    "dealer_list = input_temp.dealer_name.unique()\n",
    "dealer_list = [i.replace('/','_') for i in dealer_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "4b06cdbe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Processed 22 items for accord\n",
      "Historical OTD data : August-2022 to January-2023\n",
      "Forecast OTD data : February-2023 to July-2023\n",
      "Processing completed for accord\n",
      "\n",
      "\n",
      "Processed 24 items for cobra\n",
      "Historical OTD data : August-2022 to January-2023\n",
      "Forecast OTD data : February-2023 to July-2023\n",
      "Processing completed for cobra\n",
      "\n",
      "\n",
      "Processed 16 items for nk luck\n",
      "Historical OTD data : August-2022 to January-2023\n",
      "Forecast OTD data : February-2023 to July-2023\n",
      "Processing completed for nk luck\n",
      "\n",
      "\n",
      "Processed 19 items for ntc\n",
      "Historical OTD data : August-2022 to January-2023\n",
      "Forecast OTD data : February-2023 to July-2023\n",
      "Processing completed for ntc\n",
      "\n",
      "\n",
      "Processed 6 items for kaldera _ p2 dental\n",
      "Historical OTD data : August-2022 to January-2023\n",
      "Forecast OTD data : February-2023 to July-2023\n",
      "Processing completed for kaldera _ p2 dental\n",
      "\n",
      "\n",
      "Processed 16 items for pharmaforte\n",
      "Historical OTD data : August-2022 to January-2023\n",
      "Forecast OTD data : February-2023 to July-2023\n",
      "Processing completed for pharmaforte\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# remove all text file in output_data_folder\n",
    "output_data_folder = os.listdir(output_data)\n",
    "for logfile in output_data_folder:\n",
    "    if logfile.endswith('.txt'):\n",
    "        path = os.path.join(output_data, logfile)\n",
    "        os.remove(path)\n",
    "\n",
    "\n",
    "# create error rate dataframe\n",
    "df5 = pd.DataFrame(columns = ['Product Name','Error Rate','Dealer Name'])\n",
    "df6 = pd.DataFrame()\n",
    "\n",
    "# output files\n",
    "for d in dealer_list:\n",
    "    inp = input_temp[input_temp.dealer_name==d]\n",
    "    a,b,c = forecast6(d)\n",
    "    a.to_csv(output_data+'summary_'+d+'_'+str(curr_mth())+'.csv',index=False)\n",
    "    b.to_csv(output_data+'details_'+d+'_'+str(curr_mth())+'.csv',index=False)\n",
    "    c.to_csv(output_data+'summary_w_OTD_'+d+'_'+str(curr_mth())+'.csv',index=False)\n",
    "    \n",
    "    # create excel sheet to show the total share of revenue for forecasted products\n",
    "    rev_share2 = a.merge(fd,left_on=['Dealer Name','Product Code'], right_on=['dealer','productcode'])\n",
    "    rev_share3=rev_share2.groupby('dealer').agg(total_rev_share=('percentage_of_sales','sum'))\n",
    "    rev_share3=rev_share3.reset_index()\n",
    "    rev_share3=rev_share3.round(2)\n",
    "#     rev_share3.to_csv(output_data+'revenue_share_of_products_'+d+'_'+str(curr_mth())+'.csv',index=False)\n",
    "    df6 = pd.concat([df6,rev_share3])\n",
    "    \n",
    "    # error\n",
    "    df5 = pd.concat([df5,check_score1(d)])\n",
    "    \n",
    "df6.to_csv(output_data+'revenue_share_of products_'+str(curr_mth())+'.csv',index=False)\n",
    "    \n",
    "df5.to_csv(output_data+'accuracy_'+str(curr_mth())+'.csv',index=False)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a0ecd7e4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}