incentive_8Mar_ruth.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8e26026d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import openpyxl\n",
    "import warnings\n",
    "warnings.filterwarnings(action='ignore')\n",
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_columns', None)\n",
    "import math\n",
    "from datetime import *\n",
    "import os\n",
    "import re\n",
    "import calendar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "79ae6010",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ruth\n",
    "\n",
    "path_input = r'C:\\Users\\YeoRuth\\OneDrive - GC International AG\\incentive\\data_input\\\\'\n",
    "path_output = r'C:\\Users\\YeoRuth\\OneDrive - GC International AG\\incentive\\data_output\\\\'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "685fe0cd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "On it...................\n"
     ]
    }
   ],
   "source": [
    "print('On it...................')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a7a060ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # use with data_input1 folder\n",
    "\n",
    "# path_input = r'C:\\Users\\stephenl\\OneDrive - GC International AG\\ML\\incentive\\incentive_ph\\data_input2\\\\'\n",
    "# path_output = r'C:\\Users\\stephenl\\OneDrive - GC International AG\\ML\\incentive\\incentive_ph\\data_output2\\\\'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1fd5b36c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def roundup(x):\n",
    "    i, f = divmod(x, 1)\n",
    "    return int(i + ((f >= 0.5) if (x > 0) else (f > 0.5)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6d9324f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# working with directory\n",
    "os.chdir(path_input)\n",
    "list1 = os.listdir()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0848d86b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# read from directory\n",
    "for p in list1:\n",
    "    if p.startswith('Log'):\n",
    "        # read customer activities file\n",
    "        ca = pd.read_excel(path_input+p,header=4).drop([0,1])\n",
    "        ca.columns=ca.columns.str.lower()\n",
    "        ca.columns=ca.columns.str.replace(' ','_')\n",
    "        ca.sales_executive = [' '.join(i.split()) for i in ca.sales_executive]\n",
    "        ca.date = pd.to_datetime(ca.date, format='%Y-%m-%d')\n",
    "    if p.startswith('Transfer'):\n",
    "        # read transfer order\n",
    "        df = pd.read_excel(path_input+p, header=4)\n",
    "        df.columns=df.columns.str.lower()\n",
    "        df.columns=df.columns.str.replace(' ','_')\n",
    "        df.sales_order_date = pd.to_datetime(df.sales_order_date, format='%d-%m-%Y')\n",
    "        df.product_name = df.product_name.str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f0b99ebd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove persons from vietnam, indonesia, Philippines and thailand\n",
    "\n",
    "req = (df.sales_person_name!='Truong Son Le') & (df.sales_person_name!='Prisma Armaya') & \\\n",
    "(df.sales_person_name!='Anuchart Maithong') & (df.sales_person_name!='Victor Mallillin')\n",
    "df=df[req]\n",
    "\n",
    "req1 = (ca.sales_executive!='Truong Son Le') & (ca.sales_executive!='Prisma Armaya') & \\\n",
    "(ca.sales_executive!='Anuchart Maithong') & (ca.sales_executive!='Victor Mallillin')\n",
    "ca=ca[req1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "87af9369",
   "metadata": {},
   "outputs": [],
   "source": [
    "# define last_month\n",
    "\n",
    "def last_month():\n",
    "    m = datetime.now().month-1\n",
    "    if m==0:\n",
    "        n=12\n",
    "    else:\n",
    "        n=m\n",
    "    return n\n",
    "\n",
    "last_month=last_month()\n",
    "\n",
    "# def year():\n",
    "#     x = datetime.now().year\n",
    "#     m = datetime.now().month\n",
    "#     if m>=1:\n",
    "#         yr = x-1\n",
    "#     else:\n",
    "#         yr = x\n",
    "#     return yr\n",
    "\n",
    "# define year\n",
    "year = datetime.now().year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "038ae731",
   "metadata": {},
   "outputs": [],
   "source": [
    "shortmonth = lambda x: 1 if x=='Jan' else 2 if x=='Feb' else 3 if x=='Mar' else 4 if x=='Apr' else 5 if x=='May' else \\\n",
    "    6 if x=='Jun' else 7 if x=='Jul' else 8 if x=='Aug' else 9 if x=='Sep' else 10 if x=='Oct' else 11 if x=='Nov' else 12 \\\n",
    "    if x=='Dec' else 'NA'\n",
    "\n",
    "def first_day_month(year,last_month):\n",
    "    return datetime(year,last_month,1)\n",
    "\n",
    "\n",
    "def last_day_month(year,last_month):\n",
    "    day = calendar.monthrange(year,last_month)[1]\n",
    "    return datetime(year, last_month,day)\n",
    "\n",
    "\n",
    "# convert numerical month to string month\n",
    "longmonth = lambda x: 'Jan' if x==1 else 'Feb' if x==2 else 'Mar' if x==3 else 'Apr' if x==4 else 'May' if x==5 else 'Jun'\\\n",
    "if x==6 else 'Jul' if x==7 else 'Aug' if x==8 else 'Sep' if x==9 else 'Oct' if x==10 else 'Nov' if x==11 else 'Dec' \\\n",
    "if x==12 else 'NA'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "96558d35",
   "metadata": {},
   "outputs": [],
   "source": [
    "# customer activities (3rd criteria of evaluation)\n",
    "\n",
    "def customer_activities_last_month2():\n",
    "    \n",
    "    # define date range for customer activities\n",
    "    req1 = (ca.date >= first_day_month(year,last_month)) & (ca.date <= last_day_month(year,last_month))\n",
    "    df_ca =ca[req1]\n",
    "    \n",
    "    vc = df_ca.groupby(['sales_executive','region']).size().reset_index(name='results')\n",
    "    vc.rename(columns={'sales_executive':'sales_rep','region':'country'},inplace=True)\n",
    "    \n",
    "    \n",
    "    # calculate value of sales #---------------\n",
    "    targetset = lambda x: 80 if x=='Hoang Nhan Ly' else 80 if x=='Dinh Dung Nguyen' else 80 if x=='Tuc Dinh Vo' else 80 if \\\n",
    "    x=='Van Tien Bui' else 120 #--------------------------------------------\n",
    "    \n",
    "#     target=100\n",
    "    w=0.4\n",
    "    \n",
    "    vc['crm compliance']='customer activities'\n",
    "    vc['target']=[targetset(i) for i in vc.sales_rep] #--------------------------\n",
    "    vc['weightage']=w\n",
    "    vc['achv_ratio']=vc.results/vc.target*100\n",
    "    \n",
    "\n",
    "    # distribution table\n",
    "    \n",
    "    vc['reward']=0\n",
    "\n",
    "    for i, w, j in zip(vc.index, vc.weightage,vc['achv_ratio']):\n",
    "        if j<65:\n",
    "            vc.loc[i,'reward']=0\n",
    "\n",
    "        elif j >= 65 and j <=100:\n",
    "            x = j*w\n",
    "            vc.loc[i,'reward']=roundup(x)\n",
    "\n",
    "        elif j > 100 and j <= 104:\n",
    "            j=110\n",
    "            x = j*w\n",
    "            vc.loc[i,'reward']=roundup(x)\n",
    "\n",
    "        elif j > 104 and j <= 109:\n",
    "            j=120\n",
    "            x=j*w\n",
    "            vc.loc[i,'reward']=roundup(x)\n",
    "\n",
    "        elif j > 109 and j <=119:\n",
    "            j=130\n",
    "            x=j*w\n",
    "            vc.loc[i,'reward']=roundup(x)\n",
    "\n",
    "        else:\n",
    "            j=140\n",
    "            x=j*w\n",
    "            vc.loc[i,'reward']=roundup(x)\n",
    "            \n",
    "                \n",
    "    vc['weightage']=w\n",
    "                \n",
    "    vc1 = vc[['crm compliance','sales_rep','target','results','achv_ratio','weightage','reward','country']]\n",
    "    \n",
    "    return vc1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "39b921a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculating customer activities and assigning to custAct\n",
    "\n",
    "custAct =customer_activities_last_month2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f8d5a3b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_excel(path_input+'master.xlsx')\n",
    "\n",
    "df1.crm_product_name = df1.crm_product_name.astype(str)\n",
    "df1.crm_product_name=[' '.join(i.split()) for i in df1.crm_product_name]\n",
    "df1.sub_category=df1.sub_category.astype(str)\n",
    "df1.sub_category=[' '.join(i.split()) for i in df1.sub_category]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "0828f14c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def reward_last_month2(c,b,dff):\n",
    "    \n",
    "    # convert numerical month to string month\n",
    "    longmonth = lambda x: 'Jan' if x==1 else 'Feb' if x==2 else 'Mar' if x==3 else 'Apr' if x==4 else 'May' if x==5 else 'Jun'\\\n",
    "    if x==6 else 'Jul' if x==7 else 'Aug' if x==8 else 'Sep' if x==9 else 'Oct' if x==10 else 'Nov' if x==11 else 'Dec' \\\n",
    "    if x==12 else 'NA'\n",
    "    \n",
    "    # read incentive strategy to link product sub category name to crm product name\n",
    "#     df1 = pd.read_excel(path_input+dff, sheet_name='master')\n",
    "    df1 = pd.read_excel(path_input+'master.xlsx')\n",
    "\n",
    "    df1.crm_product_name = df1.crm_product_name.astype(str)\n",
    "    df1.crm_product_name=[' '.join(i.split()) for i in df1.crm_product_name]\n",
    "    df1.sub_category=df1.sub_category.astype(str)\n",
    "    df1.sub_category=[' '.join(i.split()) for i in df1.sub_category]\n",
    "\n",
    "\n",
    "    ph_sm=df1.copy()\n",
    "    \n",
    "#     now = datetime.now()\n",
    "    last_month1 = longmonth(last_month)\n",
    "\n",
    "    \n",
    "    # read target and weightage\n",
    "    df2 = pd.read_excel(path_input+dff, sheet_name=last_month1) # dff is name of source file\n",
    "\n",
    "    # select country in transfer order by product\n",
    "    ph=df[df.country==c]\n",
    "    \n",
    "    # define date range for transfer order by product\n",
    "    req = (df.sales_order_date >= first_day_month(year,last_month)) & \\\n",
    "    (df.sales_order_date <= last_day_month(year,last_month))\n",
    "    ph=ph[req]\n",
    "    \n",
    "    # select sales rep in the transfer order by product\n",
    "    nm = b\n",
    "    ph_sales = ph[ph.sales_person_name==nm]\n",
    "    \n",
    "    ph_sales = ph_sales.reset_index(drop=True)\n",
    "\n",
    "\n",
    "    # attached sub_category to every product code (in the transfer order)\n",
    "    ph_sales['sub_category']=np.nan\n",
    "    for i, p in zip(ph_sales.index, ph_sales.product_name):\n",
    "        for pc, sc in zip(ph_sm.crm_product_name, ph_sm.sub_category):\n",
    "            if p==pc:\n",
    "                ph_sales.loc[i,'sub_category']=sc\n",
    "                \n",
    "    ph_sales = ph_sales.reset_index(drop=True)\n",
    "\n",
    "    # groupby sub_category for transfer order\n",
    "    ph_sales1 = ph_sales.dropna(subset=['sub_category'])\n",
    "    sales_gp = ph_sales1.groupby(['sales_person_name','sub_category']).agg(qty=('qty','sum')).reset_index()\n",
    "    \n",
    "\n",
    "    # transfer qty from transfer order to target sheet based on sub-category on target sheet\n",
    "    df2['qty']=0\n",
    "    for i, s in zip(df2.index, df2.sub_cat):\n",
    "        for sc, q in zip(sales_gp.sub_category, sales_gp.qty):\n",
    "            if s==sc: # if sub category is the same\n",
    "                df2.loc[i,'qty']=q\n",
    "\n",
    "            else:\n",
    "                continue\n",
    "                \n",
    "    df2=df2.reset_index(drop=True)\n",
    "    \n",
    "    \n",
    "    # if target is zero, achieve also must be zero - based on selected sales_rep\n",
    "    for i,q,t in zip(df2.index, df2.qty, df2[nm]):\n",
    "        \n",
    "        if t<=0:\n",
    "            df2.loc[i,'achieve']=0\n",
    "        else:\n",
    "            d=q/t*100\n",
    "            df2.loc[i,'achieve']=roundup(d)\n",
    "\n",
    "\n",
    "    # calculate reward on target sheet\n",
    "    df2['reward']=0\n",
    "\n",
    "    for i, w, j in zip(df2.index, df2.weightage,df2.achieve):\n",
    "        if j<65:\n",
    "            df2.loc[i,'reward']=0\n",
    "\n",
    "        elif j >= 65 and j <=100:\n",
    "            x = j*w\n",
    "            df2.loc[i,'reward']=roundup(x)\n",
    "\n",
    "        elif j >100 and j <= 104:\n",
    "            j=110\n",
    "            x = j*w\n",
    "            df2.loc[i,'reward']=roundup(x)\n",
    "\n",
    "        elif j >104 and j <= 109:\n",
    "            j=120\n",
    "            x=j*w\n",
    "            df2.loc[i,'reward']=roundup(x)\n",
    "\n",
    "        elif j > 109 and j <=119:\n",
    "            j=130\n",
    "            x=j*w\n",
    "            df2.loc[i,'reward']=roundup(x)\n",
    "\n",
    "        else:\n",
    "            j=140\n",
    "            x=j*w\n",
    "            df2.loc[i,'reward']=roundup(x)\n",
    "\n",
    "    # rename target sheet columns and assign to df3\n",
    "    df2['sales_rep']=nm\n",
    "    df2.rename(columns={nm:'target'},inplace=True)\n",
    "    df2['country']=c\n",
    "\n",
    "    df3=df2[['sub_cat','target','qty','achieve','weightage','reward','sales_rep','country']]\n",
    "    df3.rename(columns={'achieve':'achv_ratio'},inplace=True)\n",
    "    \n",
    "    print(f\"Country: {c} | GC Assoc: {nm}\")\n",
    "    \n",
    "    # calculate value of sales (2nd criteria for evaluation) \"Transfer Order\"\n",
    "\n",
    "    w=0.4\n",
    "    v = ph_sales.value_in_base_currency.sum()\n",
    "    \n",
    "    # condition for $15000 as target\n",
    "    if nm=='Van Tien Bui' or nm=='Tuc Dinh Vo' or nm=='Dinh Dung Nguyen' or nm=='Hoang Nhan Ly': #-----------\n",
    "        target=15000\n",
    "    else:\n",
    "        target=20000\n",
    "        \n",
    "    j = v/target*100 #-------------------------\n",
    "    \n",
    "    # distribution table\n",
    "    \n",
    "    # weightage\n",
    "    w=0.4\n",
    "\n",
    "    if j<65:\n",
    "        x=0\n",
    "\n",
    "    elif j >= 65 and j <=100:\n",
    "        x = j*w\n",
    "\n",
    "    elif j > 100 and j <= 104:\n",
    "        j=110\n",
    "        x = j*w\n",
    "\n",
    "    elif j > 104 and j <= 109:\n",
    "        j=120\n",
    "        x=j*w\n",
    "\n",
    "    elif j > 109 and j <=119:\n",
    "        j=130\n",
    "        x=j*w\n",
    "\n",
    "    else:\n",
    "        j=140\n",
    "        x=j*w\n",
    "        \n",
    "    df4 = pd.DataFrame({'crm compliance':'transfer order','sales_rep':nm,'target':target,'results': v,\n",
    "                       'achv_ratio':j,'weightage':w,'reward':x, 'country':c}, index=[0])\n",
    "\n",
    "    # df3 is speed product sales (1st criteria of evaluation)\n",
    "    # df4 is total transfer order value (2nd criteria of evaluation)\n",
    "    return df3, df4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76500b49",
   "metadata": {},
   "source": [
    "## function to return last month crm speed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "350d05fd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Isthy Baroroh', 'Cecep Setiyantono'], dtype=object)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.country=='Indonesia'].sales_person_name.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "1fc60bf2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# calaulate rewards for speed sales (1st criteria of evaluation) across all sales assoc at once\n",
    "\n",
    "def sea_last_month2():\n",
    "    c='Singapore'\n",
    "    reps = df[df.country==c].sales_person_name.unique()\n",
    "    dff = 'incentive_sg2.xlsx' # use this instead of 'incentive_sg.xlsx'\n",
    "\n",
    "    b = reps[0]\n",
    "    r0,v0=reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[1]\n",
    "    r1,v1= reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[2]\n",
    "    r2,v2= reward_last_month2(c,b,dff)\n",
    "\n",
    "    sg_last_mth = pd.concat([r0,r1,r2])\n",
    "    sg_last_mth_v = pd.concat([v0,v1,v2])\n",
    "    \n",
    "    \n",
    "    \n",
    "    c='Indonesia'\n",
    "    reps = df[df.country==c].sales_person_name.unique()\n",
    "    dff = 'incentive_in2.xlsx'\n",
    "\n",
    "    b = reps[0]\n",
    "    r0,v0=reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[1]\n",
    "    r1,v1= reward_last_month2(c,b,dff)\n",
    "\n",
    "#     b = reps[2]\n",
    "#     r2,v2= reward_last_month2(c,b,dff)\n",
    "\n",
    "    in_last_mth = pd.concat([r0,r1])\n",
    "    in_last_mth_v = pd.concat([v0,v1])\n",
    "    \n",
    "    \n",
    "    \n",
    "    c='Philippines'\n",
    "    reps = df[df.country==c].sales_person_name.unique()\n",
    "    dff = 'incentive_ph2.xlsx'\n",
    "\n",
    "    b = reps[0]\n",
    "    r0,v0=reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[1]\n",
    "    r1,v1= reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[2]\n",
    "    r2,v2= reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[3]\n",
    "    r3,v3= reward_last_month2(c,b,dff)\n",
    "\n",
    "    ph_last_mth = pd.concat([r0,r1,r2,r3])\n",
    "    ph_last_mth_v = pd.concat([v0,v1,v2,v3])\n",
    "    \n",
    "    \n",
    "    \n",
    "    c='Thailand'\n",
    "    reps = df[df.country==c].sales_person_name.unique()\n",
    "    dff = 'incentive_th2.xlsx'\n",
    "\n",
    "    b = reps[0]\n",
    "    r0,v0=reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[1]\n",
    "    r1,v1= reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[2]\n",
    "    r2,v2= reward_last_month2(c,b,dff)\n",
    "\n",
    "#     b = reps[3]\n",
    "#     r3,v3= reward_last_month2(c,b,dff)\n",
    "\n",
    "    th_last_mth = pd.concat([r0,r1,r2])\n",
    "    th_last_mth_v = pd.concat([v0,v1,v2])\n",
    "    \n",
    "    \n",
    "    \n",
    "    c='Malaysia'\n",
    "    reps = df[df.country==c].sales_person_name.unique()\n",
    "    dff = 'incentive_my2.xlsx'\n",
    "\n",
    "    b = reps[0]\n",
    "    r0,v0=reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[1]\n",
    "    r1,v1= reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[2]\n",
    "    r2,v2= reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[3]\n",
    "    r3,v3= reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[4]\n",
    "    r4,v4= reward_last_month2(c,b,dff)\n",
    "\n",
    "    my_last_mth = pd.concat([r0,r1,r2,r3,r4])\n",
    "    my_last_mth_v = pd.concat([v0,v1,v2,v3,v4])\n",
    "    \n",
    "    \n",
    "    \n",
    "    c='Vietnam'\n",
    "    reps = df[df.country==c].sales_person_name.unique()\n",
    "    dff = 'incentive_vn2.xlsx'\n",
    "\n",
    "    b = reps[0]\n",
    "    r0,v0=reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[1]\n",
    "    r1,v1= reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[2]\n",
    "    r2,v2= reward_last_month2(c,b,dff)\n",
    "\n",
    "    b = reps[3]\n",
    "    r3,v3= reward_last_month2(c,b,dff)\n",
    "\n",
    "    vn_last_mth = pd.concat([r0,r1,r2,r3])\n",
    "    vn_last_mth_v = pd.concat([v0,v1,v2,v3])\n",
    "    \n",
    "    \n",
    "    # a is speed.  b is transfer order\n",
    "    a = pd.concat([ph_last_mth,sg_last_mth, th_last_mth, in_last_mth, my_last_mth, vn_last_mth],axis=0)\n",
    "    b = pd.concat([ph_last_mth_v,sg_last_mth_v, th_last_mth_v, in_last_mth_v, my_last_mth_v, vn_last_mth_v],axis=0)\n",
    "    \n",
    "    return a,b\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b35ed87",
   "metadata": {},
   "source": [
    "## Data quality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "734bd75f",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataQly=custAct.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "62d17b6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# preparing form in excel\n",
    "dataQly['crm compliance']='data quality'\n",
    "dataQly['weightage']=0.2\n",
    "dataQly.target=10\n",
    "dataQly.results=10\n",
    "dataQly['achv_ratio']=dataQly.results/dataQly.target*100\n",
    "dataQly['reward']=0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "ee5e174c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate reward on customer activities sheet\n",
    "dataQly['reward']=0\n",
    "\n",
    "for i, w, j in zip(dataQly.index, dataQly.weightage,dataQly['achv_ratio']):\n",
    "    if j<65:\n",
    "        dataQly.loc[i,'reward']=0\n",
    "\n",
    "    elif j >= 65 and j <=100:\n",
    "        x = j*w\n",
    "        dataQly.loc[i,'reward']=roundup(x)\n",
    "\n",
    "    elif j > 100 and j <= 104:\n",
    "        j=110\n",
    "        x = j*w\n",
    "        dataQly.loc[i,'reward']=roundup(x)\n",
    "\n",
    "    elif j > 104 and j <= 109:\n",
    "        j=120\n",
    "        x=j*w\n",
    "        dataQly.loc[i,'reward']=roundup(x)\n",
    "\n",
    "    elif j > 109 and j <=119:\n",
    "        j=130\n",
    "        x=j*w\n",
    "        dataQly.loc[i,'reward']=roundup(x)\n",
    "\n",
    "    else:\n",
    "        j=140\n",
    "        x=j*w\n",
    "        dataQly.loc[i,'reward']=roundup(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "62dc3621",
   "metadata": {},
   "outputs": [],
   "source": [
    "# function to calculate final rewards (w/o weights)\n",
    "def freward(j):\n",
    "    \n",
    "    if j<65:\n",
    "        x=0\n",
    "\n",
    "    elif j >= 65 and j <=100:\n",
    "        x = j\n",
    "\n",
    "    elif j > 100 and j <= 104:\n",
    "        j=110\n",
    "        x = j\n",
    "\n",
    "    elif j > 104 and j <= 109:\n",
    "        j=120\n",
    "        x=j\n",
    "\n",
    "    elif j > 109 and j <=119:\n",
    "        j=130\n",
    "        x=j\n",
    "\n",
    "    else:\n",
    "        j=140\n",
    "        x=j\n",
    "            \n",
    "    return x\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fe3f377",
   "metadata": {},
   "source": [
    "## Consolidating speed rewards calculation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "f2e0023e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Country: Singapore | GC Assoc: Jade Li\n",
      "Country: Singapore | GC Assoc: Jaslyn Koh\n",
      "Country: Singapore | GC Assoc: Patrick Teo\n",
      "Country: Indonesia | GC Assoc: Isthy Baroroh\n",
      "Country: Indonesia | GC Assoc: Cecep Setiyantono\n",
      "Country: Philippines | GC Assoc: Darlene Dela Fuente\n",
      "Country: Philippines | GC Assoc: Jonathan Familar\n",
      "Country: Philippines | GC Assoc: Randy Santos\n",
      "Country: Philippines | GC Assoc: Carlo Molina\n",
      "Country: Thailand | GC Assoc: Panaschakorn Nitiwut\n",
      "Country: Thailand | GC Assoc: Wiparath Wongpunpong\n",
      "Country: Thailand | GC Assoc: Benjawan Wisuttilat\n",
      "Country: Malaysia | GC Assoc: Palanikaviyarasan Rajaretnam\n",
      "Country: Malaysia | GC Assoc: Hanafi Nordin\n",
      "Country: Malaysia | GC Assoc: Intan Suraya\n",
      "Country: Malaysia | GC Assoc: David Ting\n",
      "Country: Malaysia | GC Assoc: Audrey Ang\n",
      "Country: Vietnam | GC Assoc: Hoang Nhan Ly\n",
      "Country: Vietnam | GC Assoc: Dinh Dung Nguyen\n",
      "Country: Vietnam | GC Assoc: Van Tien Bui\n",
      "Country: Vietnam | GC Assoc: Tuc Dinh Vo\n"
     ]
    }
   ],
   "source": [
    "# creating filenames\n",
    "# creating run month names\n",
    "# save to local folders in the same directory\n",
    "\n",
    "# now = datetime.now()\n",
    "last_month1 = longmonth(last_month)\n",
    "\n",
    "filename = 'speed_'+ last_month1 + '.csv'\n",
    "filename2 = 'transferOrder_' + last_month1 + '.csv'\n",
    "\n",
    "speed,transferOrder=sea_last_month2()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "576598e4",
   "metadata": {},
   "source": [
    "## Calculating final speed rewards with distribution table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "e2e7f7bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# consolidating first reward and assign to final_speed\n",
    "final_speed=speed.groupby(['sales_rep','country']).agg(reward1=('reward','sum')).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "c5cb3fde",
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculation of final rewards for speed and assign to final_speed['reward2']\n",
    "final_speed['speedReward']=[i*0.5 for i in final_speed.reward1]\n",
    "\n",
    "# remove rewards1 column\n",
    "final_speed = final_speed[['sales_rep','country','speedReward']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fca7f616",
   "metadata": {},
   "source": [
    "# Final Speed Reward"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "63e93501",
   "metadata": {},
   "outputs": [],
   "source": [
    "# set sales rep as index\n",
    "final_speed1=final_speed.set_index('sales_rep')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90b47e1e",
   "metadata": {},
   "source": [
    "## Final Rewards on TransferOrder, CustAct, DataQuality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "99d2066f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge transferOrder, custAct and dataQly rewards in one datafrome\n",
    "# set sales_reps as index for all 3 dataframes\n",
    "\n",
    "transferOrder1=transferOrder.set_index('sales_rep')\n",
    "custAct1=custAct.set_index('sales_rep')\n",
    "dataQly1=dataQly.set_index('sales_rep')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "2963a8fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create new dataframe to merge all 3 dataframes\n",
    "secReward = dataQly[['sales_rep','country']]\n",
    "secReward['tranOrdReward']=0\n",
    "secReward['custActReward']=0\n",
    "secReward['dataQlyReward']=0\n",
    "secReward['totalReward']=0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "c646d152",
   "metadata": {},
   "outputs": [],
   "source": [
    "secReward1=secReward.set_index('sales_rep')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "ad93b2d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# fill in the fields in secReward\n",
    "\n",
    "for i, n in zip(transferOrder1.index, transferOrder1.reward):\n",
    "    secReward1.loc[i,'tranOrdReward']=n\n",
    "for i, n in zip(custAct1.index, custAct1.reward):\n",
    "    secReward1.loc[i,'custActReward']=n\n",
    "for i, n in zip(dataQly1.index, dataQly1.reward):\n",
    "    secReward1.loc[i,'dataQlyReward']=n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "eaa609a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# add all second part of the rewards together and assign to 'totalReward' column\n",
    "secReward1['totalReward']=secReward1.tranOrdReward + secReward1.custActReward + secReward1.dataQlyReward"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "d2f11238",
   "metadata": {},
   "outputs": [],
   "source": [
    "# apply 50% weightage to totalReward and assign to 'tranOrd_custAct_dataQly_Reward' columns\n",
    "\n",
    "secReward1['tranOrd_custAct_dataQly_Reward']=[int(freward(i)*0.5) for i in secReward1.totalReward]\n",
    "# secReward1=secReward1.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df0c838f",
   "metadata": {},
   "source": [
    "## Final Reward"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "5d562557",
   "metadata": {},
   "outputs": [],
   "source": [
    "# show only 'country' and 'tranOrd_custAct_dataQly_Reward' columns\n",
    "finalReward = secReward1[['country', 'tranOrd_custAct_dataQly_Reward']].reset_index()\n",
    "\n",
    "req = (finalReward.sales_rep!='Ruth Yeo')\n",
    "\n",
    "finalReward = finalReward[req]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "b318a29c",
   "metadata": {},
   "outputs": [],
   "source": [
    "finalReward = finalReward.set_index('sales_rep')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "dfc9b6f4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# add 'speedReward' column\n",
    "for i in finalReward.index:\n",
    "    finalReward.loc[i,'speedReward']=final_speed1.loc[i,'speedReward']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "787132ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "finalReward['totalReward']=finalReward.tranOrd_custAct_dataQly_Reward + finalReward.speedReward"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "b51d45ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "finalReward=finalReward.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "f445aaed",
   "metadata": {},
   "outputs": [],
   "source": [
    "finalReward=finalReward.sort_values('totalReward', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d3bc4e5",
   "metadata": {},
   "source": [
    "# Final Result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "4dfdafcc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating filenames\n",
    "# creating run month names\n",
    "\n",
    "now = datetime.now()\n",
    "last_month1 = longmonth(last_month)\n",
    "\n",
    "filename = 'speed_'+ last_month1 + '.csv'\n",
    "filename2 = 'transferOrder_' + last_month1 + '.csv'\n",
    "filename3 = 'custActivities_' + last_month1 + '.csv'\n",
    "filename4 = 'dataQuality_' + last_month1 + '.csv'\n",
    "filename5 = 'finalReward_' + last_month1 + '.csv'\n",
    "\n",
    "# speed,transferOrder=sea_last_month2()\n",
    "speed.to_csv(path_output+filename, index=False)\n",
    "transferOrder.to_csv(path_output+filename2, index=False)\n",
    "custAct.to_csv(path_output+filename3, index=False)\n",
    "dataQly.to_csv(path_output+filename4, index=False)\n",
    "finalReward.to_csv(path_output+filename5, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "d5f1a836",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create new df for data quality for concatenation\n",
    "dataQly_sum = dataQly[['sales_rep','target','results']]\n",
    "dataQly_sum.rename(columns={'target':'dataQualityTarget','results':'dataQualityResults'},inplace=True)\n",
    "\n",
    "custAct_sum = custAct[['sales_rep','target','results']]\n",
    "custAct_sum.rename(columns={'target':'custActTarget','results':'custActResults'},inplace=True)\n",
    "\n",
    "speed_sum = speed.groupby('sales_rep').agg(speedTarget=('target','sum'),speedResults=('qty','sum')).reset_index()\n",
    "\n",
    "transferOrder_sum = transferOrder[['sales_rep','target','results']]\n",
    "transferOrder_sum.rename(columns={'target':'transferOrderTarget','results':'transferOrderResults'},inplace=True)\n",
    "\n",
    "finalReward_sum = finalReward[['sales_rep','totalReward','country']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "b2e6bd32",
   "metadata": {},
   "outputs": [],
   "source": [
    "# concatenate all def\n",
    "dataQly_cusAct = dataQly_sum.merge(custAct_sum,left_on='sales_rep', right_on='sales_rep')\n",
    "dataQly_cusAct_speed = dataQly_cusAct.merge(speed_sum,left_on='sales_rep', right_on='sales_rep')\n",
    "dataQly_cusAct_speed_transferOrder = dataQly_cusAct_speed.merge(transferOrder_sum,left_on='sales_rep', right_on='sales_rep')\n",
    "dataQly_cusAct_speed_transferOrder_totalReward = dataQly_cusAct_speed_transferOrder.merge(finalReward_sum,\n",
    "                                                                                         left_on='sales_rep', right_on='sales_rep')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "cd95b5cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create new column 'month' and populate with last_month\n",
    "dataQly_cusAct_speed_transferOrder_totalReward['month']=last_month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "32f04639",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save file for powerbi with month for historical records\n",
    "filename6 = 'incentiveReport_' + last_month1 + '.csv'\n",
    "dataQly_cusAct_speed_transferOrder_totalReward.to_csv(path_output+filename6, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "66900050",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # save file for powerbi presentation\n",
    "# filename7 = 'incentive_powerbi_' + last_month1 + '.csv'\n",
    "# dataQly_cusAct_speed_transferOrder_totalReward.to_csv(path_output+filename7, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "ab7179fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # analysis of speed\n",
    "# speed_analysis=speed.groupby(['sales_rep','sub_cat','country']).agg(speedTarget=('target','sum'),\n",
    "#                                                                     speedResults=('qty','sum')).reset_index()\n",
    "\n",
    "# req = (speed_analysis.speedTarget>0) & (speed_analysis.speedResults>0)\n",
    "# speed_analysis1 = speed_analysis[req]\n",
    "# speed_analysis1['variance']=speed_analysis1.speedResults/speed_analysis1.speedTarget\n",
    "# speed_analysis2=speed_analysis1.sort_values(['country','sales_rep','variance'], ascending=False)\n",
    "# speed_analysis2=speed_analysis2.reset_index(drop=True)\n",
    "# speed_analysis2 = speed_analysis2[['sales_rep','sub_cat','speedTarget','speedResults','variance','country']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "2c876ed7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # analysis of speed 2\n",
    "\n",
    "# req = speed.target > 0\n",
    "\n",
    "# speed_analysis3 = speed[req]\n",
    "# speed_analysis3.rename(columns={'target':'speedTarget','qty':'speedResults'},inplace=True)\n",
    "# speed_analysis3['variance']=speed_analysis3.speedResults/speed_analysis3.speedTarget\n",
    "# speed_analysis3 = speed_analysis3[['sales_rep','sub_cat','speedTarget','speedResults','variance','country']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "a79dae0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # save file for powerbi presentation\n",
    "# filename8 = 'speed_analysis_powerbi_' + last_month1 + '.csv'\n",
    "# speed_analysis3.to_csv(path_output+filename8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "4bdd4ab3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Request for incentive calculation completed!\n"
     ]
    }
   ],
   "source": [
    "# print complete\n",
    "\n",
    "print('Request for incentive calculation completed!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "885205f9",
   "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
}