Vlookup on Excel.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "dd457fff",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "eaf1248d",
   "metadata": {
    "scrolled": true
   },
   "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>SUPPLIER</th>\n",
       "      <th>BP Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AFRICA (Sky) HOLDINGS LTD</td>\n",
       "      <td>C0000002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AIRTEL NETWORKS KENYA LIMITED</td>\n",
       "      <td>C0000003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ALKEN KENYA LIMITED</td>\n",
       "      <td>C0000004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ALPINE COOLERS LTD</td>\n",
       "      <td>C0000005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SUPER XPRESS PERTOLEUM LTD</td>\n",
       "      <td>C0000006</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        SUPPLIER   BP Code\n",
       "0      AFRICA (Sky) HOLDINGS LTD  C0000002\n",
       "1  AIRTEL NETWORKS KENYA LIMITED  C0000003\n",
       "2            ALKEN KENYA LIMITED  C0000004\n",
       "3             ALPINE COOLERS LTD  C0000005\n",
       "4     SUPER XPRESS PERTOLEUM LTD  C0000006"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file_name = 'SPECIAL ORDER TEMPLATE.xlsx'\n",
    "\n",
    "order = pd.read_excel(file_name, sheet_name = 'order')\n",
    "details = pd.read_excel(file_name, sheet_name = 'details')\n",
    "supp_code = pd.read_excel(file_name, sheet_name = 'supp_code')\n",
    "whse = pd.read_excel(file_name, sheet_name = 'whse')\n",
    "\n",
    "supp_code.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "db8f37d5",
   "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>CODE</th>\n",
       "      <th>QUANTITY</th>\n",
       "      <th>STORE NAME</th>\n",
       "      <th>STORE CODE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1102496</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1137224</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1120878</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1133647</td>\n",
       "      <td>4</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1103342</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      CODE  QUANTITY             STORE NAME  STORE CODE\n",
       "0  1102496         2  Goodlife Centro House          92\n",
       "1  1137224         2  Goodlife Centro House          92\n",
       "2  1120878         2  Goodlife Centro House          92\n",
       "3  1133647         4  Goodlife Centro House          92\n",
       "4  1103342         2  Goodlife Centro House          92"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order = pd.merge(order, whse, on = 'STORE NAME')\n",
    "order.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "98b9d9e0",
   "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>CODE</th>\n",
       "      <th>QUANTITY</th>\n",
       "      <th>STORE NAME</th>\n",
       "      <th>STORE CODE</th>\n",
       "      <th>DESC</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>SUPPLIER</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1102496</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>CORSODYL MOUTHWASH MINT 300ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1137224</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>JAMIESON PROBIOTIC 10 BILLION 60'S</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GENERICS AFRICA LTD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1120878</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>BIO BALANCE SUNBLOCK 50+ CREAM 75ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>LINTONS PLACE LIMITED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1133647</td>\n",
       "      <td>4</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>MOZZISHIELD NO DEET INSECT REPELLENT 100ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>OMAERA PHARMACEUTICALS LIMITED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1103342</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>E45 MOISTURISING LOTION PUMP 500ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      CODE  QUANTITY             STORE NAME  STORE CODE  \\\n",
       "0  1102496         2  Goodlife Centro House          92   \n",
       "1  1137224         2  Goodlife Centro House          92   \n",
       "2  1120878         2  Goodlife Centro House          92   \n",
       "3  1133647         4  Goodlife Centro House          92   \n",
       "4  1103342         2  Goodlife Centro House          92   \n",
       "\n",
       "                                         DESC  STATUS  \\\n",
       "0               CORSODYL MOUTHWASH MINT 300ML  ACTIVE   \n",
       "1          JAMIESON PROBIOTIC 10 BILLION 60'S  ACTIVE   \n",
       "2         BIO BALANCE SUNBLOCK 50+ CREAM 75ML  ACTIVE   \n",
       "3  MOZZISHIELD NO DEET INSECT REPELLENT 100ML  ACTIVE   \n",
       "4          E45 MOISTURISING LOTION PUMP 500ML  ACTIVE   \n",
       "\n",
       "                         SUPPLIER  \n",
       "0       METRO PHARMACEUTICALS LTD  \n",
       "1             GENERICS AFRICA LTD  \n",
       "2           LINTONS PLACE LIMITED  \n",
       "3  OMAERA PHARMACEUTICALS LIMITED  \n",
       "4       METRO PHARMACEUTICALS LTD  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order = pd.merge(order, details, on = 'CODE')\n",
    "order.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2172336e",
   "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>CODE</th>\n",
       "      <th>QUANTITY</th>\n",
       "      <th>STORE NAME</th>\n",
       "      <th>STORE CODE</th>\n",
       "      <th>DESC</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>SUPPLIER</th>\n",
       "      <th>BP Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1102496</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>CORSODYL MOUTHWASH MINT 300ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1103342</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>E45 MOISTURISING LOTION PUMP 500ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1103324</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>E45 CREAM POT 350G</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1109564</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>SEBAMED CLEAR FACE CLEANSING BAR</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1137224</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>JAMIESON PROBIOTIC 10 BILLION 60'S</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GENERICS AFRICA LTD</td>\n",
       "      <td>C0000370</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      CODE  QUANTITY             STORE NAME  STORE CODE  \\\n",
       "0  1102496         2  Goodlife Centro House          92   \n",
       "1  1103342         2  Goodlife Centro House          92   \n",
       "2  1103324         2  Goodlife Centro House          92   \n",
       "3  1109564         2  Goodlife Centro House          92   \n",
       "4  1137224         2  Goodlife Centro House          92   \n",
       "\n",
       "                                 DESC  STATUS                   SUPPLIER  \\\n",
       "0       CORSODYL MOUTHWASH MINT 300ML  ACTIVE  METRO PHARMACEUTICALS LTD   \n",
       "1  E45 MOISTURISING LOTION PUMP 500ML  ACTIVE  METRO PHARMACEUTICALS LTD   \n",
       "2                  E45 CREAM POT 350G  ACTIVE  METRO PHARMACEUTICALS LTD   \n",
       "3    SEBAMED CLEAR FACE CLEANSING BAR  ACTIVE  METRO PHARMACEUTICALS LTD   \n",
       "4  JAMIESON PROBIOTIC 10 BILLION 60'S  ACTIVE        GENERICS AFRICA LTD   \n",
       "\n",
       "    BP Code  \n",
       "0  C0000136  \n",
       "1  C0000136  \n",
       "2  C0000136  \n",
       "3  C0000136  \n",
       "4  C0000370  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order = pd.merge(order, supp_code, on = 'SUPPLIER')\n",
    "order.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "57f39a5c",
   "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>CODE</th>\n",
       "      <th>QUANTITY</th>\n",
       "      <th>STORE NAME</th>\n",
       "      <th>STORE CODE</th>\n",
       "      <th>DESC</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>SUPPLIER</th>\n",
       "      <th>BP Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1120878</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>BIO BALANCE SUNBLOCK 50+ CREAM 75ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>LINTONS PLACE LIMITED</td>\n",
       "      <td>C0000121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1102496</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>CORSODYL MOUTHWASH MINT 300ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1103342</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>E45 MOISTURISING LOTION PUMP 500ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1103324</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>E45 CREAM POT 350G</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1109564</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>SEBAMED CLEAR FACE CLEANSING BAR</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1109772</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>SIMPLE MOIST. FOAMING FACIAL WASH 150ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>SLEEK KENYA</td>\n",
       "      <td>C0000205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1127117</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>N/AID ROSE WATER 150ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>X-CENTRIC  LIMITED</td>\n",
       "      <td>C0000265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1100927</td>\n",
       "      <td>4</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>BALLET MOSQUITO REPELLANT P/JELLY 100GM</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GOODLIFE  PHARMACY-WAREHOUSE</td>\n",
       "      <td>C0000349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1136884</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>TYLENOL EXTRA STRENGTH CAPLET 24S</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GOODLIFE  PHARMACY-WAREHOUSE</td>\n",
       "      <td>C0000349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1110329</td>\n",
       "      <td>6</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>TAMPAX TAMPONS  20S SUPER PLUS</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GOODLIFE  PHARMACY-WAREHOUSE</td>\n",
       "      <td>C0000349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1137224</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>JAMIESON PROBIOTIC 10 BILLION 60'S</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GENERICS AFRICA LTD</td>\n",
       "      <td>C0000370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1137226</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>JAMIESON PROBIOTIC KIDS CHEW 60'S</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GENERICS AFRICA LTD</td>\n",
       "      <td>C0000370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1133647</td>\n",
       "      <td>4</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>MOZZISHIELD NO DEET INSECT REPELLENT 100ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>OMAERA PHARMACEUTICALS LIMITED</td>\n",
       "      <td>C0000434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1133664</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>GARNIER EVEN &amp; MATTE SCRUB 100ML  100ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>LOREAL (CPD) EAST AFRICA LIMITED</td>\n",
       "      <td>C0000481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1128550</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>GARNIER EVEN &amp; MATTE GENTLE CLARIFYING FOAM 10...</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>LOREAL (CPD) EAST AFRICA LIMITED</td>\n",
       "      <td>C0000481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1131331</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>HC-DINE GARGLE MOUTH WASH ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>HEALTH CLASSIQUE LTD</td>\n",
       "      <td>C0000657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1133292</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>UNCOVER PIMPLE PATCHES 18PCS</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>UNCOVER SKINCARE LTD</td>\n",
       "      <td>C0000712</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       CODE  QUANTITY             STORE NAME  STORE CODE  \\\n",
       "6   1120878         2  Goodlife Centro House          92   \n",
       "0   1102496         2  Goodlife Centro House          92   \n",
       "1   1103342         2  Goodlife Centro House          92   \n",
       "2   1103324         2  Goodlife Centro House          92   \n",
       "3   1109564         2  Goodlife Centro House          92   \n",
       "13  1109772         2  Goodlife Centro House          92   \n",
       "16  1127117         2  Goodlife Centro House          92   \n",
       "9   1100927         4  Goodlife Centro House          92   \n",
       "10  1136884         2  Goodlife Centro House          92   \n",
       "11  1110329         6  Goodlife Centro House          92   \n",
       "4   1137224         2  Goodlife Centro House          92   \n",
       "5   1137226         2  Goodlife Centro House          92   \n",
       "7   1133647         4  Goodlife Centro House          92   \n",
       "15  1133664         2  Goodlife Centro House          92   \n",
       "14  1128550         2  Goodlife Centro House          92   \n",
       "8   1131331         2  Goodlife Centro House          92   \n",
       "12  1133292         2  Goodlife Centro House          92   \n",
       "\n",
       "                                                 DESC  STATUS  \\\n",
       "6                 BIO BALANCE SUNBLOCK 50+ CREAM 75ML  ACTIVE   \n",
       "0                       CORSODYL MOUTHWASH MINT 300ML  ACTIVE   \n",
       "1                  E45 MOISTURISING LOTION PUMP 500ML  ACTIVE   \n",
       "2                                  E45 CREAM POT 350G  ACTIVE   \n",
       "3                    SEBAMED CLEAR FACE CLEANSING BAR  ACTIVE   \n",
       "13            SIMPLE MOIST. FOAMING FACIAL WASH 150ML  ACTIVE   \n",
       "16                             N/AID ROSE WATER 150ML  ACTIVE   \n",
       "9             BALLET MOSQUITO REPELLANT P/JELLY 100GM  ACTIVE   \n",
       "10                  TYLENOL EXTRA STRENGTH CAPLET 24S  ACTIVE   \n",
       "11                     TAMPAX TAMPONS  20S SUPER PLUS  ACTIVE   \n",
       "4                  JAMIESON PROBIOTIC 10 BILLION 60'S  ACTIVE   \n",
       "5                   JAMIESON PROBIOTIC KIDS CHEW 60'S  ACTIVE   \n",
       "7          MOZZISHIELD NO DEET INSECT REPELLENT 100ML  ACTIVE   \n",
       "15            GARNIER EVEN & MATTE SCRUB 100ML  100ML  ACTIVE   \n",
       "14  GARNIER EVEN & MATTE GENTLE CLARIFYING FOAM 10...  ACTIVE   \n",
       "8                        HC-DINE GARGLE MOUTH WASH ML  ACTIVE   \n",
       "12                       UNCOVER PIMPLE PATCHES 18PCS  ACTIVE   \n",
       "\n",
       "                            SUPPLIER   BP Code  \n",
       "6              LINTONS PLACE LIMITED  C0000121  \n",
       "0          METRO PHARMACEUTICALS LTD  C0000136  \n",
       "1          METRO PHARMACEUTICALS LTD  C0000136  \n",
       "2          METRO PHARMACEUTICALS LTD  C0000136  \n",
       "3          METRO PHARMACEUTICALS LTD  C0000136  \n",
       "13                       SLEEK KENYA  C0000205  \n",
       "16                X-CENTRIC  LIMITED  C0000265  \n",
       "9       GOODLIFE  PHARMACY-WAREHOUSE  C0000349  \n",
       "10      GOODLIFE  PHARMACY-WAREHOUSE  C0000349  \n",
       "11      GOODLIFE  PHARMACY-WAREHOUSE  C0000349  \n",
       "4                GENERICS AFRICA LTD  C0000370  \n",
       "5                GENERICS AFRICA LTD  C0000370  \n",
       "7     OMAERA PHARMACEUTICALS LIMITED  C0000434  \n",
       "15  LOREAL (CPD) EAST AFRICA LIMITED  C0000481  \n",
       "14  LOREAL (CPD) EAST AFRICA LIMITED  C0000481  \n",
       "8               HEALTH CLASSIQUE LTD  C0000657  \n",
       "12              UNCOVER SKINCARE LTD  C0000712  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order.sort_values(by='BP Code', ascending=True, inplace=True)\n",
    "order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "df6b4ee0",
   "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>CODE</th>\n",
       "      <th>QUANTITY</th>\n",
       "      <th>STORE NAME</th>\n",
       "      <th>STORE CODE</th>\n",
       "      <th>DESC</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>SUPPLIER</th>\n",
       "      <th>BP Code</th>\n",
       "      <th>DocNum</th>\n",
       "      <th>LineNum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1120878</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>BIO BALANCE SUNBLOCK 50+ CREAM 75ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>LINTONS PLACE LIMITED</td>\n",
       "      <td>C0000121</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1102496</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>CORSODYL MOUTHWASH MINT 300ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1103342</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>E45 MOISTURISING LOTION PUMP 500ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1103324</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>E45 CREAM POT 350G</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1109564</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>SEBAMED CLEAR FACE CLEANSING BAR</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1109772</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>SIMPLE MOIST. FOAMING FACIAL WASH 150ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>SLEEK KENYA</td>\n",
       "      <td>C0000205</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1127117</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>N/AID ROSE WATER 150ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>X-CENTRIC  LIMITED</td>\n",
       "      <td>C0000265</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1100927</td>\n",
       "      <td>4</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>BALLET MOSQUITO REPELLANT P/JELLY 100GM</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GOODLIFE  PHARMACY-WAREHOUSE</td>\n",
       "      <td>C0000349</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1136884</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>TYLENOL EXTRA STRENGTH CAPLET 24S</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GOODLIFE  PHARMACY-WAREHOUSE</td>\n",
       "      <td>C0000349</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1110329</td>\n",
       "      <td>6</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>TAMPAX TAMPONS  20S SUPER PLUS</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GOODLIFE  PHARMACY-WAREHOUSE</td>\n",
       "      <td>C0000349</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1137224</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>JAMIESON PROBIOTIC 10 BILLION 60'S</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GENERICS AFRICA LTD</td>\n",
       "      <td>C0000370</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1137226</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>JAMIESON PROBIOTIC KIDS CHEW 60'S</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GENERICS AFRICA LTD</td>\n",
       "      <td>C0000370</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1133647</td>\n",
       "      <td>4</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>MOZZISHIELD NO DEET INSECT REPELLENT 100ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>OMAERA PHARMACEUTICALS LIMITED</td>\n",
       "      <td>C0000434</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1133664</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>GARNIER EVEN &amp; MATTE SCRUB 100ML  100ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>LOREAL (CPD) EAST AFRICA LIMITED</td>\n",
       "      <td>C0000481</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1128550</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>GARNIER EVEN &amp; MATTE GENTLE CLARIFYING FOAM 10...</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>LOREAL (CPD) EAST AFRICA LIMITED</td>\n",
       "      <td>C0000481</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1131331</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>HC-DINE GARGLE MOUTH WASH ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>HEALTH CLASSIQUE LTD</td>\n",
       "      <td>C0000657</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1133292</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>UNCOVER PIMPLE PATCHES 18PCS</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>UNCOVER SKINCARE LTD</td>\n",
       "      <td>C0000712</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       CODE  QUANTITY             STORE NAME  STORE CODE  \\\n",
       "6   1120878         2  Goodlife Centro House          92   \n",
       "0   1102496         2  Goodlife Centro House          92   \n",
       "1   1103342         2  Goodlife Centro House          92   \n",
       "2   1103324         2  Goodlife Centro House          92   \n",
       "3   1109564         2  Goodlife Centro House          92   \n",
       "13  1109772         2  Goodlife Centro House          92   \n",
       "16  1127117         2  Goodlife Centro House          92   \n",
       "9   1100927         4  Goodlife Centro House          92   \n",
       "10  1136884         2  Goodlife Centro House          92   \n",
       "11  1110329         6  Goodlife Centro House          92   \n",
       "4   1137224         2  Goodlife Centro House          92   \n",
       "5   1137226         2  Goodlife Centro House          92   \n",
       "7   1133647         4  Goodlife Centro House          92   \n",
       "15  1133664         2  Goodlife Centro House          92   \n",
       "14  1128550         2  Goodlife Centro House          92   \n",
       "8   1131331         2  Goodlife Centro House          92   \n",
       "12  1133292         2  Goodlife Centro House          92   \n",
       "\n",
       "                                                 DESC  STATUS  \\\n",
       "6                 BIO BALANCE SUNBLOCK 50+ CREAM 75ML  ACTIVE   \n",
       "0                       CORSODYL MOUTHWASH MINT 300ML  ACTIVE   \n",
       "1                  E45 MOISTURISING LOTION PUMP 500ML  ACTIVE   \n",
       "2                                  E45 CREAM POT 350G  ACTIVE   \n",
       "3                    SEBAMED CLEAR FACE CLEANSING BAR  ACTIVE   \n",
       "13            SIMPLE MOIST. FOAMING FACIAL WASH 150ML  ACTIVE   \n",
       "16                             N/AID ROSE WATER 150ML  ACTIVE   \n",
       "9             BALLET MOSQUITO REPELLANT P/JELLY 100GM  ACTIVE   \n",
       "10                  TYLENOL EXTRA STRENGTH CAPLET 24S  ACTIVE   \n",
       "11                     TAMPAX TAMPONS  20S SUPER PLUS  ACTIVE   \n",
       "4                  JAMIESON PROBIOTIC 10 BILLION 60'S  ACTIVE   \n",
       "5                   JAMIESON PROBIOTIC KIDS CHEW 60'S  ACTIVE   \n",
       "7          MOZZISHIELD NO DEET INSECT REPELLENT 100ML  ACTIVE   \n",
       "15            GARNIER EVEN & MATTE SCRUB 100ML  100ML  ACTIVE   \n",
       "14  GARNIER EVEN & MATTE GENTLE CLARIFYING FOAM 10...  ACTIVE   \n",
       "8                        HC-DINE GARGLE MOUTH WASH ML  ACTIVE   \n",
       "12                       UNCOVER PIMPLE PATCHES 18PCS  ACTIVE   \n",
       "\n",
       "                            SUPPLIER   BP Code  DocNum  LineNum  \n",
       "6              LINTONS PLACE LIMITED  C0000121       1        0  \n",
       "0          METRO PHARMACEUTICALS LTD  C0000136       2        0  \n",
       "1          METRO PHARMACEUTICALS LTD  C0000136       2        1  \n",
       "2          METRO PHARMACEUTICALS LTD  C0000136       2        2  \n",
       "3          METRO PHARMACEUTICALS LTD  C0000136       2        3  \n",
       "13                       SLEEK KENYA  C0000205       3        0  \n",
       "16                X-CENTRIC  LIMITED  C0000265       4        0  \n",
       "9       GOODLIFE  PHARMACY-WAREHOUSE  C0000349       5        0  \n",
       "10      GOODLIFE  PHARMACY-WAREHOUSE  C0000349       5        1  \n",
       "11      GOODLIFE  PHARMACY-WAREHOUSE  C0000349       5        2  \n",
       "4                GENERICS AFRICA LTD  C0000370       6        0  \n",
       "5                GENERICS AFRICA LTD  C0000370       6        1  \n",
       "7     OMAERA PHARMACEUTICALS LIMITED  C0000434       7        0  \n",
       "15  LOREAL (CPD) EAST AFRICA LIMITED  C0000481       8        0  \n",
       "14  LOREAL (CPD) EAST AFRICA LIMITED  C0000481       8        1  \n",
       "8               HEALTH CLASSIQUE LTD  C0000657       9        0  \n",
       "12              UNCOVER SKINCARE LTD  C0000712      10        0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order['DocNum'] = order.groupby('BP Code').ngroup() + 1\n",
    "order['LineNum'] = order.groupby('BP Code').cumcount()\n",
    "order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "e5cc7df1",
   "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>CODE</th>\n",
       "      <th>QUANTITY</th>\n",
       "      <th>STORE NAME</th>\n",
       "      <th>STORE CODE</th>\n",
       "      <th>DESC</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>SUPPLIER</th>\n",
       "      <th>BP Code</th>\n",
       "      <th>DocNum</th>\n",
       "      <th>LineNum</th>\n",
       "      <th>DocType</th>\n",
       "      <th>Comments</th>\n",
       "      <th>DocDate</th>\n",
       "      <th>DocDate2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1120878</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>BIO BALANCE SUNBLOCK 50+ CREAM 75ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>LINTONS PLACE LIMITED</td>\n",
       "      <td>C0000121</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1102496</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>CORSODYL MOUTHWASH MINT 300ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1103342</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>E45 MOISTURISING LOTION PUMP 500ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1103324</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>E45 CREAM POT 350G</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1109564</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>SEBAMED CLEAR FACE CLEANSING BAR</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>METRO PHARMACEUTICALS LTD</td>\n",
       "      <td>C0000136</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1109772</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>SIMPLE MOIST. FOAMING FACIAL WASH 150ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>SLEEK KENYA</td>\n",
       "      <td>C0000205</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1127117</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>N/AID ROSE WATER 150ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>X-CENTRIC  LIMITED</td>\n",
       "      <td>C0000265</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1100927</td>\n",
       "      <td>4</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>BALLET MOSQUITO REPELLANT P/JELLY 100GM</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GOODLIFE  PHARMACY-WAREHOUSE</td>\n",
       "      <td>C0000349</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1136884</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>TYLENOL EXTRA STRENGTH CAPLET 24S</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GOODLIFE  PHARMACY-WAREHOUSE</td>\n",
       "      <td>C0000349</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1110329</td>\n",
       "      <td>6</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>TAMPAX TAMPONS  20S SUPER PLUS</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GOODLIFE  PHARMACY-WAREHOUSE</td>\n",
       "      <td>C0000349</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1137224</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>JAMIESON PROBIOTIC 10 BILLION 60'S</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GENERICS AFRICA LTD</td>\n",
       "      <td>C0000370</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1137226</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>JAMIESON PROBIOTIC KIDS CHEW 60'S</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>GENERICS AFRICA LTD</td>\n",
       "      <td>C0000370</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1133647</td>\n",
       "      <td>4</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>MOZZISHIELD NO DEET INSECT REPELLENT 100ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>OMAERA PHARMACEUTICALS LIMITED</td>\n",
       "      <td>C0000434</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1133664</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>GARNIER EVEN &amp; MATTE SCRUB 100ML  100ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>LOREAL (CPD) EAST AFRICA LIMITED</td>\n",
       "      <td>C0000481</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1128550</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>GARNIER EVEN &amp; MATTE GENTLE CLARIFYING FOAM 10...</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>LOREAL (CPD) EAST AFRICA LIMITED</td>\n",
       "      <td>C0000481</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1131331</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>HC-DINE GARGLE MOUTH WASH ML</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>HEALTH CLASSIQUE LTD</td>\n",
       "      <td>C0000657</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1133292</td>\n",
       "      <td>2</td>\n",
       "      <td>Goodlife Centro House</td>\n",
       "      <td>92</td>\n",
       "      <td>UNCOVER PIMPLE PATCHES 18PCS</td>\n",
       "      <td>ACTIVE</td>\n",
       "      <td>UNCOVER SKINCARE LTD</td>\n",
       "      <td>C0000712</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>SPECIAL ORDER</td>\n",
       "      <td>20240313</td>\n",
       "      <td>20240313</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       CODE  QUANTITY             STORE NAME  STORE CODE  \\\n",
       "0   1120878         2  Goodlife Centro House          92   \n",
       "1   1102496         2  Goodlife Centro House          92   \n",
       "2   1103342         2  Goodlife Centro House          92   \n",
       "3   1103324         2  Goodlife Centro House          92   \n",
       "4   1109564         2  Goodlife Centro House          92   \n",
       "5   1109772         2  Goodlife Centro House          92   \n",
       "6   1127117         2  Goodlife Centro House          92   \n",
       "7   1100927         4  Goodlife Centro House          92   \n",
       "8   1136884         2  Goodlife Centro House          92   \n",
       "9   1110329         6  Goodlife Centro House          92   \n",
       "10  1137224         2  Goodlife Centro House          92   \n",
       "11  1137226         2  Goodlife Centro House          92   \n",
       "12  1133647         4  Goodlife Centro House          92   \n",
       "13  1133664         2  Goodlife Centro House          92   \n",
       "14  1128550         2  Goodlife Centro House          92   \n",
       "15  1131331         2  Goodlife Centro House          92   \n",
       "16  1133292         2  Goodlife Centro House          92   \n",
       "\n",
       "                                                 DESC  STATUS  \\\n",
       "0                 BIO BALANCE SUNBLOCK 50+ CREAM 75ML  ACTIVE   \n",
       "1                       CORSODYL MOUTHWASH MINT 300ML  ACTIVE   \n",
       "2                  E45 MOISTURISING LOTION PUMP 500ML  ACTIVE   \n",
       "3                                  E45 CREAM POT 350G  ACTIVE   \n",
       "4                    SEBAMED CLEAR FACE CLEANSING BAR  ACTIVE   \n",
       "5             SIMPLE MOIST. FOAMING FACIAL WASH 150ML  ACTIVE   \n",
       "6                              N/AID ROSE WATER 150ML  ACTIVE   \n",
       "7             BALLET MOSQUITO REPELLANT P/JELLY 100GM  ACTIVE   \n",
       "8                   TYLENOL EXTRA STRENGTH CAPLET 24S  ACTIVE   \n",
       "9                      TAMPAX TAMPONS  20S SUPER PLUS  ACTIVE   \n",
       "10                 JAMIESON PROBIOTIC 10 BILLION 60'S  ACTIVE   \n",
       "11                  JAMIESON PROBIOTIC KIDS CHEW 60'S  ACTIVE   \n",
       "12         MOZZISHIELD NO DEET INSECT REPELLENT 100ML  ACTIVE   \n",
       "13            GARNIER EVEN & MATTE SCRUB 100ML  100ML  ACTIVE   \n",
       "14  GARNIER EVEN & MATTE GENTLE CLARIFYING FOAM 10...  ACTIVE   \n",
       "15                       HC-DINE GARGLE MOUTH WASH ML  ACTIVE   \n",
       "16                       UNCOVER PIMPLE PATCHES 18PCS  ACTIVE   \n",
       "\n",
       "                            SUPPLIER   BP Code  DocNum  LineNum  \\\n",
       "0              LINTONS PLACE LIMITED  C0000121       1        0   \n",
       "1          METRO PHARMACEUTICALS LTD  C0000136       2        0   \n",
       "2          METRO PHARMACEUTICALS LTD  C0000136       2        1   \n",
       "3          METRO PHARMACEUTICALS LTD  C0000136       2        2   \n",
       "4          METRO PHARMACEUTICALS LTD  C0000136       2        3   \n",
       "5                        SLEEK KENYA  C0000205       3        0   \n",
       "6                 X-CENTRIC  LIMITED  C0000265       4        0   \n",
       "7       GOODLIFE  PHARMACY-WAREHOUSE  C0000349       5        0   \n",
       "8       GOODLIFE  PHARMACY-WAREHOUSE  C0000349       5        1   \n",
       "9       GOODLIFE  PHARMACY-WAREHOUSE  C0000349       5        2   \n",
       "10               GENERICS AFRICA LTD  C0000370       6        0   \n",
       "11               GENERICS AFRICA LTD  C0000370       6        1   \n",
       "12    OMAERA PHARMACEUTICALS LIMITED  C0000434       7        0   \n",
       "13  LOREAL (CPD) EAST AFRICA LIMITED  C0000481       8        0   \n",
       "14  LOREAL (CPD) EAST AFRICA LIMITED  C0000481       8        1   \n",
       "15              HEALTH CLASSIQUE LTD  C0000657       9        0   \n",
       "16              UNCOVER SKINCARE LTD  C0000712      10        0   \n",
       "\n",
       "            DocType       Comments   DocDate  DocDate2  \n",
       "0   dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "1   dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "2   dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "3   dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "4   dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "5   dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "6   dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "7   dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "8   dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "9   dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "10  dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "11  dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "12  dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "13  dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "14  dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "15  dDocument_Items  SPECIAL ORDER  20240313  20240313  \n",
       "16  dDocument_Items  SPECIAL ORDER  20240313  20240313  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order['DocType'] = str('dDocument_Items')\n",
    "order['Comments'] = str('SPECIAL ORDER')\n",
    "order['DocDate'] = datetime.today().strftime('%Y%m%d')\n",
    "order['DocDate2'] = datetime.today().strftime('%Y%m%d')\n",
    "order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "1f07d2a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "order.to_excel('To_order.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "982dccc1",
   "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>DocNum</th>\n",
       "      <th>DocEntry</th>\n",
       "      <th>DocType</th>\n",
       "      <th>HandWritten</th>\n",
       "      <th>Printed</th>\n",
       "      <th>DocDate</th>\n",
       "      <th>DocDueDate</th>\n",
       "      <th>CardCode</th>\n",
       "      <th>CardName</th>\n",
       "      <th>Address</th>\n",
       "      <th>...</th>\n",
       "      <th>DocumentTaxID</th>\n",
       "      <th>DateOfReportingControlStatementVAT</th>\n",
       "      <th>ReportingSectionControlStatementVAT</th>\n",
       "      <th>ExcludeFromTaxReportControlStatementVAT</th>\n",
       "      <th>POS_CashRegister</th>\n",
       "      <th>PriceMode</th>\n",
       "      <th>ShipFrom</th>\n",
       "      <th>CommissionTrade</th>\n",
       "      <th>CommissionTradeReturn</th>\n",
       "      <th>UseBillToAddrToDetermineTax</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DocNum</td>\n",
       "      <td>DocEntry</td>\n",
       "      <td>dDocument_Items</td>\n",
       "      <td>Handwrtten</td>\n",
       "      <td>Printed</td>\n",
       "      <td>DocDate</td>\n",
       "      <td>DocDueDate</td>\n",
       "      <td>CardCode</td>\n",
       "      <td>CardName</td>\n",
       "      <td>Address</td>\n",
       "      <td>...</td>\n",
       "      <td>DocTaxID</td>\n",
       "      <td>DateReport</td>\n",
       "      <td>RepSection</td>\n",
       "      <td>ExclTaxRep</td>\n",
       "      <td>PosCashReg</td>\n",
       "      <td>PriceMode</td>\n",
       "      <td>ShipToCode</td>\n",
       "      <td>ComTrade</td>\n",
       "      <td>ComTradeRt</td>\n",
       "      <td>UseBilAddr</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 175 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   DocNum  DocEntry          DocType HandWritten  Printed  DocDate  \\\n",
       "0  DocNum  DocEntry  dDocument_Items  Handwrtten  Printed  DocDate   \n",
       "\n",
       "   DocDueDate  CardCode  CardName  Address  ... DocumentTaxID  \\\n",
       "0  DocDueDate  CardCode  CardName  Address  ...      DocTaxID   \n",
       "\n",
       "  DateOfReportingControlStatementVAT ReportingSectionControlStatementVAT  \\\n",
       "0                         DateReport                          RepSection   \n",
       "\n",
       "  ExcludeFromTaxReportControlStatementVAT POS_CashRegister  PriceMode  \\\n",
       "0                              ExclTaxRep       PosCashReg  PriceMode   \n",
       "\n",
       "     ShipFrom CommissionTrade CommissionTradeReturn  \\\n",
       "0  ShipToCode        ComTrade            ComTradeRt   \n",
       "\n",
       "  UseBillToAddrToDetermineTax  \n",
       "0                  UseBilAddr  \n",
       "\n",
       "[1 rows x 175 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_row = 2\n",
    "for idx, value in enumerate(order['DocType'], start=start_row):\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45b7b143",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}