data_mining_table_making.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    TransactionID                                        Transaction  \\\n",
      "0          TI0001                   [Camera, Camera-holder, SD-card]   \n",
      "1          TI0002       [Camera, Focus-lens, Camera-holder, SD-card]   \n",
      "2          TI0003  [Auto-focus-mount-adapter, Camera, Photographi...   \n",
      "3          TI0004       [Camera, Focus-lens, Camera-holder, SD-card]   \n",
      "4          TI0005     [Camera, Photographic-lighting, Camera-holder]   \n",
      "..            ...                                                ...   \n",
      "435        TI0436                     [Screen-Protector, Cover-Case]   \n",
      "436        TI0437                         [Sim, Power-Bank, SD-card]   \n",
      "437        TI0438                   [Screen-Protector, Sim, SD-card]   \n",
      "438        TI0439                                [Mobile-phone, Sim]   \n",
      "439        TI0440                            [Screen-Protector, Sim]   \n",
      "\n",
      "    CustomerID Mobile_number  \n",
      "0      CUST023    7023711077  \n",
      "1      CUST012    7023711088  \n",
      "2      CUST034    7023711066  \n",
      "3      CUST008    7023711092  \n",
      "4      CUST029    7023711071  \n",
      "..         ...           ...  \n",
      "435    CUST006    7023711094  \n",
      "436    CUST034    7023711066  \n",
      "437    CUST021    7023711079  \n",
      "438    CUST010    7023711090  \n",
      "439    CUST016    7023711084  \n",
      "\n",
      "[440 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "#Transaction for mobile phones and Camera accessories\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from random import *\n",
    "seed(10)\n",
    "final_list=[]\n",
    "\n",
    "\n",
    "#camera possibilities\n",
    "camera_possibilities=[\"Camera\",\"Focus-lens\",\"SD-card\",\"Camera-holder\",\"Auto-focus-mount-adapter\" , \"Tripod-stand\" , \"Photographic-lighting\"]\n",
    "camera_possibilities_weights=[0.6 , 0.2 , 0.4 , 0.4 , 0.1 , 0.1, 0.07]\n",
    "\n",
    "def makecameratransactions(camera_possibilities,k,camera_possibilities_weights,final_list):\n",
    "    for i in range(50):\n",
    "        final_list.append(list(set(choices(camera_possibilities,k=4+k,weights = camera_possibilities_weights))))\n",
    "    for i in range(100):\n",
    "        final_list.append(list(set(choices(camera_possibilities,k=2+k,weights = camera_possibilities_weights))))\n",
    "    for i in range(70):\n",
    "        final_list.append(list(set(choices(camera_possibilities,k=3+k,weights = camera_possibilities_weights))))\n",
    "    return final_list\n",
    "final_list = makecameratransactions(camera_possibilities,len(camera_possibilities)-6,camera_possibilities_weights,final_list)\n",
    "\n",
    "\n",
    "#mobile-phone possibilities\n",
    "mobile_possibilities = [\"Mobile-phone\" , \"Screen-Protector\" , \"Sim\" , \"SD-card\" , \"Cover-Case\" , \"Earphones\" , \"Power-Bank\"]\n",
    "mobile_possibilities_weights = [0.7 , 0.4 , 0.5 , 0.4 , 0.2 , 0.3, 0.07]\n",
    "def makemobiletransactions(mobile_possibilities,k,mobile_possibilities_weights,final_list):\n",
    "    for i in range(50):\n",
    "        final_list.append(list(set(choices(mobile_possibilities,k=4+k,weights = mobile_possibilities_weights))))\n",
    "    for i in range(100):\n",
    "        final_list.append(list(set(choices(mobile_possibilities,k=2+k,weights = mobile_possibilities_weights))))\n",
    "    for i in range(70):\n",
    "        final_list.append(list(set(choices(mobile_possibilities,k=3+k,weights = mobile_possibilities_weights))))\n",
    "    return final_list\n",
    "final_list = makemobiletransactions(mobile_possibilities,0,mobile_possibilities_weights,final_list)\n",
    "\n",
    "\n",
    "#dataframe making\n",
    "n=len(final_list)\n",
    "l=[]\n",
    "customerid=[]\n",
    "\n",
    "def mobilenumber(row):\n",
    "    return (\"70237110\"+str(100-int(row[5:])))\n",
    "\n",
    "for i in range(1,41):\n",
    "    if i//10 > 0:\n",
    "        customerid.append(\"CUST0\"+str(i))\n",
    "    elif i>=0:\n",
    "        customerid.append(\"CUST00\"+str(i))\n",
    "\n",
    "for i in range(1,n+1):\n",
    "    if i//1000 > 0:\n",
    "        l.append(\"TI\"+str(i))\n",
    "    elif i//100 > 0:    \n",
    "        l.append(\"TI0\"+str(i))\n",
    "    elif i//10 > 0:\n",
    "        l.append(\"TI00\"+str(i))\n",
    "    elif i>=0:\n",
    "        l.append(\"TI000\"+str(i))\n",
    "\n",
    "df = pd.DataFrame({\"TransactionID\":l})\n",
    "df['Transaction']=final_list\n",
    "df['CustomerID']=choices(customerid,k=n)\n",
    "df['Mobile_number'] = df['CustomerID'].apply(mobilenumber)\n",
    "print(df)\n",
    "\n",
    "#df.to_csv(\"data_mining-data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# camera selling and costing price and profit   \n",
    "# Note -: including the gst(taking it to be 12%) in bill will at last be paid by the customer and thus our profit at\n",
    "# last will be, profit = selling price - cost\n",
    "len(camera_possibilities)\n",
    "camera_possibilities\n",
    "\n",
    "camera_selling_prices = [50000,15000,1250,2500,14700,1750,3600]\n",
    "weight_for_costing_camera = [0.7,0.64,0.95,0.75,0.85,0.67,0.75]\n",
    "#camera_costing_prices = [(50000*0.7),(150000*0.64),(1250*0.95),(2500*0.75),(14700*0.85),(1750*0.67),(3600*0.0.75)]\n",
    "camera_costing_prices = [camera_selling_prices[i]*weight_for_costing_camera[i] for i in range(len(camera_selling_prices))]\n",
    "camera_profit = [camera_selling_prices[i] - camera_costing_prices[i] for i in range(len(camera_costing_prices))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7\n"
     ]
    }
   ],
   "source": [
    "#mobile sellig and costing price and profit\n",
    "print(len(mobile_possibilities))\n",
    "mobile_possibilities\n",
    "\n",
    "\n",
    "mobile_possibilities_for_price_table = ['Mobile-phone','Screen-Protector','Sim','Cover-Case','Earphones','Power-Bank']\n",
    "mobile_selling_prices = [45000,300,150,1100,2500,2500]\n",
    "weight_for_costing_mobiles = [0.90,0.3,0.80,0.4,0.8,0.5]\n",
    "mobile_costing_prices = [mobile_selling_prices[i]*weight_for_costing_mobiles[i] for i in range(len(mobile_selling_prices))]\n",
    "mobile_profit = [mobile_selling_prices[i]-mobile_costing_prices[i] for i in range(len(mobile_possibilities_for_price_table))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>Product Name</th>\n",
       "      <th>Costing Price</th>\n",
       "      <th>Selling Price</th>\n",
       "      <th>Profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mobile-phone</td>\n",
       "      <td>40500.0</td>\n",
       "      <td>45000</td>\n",
       "      <td>4500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Screen-Protector</td>\n",
       "      <td>90.0</td>\n",
       "      <td>300</td>\n",
       "      <td>210.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sim</td>\n",
       "      <td>120.0</td>\n",
       "      <td>150</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cover-Case</td>\n",
       "      <td>440.0</td>\n",
       "      <td>1100</td>\n",
       "      <td>660.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Earphones</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>2500</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Power-Bank</td>\n",
       "      <td>1250.0</td>\n",
       "      <td>2500</td>\n",
       "      <td>1250.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Camera</td>\n",
       "      <td>35000.0</td>\n",
       "      <td>50000</td>\n",
       "      <td>15000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Focus-lens</td>\n",
       "      <td>9600.0</td>\n",
       "      <td>15000</td>\n",
       "      <td>5400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SD-card</td>\n",
       "      <td>1187.5</td>\n",
       "      <td>1250</td>\n",
       "      <td>62.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Camera-holder</td>\n",
       "      <td>1875.0</td>\n",
       "      <td>2500</td>\n",
       "      <td>625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Auto-focus-mount-adapter</td>\n",
       "      <td>12495.0</td>\n",
       "      <td>14700</td>\n",
       "      <td>2205.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Tripod-stand</td>\n",
       "      <td>1172.5</td>\n",
       "      <td>1750</td>\n",
       "      <td>577.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Photographic-lighting</td>\n",
       "      <td>2700.0</td>\n",
       "      <td>3600</td>\n",
       "      <td>900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Product Name  Costing Price  Selling Price   Profit\n",
       "0               Mobile-phone        40500.0          45000   4500.0\n",
       "1           Screen-Protector           90.0            300    210.0\n",
       "2                        Sim          120.0            150     30.0\n",
       "3                 Cover-Case          440.0           1100    660.0\n",
       "4                  Earphones         2000.0           2500    500.0\n",
       "5                 Power-Bank         1250.0           2500   1250.0\n",
       "6                     Camera        35000.0          50000  15000.0\n",
       "7                 Focus-lens         9600.0          15000   5400.0\n",
       "8                    SD-card         1187.5           1250     62.5\n",
       "9              Camera-holder         1875.0           2500    625.0\n",
       "10  Auto-focus-mount-adapter        12495.0          14700   2205.0\n",
       "11              Tripod-stand         1172.5           1750    577.5\n",
       "12     Photographic-lighting         2700.0           3600    900.0"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# making the product description table\n",
    "\n",
    "products_name = mobile_possibilities_for_price_table + camera_possibilities\n",
    "products_selling_prices = mobile_selling_prices + camera_selling_prices\n",
    "products_costing_prices = mobile_costing_prices + camera_costing_prices\n",
    "products_profit = mobile_profit + camera_profit\n",
    "\n",
    "products = pd.DataFrame({\"Product Name\":products_name})\n",
    "products[\"Costing Price\"] = products_costing_prices\n",
    "products[\"Selling Price\"] = products_selling_prices\n",
    "products[\"Profit\"] = products_profit\n",
    "products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Aahron Aakash Aalam Aamer Aamir Aaran Aaren Ballad Ballard Ballentine Ballerd Ballinamore Balshazar Baltasar Baltazar Balthazar Balthazzar Baltsaros Sady Saeed Safak Safer Safyr Saga Sagar Sagaz Sage Sagittarius Sagiv Sago Sahadev Sahale Sahen Sahib Said Saiyid Saje Sakima Sal Salamon\n"
     ]
    }
   ],
   "source": [
    "#input names of the customers\n",
    "names = list(input(\"\").split())\n",
    "#Aahron Aakash Aalam Aamer Aamir Aaran Aaren Ballad Ballard Ballentine Ballerd Ballinamore Balshazar Baltasar Baltazar\n",
    "#Balthazar Balthazzar Baltsaros Sady Saeed Safak Safer Safyr Saga Sagar Sagaz Sage Sagittarius Sagiv Sago Sahadev \n",
    "#Sahale Sahen Sahib Said Saiyid Saje Sakima Sal Salamon\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>TransactionID</th>\n",
       "      <th>Transaction</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Mobile_number</th>\n",
       "      <th>Customer Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TI0001</td>\n",
       "      <td>[Camera, Camera-holder, SD-card]</td>\n",
       "      <td>CUST023</td>\n",
       "      <td>7023711077</td>\n",
       "      <td>Safyr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TI0002</td>\n",
       "      <td>[Camera, Focus-lens, Camera-holder, SD-card]</td>\n",
       "      <td>CUST012</td>\n",
       "      <td>7023711088</td>\n",
       "      <td>Ballinamore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TI0003</td>\n",
       "      <td>[Auto-focus-mount-adapter, Camera, Photographi...</td>\n",
       "      <td>CUST034</td>\n",
       "      <td>7023711066</td>\n",
       "      <td>Sahib</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TI0004</td>\n",
       "      <td>[Camera, Focus-lens, Camera-holder, SD-card]</td>\n",
       "      <td>CUST008</td>\n",
       "      <td>7023711092</td>\n",
       "      <td>Ballad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TI0005</td>\n",
       "      <td>[Camera, Photographic-lighting, Camera-holder]</td>\n",
       "      <td>CUST029</td>\n",
       "      <td>7023711071</td>\n",
       "      <td>Sagiv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>435</th>\n",
       "      <td>TI0436</td>\n",
       "      <td>[Screen-Protector, Cover-Case]</td>\n",
       "      <td>CUST006</td>\n",
       "      <td>7023711094</td>\n",
       "      <td>Aaran</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>436</th>\n",
       "      <td>TI0437</td>\n",
       "      <td>[Sim, Power-Bank, SD-card]</td>\n",
       "      <td>CUST034</td>\n",
       "      <td>7023711066</td>\n",
       "      <td>Sahib</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>437</th>\n",
       "      <td>TI0438</td>\n",
       "      <td>[Screen-Protector, Sim, SD-card]</td>\n",
       "      <td>CUST021</td>\n",
       "      <td>7023711079</td>\n",
       "      <td>Safak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>438</th>\n",
       "      <td>TI0439</td>\n",
       "      <td>[Mobile-phone, Sim]</td>\n",
       "      <td>CUST010</td>\n",
       "      <td>7023711090</td>\n",
       "      <td>Ballentine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>TI0440</td>\n",
       "      <td>[Screen-Protector, Sim]</td>\n",
       "      <td>CUST016</td>\n",
       "      <td>7023711084</td>\n",
       "      <td>Balthazar</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>440 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    TransactionID                                        Transaction  \\\n",
       "0          TI0001                   [Camera, Camera-holder, SD-card]   \n",
       "1          TI0002       [Camera, Focus-lens, Camera-holder, SD-card]   \n",
       "2          TI0003  [Auto-focus-mount-adapter, Camera, Photographi...   \n",
       "3          TI0004       [Camera, Focus-lens, Camera-holder, SD-card]   \n",
       "4          TI0005     [Camera, Photographic-lighting, Camera-holder]   \n",
       "..            ...                                                ...   \n",
       "435        TI0436                     [Screen-Protector, Cover-Case]   \n",
       "436        TI0437                         [Sim, Power-Bank, SD-card]   \n",
       "437        TI0438                   [Screen-Protector, Sim, SD-card]   \n",
       "438        TI0439                                [Mobile-phone, Sim]   \n",
       "439        TI0440                            [Screen-Protector, Sim]   \n",
       "\n",
       "    CustomerID Mobile_number Customer Name  \n",
       "0      CUST023    7023711077         Safyr  \n",
       "1      CUST012    7023711088   Ballinamore  \n",
       "2      CUST034    7023711066         Sahib  \n",
       "3      CUST008    7023711092        Ballad  \n",
       "4      CUST029    7023711071         Sagiv  \n",
       "..         ...           ...           ...  \n",
       "435    CUST006    7023711094         Aaran  \n",
       "436    CUST034    7023711066         Sahib  \n",
       "437    CUST021    7023711079         Safak  \n",
       "438    CUST010    7023711090    Ballentine  \n",
       "439    CUST016    7023711084     Balthazar  \n",
       "\n",
       "[440 rows x 5 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#adding names to the df\n",
    "def addname(row):\n",
    "    return names[int(row[-2:])-1]\n",
    "\n",
    "df[\"Customer Name\"] = df[\"CustomerID\"].apply(addname)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#adding age to the df\n",
    "def giveage(row):\n",
    "    temp = int(row[-2:])\n",
    "    if(temp>30):\n",
    "        return int(((int(temp*1.45))%10)+50)\n",
    "    elif temp<10:\n",
    "        return int(((int(temp*0.3))%10)+10)\n",
    "    else:\n",
    "        return int(((int(temp*0.37))%20)+20)\n",
    "    \n",
    "df[\"Age\"] = df[\"CustomerID\"].apply(giveage)\n",
    "df[\"Age\"].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Age\n",
       "10    3\n",
       "11    3\n",
       "12    3\n",
       "23    1\n",
       "24    3\n",
       "25    3\n",
       "26    2\n",
       "27    3\n",
       "28    3\n",
       "29    3\n",
       "30    2\n",
       "31    1\n",
       "50    1\n",
       "52    1\n",
       "53    1\n",
       "54    1\n",
       "55    1\n",
       "56    2\n",
       "57    1\n",
       "58    1\n",
       "59    1\n",
       "Name: CustomerID, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#checking unque values count\n",
    "df.groupby(\"Age\")[\"CustomerID\"].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# adding the bill amount (with and without gst 12%)\n",
    "\n",
    "def addbill_without_gst(row):\n",
    "    billvalue = 0\n",
    "    for i in row:\n",
    "        billvalue = billvalue + int(products.loc[products[\"Product Name\"] == i][\"Selling Price\"])\n",
    "    return billvalue\n",
    "\n",
    "\n",
    "def addbill_with_gst(row):\n",
    "    return (row*1.12)\n",
    "\n",
    "df[\"Billing Without GST\"] = df[\"Transaction\"].apply(addbill_without_gst)\n",
    "df[\"Billing With GST\"] = df[\"Billing Without GST\"].apply(addbill_with_gst)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>TransactionID</th>\n",
       "      <th>Transaction</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Mobile_number</th>\n",
       "      <th>Customer Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Billing Without GST</th>\n",
       "      <th>Billing With GST</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TI0001</td>\n",
       "      <td>[Camera, Camera-holder, SD-card]</td>\n",
       "      <td>CUST023</td>\n",
       "      <td>7023711077</td>\n",
       "      <td>Safyr</td>\n",
       "      <td>28</td>\n",
       "      <td>53750</td>\n",
       "      <td>60200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TI0002</td>\n",
       "      <td>[Camera, Focus-lens, Camera-holder, SD-card]</td>\n",
       "      <td>CUST012</td>\n",
       "      <td>7023711088</td>\n",
       "      <td>Ballinamore</td>\n",
       "      <td>24</td>\n",
       "      <td>68750</td>\n",
       "      <td>77000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TI0003</td>\n",
       "      <td>[Auto-focus-mount-adapter, Camera, Photographi...</td>\n",
       "      <td>CUST034</td>\n",
       "      <td>7023711066</td>\n",
       "      <td>Sahib</td>\n",
       "      <td>59</td>\n",
       "      <td>70050</td>\n",
       "      <td>78456.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TI0004</td>\n",
       "      <td>[Camera, Focus-lens, Camera-holder, SD-card]</td>\n",
       "      <td>CUST008</td>\n",
       "      <td>7023711092</td>\n",
       "      <td>Ballad</td>\n",
       "      <td>12</td>\n",
       "      <td>68750</td>\n",
       "      <td>77000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TI0005</td>\n",
       "      <td>[Camera, Photographic-lighting, Camera-holder]</td>\n",
       "      <td>CUST029</td>\n",
       "      <td>7023711071</td>\n",
       "      <td>Sagiv</td>\n",
       "      <td>30</td>\n",
       "      <td>56100</td>\n",
       "      <td>62832.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>435</th>\n",
       "      <td>TI0436</td>\n",
       "      <td>[Screen-Protector, Cover-Case]</td>\n",
       "      <td>CUST006</td>\n",
       "      <td>7023711094</td>\n",
       "      <td>Aaran</td>\n",
       "      <td>11</td>\n",
       "      <td>1400</td>\n",
       "      <td>1568.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>436</th>\n",
       "      <td>TI0437</td>\n",
       "      <td>[Sim, Power-Bank, SD-card]</td>\n",
       "      <td>CUST034</td>\n",
       "      <td>7023711066</td>\n",
       "      <td>Sahib</td>\n",
       "      <td>59</td>\n",
       "      <td>3900</td>\n",
       "      <td>4368.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>437</th>\n",
       "      <td>TI0438</td>\n",
       "      <td>[Screen-Protector, Sim, SD-card]</td>\n",
       "      <td>CUST021</td>\n",
       "      <td>7023711079</td>\n",
       "      <td>Safak</td>\n",
       "      <td>27</td>\n",
       "      <td>1700</td>\n",
       "      <td>1904.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>438</th>\n",
       "      <td>TI0439</td>\n",
       "      <td>[Mobile-phone, Sim]</td>\n",
       "      <td>CUST010</td>\n",
       "      <td>7023711090</td>\n",
       "      <td>Ballentine</td>\n",
       "      <td>23</td>\n",
       "      <td>45150</td>\n",
       "      <td>50568.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>TI0440</td>\n",
       "      <td>[Screen-Protector, Sim]</td>\n",
       "      <td>CUST016</td>\n",
       "      <td>7023711084</td>\n",
       "      <td>Balthazar</td>\n",
       "      <td>25</td>\n",
       "      <td>450</td>\n",
       "      <td>504.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>440 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    TransactionID                                        Transaction  \\\n",
       "0          TI0001                   [Camera, Camera-holder, SD-card]   \n",
       "1          TI0002       [Camera, Focus-lens, Camera-holder, SD-card]   \n",
       "2          TI0003  [Auto-focus-mount-adapter, Camera, Photographi...   \n",
       "3          TI0004       [Camera, Focus-lens, Camera-holder, SD-card]   \n",
       "4          TI0005     [Camera, Photographic-lighting, Camera-holder]   \n",
       "..            ...                                                ...   \n",
       "435        TI0436                     [Screen-Protector, Cover-Case]   \n",
       "436        TI0437                         [Sim, Power-Bank, SD-card]   \n",
       "437        TI0438                   [Screen-Protector, Sim, SD-card]   \n",
       "438        TI0439                                [Mobile-phone, Sim]   \n",
       "439        TI0440                            [Screen-Protector, Sim]   \n",
       "\n",
       "    CustomerID Mobile_number Customer Name  Age  Billing Without GST  \\\n",
       "0      CUST023    7023711077         Safyr   28                53750   \n",
       "1      CUST012    7023711088   Ballinamore   24                68750   \n",
       "2      CUST034    7023711066         Sahib   59                70050   \n",
       "3      CUST008    7023711092        Ballad   12                68750   \n",
       "4      CUST029    7023711071         Sagiv   30                56100   \n",
       "..         ...           ...           ...  ...                  ...   \n",
       "435    CUST006    7023711094         Aaran   11                 1400   \n",
       "436    CUST034    7023711066         Sahib   59                 3900   \n",
       "437    CUST021    7023711079         Safak   27                 1700   \n",
       "438    CUST010    7023711090    Ballentine   23                45150   \n",
       "439    CUST016    7023711084     Balthazar   25                  450   \n",
       "\n",
       "     Billing With GST  \n",
       "0             60200.0  \n",
       "1             77000.0  \n",
       "2             78456.0  \n",
       "3             77000.0  \n",
       "4             62832.0  \n",
       "..                ...  \n",
       "435            1568.0  \n",
       "436            4368.0  \n",
       "437            1904.0  \n",
       "438           50568.0  \n",
       "439             504.0  \n",
       "\n",
       "[440 rows x 8 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45000"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int(products.loc[products[\"Product Name\"] == \"Mobile-phone\"][\"Selling Price\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "random_numbers_400 = []\n",
    "for i in range(10000):\n",
    "    random_numbers_400.append(randint(1,10))\n",
    "#random_numbers_400"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\deepa\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:6: FutureWarning: get_value is deprecated and will be removed in a future release. Please use .at[] or .iat[] accessors instead\n",
      "  \n",
      "C:\\Users\\deepa\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:18: FutureWarning: get_value is deprecated and will be removed in a future release. Please use .at[] or .iat[] accessors instead\n",
      "C:\\Users\\deepa\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:22: FutureWarning: get_value is deprecated and will be removed in a future release. Please use .at[] or .iat[] accessors instead\n"
     ]
    },
    {
     "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>TransactionID</th>\n",
       "      <th>Transaction</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Mobile_number</th>\n",
       "      <th>Customer Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Billing Without GST</th>\n",
       "      <th>Billing With GST</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TI0001</td>\n",
       "      <td>[Camera, Camera-holder, SD-card]</td>\n",
       "      <td>CUST023</td>\n",
       "      <td>7023711077</td>\n",
       "      <td>Safyr</td>\n",
       "      <td>28</td>\n",
       "      <td>53750</td>\n",
       "      <td>60200.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TI0002</td>\n",
       "      <td>[Camera, Focus-lens, Camera-holder, SD-card]</td>\n",
       "      <td>CUST012</td>\n",
       "      <td>7023711088</td>\n",
       "      <td>Ballinamore</td>\n",
       "      <td>24</td>\n",
       "      <td>68750</td>\n",
       "      <td>77000.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TI0003</td>\n",
       "      <td>[Auto-focus-mount-adapter, Camera, Photographi...</td>\n",
       "      <td>CUST034</td>\n",
       "      <td>7023711066</td>\n",
       "      <td>Sahib</td>\n",
       "      <td>59</td>\n",
       "      <td>70050</td>\n",
       "      <td>78456.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TI0004</td>\n",
       "      <td>[Camera, Focus-lens, Camera-holder, SD-card]</td>\n",
       "      <td>CUST008</td>\n",
       "      <td>7023711092</td>\n",
       "      <td>Ballad</td>\n",
       "      <td>12</td>\n",
       "      <td>68750</td>\n",
       "      <td>77000.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TI0005</td>\n",
       "      <td>[Camera, Photographic-lighting, Camera-holder]</td>\n",
       "      <td>CUST029</td>\n",
       "      <td>7023711071</td>\n",
       "      <td>Sagiv</td>\n",
       "      <td>30</td>\n",
       "      <td>56100</td>\n",
       "      <td>62832.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>435</th>\n",
       "      <td>TI0436</td>\n",
       "      <td>[Screen-Protector, Cover-Case]</td>\n",
       "      <td>CUST006</td>\n",
       "      <td>7023711094</td>\n",
       "      <td>Aaran</td>\n",
       "      <td>11</td>\n",
       "      <td>1400</td>\n",
       "      <td>1568.0</td>\n",
       "      <td>2017-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>436</th>\n",
       "      <td>TI0437</td>\n",
       "      <td>[Sim, Power-Bank, SD-card]</td>\n",
       "      <td>CUST034</td>\n",
       "      <td>7023711066</td>\n",
       "      <td>Sahib</td>\n",
       "      <td>59</td>\n",
       "      <td>3900</td>\n",
       "      <td>4368.0</td>\n",
       "      <td>2017-06-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>437</th>\n",
       "      <td>TI0438</td>\n",
       "      <td>[Screen-Protector, Sim, SD-card]</td>\n",
       "      <td>CUST021</td>\n",
       "      <td>7023711079</td>\n",
       "      <td>Safak</td>\n",
       "      <td>27</td>\n",
       "      <td>1700</td>\n",
       "      <td>1904.0</td>\n",
       "      <td>2017-06-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>438</th>\n",
       "      <td>TI0439</td>\n",
       "      <td>[Mobile-phone, Sim]</td>\n",
       "      <td>CUST010</td>\n",
       "      <td>7023711090</td>\n",
       "      <td>Ballentine</td>\n",
       "      <td>23</td>\n",
       "      <td>45150</td>\n",
       "      <td>50568.0</td>\n",
       "      <td>2017-06-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>TI0440</td>\n",
       "      <td>[Screen-Protector, Sim]</td>\n",
       "      <td>CUST016</td>\n",
       "      <td>7023711084</td>\n",
       "      <td>Balthazar</td>\n",
       "      <td>25</td>\n",
       "      <td>450</td>\n",
       "      <td>504.0</td>\n",
       "      <td>2017-06-25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>440 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    TransactionID                                        Transaction  \\\n",
       "0          TI0001                   [Camera, Camera-holder, SD-card]   \n",
       "1          TI0002       [Camera, Focus-lens, Camera-holder, SD-card]   \n",
       "2          TI0003  [Auto-focus-mount-adapter, Camera, Photographi...   \n",
       "3          TI0004       [Camera, Focus-lens, Camera-holder, SD-card]   \n",
       "4          TI0005     [Camera, Photographic-lighting, Camera-holder]   \n",
       "..            ...                                                ...   \n",
       "435        TI0436                     [Screen-Protector, Cover-Case]   \n",
       "436        TI0437                         [Sim, Power-Bank, SD-card]   \n",
       "437        TI0438                   [Screen-Protector, Sim, SD-card]   \n",
       "438        TI0439                                [Mobile-phone, Sim]   \n",
       "439        TI0440                            [Screen-Protector, Sim]   \n",
       "\n",
       "    CustomerID Mobile_number Customer Name  Age  Billing Without GST  \\\n",
       "0      CUST023    7023711077         Safyr   28                53750   \n",
       "1      CUST012    7023711088   Ballinamore   24                68750   \n",
       "2      CUST034    7023711066         Sahib   59                70050   \n",
       "3      CUST008    7023711092        Ballad   12                68750   \n",
       "4      CUST029    7023711071         Sagiv   30                56100   \n",
       "..         ...           ...           ...  ...                  ...   \n",
       "435    CUST006    7023711094         Aaran   11                 1400   \n",
       "436    CUST034    7023711066         Sahib   59                 3900   \n",
       "437    CUST021    7023711079         Safak   27                 1700   \n",
       "438    CUST010    7023711090    Ballentine   23                45150   \n",
       "439    CUST016    7023711084     Balthazar   25                  450   \n",
       "\n",
       "     Billing With GST       Date  \n",
       "0             60200.0 2017-01-01  \n",
       "1             77000.0 2017-01-01  \n",
       "2             78456.0 2017-01-01  \n",
       "3             77000.0 2017-01-01  \n",
       "4             62832.0 2017-01-01  \n",
       "..                ...        ...  \n",
       "435            1568.0 2017-06-21  \n",
       "436            4368.0 2017-06-22  \n",
       "437            1904.0 2017-06-23  \n",
       "438           50568.0 2017-06-24  \n",
       "439             504.0 2017-06-25  \n",
       "\n",
       "[440 rows x 9 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#making and adding dates of the transactions\n",
    "\n",
    "#making dates\n",
    "temp_dataframe = pd.DataFrame()\n",
    "temp_dataframe['Date'] = pd.date_range(start='1/1/2017',end = \"1/1/2017\", periods=5)\n",
    "last_date = temp_dataframe.get_value(len(temp_dataframe)-1,0,takeable = True)\n",
    "i=0\n",
    "while 1:\n",
    "    if(len(temp_dataframe)>428):\n",
    "        temp = pd.date_range(start = last_date, periods = 440 - len(temp_dataframe),freq =\"D\")\n",
    "        temp = pd.DataFrame({\"Date\" : temp})\n",
    "        temp_dataframe = temp_dataframe.append(temp,sort=False)\n",
    "        break\n",
    "    temp = pd.date_range(start=last_date,end = last_date, periods = (random_numbers_400[i]%5))\n",
    "    temp = pd.DataFrame({\"Date\" : temp})\n",
    "    temp_dataframe = temp_dataframe.append( temp,sort = False )\n",
    "    \n",
    "    last_date = temp_dataframe.get_value(len(temp_dataframe)-1,0,takeable = True)\n",
    "    temp = pd.date_range(start=last_date, periods=3,freq = \"D\")\n",
    "    temp = pd.DataFrame({\"Date\" : temp})\n",
    "    temp_dataframe = temp_dataframe.append( temp,sort = False )\n",
    "    last_date = temp_dataframe.get_value(len(temp_dataframe)-1,0,takeable = True)\n",
    "    i+=1\n",
    "len(temp_dataframe)\n",
    "\n",
    "df[\"Date\"] = list(temp_dataframe[\"Date\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>TransactionID</th>\n",
       "      <th>Transaction</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Mobile_number</th>\n",
       "      <th>Customer Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Billing Without GST</th>\n",
       "      <th>Billing With GST</th>\n",
       "      <th>Date</th>\n",
       "      <th>Zip Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TI0001</td>\n",
       "      <td>[Camera, Camera-holder, SD-card]</td>\n",
       "      <td>CUST023</td>\n",
       "      <td>7023711077</td>\n",
       "      <td>Safyr</td>\n",
       "      <td>28</td>\n",
       "      <td>53750</td>\n",
       "      <td>60200.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>54935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TI0002</td>\n",
       "      <td>[Camera, Focus-lens, Camera-holder, SD-card]</td>\n",
       "      <td>CUST012</td>\n",
       "      <td>7023711088</td>\n",
       "      <td>Ballinamore</td>\n",
       "      <td>24</td>\n",
       "      <td>68750</td>\n",
       "      <td>77000.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>54169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TI0003</td>\n",
       "      <td>[Auto-focus-mount-adapter, Camera, Photographi...</td>\n",
       "      <td>CUST034</td>\n",
       "      <td>7023711066</td>\n",
       "      <td>Sahib</td>\n",
       "      <td>59</td>\n",
       "      <td>70050</td>\n",
       "      <td>78456.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>54964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TI0004</td>\n",
       "      <td>[Camera, Focus-lens, Camera-holder, SD-card]</td>\n",
       "      <td>CUST008</td>\n",
       "      <td>7023711092</td>\n",
       "      <td>Ballad</td>\n",
       "      <td>12</td>\n",
       "      <td>68750</td>\n",
       "      <td>77000.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>54061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TI0005</td>\n",
       "      <td>[Camera, Photographic-lighting, Camera-holder]</td>\n",
       "      <td>CUST029</td>\n",
       "      <td>7023711071</td>\n",
       "      <td>Sagiv</td>\n",
       "      <td>30</td>\n",
       "      <td>56100</td>\n",
       "      <td>62832.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>54952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>435</th>\n",
       "      <td>TI0436</td>\n",
       "      <td>[Screen-Protector, Cover-Case]</td>\n",
       "      <td>CUST006</td>\n",
       "      <td>7023711094</td>\n",
       "      <td>Aaran</td>\n",
       "      <td>11</td>\n",
       "      <td>1400</td>\n",
       "      <td>1568.0</td>\n",
       "      <td>2017-06-21</td>\n",
       "      <td>53919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>436</th>\n",
       "      <td>TI0437</td>\n",
       "      <td>[Sim, Power-Bank, SD-card]</td>\n",
       "      <td>CUST034</td>\n",
       "      <td>7023711066</td>\n",
       "      <td>Sahib</td>\n",
       "      <td>59</td>\n",
       "      <td>3900</td>\n",
       "      <td>4368.0</td>\n",
       "      <td>2017-06-22</td>\n",
       "      <td>54964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>437</th>\n",
       "      <td>TI0438</td>\n",
       "      <td>[Screen-Protector, Sim, SD-card]</td>\n",
       "      <td>CUST021</td>\n",
       "      <td>7023711079</td>\n",
       "      <td>Safak</td>\n",
       "      <td>27</td>\n",
       "      <td>1700</td>\n",
       "      <td>1904.0</td>\n",
       "      <td>2017-06-23</td>\n",
       "      <td>54927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>438</th>\n",
       "      <td>TI0439</td>\n",
       "      <td>[Mobile-phone, Sim]</td>\n",
       "      <td>CUST010</td>\n",
       "      <td>7023711090</td>\n",
       "      <td>Ballentine</td>\n",
       "      <td>23</td>\n",
       "      <td>45150</td>\n",
       "      <td>50568.0</td>\n",
       "      <td>2017-06-24</td>\n",
       "      <td>54136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>TI0440</td>\n",
       "      <td>[Screen-Protector, Sim]</td>\n",
       "      <td>CUST016</td>\n",
       "      <td>7023711084</td>\n",
       "      <td>Balthazar</td>\n",
       "      <td>25</td>\n",
       "      <td>450</td>\n",
       "      <td>504.0</td>\n",
       "      <td>2017-06-25</td>\n",
       "      <td>54906</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>440 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    TransactionID                                        Transaction  \\\n",
       "0          TI0001                   [Camera, Camera-holder, SD-card]   \n",
       "1          TI0002       [Camera, Focus-lens, Camera-holder, SD-card]   \n",
       "2          TI0003  [Auto-focus-mount-adapter, Camera, Photographi...   \n",
       "3          TI0004       [Camera, Focus-lens, Camera-holder, SD-card]   \n",
       "4          TI0005     [Camera, Photographic-lighting, Camera-holder]   \n",
       "..            ...                                                ...   \n",
       "435        TI0436                     [Screen-Protector, Cover-Case]   \n",
       "436        TI0437                         [Sim, Power-Bank, SD-card]   \n",
       "437        TI0438                   [Screen-Protector, Sim, SD-card]   \n",
       "438        TI0439                                [Mobile-phone, Sim]   \n",
       "439        TI0440                            [Screen-Protector, Sim]   \n",
       "\n",
       "    CustomerID Mobile_number Customer Name  Age  Billing Without GST  \\\n",
       "0      CUST023    7023711077         Safyr   28                53750   \n",
       "1      CUST012    7023711088   Ballinamore   24                68750   \n",
       "2      CUST034    7023711066         Sahib   59                70050   \n",
       "3      CUST008    7023711092        Ballad   12                68750   \n",
       "4      CUST029    7023711071         Sagiv   30                56100   \n",
       "..         ...           ...           ...  ...                  ...   \n",
       "435    CUST006    7023711094         Aaran   11                 1400   \n",
       "436    CUST034    7023711066         Sahib   59                 3900   \n",
       "437    CUST021    7023711079         Safak   27                 1700   \n",
       "438    CUST010    7023711090    Ballentine   23                45150   \n",
       "439    CUST016    7023711084     Balthazar   25                  450   \n",
       "\n",
       "     Billing With GST       Date Zip Code  \n",
       "0             60200.0 2017-01-01    54935  \n",
       "1             77000.0 2017-01-01    54169  \n",
       "2             78456.0 2017-01-01    54964  \n",
       "3             77000.0 2017-01-01    54061  \n",
       "4             62832.0 2017-01-01    54952  \n",
       "..                ...        ...      ...  \n",
       "435            1568.0 2017-06-21    53919  \n",
       "436            4368.0 2017-06-22    54964  \n",
       "437            1904.0 2017-06-23    54927  \n",
       "438           50568.0 2017-06-24    54136  \n",
       "439             504.0 2017-06-25    54906  \n",
       "\n",
       "[440 rows x 10 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# adding the zipcodes as we provide zipcode for every online order\n",
    "\n",
    "centre = '54901' #shop's zipcode for relative monitoring\n",
    "\n",
    "from pyzipcode import ZipCodeDatabase\n",
    "zcdb = ZipCodeDatabase()\n",
    "\n",
    "zipcodes = [z.zip for z in zcdb.get_zipcodes_around_radius('54901', 18)]\n",
    "zipcodes = zipcodes[0:39]\n",
    "zipcodes.append('55404')\n",
    "zipcodes.append('50201')\n",
    "zipcodes.remove('54901')\n",
    "\n",
    "#print(zcdb[zipcodes[0]].latitude)\n",
    "#print(zcdb[zipcodes[0]].longitude)\n",
    "\n",
    "def addzipcode(row):\n",
    "    index = int(row[-2:])-1\n",
    "    return zipcodes[index]\n",
    "\n",
    "df[\"Zip Code\"] = df[\"CustomerID\"].apply(addzipcode)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "# exporting the tables - \n",
    "#1.data_table\n",
    "#2.products_table\n",
    "\n",
    "#removing the unnamed index in both tables\n",
    "df.set_index(\"TransactionID\",inplace = True)\n",
    "products.set_index(\"Product Name\",inplace = True)\n",
    "\n",
    "df.to_csv(\"Customer_Final_Purchasing_Dataset.csv\")\n",
    "products.to_csv(\"Products_Description.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>Transaction</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Mobile_number</th>\n",
       "      <th>Customer Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Billing Without GST</th>\n",
       "      <th>Billing With GST</th>\n",
       "      <th>Date</th>\n",
       "      <th>Zip Code</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TransactionID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>TI0001</th>\n",
       "      <td>[Camera, Camera-holder, SD-card]</td>\n",
       "      <td>CUST023</td>\n",
       "      <td>7023711077</td>\n",
       "      <td>Safyr</td>\n",
       "      <td>28</td>\n",
       "      <td>53750</td>\n",
       "      <td>60200.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>54935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TI0002</th>\n",
       "      <td>[Camera, Focus-lens, Camera-holder, SD-card]</td>\n",
       "      <td>CUST012</td>\n",
       "      <td>7023711088</td>\n",
       "      <td>Ballinamore</td>\n",
       "      <td>24</td>\n",
       "      <td>68750</td>\n",
       "      <td>77000.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>54169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TI0003</th>\n",
       "      <td>[Auto-focus-mount-adapter, Camera, Photographi...</td>\n",
       "      <td>CUST034</td>\n",
       "      <td>7023711066</td>\n",
       "      <td>Sahib</td>\n",
       "      <td>59</td>\n",
       "      <td>70050</td>\n",
       "      <td>78456.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>54964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TI0004</th>\n",
       "      <td>[Camera, Focus-lens, Camera-holder, SD-card]</td>\n",
       "      <td>CUST008</td>\n",
       "      <td>7023711092</td>\n",
       "      <td>Ballad</td>\n",
       "      <td>12</td>\n",
       "      <td>68750</td>\n",
       "      <td>77000.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>54061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TI0005</th>\n",
       "      <td>[Camera, Photographic-lighting, Camera-holder]</td>\n",
       "      <td>CUST029</td>\n",
       "      <td>7023711071</td>\n",
       "      <td>Sagiv</td>\n",
       "      <td>30</td>\n",
       "      <td>56100</td>\n",
       "      <td>62832.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>54952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TI0436</th>\n",
       "      <td>[Screen-Protector, Cover-Case]</td>\n",
       "      <td>CUST006</td>\n",
       "      <td>7023711094</td>\n",
       "      <td>Aaran</td>\n",
       "      <td>11</td>\n",
       "      <td>1400</td>\n",
       "      <td>1568.0</td>\n",
       "      <td>2017-06-21</td>\n",
       "      <td>53919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TI0437</th>\n",
       "      <td>[Sim, Power-Bank, SD-card]</td>\n",
       "      <td>CUST034</td>\n",
       "      <td>7023711066</td>\n",
       "      <td>Sahib</td>\n",
       "      <td>59</td>\n",
       "      <td>3900</td>\n",
       "      <td>4368.0</td>\n",
       "      <td>2017-06-22</td>\n",
       "      <td>54964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TI0438</th>\n",
       "      <td>[Screen-Protector, Sim, SD-card]</td>\n",
       "      <td>CUST021</td>\n",
       "      <td>7023711079</td>\n",
       "      <td>Safak</td>\n",
       "      <td>27</td>\n",
       "      <td>1700</td>\n",
       "      <td>1904.0</td>\n",
       "      <td>2017-06-23</td>\n",
       "      <td>54927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TI0439</th>\n",
       "      <td>[Mobile-phone, Sim]</td>\n",
       "      <td>CUST010</td>\n",
       "      <td>7023711090</td>\n",
       "      <td>Ballentine</td>\n",
       "      <td>23</td>\n",
       "      <td>45150</td>\n",
       "      <td>50568.0</td>\n",
       "      <td>2017-06-24</td>\n",
       "      <td>54136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TI0440</th>\n",
       "      <td>[Screen-Protector, Sim]</td>\n",
       "      <td>CUST016</td>\n",
       "      <td>7023711084</td>\n",
       "      <td>Balthazar</td>\n",
       "      <td>25</td>\n",
       "      <td>450</td>\n",
       "      <td>504.0</td>\n",
       "      <td>2017-06-25</td>\n",
       "      <td>54906</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>440 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     Transaction CustomerID  \\\n",
       "TransactionID                                                                 \n",
       "TI0001                          [Camera, Camera-holder, SD-card]    CUST023   \n",
       "TI0002              [Camera, Focus-lens, Camera-holder, SD-card]    CUST012   \n",
       "TI0003         [Auto-focus-mount-adapter, Camera, Photographi...    CUST034   \n",
       "TI0004              [Camera, Focus-lens, Camera-holder, SD-card]    CUST008   \n",
       "TI0005            [Camera, Photographic-lighting, Camera-holder]    CUST029   \n",
       "...                                                          ...        ...   \n",
       "TI0436                            [Screen-Protector, Cover-Case]    CUST006   \n",
       "TI0437                                [Sim, Power-Bank, SD-card]    CUST034   \n",
       "TI0438                          [Screen-Protector, Sim, SD-card]    CUST021   \n",
       "TI0439                                       [Mobile-phone, Sim]    CUST010   \n",
       "TI0440                                   [Screen-Protector, Sim]    CUST016   \n",
       "\n",
       "              Mobile_number Customer Name  Age  Billing Without GST  \\\n",
       "TransactionID                                                         \n",
       "TI0001           7023711077         Safyr   28                53750   \n",
       "TI0002           7023711088   Ballinamore   24                68750   \n",
       "TI0003           7023711066         Sahib   59                70050   \n",
       "TI0004           7023711092        Ballad   12                68750   \n",
       "TI0005           7023711071         Sagiv   30                56100   \n",
       "...                     ...           ...  ...                  ...   \n",
       "TI0436           7023711094         Aaran   11                 1400   \n",
       "TI0437           7023711066         Sahib   59                 3900   \n",
       "TI0438           7023711079         Safak   27                 1700   \n",
       "TI0439           7023711090    Ballentine   23                45150   \n",
       "TI0440           7023711084     Balthazar   25                  450   \n",
       "\n",
       "               Billing With GST       Date Zip Code  \n",
       "TransactionID                                        \n",
       "TI0001                  60200.0 2017-01-01    54935  \n",
       "TI0002                  77000.0 2017-01-01    54169  \n",
       "TI0003                  78456.0 2017-01-01    54964  \n",
       "TI0004                  77000.0 2017-01-01    54061  \n",
       "TI0005                  62832.0 2017-01-01    54952  \n",
       "...                         ...        ...      ...  \n",
       "TI0436                   1568.0 2017-06-21    53919  \n",
       "TI0437                   4368.0 2017-06-22    54964  \n",
       "TI0438                   1904.0 2017-06-23    54927  \n",
       "TI0439                  50568.0 2017-06-24    54136  \n",
       "TI0440                    504.0 2017-06-25    54906  \n",
       "\n",
       "[440 rows x 9 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>Costing Price</th>\n",
       "      <th>Selling Price</th>\n",
       "      <th>Profit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Product Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mobile-phone</th>\n",
       "      <td>40500.0</td>\n",
       "      <td>45000</td>\n",
       "      <td>4500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Screen-Protector</th>\n",
       "      <td>90.0</td>\n",
       "      <td>300</td>\n",
       "      <td>210.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sim</th>\n",
       "      <td>120.0</td>\n",
       "      <td>150</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cover-Case</th>\n",
       "      <td>440.0</td>\n",
       "      <td>1100</td>\n",
       "      <td>660.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Earphones</th>\n",
       "      <td>2000.0</td>\n",
       "      <td>2500</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Power-Bank</th>\n",
       "      <td>1250.0</td>\n",
       "      <td>2500</td>\n",
       "      <td>1250.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Camera</th>\n",
       "      <td>35000.0</td>\n",
       "      <td>50000</td>\n",
       "      <td>15000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Focus-lens</th>\n",
       "      <td>9600.0</td>\n",
       "      <td>15000</td>\n",
       "      <td>5400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SD-card</th>\n",
       "      <td>1187.5</td>\n",
       "      <td>1250</td>\n",
       "      <td>62.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Camera-holder</th>\n",
       "      <td>1875.0</td>\n",
       "      <td>2500</td>\n",
       "      <td>625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Auto-focus-mount-adapter</th>\n",
       "      <td>12495.0</td>\n",
       "      <td>14700</td>\n",
       "      <td>2205.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tripod-stand</th>\n",
       "      <td>1172.5</td>\n",
       "      <td>1750</td>\n",
       "      <td>577.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Photographic-lighting</th>\n",
       "      <td>2700.0</td>\n",
       "      <td>3600</td>\n",
       "      <td>900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Costing Price  Selling Price   Profit\n",
       "Product Name                                                   \n",
       "Mobile-phone                    40500.0          45000   4500.0\n",
       "Screen-Protector                   90.0            300    210.0\n",
       "Sim                               120.0            150     30.0\n",
       "Cover-Case                        440.0           1100    660.0\n",
       "Earphones                        2000.0           2500    500.0\n",
       "Power-Bank                       1250.0           2500   1250.0\n",
       "Camera                          35000.0          50000  15000.0\n",
       "Focus-lens                       9600.0          15000   5400.0\n",
       "SD-card                          1187.5           1250     62.5\n",
       "Camera-holder                    1875.0           2500    625.0\n",
       "Auto-focus-mount-adapter        12495.0          14700   2205.0\n",
       "Tripod-stand                     1172.5           1750    577.5\n",
       "Photographic-lighting            2700.0           3600    900.0"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products"
   ]
  },
  {
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}