Build  Excel File.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "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": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "09-07-2019.csv\n"
     ]
    }
   ],
   "source": [
    "path = 'Weaks 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": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "numListOfBranch = ['401 Co','402 Co','404 Co','405 Co','412 Co','416 Co',\n",
    "                   '417 Co','423 Co', '424 Co','425 Co','426 Co','429 Co','444 Co','490 Co']\n",
    "listOfBranch = ['401 Co', '401.Arq COST', '401.Cost Price', '401.V.S.P.', '401.AVG. WEEK',\n",
    "                '402 Co', '402.Arq COST', '402.Cost Price', '402.V.S.P.', '402.AVG. WEEK',\n",
    "                '404 Co', '404.Arq COST', '404.Cost Price', '404.V.S.P.', '404.AVG. WEEK',\n",
    "                '405 Co', '405.Arq COST', '405.Cost Price', '405.V.S.P.', '405.AVG. WEEK',\n",
    "                '412 Co', '412.Arq COST', '412.Cost Price', '412.V.S.P.', '412.AVG. WEEK',\n",
    "                '416 Co', '416.Arq COST', '416.Cost Price', '416.V.S.P.', '416.AVG. WEEK',\n",
    "                '417 Co', '417.Arq COST', '417.Cost Price', '417.V.S.P.', '417.AVG. WEEK',\n",
    "                '423 Co', '423.Arq COST', '423.Cost Price', '423.V.S.P.', '423.AVG. WEEK',\n",
    "                '424 Co', '424.Arq COST', '424.Cost Price', '424.V.S.P.', '424.AVG. WEEK',\n",
    "                '425 Co', '425.Arq COST', '425.Cost Price', '425.V.S.P.', '425.AVG. WEEK',\n",
    "                '426 Co', '426.Arq COST', '426.Cost Price', '426.V.S.P.', '426.AVG. WEEK',\n",
    "                '429 Co', '429.Arq COST', '429.Cost Price', '429.V.S.P.', '429.AVG. WEEK',\n",
    "                '444 Co', '444.Arq COST', '444.Cost Price', '444.V.S.P.', '444.AVG. WEEK',\n",
    "                '490 Co', '490.Arq COST', '490.Cost Price', '490.V.S.P.', '490.AVG. WEEK', '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": 4,
   "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": 5,
   "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(float(rawData[head][ide]) * float(rawData['Arq COST'][ide]))\n",
    "                producList.append(float(rawData[head][ide]) * float(rawData['Cost Price'][ide]))\n",
    "                producList.append(float(rawData[head][ide]) * float(rawData['V.S.P.'][ide]))\n",
    "                producList.append(float(rawData[head][ide]) / float(num))\n",
    "            except:\n",
    "                print(\"A number in raw data is string\" , head, ide)\n",
    "        if qbranch in rawData.head(0) and str(branch)!='444'and str(branch)!='490'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": 6,
   "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] = monthRawData.iloc[Dataide, colid] * float(rawData['Arq COST'][ide])\n",
    "            monthRawData.iloc[Dataide, colid+2] = monthRawData.iloc[Dataide, colid] * float(rawData['Cost Price'][ide])\n",
    "            monthRawData.iloc[Dataide, colid+3] = monthRawData.iloc[Dataide, colid] * float(rawData['V.S.P.'][ide])\n",
    "            monthRawData.iloc[Dataide, colid+4]= monthRawData.iloc[Dataide, colid] / float(num)\n",
    "#         print(monthRawData)\n",
    "        colid += 1\n",
    "        if qbranch in rawData.head(0) and str(branch)!='444'and str(branch)!='490'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": 7,
   "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)!='444'and str(branch)!='490':\n",
    "            return False\n",
    "    return True\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "ArghoobCost = pd.read_excel('hand made rteports\\ARQOOB Cost.xlsx',4)\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",
    "# ArghoobCost"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# a = np.zeros(shape=(1,len(header)))\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": 10,
   "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",
    "#     if tot == 0:\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",
    "\n",
    "rawData = pd.read_csv(files[-1])\n",
    "\n",
    "for i in range(len(status)):\n",
    "#         print(i)\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\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "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
}