Item Status.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "01-2019.csv\n",
      "02-2019.csv\n",
      "03-2019.csv\n",
      "04-2019.csv\n",
      "05-2019.csv\n"
     ]
    }
   ],
   "source": [
    "path = 'Monthes/'\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": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "# monthresult = ['total sale of Goods', 'Status']\n",
    "monthresult = ['total sale of Goods']\n",
    "productDetail = ['Sku', 'UPC', 'Catalogue N', 'Title', 'Label' ,'Arq COST', 'Cost Price' ,'V.S.P.', 'Latest SOH']\n",
    "header = productDetail.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "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 \" + str(sku) + \" is not in data.\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "def listOfGoods(files):\n",
    "    listOfSku = []\n",
    "    for file in files:\n",
    "        rawData = pd.read_csv(file, encoding='latin1')\n",
    "        for i in range(len(rawData['Sku'])):\n",
    "                if rawData.loc[ i , 'Sku'] in listOfSku:\n",
    "                       continue\n",
    "                else:\n",
    "                       listOfSku.append(rawData.loc[i,'Sku' ])\n",
    "                       \n",
    "    return listOfSku"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "def buildList(file, ide, productDetail, monthresult, rawData, sku,monthRawData, finalFile):\n",
    "\n",
    "    for det in productDetail:\n",
    "        if det =='Sku':\n",
    "            continue\n",
    "        monthRawData.loc[findID(sku, monthRawData),det] = rawData[det][ide]\n",
    "    file = str(file).replace('.csv','')\n",
    "    file = file.replace('Monthes/','')\n",
    "    for head in monthresult:\n",
    "        monthRawData.loc[findID(sku, monthRawData), file + ' ' + head] = rawData[head][ide]\n",
    "    if finalFile:\n",
    "        monthRawData.loc[findID(sku, monthRawData), 'Latest SOH'] = rawData['Latest SOH'][ide]\n",
    "    return monthRawData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 'Existence on Shop'\n",
    "def existence(sku, files):\n",
    "    numExistence = 0\n",
    "    for file in files:\n",
    "        Data = pd.read_csv(file)\n",
    "        try:\n",
    "            ide = findID(sku, Data)\n",
    "#             print(Data.loc[ide, 'Latest SOH'])\n",
    "            if Data.loc[ide, 'Latest SOH'] > 0:\n",
    "                numExistence +=1\n",
    "            else:\n",
    "                numExistence = 0\n",
    "        except:\n",
    "            numExistence = 0\n",
    "\n",
    "    if numExistence > 3:\n",
    "        numExistence = 3\n",
    "    return numExistence"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthRawData = pd.DataFrame(columns = list(header))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "for file in filesNoAdd:\n",
    "    file = str(file).replace('.csv','')\n",
    "    monthRawData[file + ' ' + monthresult[0]] = np.zeros(len(listOfGoods(files)))\n",
    "monthRawData['Sku'] = listOfGoods(files)\n",
    "#     print(monthresult)\n",
    "#     for head in monthresult:\n",
    "        \n",
    "\n",
    "monthRawData['Latest SOH'] = np.zeros(len(monthRawData['Sku']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "# a = np.zeros(shape=(1,len(header)))\n",
    "# monthRawData = pd.DataFrame(columns = list(header))\n",
    "fcount = len(files)\n",
    "b = []\n",
    "finalFile =False\n",
    "for file in files:\n",
    "    rawData = pd.read_csv(file , encoding='latin1')\n",
    "    sid =0\n",
    "    if file ==files[-1]:\n",
    "        finalFile = True\n",
    "    Existence = []\n",
    "#     b = 0\n",
    "    for sku in rawData['Sku']:\n",
    "        monthRawData = buildList(file, sid, productDetail, monthresult,\n",
    "                                 rawData,sku,monthRawData, finalFile)\n",
    "        if finalFile:\n",
    "            e = existence(sku, files)\n",
    "            if e == 3:\n",
    "                Existence.append('Min ' + str(e) + ' months')\n",
    "            elif e <= 1:\n",
    "                Existence.append(str(e) + ' month')\n",
    "            else:\n",
    "                Existence.append(str(e) + ' months')\n",
    "#             try:\n",
    "#                 ide = findID(sku, monthRawData)\n",
    "#                 average.append((np.sum(monthRawData.values[ide,-1:-3]))/3)\n",
    "#             except:\n",
    "#                 average.append(0)\n",
    "        sid +=1\n",
    "monthRawData['Existence on Shop'] =Existence"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "average = []\n",
    "for sku in monthRawData['Sku']:\n",
    "    ide = findID(sku, monthRawData)\n",
    "    average.append(np.round(np.sum(monthRawData.values[ide,-4:-1])/3,decimals=2))\n",
    "#     if average[-1] == 0 and monthRawData.loc[ide, 'Latest SOH'] == 0:\n",
    "#         average[-1] = 'No stock'\n",
    "average1 = average.copy() \n",
    "monthRawData['3 months avrage sale'] = average1        \n",
    "     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "  \n",
    "delList =[]\n",
    "for i in range(len(monthRawData['Sku'])):\n",
    "    if monthRawData.loc[i, 'Latest SOH'] == 0:\n",
    "        delList.append(i)\n",
    "delList = delList[::-1]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "# average[13]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "for i in delList:\n",
    "    del average[i]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "frame = pd.DataFrame(average)\n",
    "x, y, z = frame[0].quantile([0.25, 0.5, 0.75])\n",
    "# print(x,y,z)\n",
    "# print(delList,average)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "Status = strs = [\"\" for x in range(len(monthRawData['Sku']))]\n",
    "\n",
    "\n",
    "for i in range(len(monthRawData['Sku'])):\n",
    "    \n",
    "    if i in delList :\n",
    "        Status[i] = 'Run out of stock'\n",
    "    elif average[0] >= z:\n",
    "        Status[i] = 'Fast moving'\n",
    "        del average[0]\n",
    "    elif average[0] <= x:\n",
    "        Status[i] = 'Non moving'\n",
    "        del average[0]\n",
    "    elif average[0] < z and average[0] > x:\n",
    "        Status[i] = 'Slow moving'\n",
    "        del average[0]\n",
    "\n",
    "        \n",
    "monthRawData['Stock Status'] = Status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "profit = []\n",
    "for i in range(len(monthRawData['Sku'])):\n",
    "    profit.append(average1[i]*(monthRawData.loc[i,'Cost Price'] - monthRawData.loc[i, 'Arq COST']))\n",
    "profit1 = profit.copy()\n",
    "monthRawData['profit'] = profit1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(len(profit))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "48.4475 161.48 362.14\n"
     ]
    }
   ],
   "source": [
    "\n",
    "delList =[]\n",
    "for i in range(len(monthRawData['Sku'])):\n",
    "    if profit[i] == 0:\n",
    "        delList.append(i)\n",
    "delList = delList[::-1]\n",
    "\n",
    "for i in delList:\n",
    "    del profit[i]\n",
    "frame = pd.DataFrame(profit)\n",
    "x, y, z = frame[0].quantile([0.25, 0.5, 0.75])\n",
    "print(x,y,z)\n",
    "# print(delList,profit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "profitStatus = strs = [\"\" for x in range(len(monthRawData['Sku']))]\n",
    "\n",
    "\n",
    "for i in range(len(monthRawData['Sku'])):\n",
    "    if i in delList:\n",
    "        profitStatus[i] = 'Run out of stock'\n",
    "    elif profit[0] >= z:\n",
    "        profitStatus[i] = 'Most profitable'\n",
    "        del profit[0]\n",
    "    elif profit[0] <= x:\n",
    "        profitStatus[i] = 'Less profitable'\n",
    "        del profit[0]\n",
    "    else:\n",
    "        profitStatus[i] = 'Profitable'\n",
    "        del profit[0]\n",
    "    \n",
    "monthRawData['Profit status'] = profitStatus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "finalStatus = []\n",
    "for i in range(len(monthRawData['Sku'])):\n",
    "    finalStatus.append(str(Status[i]) + ' - ' + str(profitStatus[i]))\n",
    "monthRawData['Final Status'] = finalStatus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "nonMovingAction = []\n",
    "for i in range(len(monthRawData['Sku'])):\n",
    "    if Existence[i] == 'Min 3 months' and Status[i] == 'Non moving':\n",
    "        nonMovingAction.append('Buying Price')\n",
    "    elif Existence[i] == '2 months' and Status[i] == 'Non moving':\n",
    "        nonMovingAction.append('Discount')\n",
    "    elif Existence[i] == '1 month' and Status[i] == 'Non moving':\n",
    "        nonMovingAction.append('Transfer')\n",
    "    else:\n",
    "        nonMovingAction.append('-')\n",
    "monthRawData['Non Moving Action'] = nonMovingAction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "#=IF(O5=\"Run out of stock - Most Profitable\",\"No Stock - Reorder\",\n",
    "# IF(O5=\"Fast Moving - Most Profitable\",\"Profitable - Reorder\",\n",
    "# IF(O5=\"Run out of stock - Less Profitable\",\"Stop Ordering\", \" - \")))\n",
    "Reordering = []\n",
    "for i in range(len(monthRawData['Sku'])):\n",
    "    if finalStatus[i] == 'Run out of stock - Most profitable':\n",
    "        Reordering.append('Non Stock - Reorder')\n",
    "    elif finalStatus[i] == 'Fast moving - Most profitable':\n",
    "        Reordering.append('Profitable - Reorder')\n",
    "    elif finalStatus[i] == 'Run out of stock - Less profitable':\n",
    "        Reordering.append('Stop Ordering')\n",
    "    else:\n",
    "        Reordering.append('-')\n",
    "monthRawData['Reordering'] = Reordering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthRawData.to_csv('allMonthes.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#     df = pd.read_csv('allMonthes.csv')\n",
    "#     newdf = df[df['Stock Status'] == 'Fast moving']\n",
    "#     newdf = newdf.loc[:,['Sku', 'UPC', 'Catalogue N', 'Title', 'Label', 'Arq COST', \"Cost Price\", 'V.S.P.']].reindex()\n",
    "#     newdf.to_html('fast moving.html')\n",
    "#     .to_html('fast moing.html')\n",
    "#     ['Sku', 'UPC', 'Catalogue N', 'Title', 'Label', 'Arq COST', \"Cost Price\", 'V.S.P.']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# date_index = pd.date_range('1/1/2010', periods=6, freq='D')\n",
    "# df2 = pd.DataFrame({\"prices\": [100, 101, np.nan, 100, 89, 88]},\n",
    "#                     index=date_index)"
   ]
  }
 ],
 "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
}