Fuzzy String Match to New Column.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: fuzzywuzzy in c:\\users\\whuang\\appdata\\local\\continuum\\anaconda3\\lib\\site-packages (0.17.0)\n",
      "Collecting python-Levenshtein\n",
      "  Using cached https://files.pythonhosted.org/packages/42/a9/d1785c85ebf9b7dfacd08938dd028209c34a0ea3b1bcdb895208bd40a67d/python-Levenshtein-0.12.0.tar.gz\n",
      "Requirement already satisfied: setuptools in c:\\users\\whuang\\appdata\\local\\continuum\\anaconda3\\lib\\site-packages (from python-Levenshtein) (40.6.3)\n",
      "Building wheels for collected packages: python-Levenshtein\n",
      "  Running setup.py bdist_wheel for python-Levenshtein: started\n",
      "  Running setup.py bdist_wheel for python-Levenshtein: finished with status 'error'\n",
      "  Complete output from command C:\\Users\\whuang\\AppData\\Local\\Continuum\\anaconda3\\python.exe -u -c \"import setuptools, tokenize;__file__='C:\\\\Users\\\\whuang\\\\AppData\\\\Local\\\\Temp\\\\pip-install-7z2gbygy\\\\python-Levenshtein\\\\setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\\r\\n', '\\n');f.close();exec(compile(code, __file__, 'exec'))\" bdist_wheel -d C:\\Users\\whuang\\AppData\\Local\\Temp\\pip-wheel-yno5k02w --python-tag cp37:\n",
      "  running bdist_wheel\n",
      "  running build\n",
      "  running build_py\n",
      "  creating build\n",
      "  creating build\\lib.win-amd64-3.7\n",
      "  creating build\\lib.win-amd64-3.7\\Levenshtein\n",
      "  copying Levenshtein\\StringMatcher.py -> build\\lib.win-amd64-3.7\\Levenshtein\n",
      "  copying Levenshtein\\__init__.py -> build\\lib.win-amd64-3.7\\Levenshtein\n",
      "  running egg_info\n",
      "  writing python_Levenshtein.egg-info\\PKG-INFO\n",
      "  writing dependency_links to python_Levenshtein.egg-info\\dependency_links.txt\n",
      "  writing entry points to python_Levenshtein.egg-info\\entry_points.txt\n",
      "  writing namespace_packages to python_Levenshtein.egg-info\\namespace_packages.txt\n",
      "  writing requirements to python_Levenshtein.egg-info\\requires.txt\n",
      "  writing top-level names to python_Levenshtein.egg-info\\top_level.txt\n",
      "  reading manifest file 'python_Levenshtein.egg-info\\SOURCES.txt'\n",
      "  reading manifest template 'MANIFEST.in'\n",
      "  warning: no previously-included files matching '*pyc' found anywhere in distribution\n",
      "  warning: no previously-included files matching '*so' found anywhere in distribution\n",
      "  warning: no previously-included files matching '.project' found anywhere in distribution\n",
      "  warning: no previously-included files matching '.pydevproject' found anywhere in distribution\n",
      "  writing manifest file 'python_Levenshtein.egg-info\\SOURCES.txt'\n",
      "  copying Levenshtein\\_levenshtein.c -> build\\lib.win-amd64-3.7\\Levenshtein\n",
      "  copying Levenshtein\\_levenshtein.h -> build\\lib.win-amd64-3.7\\Levenshtein\n",
      "  running build_ext\n",
      "  building 'Levenshtein._levenshtein' extension\n",
      "  error: Microsoft Visual C++ 14.0 is required. Get it with \"Microsoft Visual C++ Build Tools\": https://visualstudio.microsoft.com/downloads/\n",
      "  \n",
      "  ----------------------------------------\n",
      "  Running setup.py clean for python-Levenshtein\n",
      "Failed to build python-Levenshtein\n",
      "Installing collected packages: python-Levenshtein\n",
      "  Running setup.py install for python-Levenshtein: started\n",
      "    Running setup.py install for python-Levenshtein: finished with status 'error'\n",
      "    Complete output from command C:\\Users\\whuang\\AppData\\Local\\Continuum\\anaconda3\\python.exe -u -c \"import setuptools, tokenize;__file__='C:\\\\Users\\\\whuang\\\\AppData\\\\Local\\\\Temp\\\\pip-install-7z2gbygy\\\\python-Levenshtein\\\\setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\\r\\n', '\\n');f.close();exec(compile(code, __file__, 'exec'))\" install --record C:\\Users\\whuang\\AppData\\Local\\Temp\\pip-record-gqjcivc8\\install-record.txt --single-version-externally-managed --compile:\n",
      "    running install\n",
      "    running build\n",
      "    running build_py\n",
      "    creating build\n",
      "    creating build\\lib.win-amd64-3.7\n",
      "    creating build\\lib.win-amd64-3.7\\Levenshtein\n",
      "    copying Levenshtein\\StringMatcher.py -> build\\lib.win-amd64-3.7\\Levenshtein\n",
      "    copying Levenshtein\\__init__.py -> build\\lib.win-amd64-3.7\\Levenshtein\n",
      "    running egg_info\n",
      "    writing python_Levenshtein.egg-info\\PKG-INFO\n",
      "    writing dependency_links to python_Levenshtein.egg-info\\dependency_links.txt\n",
      "    writing entry points to python_Levenshtein.egg-info\\entry_points.txt\n",
      "    writing namespace_packages to python_Levenshtein.egg-info\\namespace_packages.txt\n",
      "    writing requirements to python_Levenshtein.egg-info\\requires.txt\n",
      "    writing top-level names to python_Levenshtein.egg-info\\top_level.txt\n",
      "    reading manifest file 'python_Levenshtein.egg-info\\SOURCES.txt'\n",
      "    reading manifest template 'MANIFEST.in'\n",
      "    warning: no previously-included files matching '*pyc' found anywhere in distribution\n",
      "    warning: no previously-included files matching '*so' found anywhere in distribution\n",
      "    warning: no previously-included files matching '.project' found anywhere in distribution\n",
      "    warning: no previously-included files matching '.pydevproject' found anywhere in distribution\n",
      "    writing manifest file 'python_Levenshtein.egg-info\\SOURCES.txt'\n",
      "    copying Levenshtein\\_levenshtein.c -> build\\lib.win-amd64-3.7\\Levenshtein\n",
      "    copying Levenshtein\\_levenshtein.h -> build\\lib.win-amd64-3.7\\Levenshtein\n",
      "    running build_ext\n",
      "    building 'Levenshtein._levenshtein' extension\n",
      "    error: Microsoft Visual C++ 14.0 is required. Get it with \"Microsoft Visual C++ Build Tools\": https://visualstudio.microsoft.com/downloads/\n",
      "    \n",
      "    ----------------------------------------\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "  Failed building wheel for python-Levenshtein\n",
      "Command \"C:\\Users\\whuang\\AppData\\Local\\Continuum\\anaconda3\\python.exe -u -c \"import setuptools, tokenize;__file__='C:\\\\Users\\\\whuang\\\\AppData\\\\Local\\\\Temp\\\\pip-install-7z2gbygy\\\\python-Levenshtein\\\\setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\\r\\n', '\\n');f.close();exec(compile(code, __file__, 'exec'))\" install --record C:\\Users\\whuang\\AppData\\Local\\Temp\\pip-record-gqjcivc8\\install-record.txt --single-version-externally-managed --compile\" failed with error code 1 in C:\\Users\\whuang\\AppData\\Local\\Temp\\pip-install-7z2gbygy\\python-Levenshtein\\\n"
     ]
    }
   ],
   "source": [
    "#install packages\n",
    "!pip install fuzzywuzzy\n",
    "!pip install python-Levenshtein"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from difflib import SequenceMatcher"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9629629629629629"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#a traditional way to find the string similarity\n",
    "m = SequenceMatcher(None, 'NEW YORK METS', 'NEW YORK MEATS')\n",
    "m.ratio()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Use fuzzywuzzy to find string similarity and add a new column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\whuang\\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"
     ]
    }
   ],
   "source": [
    "from fuzzywuzzy import fuzz\n",
    "from fuzzywuzzy import process"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#use fuzzywuzzy to find the string similarity\n",
    "fuzz.ratio('NEW YOR', 'YANKEES')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fuzz.ratio('NEW YORK', 'new york')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#WRatio ignores the case sensitivity \n",
    "fuzz.WRatio('NEW YORK', 'new york')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('hiWANYI', 83), ('wannnq2', 50), ('game', 22)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#find the similarity of different strings with our query (not case sensitive)\n",
    "query = 'wanyi'\n",
    "choices = ['game', 'wannnq2', 'hiWANYI']\n",
    "process.extract(query, choices)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('hiWANYI', 83)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#extractOne extracts the most similar string in the records with similarity score\n",
    "process.extractOne(query, choices)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#suppose l is a df, rep is a col in l, we want to match the most similar string in rep['name'] with\n",
    "#row in l and then add a new column in l to correspond to the row.\n",
    "\n",
    "l = df[df.name.isnull()].rep.to_frame().drop_duplicates()\n",
    "\n",
    "for i in l.rep.tolist():\n",
    "    l.loc[l.rep == i, 'name']  = process.extractOne(i, rep['name'])[0]"
   ]
  }
 ],
 "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
}