notebooks/guides/end_to_end_em_guides/Basic EM Workflow Restaurants - 3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Basic EM workflow 3 (Restaurants data set)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "This IPython notebook explains a basic workflow two tables using *py_entitymatching*. Our goal is to come up with a workflow to match restaurants from Fodors and Zagat sites. Specifically, we want to maximize F1. The datasets contain information about the restaurants.\n",
    "\n",
    "First, we need to import *py_entitymatching* package and other libraries as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append('/Users/pradap/Documents/Research/Python-Package/anhaid/py_entitymatching/')\n",
    "\n",
    "import py_entitymatching as em\n",
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "python version: 2.7.13 | packaged by conda-forge | (default, May  2 2017, 13:29:36) \n",
      "[GCC 4.2.1 Compatible Apple LLVM 6.1.0 (clang-602.0.53)]\n",
      "pandas version: 0.20.3\n",
      "magellan version: 0.2.0\n"
     ]
    }
   ],
   "source": [
    "# Display the versions\n",
    "print('python version: ' + sys.version )\n",
    "print('pandas version: ' + pd.__version__ )\n",
    "print('magellan version: ' + em.__version__ )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Matching two tables typically consists of the following three steps:\n",
    "\n",
    "** 1. Reading the input tables **\n",
    "\n",
    "** 2. Blocking the input tables to get a candidate set **\n",
    "\n",
    "** 3. Matching the tuple pairs in the candidate set **"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read input tables\n",
    "\n",
    "We begin by loading the input tables. For the purpose of this guide, we use the datasets that are included with the package."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Get the paths\n",
    "path_A = em.get_install_path() + os.sep + 'datasets' + os.sep + 'end-to-end' + os.sep + 'restaurants/fodors.csv'\n",
    "path_B = em.get_install_path() + os.sep + 'datasets' + os.sep + 'end-to-end' + os.sep + 'restaurants/zagats.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "No handlers could be found for logger \"py_entitymatching.io.parsers\"\n"
     ]
    }
   ],
   "source": [
    "# Load csv files as dataframes and set the key attribute in the dataframe\n",
    "A = em.read_csv_metadata(path_A, key='id')\n",
    "B = em.read_csv_metadata(path_B, key='id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of tuples in A: 533\n",
      "Number of tuples in B: 331\n",
      "Number of tuples in A X B (i.e the cartesian product): 176423\n"
     ]
    }
   ],
   "source": [
    "print('Number of tuples in A: ' + str(len(A)))\n",
    "print('Number of tuples in B: ' + str(len(B)))\n",
    "print('Number of tuples in A X B (i.e the cartesian product): ' + str(len(A)*len(B)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>addr</th>\n",
       "      <th>city</th>\n",
       "      <th>phone</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>534</td>\n",
       "      <td>arnie mortons of chicago</td>\n",
       "      <td>435 s. la cienega blv.</td>\n",
       "      <td>los angeles</td>\n",
       "      <td>310/246-1501</td>\n",
       "      <td>american</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>535</td>\n",
       "      <td>arts delicatessen</td>\n",
       "      <td>12224 ventura blvd.</td>\n",
       "      <td>studio city</td>\n",
       "      <td>818/762-1221</td>\n",
       "      <td>american</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id                      name                    addr         city  \\\n",
       "0  534  arnie mortons of chicago  435 s. la cienega blv.  los angeles   \n",
       "1  535         arts delicatessen     12224 ventura blvd.  studio city   \n",
       "\n",
       "          phone      type  \n",
       "0  310/246-1501  american  \n",
       "1  818/762-1221  american  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "A.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>addr</th>\n",
       "      <th>city</th>\n",
       "      <th>phone</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>apple pan  the</td>\n",
       "      <td>10801 w. pico blvd.</td>\n",
       "      <td>west la</td>\n",
       "      <td>310-475-3585</td>\n",
       "      <td>american</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>asahi ramen</td>\n",
       "      <td>2027 sawtelle blvd.</td>\n",
       "      <td>west la</td>\n",
       "      <td>310-479-2231</td>\n",
       "      <td>noodle shops</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id            name                 addr     city         phone  \\\n",
       "0   1  apple pan  the  10801 w. pico blvd.  west la  310-475-3585   \n",
       "1   2     asahi ramen  2027 sawtelle blvd.  west la  310-479-2231   \n",
       "\n",
       "           type  \n",
       "0      american  \n",
       "1  noodle shops  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "B.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('id', 'id')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display the keys of the input tables\n",
    "em.get_key(A), em.get_key(B)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(147, 200)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# If the tables are large we can downsample the tables like this\n",
    "A1, B1 = em.down_sample(A, B, 200, 1, show_progress=False)\n",
    "len(A1), len(B1)\n",
    "\n",
    "# But for the purposes of this notebook, we will use the entire table A and B"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Block tables to get candidate set\n",
    "\n",
    "Before we do the matching, we would like to remove the obviously non-matching tuple pairs from the input tables. This would reduce the number of tuple pairs considered for matching.\n",
    "*py_entitymatching* provides four different blockers: (1) attribute equivalence, (2) overlap, (3) rule-based, and (4) black-box. The user can mix and match these blockers to form a blocking sequence applied to input tables.\n",
    "\n",
    "For the matching problem at hand, we know that two restaurants with different city names will not match. So we decide the apply blocking over names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Blocking plan\n",
    "\n",
    "# A, B -- attribute equiv. blocker [city] --------------------|---> candidate set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Create attribute equivalence blocker\n",
    "ab = em.AttrEquivalenceBlocker()\n",
    "\n",
    "# Block using city attribute\n",
    "C1 = ab.block_tables(A, B, 'city', 'city', \n",
    "                    l_output_attrs=['name', 'addr', 'city', 'phone'], \n",
    "                    r_output_attrs=['name', 'addr', 'city', 'phone']\n",
    "                    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10165"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(C1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Debug blocker output"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The number of tuple pairs considered for matching is reduced to 10165 (from 176423), but we would want to make sure that the blocker did not drop any potential matches. We could debug the blocker output in *py_entitymatching* as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Debug blocker output\n",
    "dbg = em.debug_blocker(C1, A, B, output_size=200)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>similarity</th>\n",
       "      <th>ltable_id</th>\n",
       "      <th>rtable_id</th>\n",
       "      <th>ltable_name</th>\n",
       "      <th>ltable_addr</th>\n",
       "      <th>ltable_city</th>\n",
       "      <th>ltable_phone</th>\n",
       "      <th>ltable_type</th>\n",
       "      <th>rtable_name</th>\n",
       "      <th>rtable_addr</th>\n",
       "      <th>rtable_city</th>\n",
       "      <th>rtable_phone</th>\n",
       "      <th>rtable_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>569</td>\n",
       "      <td>254</td>\n",
       "      <td>gotham bar &amp; grill</td>\n",
       "      <td>12 e. 12th st.</td>\n",
       "      <td>new york</td>\n",
       "      <td>212/620-4020</td>\n",
       "      <td>american</td>\n",
       "      <td>gotham bar &amp; grill</td>\n",
       "      <td>12 e. 12th st.</td>\n",
       "      <td>new york city</td>\n",
       "      <td>212-620-4020</td>\n",
       "      <td>american (new)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0.777778</td>\n",
       "      <td>561</td>\n",
       "      <td>246</td>\n",
       "      <td>cafe des artistes</td>\n",
       "      <td>1 w. 67th st.</td>\n",
       "      <td>new york</td>\n",
       "      <td>212/877-3500</td>\n",
       "      <td>continental</td>\n",
       "      <td>cafe des artistes</td>\n",
       "      <td>1 w. 67th st.</td>\n",
       "      <td>new york city</td>\n",
       "      <td>212-877-3500</td>\n",
       "      <td>french (classic)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>0.777778</td>\n",
       "      <td>599</td>\n",
       "      <td>284</td>\n",
       "      <td>union square cafe</td>\n",
       "      <td>21 e. 16th st.</td>\n",
       "      <td>new york</td>\n",
       "      <td>212/243-4020</td>\n",
       "      <td>american</td>\n",
       "      <td>union square cafe</td>\n",
       "      <td>21 e. 16th st.</td>\n",
       "      <td>new york city</td>\n",
       "      <td>212-243-4020</td>\n",
       "      <td>american (new)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>571</td>\n",
       "      <td>256</td>\n",
       "      <td>island spice</td>\n",
       "      <td>402 w. 44th st.</td>\n",
       "      <td>new york</td>\n",
       "      <td>212/765-1737</td>\n",
       "      <td>tel caribbean</td>\n",
       "      <td>island spice</td>\n",
       "      <td>402 w. 44th st.</td>\n",
       "      <td>new york city</td>\n",
       "      <td>212-765-1737</td>\n",
       "      <td>caribbean</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>572</td>\n",
       "      <td>257</td>\n",
       "      <td>jo jo</td>\n",
       "      <td>160 e. 64th st.</td>\n",
       "      <td>new york</td>\n",
       "      <td>212/223-5656</td>\n",
       "      <td>american</td>\n",
       "      <td>jo jo</td>\n",
       "      <td>160 e. 64th st.</td>\n",
       "      <td>new york city</td>\n",
       "      <td>212-223-5656</td>\n",
       "      <td>french bistro</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _id  similarity  ltable_id  rtable_id         ltable_name      ltable_addr  \\\n",
       "0    0    0.800000        569        254  gotham bar & grill   12 e. 12th st.   \n",
       "1    1    0.777778        561        246   cafe des artistes    1 w. 67th st.   \n",
       "2    2    0.777778        599        284   union square cafe   21 e. 16th st.   \n",
       "3    3    0.750000        571        256        island spice  402 w. 44th st.   \n",
       "4    4    0.750000        572        257               jo jo  160 e. 64th st.   \n",
       "\n",
       "  ltable_city  ltable_phone    ltable_type         rtable_name  \\\n",
       "0    new york  212/620-4020       american  gotham bar & grill   \n",
       "1    new york  212/877-3500    continental   cafe des artistes   \n",
       "2    new york  212/243-4020       american   union square cafe   \n",
       "3    new york  212/765-1737  tel caribbean        island spice   \n",
       "4    new york  212/223-5656       american               jo jo   \n",
       "\n",
       "       rtable_addr    rtable_city  rtable_phone       rtable_type  \n",
       "0   12 e. 12th st.  new york city  212-620-4020    american (new)  \n",
       "1    1 w. 67th st.  new york city  212-877-3500  french (classic)  \n",
       "2   21 e. 16th st.  new york city  212-243-4020    american (new)  \n",
       "3  402 w. 44th st.  new york city  212-765-1737         caribbean  \n",
       "4  160 e. 64th st.  new york city  212-223-5656     french bistro  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display first few tuple pairs from the debug_blocker's output\n",
    "dbg.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the debug blocker's output we observe that the current blocker drops quite a few potential matches. We would want to update the blocking sequence to avoid dropping these potential matches.\n",
    "\n",
    "For the considered dataset, we know that for the restaurants to match the  names must overlap between them. We could use overlap blocker for this purpose. Finally, we would want to union the outputs from the attribute equivalence blocker and the overlap blocker to get a consolidated candidate set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Updated blocking sequence\n",
    "# A, B ------ attribute equivalence [city] -----> C1--\n",
    "#                                                     |----> C\n",
    "# A, B ------ overlap blocker [name] --------> C2--"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2915"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create overlap blocker\n",
    "ob = em.OverlapBlocker()\n",
    "\n",
    "# Block tables using 'name' attribute \n",
    "C2 = ob.block_tables(A, B, 'name', 'name', \n",
    "                    l_output_attrs=['name', 'addr', 'city', 'phone'], \n",
    "                    r_output_attrs=['name', 'addr', 'city', 'phone'],\n",
    "                    overlap_size=1,\n",
    "                    show_progress=False\n",
    "                    )\n",
    "len(C2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>ltable_id</th>\n",
       "      <th>rtable_id</th>\n",
       "      <th>ltable_name</th>\n",
       "      <th>ltable_addr</th>\n",
       "      <th>ltable_city</th>\n",
       "      <th>ltable_phone</th>\n",
       "      <th>rtable_name</th>\n",
       "      <th>rtable_addr</th>\n",
       "      <th>rtable_city</th>\n",
       "      <th>rtable_phone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>543</td>\n",
       "      <td>1</td>\n",
       "      <td>grill on the alley</td>\n",
       "      <td>9560 dayton way</td>\n",
       "      <td>los angeles</td>\n",
       "      <td>310/276-0615</td>\n",
       "      <td>apple pan  the</td>\n",
       "      <td>10801 w. pico blvd.</td>\n",
       "      <td>west la</td>\n",
       "      <td>310-475-3585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>549</td>\n",
       "      <td>1</td>\n",
       "      <td>the palm</td>\n",
       "      <td>9001 santa monica blvd.</td>\n",
       "      <td>los angeles</td>\n",
       "      <td>310/550-8811</td>\n",
       "      <td>apple pan  the</td>\n",
       "      <td>10801 w. pico blvd.</td>\n",
       "      <td>west la</td>\n",
       "      <td>310-475-3585</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _id  ltable_id  rtable_id         ltable_name              ltable_addr  \\\n",
       "0    0        543          1  grill on the alley          9560 dayton way   \n",
       "1    1        549          1            the palm  9001 santa monica blvd.   \n",
       "\n",
       "   ltable_city  ltable_phone     rtable_name          rtable_addr rtable_city  \\\n",
       "0  los angeles  310/276-0615  apple pan  the  10801 w. pico blvd.     west la   \n",
       "1  los angeles  310/550-8811  apple pan  the  10801 w. pico blvd.     west la   \n",
       "\n",
       "   rtable_phone  \n",
       "0  310-475-3585  \n",
       "1  310-475-3585  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display first two rows from C2\n",
    "C2.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Combine blocker outputs\n",
    "C = em.combine_blocker_outputs_via_union([C1, C2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12530"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(C)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We observe that the number of tuple pairs considered for matching is increased to 12530 (from 10165). Now let us debug the blocker output again to check if the current blocker sequence is dropping any potential matches."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Debug again\n",
    "dbg = em.debug_blocker(C, A, B)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>similarity</th>\n",
       "      <th>ltable_id</th>\n",
       "      <th>rtable_id</th>\n",
       "      <th>ltable_name</th>\n",
       "      <th>ltable_addr</th>\n",
       "      <th>ltable_city</th>\n",
       "      <th>ltable_phone</th>\n",
       "      <th>ltable_type</th>\n",
       "      <th>rtable_name</th>\n",
       "      <th>rtable_addr</th>\n",
       "      <th>rtable_city</th>\n",
       "      <th>rtable_phone</th>\n",
       "      <th>rtable_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0.4</td>\n",
       "      <td>667</td>\n",
       "      <td>230</td>\n",
       "      <td>drais</td>\n",
       "      <td>730 n. la cienega blvd.</td>\n",
       "      <td>los angeles</td>\n",
       "      <td>310/358-8585</td>\n",
       "      <td>french</td>\n",
       "      <td>lorangerie</td>\n",
       "      <td>903 n. la cienega blvd.</td>\n",
       "      <td>w. hollywood</td>\n",
       "      <td>310-652-9770</td>\n",
       "      <td>french (classic)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0.4</td>\n",
       "      <td>548</td>\n",
       "      <td>230</td>\n",
       "      <td>matsuhisa</td>\n",
       "      <td>129 n. la cienega blvd.</td>\n",
       "      <td>beverly hills</td>\n",
       "      <td>310/659-9639</td>\n",
       "      <td>asian</td>\n",
       "      <td>lorangerie</td>\n",
       "      <td>903 n. la cienega blvd.</td>\n",
       "      <td>w. hollywood</td>\n",
       "      <td>310-652-9770</td>\n",
       "      <td>french (classic)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>0.4</td>\n",
       "      <td>545</td>\n",
       "      <td>233</td>\n",
       "      <td>lorangerie</td>\n",
       "      <td>903 n. la cienega blvd.</td>\n",
       "      <td>los angeles</td>\n",
       "      <td>310/652-9770</td>\n",
       "      <td>french</td>\n",
       "      <td>matsuhisa</td>\n",
       "      <td>129 n. la cienega blvd.</td>\n",
       "      <td>beverly hills</td>\n",
       "      <td>310-659-9639</td>\n",
       "      <td>seafood</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _id  similarity  ltable_id  rtable_id ltable_name              ltable_addr  \\\n",
       "0    0         0.4        667        230       drais  730 n. la cienega blvd.   \n",
       "1    1         0.4        548        230   matsuhisa  129 n. la cienega blvd.   \n",
       "2    2         0.4        545        233  lorangerie  903 n. la cienega blvd.   \n",
       "\n",
       "     ltable_city  ltable_phone ltable_type rtable_name  \\\n",
       "0    los angeles  310/358-8585      french  lorangerie   \n",
       "1  beverly hills  310/659-9639       asian  lorangerie   \n",
       "2    los angeles  310/652-9770      french   matsuhisa   \n",
       "\n",
       "               rtable_addr    rtable_city  rtable_phone       rtable_type  \n",
       "0  903 n. la cienega blvd.   w. hollywood  310-652-9770  french (classic)  \n",
       "1  903 n. la cienega blvd.   w. hollywood  310-652-9770  french (classic)  \n",
       "2  129 n. la cienega blvd.  beverly hills  310-659-9639           seafood  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display first few rows from the debugger output\n",
    "dbg.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We observe that the current blocker sequence does not drop obvious potential matches, and we can proceed with the matching step now. A subtle point to note here is, debugging blocker output practically provides a stopping criteria for modifying the blocker sequence.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Matching tuple pairs in the candidate set"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this step, we would want to match the tuple pairs in the candidate set. Specifically, we use learning-based method for matching purposes.\n",
    "This typically involves the following five steps:\n",
    "1. Sampling and labeling the candidate set\n",
    "2. Splitting the labeled data into development and evaluation set\n",
    "3. Selecting the best learning based matcher using the development set\n",
    "4. Evaluating the selected matcher using the evaluation set"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sampling and labeling the candidate set"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, we randomly sample 450 tuple pairs for labeling purposes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Sample  candidate set\n",
    "S = em.sample_table(C, 450)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we label the sampled candidate set. Specify we would enter 1 for a match and 0 for a non-match."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Label S\n",
    "G = em.label_table(S, 'gold')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the purposes of this guide, we will load in a pre-labeled dataset (of 450 tuple pairs) included in this package."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "450"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load the pre-labeled data\n",
    "path_G = em.get_install_path() + os.sep + 'datasets' + os.sep + 'end-to-end' + os.sep + 'restaurants/lbl_restnt_wf1.csv'\n",
    "G = em.read_csv_metadata(path_G, \n",
    "                         key='_id',\n",
    "                         ltable=A, rtable=B, \n",
    "                         fk_ltable='ltable_id', fk_rtable='rtable_id')\n",
    "len(G)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Splitting the labeled data into development and evaluation set"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this step, we split the labeled data into two sets: development (I) and evaluation (J). Specifically, the development set is used to come up with the best learning-based matcher and the evaluation set used to evaluate the selected matcher on unseen data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Split S into development set (I) and evaluation set (J)\n",
    "IJ = em.split_train_test(G, train_proportion=0.7, random_state=0)\n",
    "I = IJ['train']\n",
    "J = IJ['test']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selecting the best learning-based matcher "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Selecting the best learning-based matcher typically involves the following steps:\n",
    "\n",
    "1. Creating a set of learning-based matchers\n",
    "2. Creating features\n",
    "3. Converting the development set into feature vectors\n",
    "4. Selecting the best learning-based matcher using k-fold cross validation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating a set of learning-based matchers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Create a set of ML-matchers\n",
    "dt = em.DTMatcher(name='DecisionTree', random_state=0)\n",
    "svm = em.SVMMatcher(name='SVM', random_state=0)\n",
    "rf = em.RFMatcher(name='RF', random_state=0)\n",
    "lg = em.LogRegMatcher(name='LogReg', random_state=0)\n",
    "ln = em.LinRegMatcher(name='LinReg')\n",
    "nb = em.NBMatcher(name='NaiveBayes')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we need to create a set of features for the development set. *py_entitymatching* provides a way to automatically generate features based on the attributes in the input tables. For the purposes of this guide, we use the automatically generated features."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Generate features\n",
    "feature_table = em.get_features_for_matching(A, B, validate_inferred_attr_types=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                         id_id_exm\n",
       "1                         id_id_anm\n",
       "2                    id_id_lev_dist\n",
       "3                     id_id_lev_sim\n",
       "4         name_name_jac_qgm_3_qgm_3\n",
       "5     name_name_cos_dlm_dc0_dlm_dc0\n",
       "6     name_name_jac_dlm_dc0_dlm_dc0\n",
       "7                     name_name_mel\n",
       "8                name_name_lev_dist\n",
       "9                 name_name_lev_sim\n",
       "10                    name_name_nmw\n",
       "11                     name_name_sw\n",
       "12        addr_addr_jac_qgm_3_qgm_3\n",
       "13    addr_addr_cos_dlm_dc0_dlm_dc0\n",
       "14    addr_addr_jac_dlm_dc0_dlm_dc0\n",
       "15                    addr_addr_mel\n",
       "16               addr_addr_lev_dist\n",
       "17                addr_addr_lev_sim\n",
       "18                    addr_addr_nmw\n",
       "19                     addr_addr_sw\n",
       "20        city_city_jac_qgm_3_qgm_3\n",
       "21    city_city_cos_dlm_dc0_dlm_dc0\n",
       "22    city_city_jac_dlm_dc0_dlm_dc0\n",
       "23                    city_city_mel\n",
       "24               city_city_lev_dist\n",
       "25                city_city_lev_sim\n",
       "26                    city_city_nmw\n",
       "27                     city_city_sw\n",
       "28        type_type_jac_qgm_3_qgm_3\n",
       "29    type_type_cos_dlm_dc0_dlm_dc0\n",
       "30    type_type_jac_dlm_dc0_dlm_dc0\n",
       "31                    type_type_mel\n",
       "32               type_type_lev_dist\n",
       "33                type_type_lev_sim\n",
       "34                    type_type_nmw\n",
       "35                     type_type_sw\n",
       "Name: feature_name, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# List the names of the features generated\n",
    "feature_table['feature_name']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Converting the development set to feature vectors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Convert the I into a set of feature vectors using F\n",
    "H = em.extract_feature_vecs(I, \n",
    "                            feature_table=feature_table, \n",
    "                            attrs_after='gold',\n",
    "                            show_progress=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>ltable_id</th>\n",
       "      <th>rtable_id</th>\n",
       "      <th>id_id_exm</th>\n",
       "      <th>id_id_anm</th>\n",
       "      <th>id_id_lev_dist</th>\n",
       "      <th>id_id_lev_sim</th>\n",
       "      <th>name_name_jac_qgm_3_qgm_3</th>\n",
       "      <th>name_name_cos_dlm_dc0_dlm_dc0</th>\n",
       "      <th>name_name_jac_dlm_dc0_dlm_dc0</th>\n",
       "      <th>...</th>\n",
       "      <th>city_city_sw</th>\n",
       "      <th>type_type_jac_qgm_3_qgm_3</th>\n",
       "      <th>type_type_cos_dlm_dc0_dlm_dc0</th>\n",
       "      <th>type_type_jac_dlm_dc0_dlm_dc0</th>\n",
       "      <th>type_type_mel</th>\n",
       "      <th>type_type_lev_dist</th>\n",
       "      <th>type_type_lev_sim</th>\n",
       "      <th>type_type_nmw</th>\n",
       "      <th>type_type_sw</th>\n",
       "      <th>gold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>1790</td>\n",
       "      <td>563</td>\n",
       "      <td>248</td>\n",
       "      <td>0</td>\n",
       "      <td>0.440497</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.235294</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.883333</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.416667</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>794</td>\n",
       "      <td>544</td>\n",
       "      <td>116</td>\n",
       "      <td>0</td>\n",
       "      <td>0.213235</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.258065</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.466667</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>2315</td>\n",
       "      <td>589</td>\n",
       "      <td>305</td>\n",
       "      <td>0</td>\n",
       "      <td>0.517827</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.172414</td>\n",
       "      <td>0.353553</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.451923</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-11.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 40 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      _id  ltable_id  rtable_id  id_id_exm  id_id_anm  id_id_lev_dist  \\\n",
       "221  1790        563        248          0   0.440497               3   \n",
       "439   794        544        116          0   0.213235               3   \n",
       "191  2315        589        305          0   0.517827               3   \n",
       "\n",
       "     id_id_lev_sim  name_name_jac_qgm_3_qgm_3  name_name_cos_dlm_dc0_dlm_dc0  \\\n",
       "221            0.0                   1.000000                       1.000000   \n",
       "439            0.0                   0.258065                       0.500000   \n",
       "191            0.0                   0.172414                       0.353553   \n",
       "\n",
       "     name_name_jac_dlm_dc0_dlm_dc0  ...   city_city_sw  \\\n",
       "221                       1.000000  ...            8.0   \n",
       "439                       0.333333  ...            1.0   \n",
       "191                       0.200000  ...            1.0   \n",
       "\n",
       "     type_type_jac_qgm_3_qgm_3  type_type_cos_dlm_dc0_dlm_dc0  \\\n",
       "221                   0.235294                            0.0   \n",
       "439                   0.000000                            0.0   \n",
       "191                   0.000000                            0.0   \n",
       "\n",
       "     type_type_jac_dlm_dc0_dlm_dc0  type_type_mel  type_type_lev_dist  \\\n",
       "221                            0.0       0.883333                 7.0   \n",
       "439                            0.0       0.466667                 4.0   \n",
       "191                            0.0       0.451923                24.0   \n",
       "\n",
       "     type_type_lev_sim  type_type_nmw  type_type_sw  gold  \n",
       "221           0.416667           -2.0           4.0     1  \n",
       "439           0.200000            0.0           1.0     0  \n",
       "191           0.000000          -11.0           2.0     0  \n",
       "\n",
       "[3 rows x 40 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display first few rows\n",
    "H.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting the best matcher using cross-validation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we select the best matcher using k-fold cross-validation. For the purposes of this guide, we use five fold cross validation and use 'precision' and 'recall' metric to select the best matcher."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Matcher</th>\n",
       "      <th>Average precision</th>\n",
       "      <th>Average recall</th>\n",
       "      <th>Average f1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DecisionTree</td>\n",
       "      <td>0.976471</td>\n",
       "      <td>0.936450</td>\n",
       "      <td>0.954078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>RF</td>\n",
       "      <td>0.975735</td>\n",
       "      <td>0.963235</td>\n",
       "      <td>0.968450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SVM</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.297437</td>\n",
       "      <td>0.440952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LinReg</td>\n",
       "      <td>0.944444</td>\n",
       "      <td>0.920497</td>\n",
       "      <td>0.929657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LogReg</td>\n",
       "      <td>0.974603</td>\n",
       "      <td>0.948214</td>\n",
       "      <td>0.960212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaiveBayes</td>\n",
       "      <td>0.953947</td>\n",
       "      <td>0.973214</td>\n",
       "      <td>0.962530</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Matcher  Average precision  Average recall  Average f1\n",
       "0  DecisionTree           0.976471        0.936450    0.954078\n",
       "1            RF           0.975735        0.963235    0.968450\n",
       "2           SVM           1.000000        0.297437    0.440952\n",
       "3        LinReg           0.944444        0.920497    0.929657\n",
       "4        LogReg           0.974603        0.948214    0.960212\n",
       "5    NaiveBayes           0.953947        0.973214    0.962530"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select the best ML matcher using CV\n",
    "result = em.select_matcher([dt, rf, svm, ln, lg, nb], table=H, \n",
    "        exclude_attrs=['_id', 'ltable_id', 'rtable_id', 'gold'],\n",
    "        k=5,\n",
    "        target_attr='gold', metric_to_select_matcher='f1', random_state=0)\n",
    "result['cv_stats']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Debugging matcher"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We observe that the best matcher is not maximizing F1. We debug the matcher to see what might be wrong.\n",
    "To do this, first we split the feature vectors into train and test."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#  Split feature vectors into train and test\n",
    "UV = em.split_train_test(H, train_proportion=0.5)\n",
    "U = UV['train']\n",
    "V = UV['test']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we debug the matcher using GUI. For the purposes of this guide, we use random forest matcher for debugging purposes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Debug decision tree using GUI\n",
    "em.vis_debug_rf(rf, U, V, \n",
    "        exclude_attrs=['_id', 'ltable_id', 'rtable_id', 'gold'],\n",
    "        target_attr='gold')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the GUI, we observe that phone numbers seem to be an important attribute, but they are in different format. Current features does not capture and adding a feature incorporating this difference in format can potentially improve \n",
    "the F1 numbers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def phone_phone_feature(ltuple, rtuple):\n",
    "    p1 = ltuple.phone\n",
    "    p2 = rtuple.phone\n",
    "    p1 = p1.replace('/','-')\n",
    "    p1 = p1.replace(' ','')\n",
    "    p2 = p2.replace('/','-')\n",
    "    p2 = p2.replace(' ','')    \n",
    "    if p1 == p2:\n",
    "        return 1.0\n",
    "    else:\n",
    "        return 0.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The table shows the corresponding attributes along with their respective types.\n",
      "Please confirm that the information  has been correctly inferred.\n",
      "If you would like to skip this validation process in the future,\n",
      "please set the flag validate_inferred_attr_types equal to false.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Left Attribute</th>\n",
       "      <th>Right Attribute</th>\n",
       "      <th>Left Attribute Type</th>\n",
       "      <th>Right Attribute Type</th>\n",
       "      <th>Example Features</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>id</td>\n",
       "      <td>id</td>\n",
       "      <td>numeric</td>\n",
       "      <td>numeric</td>\n",
       "      <td>Exact Match; Absolute Norm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>name</td>\n",
       "      <td>name</td>\n",
       "      <td>short string (1 word to 5 words)</td>\n",
       "      <td>short string (1 word to 5 words)</td>\n",
       "      <td>Jaccard Similarity [3-grams, 3-grams]; Cosine Similarity [Space Delimiter, Space Delimiter]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>addr</td>\n",
       "      <td>addr</td>\n",
       "      <td>short string (1 word to 5 words)</td>\n",
       "      <td>short string (1 word to 5 words)</td>\n",
       "      <td>Jaccard Similarity [3-grams, 3-grams]; Cosine Similarity [Space Delimiter, Space Delimiter]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>city</td>\n",
       "      <td>city</td>\n",
       "      <td>short string (1 word to 5 words)</td>\n",
       "      <td>short string (1 word to 5 words)</td>\n",
       "      <td>Jaccard Similarity [3-grams, 3-grams]; Cosine Similarity [Space Delimiter, Space Delimiter]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>phone</td>\n",
       "      <td>phone</td>\n",
       "      <td>short string (1 word to 5 words)</td>\n",
       "      <td>short string (1 word)</td>\n",
       "      <td>Not Applicable: Types do not match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>type</td>\n",
       "      <td>type</td>\n",
       "      <td>short string (1 word to 5 words)</td>\n",
       "      <td>short string (1 word to 5 words)</td>\n",
       "      <td>Jaccard Similarity [3-grams, 3-grams]; Cosine Similarity [Space Delimiter, Space Delimiter]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Left Attribute Right Attribute               Left Attribute Type  \\\n",
       "0             id              id                           numeric   \n",
       "1           name            name  short string (1 word to 5 words)   \n",
       "2           addr            addr  short string (1 word to 5 words)   \n",
       "3           city            city  short string (1 word to 5 words)   \n",
       "4          phone           phone  short string (1 word to 5 words)   \n",
       "5           type            type  short string (1 word to 5 words)   \n",
       "\n",
       "               Right Attribute Type  \\\n",
       "0                           numeric   \n",
       "1  short string (1 word to 5 words)   \n",
       "2  short string (1 word to 5 words)   \n",
       "3  short string (1 word to 5 words)   \n",
       "4             short string (1 word)   \n",
       "5  short string (1 word to 5 words)   \n",
       "\n",
       "                                                                              Example Features  \n",
       "0                                                                   Exact Match; Absolute Norm  \n",
       "1  Jaccard Similarity [3-grams, 3-grams]; Cosine Similarity [Space Delimiter, Space Delimiter]  \n",
       "2  Jaccard Similarity [3-grams, 3-grams]; Cosine Similarity [Space Delimiter, Space Delimiter]  \n",
       "3  Jaccard Similarity [3-grams, 3-grams]; Cosine Similarity [Space Delimiter, Space Delimiter]  \n",
       "4                                                           Not Applicable: Types do not match  \n",
       "5  Jaccard Similarity [3-grams, 3-grams]; Cosine Similarity [Space Delimiter, Space Delimiter]  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Do you want to proceed? (y/n):y\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_table = em.get_features_for_matching(A, B)\n",
    "em.add_blackbox_feature(feature_table, 'phone_phone_feature', phone_phone_feature)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we repeat extracting feature vectors (this time with updated feature table), imputing table and selecting the best matcher again using cross-validation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "H = em.extract_feature_vecs(I, feature_table=feature_table, attrs_after='gold', show_progress=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Matcher</th>\n",
       "      <th>Average precision</th>\n",
       "      <th>Average recall</th>\n",
       "      <th>Average f1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DecisionTree</td>\n",
       "      <td>0.986667</td>\n",
       "      <td>0.974167</td>\n",
       "      <td>0.979755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>RF</td>\n",
       "      <td>0.988889</td>\n",
       "      <td>0.987500</td>\n",
       "      <td>0.987834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SVM</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.297437</td>\n",
       "      <td>0.440952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LinReg</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.974167</td>\n",
       "      <td>0.986652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LogReg</td>\n",
       "      <td>0.974603</td>\n",
       "      <td>0.948214</td>\n",
       "      <td>0.960212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaiveBayes</td>\n",
       "      <td>0.976389</td>\n",
       "      <td>0.985714</td>\n",
       "      <td>0.980427</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Matcher  Average precision  Average recall  Average f1\n",
       "0  DecisionTree           0.986667        0.974167    0.979755\n",
       "1            RF           0.988889        0.987500    0.987834\n",
       "2           SVM           1.000000        0.297437    0.440952\n",
       "3        LinReg           1.000000        0.974167    0.986652\n",
       "4        LogReg           0.974603        0.948214    0.960212\n",
       "5    NaiveBayes           0.976389        0.985714    0.980427"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select the best ML matcher using CV\n",
    "result = em.select_matcher([dt, rf, svm, ln, lg, nb], table=H, \n",
    "        exclude_attrs=['_id', 'ltable_id', 'rtable_id', 'gold'],\n",
    "        k=5,\n",
    "        target_attr='gold', metric_to_select_matcher='f1', random_state=0)\n",
    "result['cv_stats']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, observe the best matcher is achieving a better F1. Let us stop here and  proceed on to evaluating the best matcher on the unseen data (the evaluation set)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Evaluating the matching output"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Evaluating the matching outputs for the evaluation set typically involves the following four steps:\n",
    "1. Converting the evaluation set to feature vectors\n",
    "2. Training matcher using the feature vectors extracted from the development set\n",
    "3. Predicting the evaluation set using the trained matcher\n",
    "4. Evaluating the predicted matches"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Converting the evaluation set to  feature vectors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As before, we convert to the feature vectors (using the feature table and the evaluation set)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Convert J into a set of feature vectors using feature table\n",
    "L = em.extract_feature_vecs(J, feature_table=feature_table,\n",
    "                            attrs_after='gold', show_progress=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Training the selected matcher"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we train the matcher using all of the feature vectors from the development set. For the purposes of this guide we use random forest as the selected matcher."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Train using feature vectors from I \n",
    "rf.fit(table=H, \n",
    "       exclude_attrs=['_id', 'ltable_id', 'rtable_id', 'gold'], \n",
    "       target_attr='gold')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Predicting the matches"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we predict the matches for the evaluation set (using the feature vectors extracted from it)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Predict on L \n",
    "predictions = rf.predict(table=L, exclude_attrs=['_id', 'ltable_id', 'rtable_id', 'gold'], \n",
    "              append=True, target_attr='predicted', inplace=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Evaluating the predictions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we evaluate the accuracy of predicted outputs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Precision : 100.0% (34/34)\n",
      "Recall : 100.0% (34/34)\n",
      "F1 : 100.0%\n",
      "False positives : 0 (out of 34 positive predictions)\n",
      "False negatives : 0 (out of 101 negative predictions)\n"
     ]
    }
   ],
   "source": [
    "# Evaluate the predictions\n",
    "eval_result = em.eval_matches(predictions, 'gold', 'predicted')\n",
    "em.print_eval_summary(eval_result)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}