withFinalSelectedFeaturesWeights.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a0eb473c",
   "metadata": {},
   "source": [
    "# Pre-requisites"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "327bb8e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#********************** to be run only once ************************************\n",
    "#pip install -U sentence-transformers\n",
    "#pip install --user annoy\n",
    "#export CC=/usr/bin/clang ; export CXX=/usr/bin/clang++ # only if needed\n",
    "#nltk.download('wordnet')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb8a2eef",
   "metadata": {},
   "source": [
    "# Code Starts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4dea0216",
   "metadata": {},
   "outputs": [],
   "source": [
    "import edaSQL\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "from matplotlib import pyplot as plt\n",
    "import time\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0a77866",
   "metadata": {},
   "outputs": [],
   "source": [
    "#DB connection\n",
    "edasql = edaSQL.SQL()\n",
    "edasql.connectToDataBase(server='', #Info removed to comply with the NDA\n",
    "                         database='', #Info removed to comply with the NDA\n",
    "                         user='', #Info removed to comply with the NDA\n",
    "                         password='',#Info removed to comply with the NDA\n",
    "                         sqlDriver='ODBC Driver 17 for SQL Server')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14e93672",
   "metadata": {},
   "source": [
    "# Fetching Data from the Db : Type1s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9a130481",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 30.94 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Getting Type1s data:\n",
    "ctrQuery = \"select * from <> ctr\" #Info removed to comply with the NDA\n",
    "ctrData = pd.read_sql(ctrQuery, edasql.dbConnection)\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "61bbfc1a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'No Info'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Missing values\n",
    "ctrData['Type1ingProcedureType'] = ctrData['Type1ingProcedureType'].replace('', 'No Info')\n",
    "ctrData['Type1ingProcedureType'][13833]\n",
    "\n",
    "ctrData['Type1Type'] = ctrData['Type1Type'].replace('', 'No Info')\n",
    "ctrData['Type1Type'][13833]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3414b017",
   "metadata": {},
   "outputs": [],
   "source": [
    "#set seaborn plotting aesthetics\n",
    "sns.set(rc = {'figure.figsize':(30,20)})\n",
    "\n",
    "#Plot\n",
    "plot1 = sns.countplot(data=ctrData, x=\"Type1ingProcedureType\")\n",
    "plot1.bar_label(plot1.containers[0], size=20)\n",
    "plt.title('Type1s - Type1ing Procedure Type', fontsize=35)\n",
    "plt.xticks(fontsize=20, rotation ='vertical')\n",
    "plt.yticks(fontsize=20)\n",
    "plt.xlabel('Type1ing Procedure Type', fontsize=25)\n",
    "plt.ylabel('Type1s Count', fontsize=25)\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1ccdc728",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 2.49 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Getting Type1s data: parsedPriceBracket, executionDeadlineDays\n",
    "ctrQuery_1 = \"select * from <> ctr \"#Info removed to comply with the NDA\n",
    "ctrData_1 = pd.read_sql(ctrQuery_1, edasql.dbConnection)\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "raw",
   "id": "d6895f36",
   "metadata": {},
   "source": [
    "# parsedPriceBracket \n",
    "#   0 --'<0' \n",
    "#   1 --'0-100000' \n",
    "#\t2 --'100001-500000' \n",
    "#\t3 --'500001-1000000' \n",
    "#\t4 --'1000001-3000000' \n",
    "#\t5 --'3000001-9000000' \n",
    "#\t6 --'9000000-20000000' \n",
    "#\t7 --'20000001-50000000' \n",
    "#\t8 --'50000001-100000000' \n",
    "#\t9 --'100000001-200000000' \n",
    "#\t10 --'200000001-400000000' \n",
    "#\t11 --'400000001-1000000000' \n",
    "#\t12 --'>1000000000'\n",
    "\n",
    "# executionDeadlineDays (Brackets)\n",
    "#   1 --'0-15' \n",
    "#   2 --'15-30' \n",
    "#   3 --'31-45' \n",
    "#   4 --'46-60' \n",
    "#   5 --'61-90' \n",
    "#   6 --'91-120' \n",
    "#   7 --'121-150' \n",
    "#   8 --'151-180' \n",
    "#   9 --'181-270' \n",
    "#   10 --'271-365' \n",
    "#   11 --'366-730' \n",
    "#   12 --'731-1095' \n",
    "#   13 --'1096-1460' \n",
    "#   14 --'1461-1825' \n",
    "#   15 --'>1825'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "62b259d0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 1.10 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Merge Type1s data: with parsedPriceBracket, executionDeadlineDays\n",
    "Data_ctr = []\n",
    "Data_ctr = ctrData.merge(ctrData_1, how='left', on='id')\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd945d58",
   "metadata": {},
   "outputs": [],
   "source": [
    "#set seaborn plotting aesthetics as default\n",
    "sns.set()\n",
    "\n",
    "#define plotting region (2 rows, 2 columns)\n",
    "fig, axs = plt.subplots(1, 2)\n",
    "\n",
    "#Plot\n",
    "plot2 = sns.histplot(data=Data_ctr, x=\"parsedPriceBracket\", ax=axs[0])\n",
    "plot2.axes.set_title(\"Type1s - Parsed Price\",fontsize=50)\n",
    "plot2.set_xlabel(\"Parsed Price Bracket\",fontsize=30)\n",
    "plot2.set_ylabel(\"Type1s Count\",fontsize=20)\n",
    "plot2.bar_label(plot2.containers[0], size=20)\n",
    "plot2.tick_params(labelsize=20)\n",
    "\n",
    "plot3 = sns.histplot(data=Data_ctr, x=\"executionDeadlineDays\", ax=axs[1])\n",
    "plot3.axes.set_title(\"Type1s - Execution DeadLine\",fontsize=50)\n",
    "plot3.bar_label(plot3.containers[0], size=20)\n",
    "plot3.set_xlabel(\"Execution Deadline Days\",fontsize=30)\n",
    "plot3.set_ylabel(\"Type1s Count\",fontsize=20)\n",
    "plot3.bar_label(plot3.containers[0], size=20)\n",
    "plot3.tick_params(labelsize=20)\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "9276fc97",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 2.94 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Getting Type1s data: Category1Split\n",
    "ctrQuery_2 = \"select * from <> ctr cross apply string_split(ctr.Category1,'|')) abc cross apply string_split(abc.Category1,'>')\" #Info removed to comply with the NDA\n",
    "ctrData_2 = pd.read_sql(ctrQuery_2, edasql.dbConnection)\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7470abe5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 0.80 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Merge Type1s data: with Category1Split\n",
    "Data_ctr = Data_ctr.merge(ctrData_2, how='inner', on='id')\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80d3ff83",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Plot\n",
    "sns.scatterplot(data=Data_ctr, x=\"Type1ingProcedureType\", y=\"parsedPriceBracket\", size=\"id\", sizes=(40, 400))\n",
    "plt.title('Type1s distribution', fontsize=35)\n",
    "plt.xticks(fontsize=20, rotation ='vertical')\n",
    "plt.yticks(fontsize=20)\n",
    "plt.xlabel('Type1ing Procedure Type', fontsize=25)\n",
    "plt.ylabel('Parsed price Bracket', fontsize=25)\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "310c3acb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Plot\n",
    "df2 = pd.concat([Data_ctr['Type1ingProcedureType'],Data_ctr['Category1Split']], axis=1)\n",
    "df2 = df2.drop_duplicates()\n",
    "\n",
    "sns.set(rc = {'figure.figsize':(30,20)})\n",
    "plot1 = sns.countplot(data=df2, x=\"Type1ingProcedureType\")\n",
    "plot1.bar_label(plot1.containers[0], size=20)\n",
    "plt.title('Distinct Category1Split count per Type1ing Procedure Type', fontsize=35)\n",
    "plt.xticks(fontsize=20, rotation ='vertical')\n",
    "plt.yticks(fontsize=20)\n",
    "plt.xlabel('Type1ing Procedure Type', fontsize=25)\n",
    "plt.ylabel('Distinct Category1Split Count', fontsize=25)\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "465adc08",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 2.69 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Getting Type1s data: nif\n",
    "ctrQuery_nif = \"select * from  <> ctr union select * from <> nif\" #Info removed to comply with the NDA\n",
    "ctrData_nif = pd.read_sql(ctrQuery_nif, edasql.dbConnection)\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e6905882",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 1.69 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Merge Type1s data: with nif\n",
    "Data_ctr = Data_ctr.merge(ctrData_nif, how='left', on='id')\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "671c756a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = Data_ctr[[\"Type1ingProcedureType\",\"nif\"]]\n",
    "df3 = df3.drop_duplicates()\n",
    "\n",
    "#Plot\n",
    "sns.set(rc = {'figure.figsize':(30,20)})\n",
    "plot1 = sns.countplot(data=df3, x=\"Type1ingProcedureType\")\n",
    "plot1.bar_label(plot1.containers[0], size=20)\n",
    "plt.title('Distinct Buyers per Type1ing Procedure Type', fontsize=35)\n",
    "plt.xticks(fontsize=20, rotation ='vertical')\n",
    "plt.yticks(fontsize=20)\n",
    "plt.xlabel('Type1ing Procedure Type', fontsize=25)\n",
    "plt.ylabel('Distinct Buyers', fontsize=25)\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2bdd2791",
   "metadata": {},
   "outputs": [],
   "source": [
    "df4 = Data_ctr[[\"id\", \"parsedPriceBracket\",\"executionDeadlineDays\"]].reset_index(drop=True)\n",
    "df4 = df4.drop_duplicates().reset_index(drop=True)\n",
    "df4 = df4.pivot_table(index='executionDeadlineDays', columns='parsedPriceBracket', values='id', aggfunc='count')\n",
    "\n",
    "#Plot\n",
    "ax = sns.heatmap(df4,cmap=\"PiYG\", annot=True, fmt=\".0f\", annot_kws={\"fontsize\":20})\n",
    "cbar = ax.collections[0].colorbar\n",
    "cbar.ax.tick_params(labelsize=20)\n",
    "plt.title('Type1s Data', fontsize=35)\n",
    "plt.xticks(fontsize=20)\n",
    "plt.yticks(fontsize=20, rotation ='vertical')\n",
    "plt.xlabel('Execution Deadline Days Bracket', fontsize=25)\n",
    "plt.ylabel('Parsed Price Bracket', fontsize=25)\n",
    "plt.show()\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a9f6a77",
   "metadata": {},
   "source": [
    "# Fetching Data from the Db : Type2s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "55ef9556",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 8.93 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Getting Type2s data:\n",
    "Type2sQuery = \"select * from <> Type2s\" #Info removed to comply with the NDA\n",
    "Type2sData = pd.read_sql(Type2sQuery, edasql.dbConnection)\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "42328b50",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 0.42 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Getting Type2s data: parsedPriceBracket, executionDeadlineDays\n",
    "Type2sQuery_1 = \"select * from <> Type2s\" #Info removed to comply with the NDA\n",
    "Type2sData_1 = pd.read_sql(Type2sQuery_1, edasql.dbConnection)\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "6d3c8ce6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 0.19 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Merge Type2s data: with parsedPriceBracket, executionDeadlineDays\n",
    "Data_Type2s = []\n",
    "Data_Type2s = Type2sData.merge(Type2sData_1, how='left', on='id')\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ba7e4ef5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 0.60 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Getting Type2s data: Category1Split\n",
    "Type2sQuery_2 = \"select * from <> Type2s cross apply string_split(Type2s.Category1,'|')) abc cross apply string_split(abc.Category1,'>')\" #Info removed to comply with the NDA\n",
    "Type2sData_2 = pd.read_sql(Type2sQuery_2, edasql.dbConnection)\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "6edfba13",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 0.11 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Merge Type2s data: with Category1Split\n",
    "Data_Type2s = Data_Type2s.merge(Type2sData_2, how='inner', on='id')\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "cbc2d2c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 0.50 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Getting Type2s data: nif\n",
    "Type2sQuery_nif = \"select * from <> Type2s\" #Info removed to comply with the NDA\n",
    "Type2sData_nif = pd.read_sql(Type2sQuery_nif, edasql.dbConnection)\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "00522bce",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 0.13 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Merge Type2s data: with nif\n",
    "Data_Type2s = Data_Type2s.merge(Type2sData_nif, how='left', on='id')\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41a5a0b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#set seaborn plotting aesthetics as default\n",
    "sns.set()\n",
    "\n",
    "#define plotting region (2 rows, 2 columns)\n",
    "fig, axs = plt.subplots(1, 2)\n",
    "\n",
    "#Plot\n",
    "plot2 = sns.countplot(data=Data_Type2s, x=\"parsedPriceBracket\", ax=axs[0])\n",
    "plot2.axes.set_title(\"Type2s - Parsed Price\",fontsize=50)\n",
    "plot2.set_xlabel(\"Parced Price Bracket\",fontsize=30)\n",
    "plot2.set_ylabel(\"Type2s Count\",fontsize=20)\n",
    "plot2.bar_label(plot2.containers[0], size=20)\n",
    "plot2.tick_params(labelsize=20)\n",
    "\n",
    "df5 = Data_Type2s[[\"parsedPriceBracket\",\"nif\"]].reset_index(drop=True)\n",
    "df5 = df5.drop_duplicates().reset_index(drop=True)\n",
    "\n",
    "#Plot\n",
    "plot3 = sns.countplot(data=df5, x=\"parsedPriceBracket\", ax=axs[1])\n",
    "plot3.axes.set_title(\"Buyers - Parsed Price\",fontsize=50)\n",
    "plot3.bar_label(plot3.containers[0], size=20)\n",
    "plot3.set_xlabel(\"Parced Price Bracket\",fontsize=30)\n",
    "plot3.set_ylabel(\"Distinct Buyers Count\",fontsize=20)\n",
    "plot3.bar_label(plot3.containers[0], size=20)\n",
    "plot3.tick_params(labelsize=20)\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0cf5ddbe",
   "metadata": {},
   "source": [
    "# Combine Type1s and Type2s into single df for Location info merging"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "c014361a",
   "metadata": {},
   "outputs": [],
   "source": [
    "fullData =  pd.concat([Data_ctr, Data_Type2s], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "3106c613",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 0.62 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Getting data: loc\n",
    "Query_loc = \"select * \" #Info removed to comply with the NDA\n",
    "Data_loc = pd.read_sql(Query_loc, edasql.dbConnection)\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "0252a0fc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 0.70 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Merge loc data:\n",
    "Full_Data = fullData.merge(Data_loc, how='left', on='nif')\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "971120af",
   "metadata": {},
   "outputs": [],
   "source": [
    "Full_Data['country_nif'] = Full_Data['country_nif'].str.capitalize()\n",
    "Full_Data['country'] = np.where((Full_Data['country_orig'] == 'NA'), Full_Data['country_nif'], Full_Data['country_orig'])\n",
    "Full_Data['country'] = np.where((Full_Data['country'] == None), Full_Data['country_nif'], Full_Data['country'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "227ee882",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 0.10 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Getting Geo data\n",
    "Query_geo = \"select *\" #Info removed to comply with the NDA\n",
    "Data_geo = pd.read_sql(Query_geo, edasql.dbConnection)\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "2fa49fcc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 0.87 s to compute.\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "\n",
    "# Merge Geo data:\n",
    "Full_Data = Full_Data.merge(Data_geo, how='left', on='county')\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "1fbd38f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "Full_Data[\"country_geo\"].fillna(\"No Info\", inplace = True)\n",
    "Full_Data[\"country_orig\"].fillna(\"No Info\", inplace = True)\n",
    "Full_Data.country_orig[Full_Data['country_orig'].str.contains('Portugal')] = 'Portugal'\n",
    "Full_Data.country_orig[(Full_Data['country_orig'].str.contains('<BR/>'))& (Full_Data['country_geo']!= 'Portugal')] = 'Multiple'\n",
    "Full_Data.country_orig[(Full_Data['country_orig'].str.contains('<BR/>'))& (Full_Data['country_geo']== 'Portugal')] = 'Portugal'\n",
    "Full_Data.country_orig[Full_Data['country_orig'] == ''] = 'No Info'\n",
    "Full_Data.country_orig[Full_Data['country_orig'] == 'NA'] = 'No Info'\n",
    "\n",
    "data_df = Full_Data.drop(columns = ['country', 'country_nif', 'country_geo', 'Type1Count'])\n",
    "data_df.rename(columns = {'country_orig':'country'}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84f9c9f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#Plot\n",
    "sns.set(rc = {'figure.figsize':(30,20)})\n",
    "plot1 = sns.countplot(data=Full_Data, x=\"ctr_Type2s\")\n",
    "plot1.bar_label(plot1.containers[0], size=20)\n",
    "plt.title('Type1s and Type2s per Country of Origin', fontsize=35)\n",
    "plt.xticks(fontsize=20, rotation ='vertical')\n",
    "plt.yticks(fontsize=20)\n",
    "plt.xlabel('Type1s and Type2s', fontsize=25)\n",
    "plt.ylabel('Count', fontsize=25)\n",
    "#plt.legend(loc='upper right')\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f2b803f",
   "metadata": {},
   "source": [
    "# Ordinal Encoding for Categorical Features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "4477eecf",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df[\"ctr_Type2s\"] = data_df[\"ctr_Type2s\"].astype('category')\n",
    "data_df[\"Type1ingProcedureType\"] = data_df[\"Type1ingProcedureType\"].astype('category')\n",
    "data_df[\"Type1Type\"] = data_df[\"Type1Type\"].astype('category')\n",
    "data_df[\"parsedPriceBracket\"] = data_df[\"parsedPriceBracket\"].astype('category')\n",
    "data_df[\"executionDeadlineDays\"] = data_df[\"executionDeadlineDays\"].astype('category')\n",
    "#data_df[\"Category1Split\"] = data_df[\"Category1Split\"].astype('category')\n",
    "data_df[\"nif\"] = data_df[\"nif\"].astype('category')\n",
    "data_df[\"county\"] = data_df[\"county\"].astype('category')\n",
    "data_df[\"country\"] = data_df[\"country\"].astype('category')\n",
    "\n",
    "data_df[\"ctr_Type2s\"] = data_df[\"ctr_Type2s\"].cat.codes\n",
    "#data_df[\"Type1ingProcedureType\"] = data_df[\"Type1ingProcedureType\"].cat.codes\n",
    "#data_df[\"Type1Type\"] = data_df[\"Type1Type\"].cat.codes\n",
    "data_df[\"parsedPriceBracket\"] = data_df[\"parsedPriceBracket\"].cat.codes\n",
    "data_df[\"executionDeadlineDays\"] = data_df[\"executionDeadlineDays\"].cat.codes\n",
    "#data_df[\"Category1Split\"] = data_df[\"Category1Split\"].cat.codes\n",
    "data_df[\"nif\"] = data_df[\"nif\"].cat.codes\n",
    "data_df[\"county\"] = data_df[\"county\"].cat.codes\n",
    "data_df[\"country\"] = data_df[\"country\"].cat.codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "ea8e9779",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df = data_df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ae9615d",
   "metadata": {},
   "source": [
    "# Fetching Category1 Text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "8b579da6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#The csv is off the internet https://simap.ted.europa.eu/cpv\n",
    "Category1_data = pd.read_csv('./Category1.csv')  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "5b2c04c1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 12.43 s to compute.\n"
     ]
    }
   ],
   "source": [
    "#Text Lemmetizer for Category1 Text\n",
    "import nltk\n",
    "import re\n",
    "from nltk.corpus import stopwords\n",
    "from nltk.stem import WordNetLemmatizer\n",
    "\n",
    "lemmatizer = WordNetLemmatizer()\n",
    "\n",
    "def lemmatize_words(text):\n",
    "    text = re.sub('\\d', ' ', text)\n",
    "    text = text.lower()\n",
    "    words = text.split()\n",
    "    words = [lemmatizer.lemmatize(word,pos='v') for word in words if not word in set(stopwords.words('portuguese'))]\n",
    "    return ' '.join(words)\n",
    "\n",
    "start = time.time()\n",
    "\n",
    "Category1_data['Category1_text_lemm'] = Category1_data['Category1_text'].apply(lemmatize_words)\n",
    "\n",
    "end = time.time()\n",
    "# Total time to compute\n",
    "\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6eabd6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df\n",
    "\n",
    "#Cleared Output to comply with the NDA"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ba187cc",
   "metadata": {},
   "source": [
    "# Sampling for Faster Processing"
   ]
  },
  {
   "cell_type": "raw",
   "id": "7bbd0ec6",
   "metadata": {},
   "source": [
    "#Using Subset of the data as the dataset is huge while processing capacity is limited (on my laptop).\n",
    "#This same code can be deployed using scalable cpu for the whole dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "30a14edc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#using Type1 nos from the test set\n",
    "ctr_list = [9054597, 9420880, 5415030, 6999583, 6732455, 9542508, 9686722, 5422329, 5500953, 5364767, 5717386, 5673208, 7763238, 7330587, 6751870, 7326750, 7373595, 6819221, 5883632, 7780646, 8462477, 9177328, 6038559, 9208104, 5434232, 8856494, 7746505, 5663839, 9212307, 6055936, 9635760, 9268166, 6503057, 9203827, 5245381, 7718927, 5757117, 9345133, 8615546, 7114808, 7348302, 9605674, 7824757, 9375703, 5365166, 8881176, 7594591, 7377455, 5744864, 6858177, 9253429, 7724046, 7666846, 9663741, 9458036, 8344201, 7633924, 7726982, 9098094, 6199018, 7656179, 6222397, 6510959, 9160613, 5169070, 5859011, 5709116, 5872122, 7721125, 7356135, 9376824, 6426667, 6092806, 6103349, 5785867, 7864060, 9648480, 9304174, 9345223, 5680498, 8877987, 6446423, 8407049, 7695452, 5153477, 9481223, 9592921, 6025307, 7258963, 8968718, 9544630, 9321672, 9212973, 8537251, 9432577, 9602095, 6131945, 6092116, 6407988, 7495110, 6307468, 6784489, 6625446, 7668795, 7496238, 9432874, 7745470, 8409478, 9405929, 7662634, 7410056, 6635838, 6710639, 8651658, 7844298, 7573826, 9191202, 5861454, 9499682, 7790390, 7422692, 6246256, 9061928, 5536539, 7350961, 6339633, 7535704, 5508144, 7862268, 7586320, 7151370, 9329015, 9127999, 5786997, 5946610, 5718659, 9595799, 7690093, 7249124, 9150498, 7233678, 9417152, 5979808, 9603850, 7670115, 5554267, 7356688, 9635466, 6682745, 9649540, 7387368, 6544598, 7618756, 7534366, 5224338, 7477268, 7636536, 7490444, 6510652, 7977039, 6477882, 5248884, 8880142, 8888253, 9335502, 6738297, 5506629, 6097991, 5531357, 7886630, 7534059, 6555458, 5836326, 5627843, 6051533, 7835012, 9511077, 6005969, 9575858, 9450746, 9100625, 5618104, 9334063, 5856386, 8509651, 7804371, 5417744, 7352272, 8454066, 8108119, 9070241, 6662594, 8893570, 5904892, 5248587, 7215932, 5772926, 8537836, 7245108, 7534088]\n",
    "\n",
    "#plus 10K Type1s\n",
    "data_df_ctr = data_df[['ctr_Type2s', 'id', 'objectBriefDescription', 'lemm', 'parsedPriceBracket', 'Category1Split']][data_df.ctr_Type2s == 1]\n",
    "data_df_ctr = data_df_ctr.drop_duplicates()\n",
    "data_df_ctr = data_df_ctr.merge(Category1_data, how='inner', on='Category1Split')\n",
    "samp_ctr = data_df_ctr.head(10000)\n",
    "samp_ctr = samp_ctr.append(data_df_ctr[data_df_ctr['id'].isin(ctr_list)]).reset_index(drop=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "002b307a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#using Type2s nos from the test set\n",
    "Type2s_list = [332346, 335271, 328817, 324530, 337427, 415377084, 415377376, 336657, 332056, 323268, 328236, 335086, 337743, 327575, 337113, 331022, 323093, 332294, 326363, 334728, 333915, 324093, 327220, 327077, 333674, 336171, 415381263, 333708, 329672, 322798, 337146, 326743, 326947, 337459, 329770, 334049, 334706, 331674, 336695, 328725, 322869, 329778, 335592, 324644, 415343193, 325589, 331901, 325563, 329994, 331889, 328902, 332031, 322220, 334147, 415817838, 197060, 188762, 214941, 87573, 415337612, 327192, 320479, 327674, 330527, 253576, 92102, 325172, 110397, 138838, 90899, 188661, 69756, 335484, 83803, 259381, 305933, 76898, 337267, 336594, 195666, 77636, 304422, 257582, 337473, 337133, 334947, 328806, 334729, 337870, 195827, 337840, 335232, 186158, 338020, 337979, 126090, 319345, 58837, 336628, 337850, 328299, 210305, 322192, 331611, 334465, 337934, 331595, 216768, 338058, 337855, 321056, 334818, 334844, 332731, 328191, 303549, 260547, 335867, 326454, 337443, 337247, 338096, 159886, 336933, 335654, 335189, 332652, 336768, 334988, 335035, 335004, 213760, 276117, 263661, 335715, 336970, 337063, 303040, 337559, 336182, 232269, 185439, 98450, 335968, 337236, 336815, 302330, 213854, 230848, 328719, 338063, 336974, 337269, 337880, 337844, 337772, 337035, 335537, 336305, 335553, 326630, 336018, 336142, 337950, 337781, 338054, 319739, 304790, 338049, 254608, 337463, 337906, 321485, 335285, 337588, 154863, 333239, 335716, 415402639, 336391, 336930, 260707, 337706, 332450, 337464, 336370, 337485, 305348, 322278, 327112, 337287, 331983, 336013, 330610, 280855, 337483, 73189, 335480, 338073, 318485, 337981, 338027, 184153, 219700, 337996, 196252, 337310, 332880, 337218, 415403108, 308028, 259553]\n",
    "\n",
    "#plus 10K Type2s\n",
    "data_df_Type2s = data_df[['ctr_Type2s', 'id', 'objectBriefDescription', 'lemm', 'parsedPriceBracket', 'Category1Split']][(data_df.ctr_Type2s == 0)]\n",
    "data_df_Type2s = data_df_Type2s.drop_duplicates()\n",
    "data_df_Type2s = data_df_Type2s.merge(Category1_data, how='inner', on='Category1Split')\n",
    "samp_Type2s = data_df_Type2s.head(10000)\n",
    "samp_Type2s = samp_Type2s.append(data_df_Type2s[data_df_Type2s['id'].isin(Type2s_list)]).reset_index(drop=True) \n",
    "samp_Type2s['Category1_text'] = samp_Type2s['Category1_text'].fillna('Não definido.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "ebef1972",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Saving the intermediate data\n",
    "samp_ctr.to_csv(\"./samp_ctr.csv\")\n",
    "samp_Type2s.to_csv(\"./samp_Type2s.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd6ad252",
   "metadata": {},
   "source": [
    "# Removing Numerals and Special Characters from the lemmetised text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "426cf59c",
   "metadata": {},
   "outputs": [],
   "source": [
    "samp_ctr['lemm'] = samp_ctr['lemm'].str.replace('\\w*\\d\\w*', ' ', regex=True)\n",
    "samp_ctr['lemm'] = samp_ctr['lemm'].str.replace('_', ' ', regex=True)\n",
    "samp_ctr['lemm'] = samp_ctr['lemm'].str.replace('\\W', ' ', regex=True)\n",
    "samp_ctr['lemm'] = samp_ctr['lemm'].str.replace('\\\\b\\\\w{1,2}\\\\b', ' ', regex=True)\n",
    "\n",
    "samp_ctr['lemm'] = samp_ctr['lemm'].str.strip()\n",
    "samp_ctr = samp_ctr[samp_ctr['lemm'] != '']\n",
    "samp_ctr.drop_duplicates()\n",
    "samp_ctr.reset_index(inplace = True,drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "32a5849f",
   "metadata": {},
   "outputs": [],
   "source": [
    "samp_Type2s['lemm'] = samp_Type2s['lemm'].str.replace('\\w*\\d\\w*', ' ', regex=True)\n",
    "samp_Type2s['lemm'] = samp_Type2s['lemm'].str.replace('_', ' ', regex=True)\n",
    "samp_Type2s['lemm'] = samp_Type2s['lemm'].str.replace('\\W', ' ', regex=True)\n",
    "samp_Type2s['lemm'] = samp_Type2s['lemm'].str.replace('\\\\b\\\\w{1,2}\\\\b', ' ', regex=True)\n",
    "\n",
    "samp_Type2s['lemm'] = samp_Type2s['lemm'].str.strip()\n",
    "samp_Type2s = samp_Type2s[samp_Type2s['lemm'] != '']\n",
    "samp_Type2s.drop_duplicates()\n",
    "samp_Type2s.reset_index(inplace = True,drop = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ccf7444",
   "metadata": {},
   "source": [
    "# Text Embedding Using distiluse-base-multilingual-cased-v1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "2b33bada",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 10202 records to process today\n",
      "10202 records are added\n",
      "\n",
      "This function took 465.69 s to compute.\n"
     ]
    }
   ],
   "source": [
    "#Text Embedding for Type1 Titles\n",
    "\n",
    "from sentence_transformers import SentenceTransformer\n",
    "model = SentenceTransformer('distiluse-base-multilingual-cased-v1')\n",
    "\n",
    "start = time.time()\n",
    "embeddings_ctr_2 = []\n",
    "\n",
    "n = len(samp_ctr['lemm'])\n",
    "\n",
    "print(f\"There are {n} records to process today\")\n",
    "embeddings_ctr_2 = model.encode(samp_ctr['lemm'])\n",
    "print(f\"{n} records are added\")\n",
    "\n",
    "end = time.time()\n",
    "# Total time to compute\n",
    "\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "a8d1f962",
   "metadata": {},
   "outputs": [],
   "source": [
    "ctr_embeds_df_2 = pd.DataFrame(embeddings_ctr_2)\n",
    "ctr_embeds_df_2.columns = ['col_'+str(num) for num in range(embeddings_ctr_2.shape[1])]\n",
    "ctr_embeds_df_2.to_csv(\"./ctr_embeds_df_2.csv\") #saving intermediate data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "f973c02d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 10202 records to process today\n",
      "10202 records are added\n",
      "\n",
      "This function took 115.84 s to compute.\n"
     ]
    }
   ],
   "source": [
    "#Text Embedding for Type1s Category1\n",
    "\n",
    "from sentence_transformers import SentenceTransformer\n",
    "model = SentenceTransformer('distiluse-base-multilingual-cased-v1')\n",
    "\n",
    "start = time.time()\n",
    "embeddings_ctr_Category1 = []\n",
    "\n",
    "n = len(samp_ctr['Category1_text_lemm'])\n",
    "\n",
    "print(f\"There are {n} records to process today\")\n",
    "embeddings_ctr_Category1 = model.encode(samp_ctr['Category1_text_lemm'])\n",
    "print(f\"{n} records are added\")\n",
    "\n",
    "end = time.time()\n",
    "# Total time to compute\n",
    "\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "103228b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "ctr_embeds_df_Category1 = pd.DataFrame(embeddings_ctr_Category1)\n",
    "ctr_embeds_df_Category1.columns = ['col_'+str(num) for num in range(embeddings_ctr_Category1.shape[1])]\n",
    "ctr_embeds_df_Category1.to_csv(\"./ctr_embeds_df_Category1.csv\") #saving intermediate data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "fe1a1952",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 10153 records to process today\n",
      "10153 records are added\n",
      "\n",
      "This function took 408.77 s to compute.\n"
     ]
    }
   ],
   "source": [
    "#Text Embedding for Ad Titles\n",
    "\n",
    "from sentence_transformers import SentenceTransformer\n",
    "model = SentenceTransformer('distiluse-base-multilingual-cased-v1')\n",
    "\n",
    "start = time.time()\n",
    "embeddings_Type2s_2 = []\n",
    "\n",
    "n = len(samp_Type2s['lemm'])\n",
    "\n",
    "print(f\"There are {n} records to process today\")\n",
    "embeddings_Type2s_2 = model.encode(samp_Type2s['lemm'])\n",
    "print(f\"{n} records are added\")\n",
    "\n",
    "end = time.time()\n",
    "# Total time to compute\n",
    "\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "47f81d8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "Type2s_embeds_df_2 = pd.DataFrame(embeddings_Type2s_2)\n",
    "Type2s_embeds_df_2.columns = ['col_'+str(num) for num in range(embeddings_Type2s_2.shape[1])]\n",
    "Type2s_embeds_df_2.to_csv(\"./Type2s_embeds_df_2\") #saving intermediate data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "1c161348",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 10153 records to process today\n",
      "10153 records are added\n",
      "\n",
      "This function took 3853.54 s to compute.\n"
     ]
    }
   ],
   "source": [
    "#Text Embedding for Type2s Category1\n",
    "\n",
    "from sentence_transformers import SentenceTransformer\n",
    "model = SentenceTransformer('distiluse-base-multilingual-cased-v1')\n",
    "\n",
    "start = time.time()\n",
    "embeddings_Type2s_Category1 = []\n",
    "\n",
    "n = len(samp_Type2s['Category1_text_lemm'])\n",
    "\n",
    "print(f\"There are {n} records to process today\")\n",
    "embeddings_Type2s_Category1 = model.encode(samp_Type2s['Category1_text_lemm'])\n",
    "print(f\"{n} records are added\")\n",
    "\n",
    "end = time.time()\n",
    "# Total time to compute\n",
    "\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "13625afc",
   "metadata": {},
   "outputs": [],
   "source": [
    "Type2s_embeds_df_Category1 = pd.DataFrame(embeddings_Type2s_Category1)\n",
    "Type2s_embeds_df_Category1.columns = ['col_'+str(num) for num in range(embeddings_Type2s_Category1.shape[1])]\n",
    "Type2s_embeds_df_Category1.to_csv(\"./Type2s_embeds_df_Category1.csv\") #saving intermediate data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7dca24cc",
   "metadata": {},
   "source": [
    "# Trial weighted features - text, Category1split, nif, parsedPriceBracket"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "68429329",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Weighing features + sequencing for priority : Type1s\n",
    "final_ctr = pd.concat([ctr_embeds_df_2 * 0.6, ctr_embeds_df_Category1 * 0.35, samp_ctr['parsedPriceBracket'] * 0.05], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "8afd4baa",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Weighing features + sequencing for priority : Type2s\n",
    "final_Type2s = pd.concat([Type2s_embeds_df_2 * 0.6, Type2s_embeds_df_Category1 * 0.35, samp_Type2s['parsedPriceBracket'] * 0.05], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f84a7413",
   "metadata": {},
   "source": [
    "# Build the ANNOY index using weighted features"
   ]
  },
  {
   "cell_type": "raw",
   "id": "4e875ce2",
   "metadata": {},
   "source": [
    "#ANNOY works using indexes and not identifiers like Type1_id or ad_id. \n",
    "#Hence they always need to be converted to indexes while testing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "8227c55a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 56.36 s to compute.\n"
     ]
    }
   ],
   "source": [
    "#Using ANNOY: Approximate Nearest Neighbours Oh yeah! by Spotify for Type1s\n",
    "from annoy import AnnoyIndex\n",
    "\n",
    "import time\n",
    "start = time.time()\n",
    "\n",
    "f = final_ctr.shape[1]\n",
    "\n",
    "t_ctr_ftr_2_wt = AnnoyIndex(f, 'angular')  #Length of item vector that will be indexed\n",
    "\n",
    "for index, row in final_ctr.iterrows():\n",
    "    t_ctr_ftr_2_wt.add_item(index, row)\n",
    "\n",
    "t_ctr_ftr_2_wt.build(10) # 10 trees\n",
    "t_ctr_ftr_2_wt.save('test_ctr_ftr_2_wt.ann')\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "70aa6426",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "This function took 60.64 s to compute.\n"
     ]
    }
   ],
   "source": [
    "#Using ANNOY: Approximate Nearest Neighbours Oh yeah! by Spotify for Type2s\n",
    "from annoy import AnnoyIndex\n",
    "\n",
    "import time\n",
    "start = time.time()\n",
    "\n",
    "f = final_Type2s.shape[1]\n",
    "\n",
    "t_Type2s_ftr_2_wt = AnnoyIndex(f, 'angular')  #Length of item vector that will be indexed\n",
    "\n",
    "for index, row in final_Type2s.iterrows():\n",
    "    t_Type2s_ftr_2_wt.add_item(index, row)\n",
    "\n",
    "t_Type2s_ftr_2_wt.build(10) # 10 trees\n",
    "t_Type2s_ftr_2_wt.save('test_Type2s_ftr_2_wt.ann')\n",
    "\n",
    "end = time.time()\n",
    "\n",
    "# Total time to compute\n",
    "print('\\nThis function took {:.2f} s to compute.'.format(end - start))   "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5537c1e9",
   "metadata": {},
   "source": [
    "# Testing Scripts : Type1s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4c71bb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#TC to find Top Matches for a list of Type1s\n",
    "\n",
    "c_no_list = [9054597, 9420880, 5415030, 6999583, 6732455, 9542508, 9686722, 5422329, 5500953, 5364767, 5717386]\n",
    "\n",
    "u = AnnoyIndex(f, 'angular')\n",
    "u.load('test_ctr_ftr_2_wt.ann') # super fast, will just mmap the file\n",
    "ctr_top_matches_df = pd.DataFrame()\n",
    "\n",
    "for c_no in c_no_list:\n",
    "    result_df  = pd.DataFrame()\n",
    "    indx = samp_ctr.index[samp_ctr['id'] == c_no].to_list()\n",
    "    result_df = pd.DataFrame(u.get_nns_by_item(indx[0], 10 )) # will find the 10 nearest neighbors\n",
    "    result_df['rank']= result_df.index+1\n",
    "    result_df['ref_Type1_no'] = c_no\n",
    "    result_df.rename(columns = {0:'index'}, inplace = True)\n",
    "\n",
    "    abc = samp_ctr[samp_ctr.index.isin(result_df['index'])]\n",
    "    abc['index']= abc.index\n",
    "    result_df = result_df.merge(abc, how='inner',on='index')\n",
    "    ctr_top_matches_df = ctr_top_matches_df.append(result_df)\n",
    "    \n",
    "ctr_top_matches_df\n",
    "#ctr_top_matches_df.to_csv(\"./top_10_matches_Type1s.csv\")\n",
    "\n",
    "#Cleared Output to comply with the NDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd9ee210",
   "metadata": {},
   "outputs": [],
   "source": [
    "#TC to check if a Type1 matches the list of given Type1s [one Type1 at a time]\n",
    "\n",
    "c_no = 7348302\n",
    "c_list = [7534059, 6555458, 5836326]\n",
    "\n",
    "\n",
    "indx = samp_ctr.index[samp_ctr['id'] == c_no].tolist()\n",
    "print(f\"Type1 No.: {c_no} index: {indx}\")\n",
    "print(\"\\nIndexes of Type1s to be matched: \")\n",
    "print(samp_ctr[samp_ctr['id'].isin(c_list)]['id'])\n",
    "lst = samp_ctr[samp_ctr['id'].isin(c_list)].index\n",
    "\n",
    "u = AnnoyIndex(f, 'angular')\n",
    "u.load('test_ctr_ftr_2_wt.ann') # super fast, will just mmap the file\n",
    "result = pd.DataFrame()\n",
    "result['Col1'] = pd.DataFrame(u.get_nns_by_item(indx[0], 863 )) # will find the 1000 nearest neighbors\n",
    "print(\"\\nMatch Result# Testing Scripts : Type1ss\")\n",
    "res = result[result['Col1'].isin(lst)].Col1\n",
    "print(f\"{len(res)} records match\")\n",
    "res\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b02cf5d",
   "metadata": {},
   "source": [
    "# Testing Scripts : Type2s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cca90f32",
   "metadata": {},
   "outputs": [],
   "source": [
    "#TC to find Top Matches for a list of Type2s\n",
    "\n",
    "a_no_list = [337743, 327575, 337113, 331022, 323093, 332294, 326363, 334728, 333915, 324093, 327220, 327077]\n",
    "\n",
    "u = AnnoyIndex(f, 'angular')\n",
    "u.load('test_Type2s_ftr_2_wt.ann') # super fast, will just mmap the file\n",
    "Type2s_top_matches_df = pd.DataFrame()\n",
    "\n",
    "for a_no in a_no_list:\n",
    "    result_df  = pd.DataFrame()\n",
    "    indx = samp_Type2s.index[samp_Type2s['id'] == a_no].to_list()\n",
    "    result_df = pd.DataFrame(u.get_nns_by_item(indx[0], 10 )) # will find the 10 nearest neighbors\n",
    "    result_df['rank']= result_df.index+1\n",
    "    result_df['ref_ad_no'] = a_no\n",
    "    result_df.rename(columns = {0:'index'}, inplace = True)\n",
    "\n",
    "    abc = samp_Type2s[samp_Type2s.index.isin(result_df['index'])]\n",
    "    abc['index']= abc.index\n",
    "    result_df = result_df.merge(abc, how='inner',on='index')\n",
    "    Type2s_top_matches_df = Type2s_top_matches_df.append(result_df)\n",
    "    \n",
    "Type2s_top_matches_df\n",
    "#Type2s_top_matches_df.to_csv(\"./top_10_matches_Type1s.csv\")\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14bd0409",
   "metadata": {},
   "outputs": [],
   "source": [
    "#TC to check if the an ad matches the list of given Type2s [one Ad at a time]\n",
    "\n",
    "a_no = 327077\n",
    "a_list = [159886, 336933, 335654]\n",
    "\n",
    "\n",
    "indx = samp_Type2s.index[samp_Type2s['id'] == a_no].tolist()\n",
    "print(f\"Ad No.: {a_no} index: {indx}\")\n",
    "\n",
    "print(\"\\nIndexes of Type2s to be matched: \")\n",
    "print(samp_Type2s[samp_Type2s['id'].isin(a_list)]['id'])\n",
    "\n",
    "lst = samp_Type2s[samp_Type2s['id'].isin(a_list)].index\n",
    "\n",
    "u = AnnoyIndex(f, 'angular')\n",
    "u.load('test_Type2s_ftr_2_wt.ann') # super fast, will just mmap the file\n",
    "\n",
    "result = pd.DataFrame()\n",
    "result['Col1'] = pd.DataFrame(u.get_nns_by_item(indx[0], 1000 )) # will find the 1000 nearest neighbors\n",
    "\n",
    "print(\"\\nMatch Result# Testing Scripts : Type2s\")\n",
    "res = result[result['Col1'].isin(lst)].Col1\n",
    "print(f\"{len(res)} records match\")\n",
    "res\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3009c65",
   "metadata": {},
   "source": [
    "# Testing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19584cd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#TC to find Top Matches for a list of Type1s\n",
    "\n",
    "c_no_list = [9054597, 9420880, 5415030, 6999583, 6732455, 9542508, 9686722, 5422329, 5500953, 5364767, 5717386]\n",
    "\n",
    "u = AnnoyIndex(f, 'angular')\n",
    "u.load('test_ctr_ftr_2_wt.ann') # super fast, will just mmap the file\n",
    "ctr_top_matches_df = pd.DataFrame()\n",
    "\n",
    "for c_no in c_no_list:\n",
    "    result_df  = pd.DataFrame()\n",
    "    indx = samp_ctr.index[samp_ctr['id'] == c_no].to_list()\n",
    "    result_df = pd.DataFrame(u.get_nns_by_item(indx[0], 10 )) # will find the 10 nearest neighbors\n",
    "    result_df['rank']= result_df.index+1\n",
    "    result_df['ref_Type1_no'] = c_no\n",
    "    result_df.rename(columns = {0:'index'}, inplace = True)\n",
    "\n",
    "    abc = samp_ctr[samp_ctr.index.isin(result_df['index'])]\n",
    "    abc['index']= abc.index\n",
    "    result_df = result_df.merge(abc, how='inner',on='index')\n",
    "    ctr_top_matches_df = ctr_top_matches_df.append(result_df)\n",
    "    \n",
    "ctr_top_matches_df\n",
    "#ctr_top_matches_df.to_csv(\"./top_10_matches_Type1s.csv\")\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54ab2c48",
   "metadata": {},
   "outputs": [],
   "source": [
    "#TC to check if the given Type1s match in the output for a single Type1\n",
    "\n",
    "c_no = 9054597\n",
    "c_list = [9253429, 7724046, 7666846]\n",
    "\n",
    "\n",
    "indx = samp_ctr.index[samp_ctr['id'] == c_no].tolist()\n",
    "print(f\"Type1 No.: {c_no} index: {indx}\")\n",
    "print(\"\\nIndexes of Type1s to be matched: \")\n",
    "print(samp_ctr[samp_ctr['id'].isin(c_list)]['id'])\n",
    "lst = samp_ctr[samp_ctr['id'].isin(c_list)].index\n",
    "\n",
    "u = AnnoyIndex(f, 'angular')\n",
    "u.load('test_ctr_ftr_2_wt.ann') # super fast, will just mmap the file\n",
    "result = pd.DataFrame()\n",
    "result['Col1'] = pd.DataFrame(u.get_nns_by_item(indx[0], 600 )) # will find the 1000 nearest neighbors\n",
    "print(\"\\nMatch Result# Testing Scripts : Type1ss\")\n",
    "res = result[result['Col1'].isin(lst)].Col1\n",
    "print(f\"{len(res)} records match\")\n",
    "res\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e683e7b3",
   "metadata": {},
   "source": [
    "# Scripts to Find the Top n Matches for given Type1 or Type2 No."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad75a008",
   "metadata": {},
   "outputs": [],
   "source": [
    "#To find the top n matches for given Type1 c_no\n",
    "\n",
    "c_no = 9054597 # Enter the Type1 no.\n",
    "n = 10 #Enter the no. of top matches you need for the given Type1 no.\n",
    "\n",
    "u = AnnoyIndex(f, 'angular')\n",
    "u.load('test_ctr_ftr_2_wt.ann') # super fast, will just mmap the file\n",
    "ctr_top_matches_df = pd.DataFrame()\n",
    "\n",
    "result_df  = pd.DataFrame()\n",
    "indx = samp_ctr.index[samp_ctr['id'] == c_no].to_list()\n",
    "result_df = pd.DataFrame(u.get_nns_by_item(indx[0], n )) # will find the 10 nearest neighbors\n",
    "result_df['rank']= result_df.index+1\n",
    "result_df['ref_Type1_no'] = c_no\n",
    "result_df.rename(columns = {0:'index'}, inplace = True)\n",
    "\n",
    "abc = samp_ctr[samp_ctr.index.isin(result_df['index'])]\n",
    "abc['index']= abc.index\n",
    "result_df = result_df.merge(abc, how='inner',on='index')\n",
    "\n",
    "print(f\"Top {n} matches for Type1 no.: {c_no} are\")\n",
    "result_df\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f55fa0c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#To find the top n matches for given Type2 a_no\n",
    "\n",
    "a_no = 337743 # Enter the Tendor(Ad) no.\n",
    "n = 10 #Enter the no. of top matches you need for the given Type1 no.\n",
    "\n",
    "u = AnnoyIndex(f, 'angular')\n",
    "u.load('test_Type2s_ftr_2_wt.ann') # super fast, will just mmap the file\n",
    "Type2s_top_matches_df = pd.DataFrame()\n",
    "\n",
    "result_df  = pd.DataFrame()\n",
    "indx = samp_Type2s.index[samp_Type2s['id'] == a_no].to_list()\n",
    "result_df = pd.DataFrame(u.get_nns_by_item(indx[0], n )) # will find the 10 nearest neighbors\n",
    "result_df['rank']= result_df.index+1\n",
    "result_df['ref_ad_no'] = a_no\n",
    "result_df.rename(columns = {0:'index'}, inplace = True)\n",
    "\n",
    "abc = samp_Type2s[samp_Type2s.index.isin(result_df['index'])]\n",
    "abc['index']= abc.index\n",
    "result_df = result_df.merge(abc, how='inner',on='index')\n",
    "\n",
    "print(f\"Top {n} matches for Type2 no.: {a_no} are\")\n",
    "result_df\n",
    "\n",
    "#Cleared Output to comply with the NDA\n"
   ]
  }
 ],
 "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}