VQTR_Monthly.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "# import subprocess\n",
    "# subprocess.check_call(['pip', 'install', 'numpy']) # install pkg\n",
    "# subprocess.check_call(['pip', 'install',\"--upgrade\", 'numpy']) # upgrade pkg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "09-07-2019.csv\n"
     ]
    }
   ],
   "source": [
    "path = 'QTRWeaks of Month/'\n",
    "\n",
    "files = []\n",
    "filesNoAdd = []\n",
    "# r=root, d=directories, f = files\n",
    "for r, d, f in os.walk(path):\n",
    "    for file in f:\n",
    "        if '.csv' in file:\n",
    "            files.append(os.path.join(r, file))\n",
    "            filesNoAdd.append(file)\n",
    "\n",
    "for f in filesNoAdd:\n",
    "    print(f)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "numListOfBranch = ['301 Co','302 Co','306 Co','307 Co']\n",
    "listOfBranch = ['301 Co', '301.Arq COST', '301.Cost Price', '301.V.S.P.', '301.AVG. WEEK',\n",
    "                '302 Co', '302.Arq COST', '302.Cost Price', '302.V.S.P.', '302.AVG. WEEK',\n",
    "                '306 Co', '306.Arq COST', '306.Cost Price', '306.V.S.P.', '306.AVG. WEEK',\n",
    "                '307 Co', '307.Arq COST', '307.Cost Price', '307.V.S.P.', '307.AVG. WEEK',\n",
    "                 'Latest SOH']\n",
    "\n",
    "productDetail = ['Sku', 'UPC', 'Catalogue N', 'Title', 'Label' ,'Arq COST', 'Cost Price' ,'V.S.P.']\n",
    "header = productDetail.copy()\n",
    "header.extend(listOfBranch.copy())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "def findID(sku, Data):\n",
    "    numb = 0\n",
    "    for SKUref in Data['Sku']:\n",
    "        \n",
    "        if str(sku) == str(SKUref):\n",
    "            return numb\n",
    "        numb += 1\n",
    "    return \"This good with Sku of \" + sku + \" is not in data.\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "def buildList(num, ide, productDetail, listOfBranch, rawData, final):\n",
    "    producList = []\n",
    "    qSum = 0\n",
    "    for det in productDetail:\n",
    "        producList.append(rawData[det][ide])\n",
    "    for branch in listOfBranch:\n",
    "        branch = branch.replace(' Co','')\n",
    "        qbranch = str(branch + '.Quantit')\n",
    "        head = str(branch + '.Sales Quantity')\n",
    "        if head in rawData.head(0):\n",
    "            try:\n",
    "                producList.append(float(rawData[head][ide]))\n",
    "                producList.append(np.round(float(rawData[head][ide]) * float(rawData['Arq COST'][ide]),2))\n",
    "                producList.append(np.round(float(rawData[head][ide]) * float(rawData['Cost Price'][ide]),2))\n",
    "                producList.append(np.round(float(rawData[head][ide]) * float(rawData['V.S.P.'][ide]),2))\n",
    "                producList.append(np.round(float(rawData[head][ide]) / float(num),2))\n",
    "            except:\n",
    "                print(\"A number in raw data is string\" , head, ide)\n",
    "        if qbranch in rawData.head(0) and str(branch)!='344'and str(branch)!='390'and final==True:\n",
    "            try:\n",
    "                qSum = qSum + rawData[qbranch][ide]\n",
    "            except:\n",
    "                print(\"A number in raw data is string\", qbranch, ide)\n",
    "    producList.append(qSum)\n",
    "\n",
    "                \n",
    "    return producList"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "def listAddition(Dataide, ide, num, productDetail, listOfBranch, rawData, monthRawData, final):\n",
    "    colid = 8\n",
    "    qSum = 0\n",
    "    buf = 0\n",
    "    for branch in listOfBranch:        \n",
    "        branch = branch.replace(' Co','')\n",
    "        qbranch = str(branch + '.Quantit')        \n",
    "        head = str(branch + '.Sales Quantity')\n",
    "        if head in rawData.head(0):\n",
    "            buf = colid + 5\n",
    "#             print(colid)\n",
    "            monthRawData.iloc[Dataide, colid] = monthRawData.iloc[Dataide, colid] + rawData[head][ide]\n",
    "            monthRawData.iloc[Dataide, colid+1] = np.round(monthRawData.iloc[Dataide, colid] * float(rawData['Arq COST'][ide]),2)\n",
    "            monthRawData.iloc[Dataide, colid+2] = np.round(monthRawData.iloc[Dataide, colid] * float(rawData['Cost Price'][ide]),2)\n",
    "            monthRawData.iloc[Dataide, colid+3] = np.round(monthRawData.iloc[Dataide, colid] * float(rawData['V.S.P.'][ide]),2)\n",
    "            monthRawData.iloc[Dataide, colid+4] = np.round(monthRawData.iloc[Dataide, colid] / float(num),2)\n",
    "#         print(monthRawData)\n",
    "        colid += 1\n",
    "        if qbranch in rawData.head(0) and str(branch)!='344'and str(branch)!='390'and final==True:\n",
    "            try:\n",
    "                qSum = qSum + rawData[qbranch][ide]\n",
    "            except:\n",
    "                print(\"A number in raw data is string\", qbranch, ide)\n",
    "\n",
    "    if final:\n",
    "#         print(qSum)\n",
    "        \n",
    "        monthRawData.iloc[Dataide,buf] = qSum\n",
    "        \n",
    "    return monthRawData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "def noStock(sku,rawData):\n",
    "    sku = str(sku)\n",
    "    try:\n",
    "        i = findID(sku, rawData)\n",
    "        i = int(i)\n",
    "    except:\n",
    "        return True\n",
    "    for branch in numListOfBranch:        \n",
    "        branch = branch.replace(' Co','')\n",
    "        qbranch = str(branch + '.Quantit')\n",
    "        if rawData.loc[i,qbranch] > 0 and str(branch)!='344'and str(branch)!='390':\n",
    "            return False\n",
    "    return True\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ArghoobCost\n",
    "ArghoobCost = pd.read_excel('hand made rteports\\ARQOOB Cost.xlsx', encoding = 'latine1')\n",
    "for f in files:\n",
    "    rawData = pd.read_csv(f , encoding='latin1')\n",
    "    i = 0\n",
    "    for sku in rawData['Sku']:\n",
    "        j = findID(sku, ArghoobCost)\n",
    "        rawData.loc[i,'Arq COST'] = ArghoobCost.loc[j, 'Arq COST']\n",
    "        rawData.loc[i,'UPC'] = ArghoobCost.loc[j, 'UPC Code']\n",
    "        i += 1\n",
    "    rawData.to_csv(f, index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "monthRawData = pd.DataFrame(columns = list(header))\n",
    "\n",
    "fcount = len(files)\n",
    "b = []\n",
    "finalFile =False\n",
    "for f in files:\n",
    "#     f1 = (path + f)\n",
    "    f1 = (f)\n",
    "    rawData = pd.read_csv(f1 , encoding='latin1')\n",
    "\n",
    "    if f == files[-1]:\n",
    "        finalFile = True\n",
    "    sid =0\n",
    "    b = 0\n",
    "    \n",
    "    for sku in rawData['Sku']:\n",
    "        if sku not in list(monthRawData['Sku']):\n",
    "            df = pd.DataFrame([list(buildList(fcount, sid, productDetail,\n",
    "                            listOfBranch, rawData, finalFile))], columns= list(header))\n",
    "            monthRawData = monthRawData.append(df)\n",
    "            \n",
    "        else:\n",
    "#             print(f,sid)\n",
    "            Dataide = findID(sku, monthRawData)\n",
    "            b = listAddition(Dataide, sid, fcount, productDetail, listOfBranch, rawData,\n",
    "                             monthRawData, finalFile)\n",
    "        sid +=1\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "totalGoodSale = []\n",
    "count = 0\n",
    "for sku in monthRawData['Sku']:\n",
    "    saleOfGood = []\n",
    "    for numB in numListOfBranch:\n",
    "        saleOfGood.append(monthRawData.iloc[count][numB])\n",
    "    totalGoodSale.append(np.sum(saleOfGood))\n",
    "    count += 1\n",
    "monthRawData[\"total sale of Goods\"] = totalGoodSale\n",
    "status = []\n",
    "for tot in totalGoodSale:\n",
    "\n",
    "    if tot <= 0:\n",
    "        status.append('Non moving')\n",
    "    if (tot > 0) and (tot <=2):\n",
    "        status.append('Slow moving')\n",
    "    if tot > 2:\n",
    "        status.append('Fast moving')\n",
    "#\n",
    "rawData = pd.read_csv(files[-1])\n",
    "\n",
    "for i in range(len(status)):\n",
    "\n",
    "    try:\n",
    "        if noStock(monthRawData.iloc[i]['Sku'],rawData) and status[i]=='Non moving':\n",
    "            status[i] = 'Run out of stock'\n",
    "    except:\n",
    "        continue\n",
    "#\n",
    "monthRawData[\"Status\"] = status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthRawData.to_csv(\"toalOfMonth.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}