fuzzy-matching-combined.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "import pandas as pd\n",
    "import recordlinkage\n",
    "\n",
    "REQD_COLUMNS = ['name', 'street', 'city', 'state', 'zip']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "city_df = pd.read_csv(\"./data/UE-cities.csv\")\n",
    "df_sba = pd.read_csv(\"./data/PPP Data 150k plus.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "Cannot convert non-finite values (NA or inf) to integer",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-11-75750021c9ae>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf_sba\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'Zip'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_sba\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'Zip'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mint\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mzfill\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m5\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mdf_a\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_sba\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'BusinessName'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'Address'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'City'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'State'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'Zip'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mdf_a\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mREQD_COLUMNS\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/home/gsunit/my-env/lib/python3.6/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36mastype\u001b[1;34m(self, dtype, copy, errors)\u001b[0m\n\u001b[0;32m   5535\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5536\u001b[0m             \u001b[1;31m# else, only a single dtype is given\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 5537\u001b[1;33m             \u001b[0mnew_data\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_mgr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcopy\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrors\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0merrors\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   5538\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_constructor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnew_data\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__finalize__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"astype\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5539\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/home/gsunit/my-env/lib/python3.6/site-packages/pandas/core/internals/managers.py\u001b[0m in \u001b[0;36mastype\u001b[1;34m(self, dtype, copy, errors)\u001b[0m\n\u001b[0;32m    565\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mbool\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrors\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mstr\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m\"raise\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    566\u001b[0m     ) -> \"BlockManager\":\n\u001b[1;32m--> 567\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"astype\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcopy\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrors\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0merrors\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    568\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    569\u001b[0m     def convert(\n",
      "\u001b[1;32m/home/gsunit/my-env/lib/python3.6/site-packages/pandas/core/internals/managers.py\u001b[0m in \u001b[0;36mapply\u001b[1;34m(self, f, align_keys, **kwargs)\u001b[0m\n\u001b[0;32m    394\u001b[0m                 \u001b[0mapplied\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    395\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 396\u001b[1;33m                 \u001b[0mapplied\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mgetattr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mb\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    397\u001b[0m             \u001b[0mresult_blocks\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_extend_blocks\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mapplied\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mresult_blocks\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    398\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/home/gsunit/my-env/lib/python3.6/site-packages/pandas/core/internals/blocks.py\u001b[0m in \u001b[0;36mastype\u001b[1;34m(self, dtype, copy, errors)\u001b[0m\n\u001b[0;32m    588\u001b[0m             \u001b[0mvals1d\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mvalues\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mravel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    589\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 590\u001b[1;33m                 \u001b[0mvalues\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mastype_nansafe\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvals1d\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    591\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mValueError\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mTypeError\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    592\u001b[0m                 \u001b[1;31m# e.g. astype_nansafe can fail on object-dtype of strings\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/home/gsunit/my-env/lib/python3.6/site-packages/pandas/core/dtypes/cast.py\u001b[0m in \u001b[0;36mastype_nansafe\u001b[1;34m(arr, dtype, copy, skipna)\u001b[0m\n\u001b[0;32m    958\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    959\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0misfinite\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 960\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"Cannot convert non-finite values (NA or inf) to integer\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    961\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    962\u001b[0m     \u001b[1;32melif\u001b[0m \u001b[0mis_object_dtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: Cannot convert non-finite values (NA or inf) to integer"
     ]
    }
   ],
   "source": [
    "df_sba['Zip'] = df_sba['Zip'].astype(int).astype(str).str.zfill(5)\n",
    "\n",
    "df_a = df_sba[['BusinessName', 'Address', 'City', 'State', 'Zip']]\n",
    "df_a.columns = REQD_COLUMNS\n",
    "\n",
    "df_a = df_a.fillna({'name':\"\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for url in city_df['url'][0:1]:\n",
    "    cityname = url.split(\"/\")[-1]\n",
    "    cityfile = \"../Extreme-Uber-Eats-Scraping/scraped-data/restaurant-details/\" + cityname + \"-details.csv\"\n",
    "    df_ue = pd.read_csv(cityfile, dtype=str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "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>name</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1186 BROADWAY RESTAURANT LLC</td>\n",
       "      <td>30 W 26th Street Floor 12</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>NY</td>\n",
       "      <td>10010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           name                     street      city state  \\\n",
       "0  1186 BROADWAY RESTAURANT LLC  30 W 26th Street Floor 12  NEW YORK    NY   \n",
       "\n",
       "     zip  \n",
       "0  10010  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>name</th>\n",
       "      <th>url</th>\n",
       "      <th>city</th>\n",
       "      <th>cuisine</th>\n",
       "      <th>avgRating</th>\n",
       "      <th>numReviews</th>\n",
       "      <th>priceRange</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>telephone</th>\n",
       "      <th>postalCode</th>\n",
       "      <th>streetAddress</th>\n",
       "      <th>addressLocality</th>\n",
       "      <th>addressRegion</th>\n",
       "      <th>openingHoursSpecification</th>\n",
       "      <th>addressString</th>\n",
       "      <th>geoString</th>\n",
       "      <th>ratingString</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mr. Gatti's Pizza - Abilene, TX</td>\n",
       "      <td>https://www.ubereats.com/abilene/food-delivery...</td>\n",
       "      <td>abilene</td>\n",
       "      <td>['Pizza', 'Comfort Food', 'Italian']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$</td>\n",
       "      <td>32.4175466</td>\n",
       "      <td>-99.74861399999999</td>\n",
       "      <td>+13256926326</td>\n",
       "      <td>79605</td>\n",
       "      <td>2665 Buffalo Gap Rd</td>\n",
       "      <td>Abilene</td>\n",
       "      <td>TX</td>\n",
       "      <td>[{'@type': 'OpeningHoursSpecification', 'dayOf...</td>\n",
       "      <td>{'@type': 'PostalAddress', 'addressLocality': ...</td>\n",
       "      <td>{'@type': 'GeoCoordinates', 'latitude': 32.417...</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              name  \\\n",
       "0  Mr. Gatti's Pizza - Abilene, TX   \n",
       "\n",
       "                                                 url     city  \\\n",
       "0  https://www.ubereats.com/abilene/food-delivery...  abilene   \n",
       "\n",
       "                                cuisine avgRating numReviews priceRange  \\\n",
       "0  ['Pizza', 'Comfort Food', 'Italian']       NaN        NaN          $   \n",
       "\n",
       "     latitude           longitude     telephone postalCode  \\\n",
       "0  32.4175466  -99.74861399999999  +13256926326      79605   \n",
       "\n",
       "         streetAddress addressLocality addressRegion  \\\n",
       "0  2665 Buffalo Gap Rd         Abilene            TX   \n",
       "\n",
       "                           openingHoursSpecification  \\\n",
       "0  [{'@type': 'OpeningHoursSpecification', 'dayOf...   \n",
       "\n",
       "                                       addressString  \\\n",
       "0  {'@type': 'PostalAddress', 'addressLocality': ...   \n",
       "\n",
       "                                           geoString ratingString  \n",
       "0  {'@type': 'GeoCoordinates', 'latitude': 32.417...           {}  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_a.head(1)\n",
    "df_ue.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_ue['name'] = df_ue['name'].str.upper()\n",
    "df_ue['addressLocality'] = df_ue['addressLocality'].str.upper()\n",
    "df_ue['addressRegion'] = df_ue['addressRegion'].str.upper()\n",
    "\n",
    "df_b = df_ue[['name', 'streetAddress', 'addressLocality', 'addressRegion', 'postalCode']]\n",
    "df_b.columns = REQD_COLUMNS\n",
    "\n",
    "df_b = df_b.dropna(subset=['name'])\n",
    "df_b = df_b.fillna({'street':\"\", 'city':\"\", 'state':\"\", 'zip':\"\"})\n",
    "\n",
    "df_b['city'] = df_b['city'].value_counts().keys()[0]\n",
    "df_b['state'] = df_b['state'].value_counts().keys()[0]\n",
    "df_b['zip'] = df_b['zip'].apply(lambda x: \"\" if len(x) != 5 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "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>name</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1186 BROADWAY RESTAURANT LLC</td>\n",
       "      <td>30 W 26th Street Floor 12</td>\n",
       "      <td>NEW YORK</td>\n",
       "      <td>NY</td>\n",
       "      <td>10010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           name                     street      city state  \\\n",
       "0  1186 BROADWAY RESTAURANT LLC  30 W 26th Street Floor 12  NEW YORK    NY   \n",
       "\n",
       "     zip  \n",
       "0  10010  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>name</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MR. GATTI'S PIZZA - ABILENE, TX</td>\n",
       "      <td>2665 Buffalo Gap Rd</td>\n",
       "      <td>ABILENE</td>\n",
       "      <td>TX</td>\n",
       "      <td>79605</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              name               street     city state    zip\n",
       "0  MR. GATTI'S PIZZA - ABILENE, TX  2665 Buffalo Gap Rd  ABILENE    TX  79605"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_a.head(1)\n",
    "df_b.head(1)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Index>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<Compare>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "<Compare>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "<Compare>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Series([], dtype: int64)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>level_0</th>\n",
       "      <th>level_1</th>\n",
       "      <th>name_low</th>\n",
       "      <th>name_high</th>\n",
       "      <th>street</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [level_0, level_1, name_low, name_high, street, score]\n",
       "Index: []"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indexer = recordlinkage.Index()\n",
    "indexer.block('zip')\n",
    "\n",
    "candidates = indexer.index(df_a, df_b)\n",
    "print(len(candidates))\n",
    "\n",
    "compare = recordlinkage.Compare()\n",
    "# compare.exact('city', 'Provider City', label='City')\n",
    "compare.string('name',\n",
    "            'name',\n",
    "            method='jarowinkler',\n",
    "            threshold=0.75,\n",
    "            label='name_low')\n",
    "compare.string('name',\n",
    "            'name',\n",
    "            method='jarowinkler',\n",
    "            threshold=0.9,\n",
    "            label='name_high')\n",
    "compare.string('street',\n",
    "            'street',\n",
    "            method='jarowinkler',\n",
    "            threshold=0.85,\n",
    "            label='street')\n",
    "# compare.exact('zip',\n",
    "#             'zip',\n",
    "#             label='zip')\n",
    "features = compare.compute(candidates, df_a, df_b)\n",
    "features.sum(axis=1).value_counts().sort_index(ascending=False)\n",
    "\n",
    "potential_matches = features[features.sum(axis=1) > 1].reset_index()\n",
    "potential_matches['score'] = potential_matches.loc[:, 'name_low':'street'].sum(axis=1)\n",
    "potential_matches.head(5)"
   ]
  }
 ],
 "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.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}