Approximate String Matchinu Using Fuzzy Matching.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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>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",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(193379, 10)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MAT_DESC        1669\n",
       "UNIT               1\n",
       "MTL_CODE           0\n",
       "CURRENT_RATE       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features=[\"MAT_DESC\",\"UNIT\",\"MTL_CODE\",\"CURRENT_RATE\"]\n",
    "df1=df[features]\n",
    "df[features].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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"
     ]
    }
   ],
   "source": [
    "df1.dropna(axis=0,how='any',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MAT_DESC        0\n",
       "UNIT            0\n",
       "MTL_CODE        0\n",
       "CURRENT_RATE    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=df1[:1000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['PIN', 'WASHER', 'LABEL', 'LED LAMP', 'TERMINAL', 'CT', 'SPRING', 'LEVER', 'BRACKET', 'CU DET', 'SPACER', 'METER', 'RELAY', 'GASKET', 'PT', 'SLEEVE', 'SHIM', 'NUMBER DISC', 'CLIP', 'AMMETER', 'SUPPORT ROLLER', 'BUSH', 'ROD', 'SWING-BOLT', 'STUD', 'ROLLER', 'LIMIT SWITCH', 'INDICATING LAMP.', 'TUBE', 'COPPER CABLE']\n"
     ]
    }
   ],
   "source": [
    "from collections import Counter\n",
    "all_names = df2['MAT_DESC'][df2['CURRENT_RATE']>-19]\n",
    "names_freq = Counter()\n",
    "for name in all_names:\n",
    "    names_freq.update(str(name).split(\" /\"))\n",
    "key_words = [word for (word,_) in names_freq.most_common(30)]\n",
    "print(key_words)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(all_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_names=all_names.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_main_name = pd.DataFrame(columns=['MTL_CODE','sort_gp','names','alias','score','unit','price'])\n",
    "all_names.sort()\n",
    "all_main_name['names'] = all_names\n",
    "all_main_name['sort_gp'] = all_main_name['names'].apply(lambda x: x[0])\n",
    "all_main_name['MTL_CODE'] =df2['MTL_CODE']\n",
    "all_main_name['unit']=df2['UNIT']\n",
    "all_main_name['price']=df2['CURRENT_RATE']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000, 7)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_main_name.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\6031811test.BHELBPL\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\fuzzywuzzy\\fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning\n",
      "  warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')\n",
      "C:\\Users\\6031811test.BHELBPL\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:189: 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",
      "  self._setitem_with_indexer(indexer, value)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "progress: 0.00%\n",
      "progress: 10.00%\n",
      "progress: 20.00%\n",
      "progress: 30.00%\n",
      "progress: 40.00%\n",
      "progress: 50.00%\n",
      "progress: 60.00%\n",
      "progress: 70.00%\n",
      "progress: 80.00%\n",
      "progress: 90.00%\n"
     ]
    }
   ],
   "source": [
    "from fuzzywuzzy import fuzz\n",
    "\n",
    "all_sort_gp = all_main_name['sort_gp'].unique()\n",
    "n=0\n",
    "\n",
    "def no_key_word(name):\n",
    "   \n",
    "    output = True\n",
    "    for key in key_words:\n",
    "        if key in name:\n",
    "            output = False\n",
    "    return output\n",
    "\n",
    "for sortgp in all_sort_gp:\n",
    "    this_gp = all_main_name.groupby(['sort_gp']).get_group(sortgp)\n",
    "    gp_start = this_gp.index.min()\n",
    "    gp_end = this_gp.index.max()\n",
    "    for i in range(gp_start,gp_end+1):\n",
    "    \n",
    "        # if self has not got alias, asign to be alias of itself\n",
    "        if pd.isna(all_main_name['alias'].iloc[i]):\n",
    "            all_main_name['alias'].iloc[i] = all_main_name['names'].iloc[i];\n",
    "            all_main_name['score'].iloc[i] = 100\n",
    "        \n",
    "        # if the following has not got alias and fuzzy match, asign to be alias of this one\n",
    "        for j in range(i+1,gp_end+1):\n",
    "            if pd.isna(all_main_name['alias'].iloc[j]):\n",
    "                fuzz_socre = fuzz.token_set_ratio(all_main_name['names'].iloc[i],all_main_name['names'].iloc[j])\n",
    "                if not no_key_word(all_main_name['names'].iloc[j]):\n",
    "                    fuzz_socre -= 10\n",
    "                if (fuzz_socre > 75):\n",
    "                    all_main_name['alias'].iloc[j] = all_main_name['alias'].iloc[i]\n",
    "                    all_main_name['score'].iloc[j] = fuzz_socre\n",
    "                    \n",
    "        if i % (len(all_names)//10) == 0:\n",
    "            print(\"progress: %.2f\" % (100*i/len(all_names)) + \"%\")\n",
    "                \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>MTL_CODE</th>\n",
       "      <th>sort_gp</th>\n",
       "      <th>names</th>\n",
       "      <th>alias</th>\n",
       "      <th>score</th>\n",
       "      <th>unit</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4.670264e+09</td>\n",
       "      <td>\"</td>\n",
       "      <td>\"DMC\" COMPOUND TO AA22826</td>\n",
       "      <td>\"DMC\" COMPOUND TO AA22826</td>\n",
       "      <td>100</td>\n",
       "      <td>NO</td>\n",
       "      <td>7138.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.900107e+09</td>\n",
       "      <td>'</td>\n",
       "      <td>'O'RING</td>\n",
       "      <td>'O'RING</td>\n",
       "      <td>100</td>\n",
       "      <td>NO</td>\n",
       "      <td>5420.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.900107e+09</td>\n",
       "      <td>'</td>\n",
       "      <td>'YASHMUN' INDUSTRIAL TYPE BELL UNITS</td>\n",
       "      <td>'YASHMUN' INDUSTRIAL TYPE BELL UNITS</td>\n",
       "      <td>100</td>\n",
       "      <td>NO</td>\n",
       "      <td>1300.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.900107e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>0-1500 OHMS RESISTOR</td>\n",
       "      <td>0-1500 OHMS RESISTOR</td>\n",
       "      <td>100</td>\n",
       "      <td>NO</td>\n",
       "      <td>4428.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.900107e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>0.65 MM STATOR SEGMENT</td>\n",
       "      <td>0.65 MM STATOR SEGMENT</td>\n",
       "      <td>100</td>\n",
       "      <td>NO</td>\n",
       "      <td>36412.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4.900107e+09</td>\n",
       "      <td>1</td>\n",
       "      <td>1 K OHMS RESISTORS</td>\n",
       "      <td>1 K OHMS RESISTORS</td>\n",
       "      <td>100</td>\n",
       "      <td>NO</td>\n",
       "      <td>3923.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4.900107e+09</td>\n",
       "      <td>1</td>\n",
       "      <td>1-PH PT</td>\n",
       "      <td>1-PH PT</td>\n",
       "      <td>100</td>\n",
       "      <td>NO</td>\n",
       "      <td>3685.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4.900107e+09</td>\n",
       "      <td>1</td>\n",
       "      <td>1.5MMSQ GREEN CABLE</td>\n",
       "      <td>1.5MMSQ GREEN CABLE</td>\n",
       "      <td>100</td>\n",
       "      <td>NO</td>\n",
       "      <td>263.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4.900067e+09</td>\n",
       "      <td>1</td>\n",
       "      <td>1.5X6 PICC-LPD 903.5</td>\n",
       "      <td>1.5X6 PICC-LPD 903.5</td>\n",
       "      <td>100</td>\n",
       "      <td>NO</td>\n",
       "      <td>4858.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4.900067e+09</td>\n",
       "      <td>1</td>\n",
       "      <td>10 MM DIA BRT.STEEL BAR.</td>\n",
       "      <td>10 MM DIA BRT.STEEL BAR.</td>\n",
       "      <td>100</td>\n",
       "      <td>NO</td>\n",
       "      <td>4944.46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       MTL_CODE sort_gp                                 names  \\\n",
       "0  4.670264e+09       \"             \"DMC\" COMPOUND TO AA22826   \n",
       "1  4.900107e+09       '                               'O'RING   \n",
       "2  4.900107e+09       '  'YASHMUN' INDUSTRIAL TYPE BELL UNITS   \n",
       "3  4.900107e+09       0                  0-1500 OHMS RESISTOR   \n",
       "4  4.900107e+09       0                0.65 MM STATOR SEGMENT   \n",
       "5  4.900107e+09       1                    1 K OHMS RESISTORS   \n",
       "6  4.900107e+09       1                               1-PH PT   \n",
       "7  4.900107e+09       1                   1.5MMSQ GREEN CABLE   \n",
       "8  4.900067e+09       1                  1.5X6 PICC-LPD 903.5   \n",
       "9  4.900067e+09       1              10 MM DIA BRT.STEEL BAR.   \n",
       "\n",
       "                                  alias  score unit     price  \n",
       "0             \"DMC\" COMPOUND TO AA22826    100   NO   7138.60  \n",
       "1                               'O'RING    100   NO   5420.33  \n",
       "2  'YASHMUN' INDUSTRIAL TYPE BELL UNITS    100   NO   1300.93  \n",
       "3                  0-1500 OHMS RESISTOR    100   NO   4428.94  \n",
       "4                0.65 MM STATOR SEGMENT    100   NO  36412.03  \n",
       "5                    1 K OHMS RESISTORS    100   NO   3923.89  \n",
       "6                               1-PH PT    100   NO   3685.39  \n",
       "7                   1.5MMSQ GREEN CABLE    100   NO    263.41  \n",
       "8                  1.5X6 PICC-LPD 903.5    100   NO   4858.09  \n",
       "9              10 MM DIA BRT.STEEL BAR.    100   NO   4944.46  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_main_name[:10]"
   ]
  },
  {
   "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
}