Approximate String Matching using TF-IDF and N-Grams.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The shape: 193379 x 10\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>Unnamed: 0</th>\n",
       "      <th>DEPT</th>\n",
       "      <th>STORE_CODE</th>\n",
       "      <th>PREFIX_MTL_CODE</th>\n",
       "      <th>MTL_CODE</th>\n",
       "      <th>LOCATION_CODE</th>\n",
       "      <th>MTL_DESC</th>\n",
       "      <th>MAT_DESC</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>CURRENT_RATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>206</td>\n",
       "      <td>2</td>\n",
       "      <td>TR</td>\n",
       "      <td>4670263510</td>\n",
       "      <td>M/SHED R-8 KE SAMNE</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>NO</td>\n",
       "      <td>7138.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>209</td>\n",
       "      <td>3</td>\n",
       "      <td>SG</td>\n",
       "      <td>4900107255</td>\n",
       "      <td>KA-40</td>\n",
       "      <td>RELAY VAGM</td>\n",
       "      <td>RELAY VAGM</td>\n",
       "      <td>NO</td>\n",
       "      <td>5420.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>209</td>\n",
       "      <td>3</td>\n",
       "      <td>SG</td>\n",
       "      <td>4900107280</td>\n",
       "      <td>MR</td>\n",
       "      <td>RELAY VAJH-13</td>\n",
       "      <td>RELAY VAJH-13</td>\n",
       "      <td>NO</td>\n",
       "      <td>1300.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>209</td>\n",
       "      <td>3</td>\n",
       "      <td>SG</td>\n",
       "      <td>4900107298</td>\n",
       "      <td>KB-48</td>\n",
       "      <td>RELAY VAX31</td>\n",
       "      <td>RELAY VAX31</td>\n",
       "      <td>NO</td>\n",
       "      <td>4428.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>209</td>\n",
       "      <td>3</td>\n",
       "      <td>SG</td>\n",
       "      <td>4900107301</td>\n",
       "      <td>MR</td>\n",
       "      <td>RELAY DTH31</td>\n",
       "      <td>RELAY DTH31</td>\n",
       "      <td>NO</td>\n",
       "      <td>36412.03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  DEPT  STORE_CODE PREFIX_MTL_CODE    MTL_CODE  \\\n",
       "0  1           206   2           TR              4670263510   \n",
       "1  2           209   3           SG              4900107255   \n",
       "2  3           209   3           SG              4900107280   \n",
       "3  4           209   3           SG              4900107298   \n",
       "4  5           209   3           SG              4900107301   \n",
       "\n",
       "         LOCATION_CODE       MTL_DESC       MAT_DESC UNIT  CURRENT_RATE  \n",
       "0  M/SHED R-8 KE SAMNE  CU DET         CU DET         NO   7138.60       \n",
       "1  KA-40                RELAY VAGM     RELAY VAGM     NO   5420.33       \n",
       "2  MR                   RELAY VAJH-13  RELAY VAJH-13  NO   1300.93       \n",
       "3  KB-48                RELAY VAX31    RELAY VAX31    NO   4428.94       \n",
       "4  MR                   RELAY DTH31    RELAY DTH31    NO   36412.03      "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv(\"Documents/Materials.csv\",encoding = \"ISO-8859-1\")\n",
    "pd.set_option('display.max_colwidth', -1)\n",
    "print('The shape: %d x %d' % df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=df[[\"MTL_CODE\",\"MAT_DESC\",\"CURRENT_RATE\",\"UNIT\"]]\n",
    "df1=df[[\"MTL_CODE\",\"MAT_DESC\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\6031811test.BHELBPL\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n",
      "C:\\Users\\6031811test.BHELBPL\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "df1.dropna(how=\"any\",axis=0,inplace=True)\n",
    "df2.dropna(how=\"any\",axis=0,inplace=True)\n",
    "df.dropna(how=\"any\",axis=0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(182683, 10)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "names=df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All 3-grams in \"McDonalds\":\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['McD', 'cDo', 'Don', 'ona', 'nal', 'alE', 'lEE', 'EEd', 'Eds']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "def ngrams(string, n=3):\n",
    "    string = re.sub(r'[,.-/-]|\\sBD|[0-9]|[\"\"]|[(+*)]|\\s|[\\(\\[].*?[\\)\\]]|(\".*?\\((.*?)\\))|[X]| ?\\([^)]+\\)',r'', string)\n",
    "    ngrams = zip(*[string[i:] for i in range(n)])\n",
    "    return [''.join(ngram) for ngram in ngrams]\n",
    "\n",
    "print('All 3-grams in \"McDonalds\":')\n",
    "ngrams('McDo1n\"\"a-.l,(EE223)dX[] s')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "company_names = names['MAT_DESC'].tolist()\n",
    "vectorizer = TfidfVectorizer(min_df=1, analyzer=ngrams)\n",
    "tf_idf_matrix = vectorizer.fit_transform(company_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  (0, 2378)\t0.59329542643255\n",
      "  (0, 11686)\t0.5906284261076192\n",
      "  (0, 2670)\t0.5469539279023922\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['CUD', 'UDE', 'DET']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(tf_idf_matrix[0])\n",
    "\n",
    "# Check if this makes sense:\n",
    "\n",
    "ngrams('CU-DET')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "from scipy.sparse import csr_matrix\n",
    "import sparse_dot_topn.sparse_dot_topn as ct\n",
    "\n",
    "def awesome_cossim_top(A, B, ntop, lower_bound=0):\n",
    "    # force A and B as a CSR matrix.\n",
    "    # If they have already been CSR, there is no overhead\n",
    "    A = A.tocsr()\n",
    "    B = B.tocsr()\n",
    "    M, _ = A.shape\n",
    "    _, N = B.shape\n",
    " \n",
    "    idx_dtype = np.int32\n",
    " \n",
    "    nnz_max = M*ntop\n",
    " \n",
    "    indptr = np.zeros(M+1, dtype=idx_dtype)\n",
    "    indices = np.zeros(nnz_max, dtype=idx_dtype)\n",
    "    data = np.zeros(nnz_max, dtype=A.dtype)\n",
    "\n",
    "    ct.sparse_dot_topn(\n",
    "        M, N, np.asarray(A.indptr, dtype=idx_dtype),\n",
    "        np.asarray(A.indices, dtype=idx_dtype),\n",
    "        A.data,\n",
    "        np.asarray(B.indptr, dtype=idx_dtype),\n",
    "        np.asarray(B.indices, dtype=idx_dtype),\n",
    "        B.data,\n",
    "        ntop,\n",
    "        lower_bound,\n",
    "        indptr, indices, data)\n",
    "\n",
    "    return csr_matrix((data,indices,indptr),shape=(M,N))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELFTIMED: 54.635146141052246\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "t1 = time.time()\n",
    "matches = awesome_cossim_top(tf_idf_matrix, tf_idf_matrix.transpose(),200,0.8)\n",
    "t = time.time()-t1\n",
    "print(\"SELFTIMED:\", t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_matches_df(sparse_matrix, name_vector, top=100):\n",
    "    non_zeros = sparse_matrix.nonzero()\n",
    "    \n",
    "    sparserows = non_zeros[0]\n",
    "    sparsecols = non_zeros[1]\n",
    "    \n",
    "    if top:\n",
    "        nr_matches = top\n",
    "    else:\n",
    "        nr_matches = sparsecols.size\n",
    "    \n",
    "    left_side = np.empty([nr_matches], dtype=object)\n",
    "    right_side = np.empty([nr_matches], dtype=object)\n",
    "    similairity = np.zeros(nr_matches)\n",
    "    \n",
    "    for index in range(0, nr_matches):\n",
    "        left_side[index] = name_vector[sparserows[index]]\n",
    "        right_side[index] = name_vector[sparsecols[index]]\n",
    "        similairity[index] = sparse_matrix.data[index]\n",
    "    \n",
    "    return pd.DataFrame({'left_side': left_side,'right_side': right_side,'similairity': similairity,'Material Codes':df[\"MTL_CODE\"],'CURRENT_RATE':df[\"CURRENT_RATE\"],'UNIT':df[\"UNIT\"]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>left_side</th>\n",
       "      <th>right_side</th>\n",
       "      <th>similairity</th>\n",
       "      <th>Material Codes</th>\n",
       "      <th>CURRENT_RATE</th>\n",
       "      <th>UNIT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4670263510</td>\n",
       "      <td>7138.60</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900107255</td>\n",
       "      <td>5420.33</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900107280</td>\n",
       "      <td>1300.93</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900107298</td>\n",
       "      <td>4428.94</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900107301</td>\n",
       "      <td>36412.03</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900107310</td>\n",
       "      <td>3923.89</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900107328</td>\n",
       "      <td>3685.39</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900107344</td>\n",
       "      <td>263.41</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900067240</td>\n",
       "      <td>4858.09</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900067250</td>\n",
       "      <td>4944.46</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9099433239</td>\n",
       "      <td>1.28</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4970097300</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9099437331</td>\n",
       "      <td>10.73</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900060190</td>\n",
       "      <td>303.66</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9049254860</td>\n",
       "      <td>1.25</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900517320</td>\n",
       "      <td>1890.34</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900067460</td>\n",
       "      <td>216.91</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900067470</td>\n",
       "      <td>216.91</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900067480</td>\n",
       "      <td>216.91</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900067520</td>\n",
       "      <td>216.91</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4900067530</td>\n",
       "      <td>216.91</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9099432240</td>\n",
       "      <td>0.99</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9099432380</td>\n",
       "      <td>0.84</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CUD ET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4690087016</td>\n",
       "      <td>254.14</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CUDET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9099432356</td>\n",
       "      <td>3.05</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9099431104</td>\n",
       "      <td>119.39</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9099432577</td>\n",
       "      <td>177.38</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9099433158</td>\n",
       "      <td>25.22</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9029116382</td>\n",
       "      <td>4.48</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>CU DET</td>\n",
       "      <td>CU DET</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9029116374</td>\n",
       "      <td>5.53</td>\n",
       "      <td>NO</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193349</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341080083</td>\n",
       "      <td>96000.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193350</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341080091</td>\n",
       "      <td>104000.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193351</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341080105</td>\n",
       "      <td>70000.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193352</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER   35216601983-003</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341080113</td>\n",
       "      <td>4500.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193353</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341080121</td>\n",
       "      <td>17000.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193354</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341080130</td>\n",
       "      <td>6000.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193355</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341080148</td>\n",
       "      <td>7500.00</td>\n",
       "      <td>ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193356</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341080156</td>\n",
       "      <td>5500.00</td>\n",
       "      <td>ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193357</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341080164</td>\n",
       "      <td>100.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193358</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7750103015</td>\n",
       "      <td>41000.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193359</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7750103023</td>\n",
       "      <td>2600.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193360</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7750103031</td>\n",
       "      <td>760.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193361</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7750103040</td>\n",
       "      <td>1400.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193362</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7750103058</td>\n",
       "      <td>70.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193363</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7750103066</td>\n",
       "      <td>2000.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193364</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7750103074</td>\n",
       "      <td>85000.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193365</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4441857209</td>\n",
       "      <td>150.00</td>\n",
       "      <td>LT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193366</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4441767145</td>\n",
       "      <td>174.00</td>\n",
       "      <td>LT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193367</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341049577</td>\n",
       "      <td>22250.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193368</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341049585</td>\n",
       "      <td>98250.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193369</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER 35225001500-3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341049593</td>\n",
       "      <td>27000.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193370</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341049607</td>\n",
       "      <td>55833.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193371</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4341049615</td>\n",
       "      <td>34250.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193372</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4970606000</td>\n",
       "      <td>316750.00</td>\n",
       "      <td>ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193373</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4385457115</td>\n",
       "      <td>65.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193374</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4385457123</td>\n",
       "      <td>90.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193375</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4362301372</td>\n",
       "      <td>293.00</td>\n",
       "      <td>MR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193376</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4755053770</td>\n",
       "      <td>30000.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193377</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4755053770</td>\n",
       "      <td>30000.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193378</th>\n",
       "      <td>BARRIER</td>\n",
       "      <td>BARRIER</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4481097019</td>\n",
       "      <td>29600.00</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>182683 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       left_side                 right_side  similairity  Material Codes  \\\n",
       "0       CU DET    CU DET                     1.0          4670263510       \n",
       "1       CU DET    CU DET                     1.0          4900107255       \n",
       "2       CU DET    CU DET                     1.0          4900107280       \n",
       "3       CU DET    CU DET                     1.0          4900107298       \n",
       "4       CU DET    CU DET                     1.0          4900107301       \n",
       "5       CU DET    CU DET                     1.0          4900107310       \n",
       "6       CU DET    CU DET                     1.0          4900107328       \n",
       "7       CU DET    CU DET                     1.0          4900107344       \n",
       "8       CU DET    CU DET                     1.0          4900067240       \n",
       "9       CU DET    CU DET                     1.0          4900067250       \n",
       "10      CU DET    CU DET                     1.0          9099433239       \n",
       "11      CU DET    CU DET                     1.0          4970097300       \n",
       "12      CU DET    CU DET                     1.0          9099437331       \n",
       "13      CU DET    CU DET                     1.0          4900060190       \n",
       "14      CU DET    CU DET                     1.0          9049254860       \n",
       "15      CU DET    CU DET                     1.0          4900517320       \n",
       "16      CU DET    CU DET                     1.0          4900067460       \n",
       "17      CU DET    CU DET                     1.0          4900067470       \n",
       "18      CU DET    CU DET                     1.0          4900067480       \n",
       "19      CU DET    CU DET                     1.0          4900067520       \n",
       "20      CU DET    CU DET                     1.0          4900067530       \n",
       "21      CU DET    CU DET                     1.0          9099432240       \n",
       "22      CU DET    CU DET                     1.0          9099432380       \n",
       "23      CU DET    CUD ET                     1.0          4690087016       \n",
       "24      CU DET    CUDET                      1.0          9099432356       \n",
       "25      CU DET    CU DET                     1.0          9099431104       \n",
       "26      CU DET    CU DET                     1.0          9099432577       \n",
       "27      CU DET    CU DET                     1.0          9099433158       \n",
       "28      CU DET    CU DET                     1.0          9029116382       \n",
       "29      CU DET    CU DET                     1.0          9029116374       \n",
       "...        ...       ...                     ...                 ...       \n",
       "193349  BARRIER   BARRIER                    1.0          4341080083       \n",
       "193350  BARRIER   BARRIER                    1.0          4341080091       \n",
       "193351  BARRIER   BARRIER                    1.0          4341080105       \n",
       "193352  BARRIER   BARRIER   35216601983-003  1.0          4341080113       \n",
       "193353  BARRIER   BARRIER                    1.0          4341080121       \n",
       "193354  BARRIER   BARRIER                    1.0          4341080130       \n",
       "193355  BARRIER   BARRIER                    1.0          4341080148       \n",
       "193356  BARRIER   BARRIER                    1.0          4341080156       \n",
       "193357  BARRIER   BARRIER                    1.0          4341080164       \n",
       "193358  BARRIER   BARRIER                    1.0          7750103015       \n",
       "193359  BARRIER   BARRIER                    1.0          7750103023       \n",
       "193360  BARRIER   BARRIER                    1.0          7750103031       \n",
       "193361  BARRIER   BARRIER                    1.0          7750103040       \n",
       "193362  BARRIER   BARRIER                    1.0          7750103058       \n",
       "193363  BARRIER   BARRIER                    1.0          7750103066       \n",
       "193364  BARRIER   BARRIER                    1.0          7750103074       \n",
       "193365  BARRIER   BARRIER                    1.0          4441857209       \n",
       "193366  BARRIER   BARRIER                    1.0          4441767145       \n",
       "193367  BARRIER   BARRIER                    1.0          4341049577       \n",
       "193368  BARRIER   BARRIER                    1.0          4341049585       \n",
       "193369  BARRIER   BARRIER 35225001500-3      1.0          4341049593       \n",
       "193370  BARRIER   BARRIER                    1.0          4341049607       \n",
       "193371  BARRIER   BARRIER                    1.0          4341049615       \n",
       "193372  BARRIER   BARRIER                    1.0          4970606000       \n",
       "193373  BARRIER   BARRIER                    1.0          4385457115       \n",
       "193374  BARRIER   BARRIER                    1.0          4385457123       \n",
       "193375  BARRIER   BARRIER                    1.0          4362301372       \n",
       "193376  BARRIER   BARRIER                    1.0          4755053770       \n",
       "193377  BARRIER   BARRIER                    1.0          4755053770       \n",
       "193378  BARRIER   BARRIER                    1.0          4481097019       \n",
       "\n",
       "        CURRENT_RATE UNIT  \n",
       "0       7138.60       NO   \n",
       "1       5420.33       NO   \n",
       "2       1300.93       NO   \n",
       "3       4428.94       NO   \n",
       "4       36412.03      NO   \n",
       "5       3923.89       NO   \n",
       "6       3685.39       NO   \n",
       "7       263.41        NO   \n",
       "8       4858.09       NO   \n",
       "9       4944.46       NO   \n",
       "10      1.28          NO   \n",
       "11      1.00          NO   \n",
       "12      10.73         NO   \n",
       "13      303.66        NO   \n",
       "14      1.25          NO   \n",
       "15      1890.34       NO   \n",
       "16      216.91        NO   \n",
       "17      216.91        NO   \n",
       "18      216.91        NO   \n",
       "19      216.91        NO   \n",
       "20      216.91        NO   \n",
       "21      0.99          NO   \n",
       "22      0.84          NO   \n",
       "23      254.14        NO   \n",
       "24      3.05          NO   \n",
       "25      119.39        NO   \n",
       "26      177.38        NO   \n",
       "27      25.22         NO   \n",
       "28      4.48          NO   \n",
       "29      5.53          NO   \n",
       "...      ...          ..   \n",
       "193349  96000.00      NO   \n",
       "193350  104000.00     NO   \n",
       "193351  70000.00      NO   \n",
       "193352  4500.00       NO   \n",
       "193353  17000.00      NO   \n",
       "193354  6000.00       NO   \n",
       "193355  7500.00       ST   \n",
       "193356  5500.00       ST   \n",
       "193357  100.00        NO   \n",
       "193358  41000.00      NO   \n",
       "193359  2600.00       NO   \n",
       "193360  760.00        NO   \n",
       "193361  1400.00       NO   \n",
       "193362  70.00         NO   \n",
       "193363  2000.00       NO   \n",
       "193364  85000.00      NO   \n",
       "193365  150.00        LT   \n",
       "193366  174.00        LT   \n",
       "193367  22250.00      NO   \n",
       "193368  98250.00      NO   \n",
       "193369  27000.00      NO   \n",
       "193370  55833.00      NO   \n",
       "193371  34250.00      NO   \n",
       "193372  316750.00     ST   \n",
       "193373  65.00         NO   \n",
       "193374  90.00         NO   \n",
       "193375  293.00        MR   \n",
       "193376  30000.00      NO   \n",
       "193377  30000.00      NO   \n",
       "193378  29600.00      NO   \n",
       "\n",
       "[182683 rows x 6 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matches_df = get_matches_df(matches,company_names,top=182683)\n",
    "#matches_df = matches_df[matches_df['similairity'] < 0.99999]\n",
    "#with pd.option_context('display.max_rows', None, 'display.max_columns', None):\n",
    "matches_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(182683, 6)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matches_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "matches_df.to_excel (r'D:\\FINAL_PROJECTkai.xlsx')"
   ]
  },
  {
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}