Entity_Matching_Tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pd.set_option('display.max_colwidth', None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load data for each source"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we load the data from each source and inspect how it looks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of records in source 1: 84\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>name</th>\n",
       "      <th>address</th>\n",
       "      <th>phone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a1</td>\n",
       "      <td>Westerns Country Inn</td>\n",
       "      <td>447 West Route 77, Johnscity, AZ 86033, United States of America</td>\n",
       "      <td>928-345-4567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a2</td>\n",
       "      <td>Natpolin Hostel</td>\n",
       "      <td>Kanuk Ayasofya Mahallesi Yeğen Sok. No:3/1 Sultanahmet , Fatih, 34110 Istanbul, Turkey</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a3</td>\n",
       "      <td>Sleep In! Cairo Hostel</td>\n",
       "      <td>11 B,Ramsis St. Marouf Tower.Downtown, 99999 Cairo, Egypt</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a4</td>\n",
       "      <td>Mekking House</td>\n",
       "      <td>11 Lorong Carnarvon, 10220 George Town, Malaysia</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a5</td>\n",
       "      <td>Rolando &amp; Chateaux Palazzo Seneca</td>\n",
       "      <td>Via Cesare Battisti 5, 06044 Isla, Italy</td>\n",
       "      <td>0712 567834</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID                               name  \\\n",
       "0  a1               Westerns Country Inn   \n",
       "1  a2                    Natpolin Hostel   \n",
       "2  a3             Sleep In! Cairo Hostel   \n",
       "3  a4                      Mekking House   \n",
       "4  a5  Rolando & Chateaux Palazzo Seneca   \n",
       "\n",
       "                                                                                  address  \\\n",
       "0                        447 West Route 77, Johnscity, AZ 86033, United States of America   \n",
       "1  Kanuk Ayasofya Mahallesi Yeğen Sok. No:3/1 Sultanahmet , Fatih, 34110 Istanbul, Turkey   \n",
       "2                               11 B,Ramsis St. Marouf Tower.Downtown, 99999 Cairo, Egypt   \n",
       "3                                        11 Lorong Carnarvon, 10220 George Town, Malaysia   \n",
       "4                                                Via Cesare Battisti 5, 06044 Isla, Italy   \n",
       "\n",
       "          phone  \n",
       "0  928-345-4567  \n",
       "1                \n",
       "2                \n",
       "3                \n",
       "4   0712 567834  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_source1 = pd.read_csv(\"./data/source_one.csv\")\n",
    "df_source1 = df_source1.fillna('')  # normalize NaN values\n",
    "print(\"Number of records in source 1:\", len(df_source1))\n",
    "df_source1.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of records in source 2: 84\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>zip</th>\n",
       "      <th>street</th>\n",
       "      <th>phone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ba1</td>\n",
       "      <td>Westerns Country Inn Bed &amp; Breakfast</td>\n",
       "      <td>Johnscity</td>\n",
       "      <td>United States</td>\n",
       "      <td>86033</td>\n",
       "      <td>West Route 77 447</td>\n",
       "      <td>+1 928-345-4567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ba2</td>\n",
       "      <td>Natpolin Hotel</td>\n",
       "      <td>Istanbul</td>\n",
       "      <td>Turkey</td>\n",
       "      <td>34110</td>\n",
       "      <td>Kanuk Ayasofya Mahallesi Yeğen Sok. No:3/1 Sultanahmet , Fatih, ,</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ba3</td>\n",
       "      <td>Sleep IN Cairo Hostel</td>\n",
       "      <td>Cairo</td>\n",
       "      <td>Egypt</td>\n",
       "      <td>11511</td>\n",
       "      <td>11 B, Ramses Street, Downtown</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ba4</td>\n",
       "      <td>Mekking House</td>\n",
       "      <td>George Town</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>10220</td>\n",
       "      <td>11 Lorong Carnarvon</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ba5</td>\n",
       "      <td>Palazzo Seneca</td>\n",
       "      <td>Isla</td>\n",
       "      <td>Italy</td>\n",
       "      <td>6046</td>\n",
       "      <td>Via Cesare Battisti, 5</td>\n",
       "      <td>+39 0712 567834</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID                                  name         city        country  \\\n",
       "0  ba1  Westerns Country Inn Bed & Breakfast    Johnscity  United States   \n",
       "1  ba2                        Natpolin Hotel     Istanbul         Turkey   \n",
       "2  ba3                 Sleep IN Cairo Hostel        Cairo          Egypt   \n",
       "3  ba4                         Mekking House  George Town       Malaysia   \n",
       "4  ba5                        Palazzo Seneca         Isla          Italy   \n",
       "\n",
       "     zip                                                             street  \\\n",
       "0  86033                                                  West Route 77 447   \n",
       "1  34110  Kanuk Ayasofya Mahallesi Yeğen Sok. No:3/1 Sultanahmet , Fatih, ,   \n",
       "2  11511                                      11 B, Ramses Street, Downtown   \n",
       "3  10220                                                11 Lorong Carnarvon   \n",
       "4   6046                                             Via Cesare Battisti, 5   \n",
       "\n",
       "             phone  \n",
       "0  +1 928-345-4567  \n",
       "1                   \n",
       "2                   \n",
       "3                   \n",
       "4  +39 0712 567834  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_source2 = pd.read_csv(\"./data/source_two.csv\")\n",
    "df_source2 = df_source2.fillna('')  # normalize NaN values\n",
    "print(\"Number of records in source 2:\", len(df_source2))\n",
    "df_source2.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Normalize record identifiers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this particular example, source one has the following columns: name, address and phone. Source two has name, city, country, zip, street and phone. \n",
    "\n",
    "In order to compare each record identifier separately, we need to make sure that the two datasets have the same columns. In this case, we have two options: \n",
    "\n",
    "* in the second source, combine each single address identifier per record into one address column\n",
    "* in the first source, programmatically extract city, country, zip and street from the single address column\n",
    "\n",
    "The second option will allow us to be more precise in our comparisons and will also help us create better record candidate pairs. Fortunately, there are tools in the Python ecosystem that will allow us to do such data pre-preprocessing.\n",
    "\n",
    "We start with extracting specific address information from the first source by using a Python package called \"postal\" (https://github.com/openvenues/pypostal)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from postal.parser import parse_address"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def split_address_column(address_column):\n",
    "    \"\"\"\n",
    "    This function splits a single address string into city, country, zip code and street by using the postal.parse_address function.\n",
    "    \"\"\"\n",
    "    result = parse_address(address_column)\n",
    "    street_elements = []\n",
    "    \n",
    "    street = \"\"\n",
    "    city = \"\"\n",
    "    zipcode = \"\"\n",
    "    country = \"\"\n",
    "    \n",
    "    for element in result:\n",
    "        if element[1] == \"city\":\n",
    "            city = element[0]\n",
    "        elif element[1] == \"postcode\":\n",
    "            zipcode = element[0]\n",
    "        elif element[1] == \"country\":\n",
    "            country = element[0]\n",
    "        else:\n",
    "            street_elements.append(element[0])\n",
    "            \n",
    "    street = \" \".join(street_elements)\n",
    "    \n",
    "    return city, country, zipcode, street"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_source1[['city','country', \"zip\", \"street\"]] = df_source1.address.apply(lambda x: pd.Series(split_address_column(x)))\n",
    "del df_source1[\"address\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the above commands we have split the address column from source 1  into city, country, zipcode and street. The pstal Python library has also did some internal pre-processing of the strings, which is unfortunately not easily reversable without modifying the library itself, however, we will use similar pre-processing on the data from source 2 later on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>name</th>\n",
       "      <th>phone</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>zip</th>\n",
       "      <th>street</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a1</td>\n",
       "      <td>Westerns Country Inn</td>\n",
       "      <td>928-345-4567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>united states of america</td>\n",
       "      <td>86033</td>\n",
       "      <td>447 west route 77 az</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a2</td>\n",
       "      <td>Natpolin Hostel</td>\n",
       "      <td></td>\n",
       "      <td>istanbul</td>\n",
       "      <td>turkey</td>\n",
       "      <td>34110</td>\n",
       "      <td>kanuk ayasofya mahallesi yeğen sok. no 3/1 sultanahmet fatih</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a3</td>\n",
       "      <td>Sleep In! Cairo Hostel</td>\n",
       "      <td></td>\n",
       "      <td>cairo</td>\n",
       "      <td>egypt</td>\n",
       "      <td>99999</td>\n",
       "      <td>11 b ramsis st. marouf tower.downtown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a4</td>\n",
       "      <td>Mekking House</td>\n",
       "      <td></td>\n",
       "      <td>george town</td>\n",
       "      <td>malaysia</td>\n",
       "      <td></td>\n",
       "      <td>11 lorong carnarvon 10220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a5</td>\n",
       "      <td>Rolando &amp; Chateaux Palazzo Seneca</td>\n",
       "      <td>0712 567834</td>\n",
       "      <td>isla</td>\n",
       "      <td>italy</td>\n",
       "      <td>06044</td>\n",
       "      <td>via cesare battisti 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>a495</td>\n",
       "      <td>OldCity Open-hotel - Suites &amp; Apartments</td>\n",
       "      <td></td>\n",
       "      <td>cairo</td>\n",
       "      <td>egypt</td>\n",
       "      <td>99999</td>\n",
       "      <td>39 chark compound campus new cairo city</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>a496</td>\n",
       "      <td>Live Party Bed &amp; Breakfast</td>\n",
       "      <td></td>\n",
       "      <td>desenzano del garda</td>\n",
       "      <td>italy</td>\n",
       "      <td>25015</td>\n",
       "      <td>woal cavour 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>a497</td>\n",
       "      <td>Lala Hostala</td>\n",
       "      <td></td>\n",
       "      <td>chefchaouen</td>\n",
       "      <td>morocco</td>\n",
       "      <td>91000</td>\n",
       "      <td>lalla lambda 22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>a498</td>\n",
       "      <td>Orka Mountain villa 10</td>\n",
       "      <td></td>\n",
       "      <td>ovacik</td>\n",
       "      <td>turkey</td>\n",
       "      <td>48340</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>a499</td>\n",
       "      <td>Arltanka Silver Triangle</td>\n",
       "      <td></td>\n",
       "      <td>golden triangle</td>\n",
       "      <td>thailand</td>\n",
       "      <td>57150</td>\n",
       "      <td>11 moo 1 chiang ikan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID                                      name         phone  \\\n",
       "0     a1                      Westerns Country Inn  928-345-4567   \n",
       "1     a2                           Natpolin Hostel                 \n",
       "2     a3                    Sleep In! Cairo Hostel                 \n",
       "3     a4                             Mekking House                 \n",
       "4     a5         Rolando & Chateaux Palazzo Seneca   0712 567834   \n",
       "..   ...                                       ...           ...   \n",
       "79  a495  OldCity Open-hotel - Suites & Apartments                 \n",
       "80  a496                Live Party Bed & Breakfast                 \n",
       "81  a497                              Lala Hostala                 \n",
       "82  a498                    Orka Mountain villa 10                 \n",
       "83  a499                  Arltanka Silver Triangle                 \n",
       "\n",
       "                   city                   country    zip  \\\n",
       "0             johnscity  united states of america  86033   \n",
       "1              istanbul                    turkey  34110   \n",
       "2                 cairo                     egypt  99999   \n",
       "3           george town                  malaysia          \n",
       "4                  isla                     italy  06044   \n",
       "..                  ...                       ...    ...   \n",
       "79                cairo                     egypt  99999   \n",
       "80  desenzano del garda                     italy  25015   \n",
       "81          chefchaouen                   morocco  91000   \n",
       "82               ovacik                    turkey  48340   \n",
       "83      golden triangle                  thailand  57150   \n",
       "\n",
       "                                                          street  \n",
       "0                                           447 west route 77 az  \n",
       "1   kanuk ayasofya mahallesi yeğen sok. no 3/1 sultanahmet fatih  \n",
       "2                          11 b ramsis st. marouf tower.downtown  \n",
       "3                                      11 lorong carnarvon 10220  \n",
       "4                                          via cesare battisti 5  \n",
       "..                                                           ...  \n",
       "79                       39 chark compound campus new cairo city  \n",
       "80                                                 woal cavour 2  \n",
       "81                                               lalla lambda 22  \n",
       "82                                                                \n",
       "83                                          11 moo 1 chiang ikan  \n",
       "\n",
       "[84 rows x 7 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_source1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Pre-processing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We still have multiple inconsistencies between data sources in terms of how each column is repsented. If investigate each column in more detail we can see that:\n",
    "* the country names are inconsistent, for example, in one source we can see \"United States\", while in the other \"United States of America\"\n",
    "* phone number in source 1 have no country code, while the records in source 2 have the country code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# lowercase all text fields\n",
    "df_source1=df_source1.apply(lambda x: x.astype(str).str.lower() if(x.dtype == 'object') else x)\n",
    "df_source2=df_source2.apply(lambda x: x.astype(str).str.lower() if(x.dtype == 'object') else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>zip</th>\n",
       "      <th>street</th>\n",
       "      <th>phone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ba1</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>united states</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>+1 928-345-4567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ba2</td>\n",
       "      <td>natpolin hotel</td>\n",
       "      <td>istanbul</td>\n",
       "      <td>turkey</td>\n",
       "      <td>34110</td>\n",
       "      <td>kanuk ayasofya mahallesi yeğen sok. no:3/1 sultanahmet , fatih, ,</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ba3</td>\n",
       "      <td>sleep in cairo hostel</td>\n",
       "      <td>cairo</td>\n",
       "      <td>egypt</td>\n",
       "      <td>11511</td>\n",
       "      <td>11 b, ramses street, downtown</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ba4</td>\n",
       "      <td>mekking house</td>\n",
       "      <td>george town</td>\n",
       "      <td>malaysia</td>\n",
       "      <td>10220</td>\n",
       "      <td>11 lorong carnarvon</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ba5</td>\n",
       "      <td>palazzo seneca</td>\n",
       "      <td>isla</td>\n",
       "      <td>italy</td>\n",
       "      <td>6046</td>\n",
       "      <td>via cesare battisti, 5</td>\n",
       "      <td>+39 0712 567834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>ba495</td>\n",
       "      <td>newcity suites &amp; apartments</td>\n",
       "      <td>cairo</td>\n",
       "      <td>egypt</td>\n",
       "      <td>11835</td>\n",
       "      <td>23 arban compound infront of new aac campus- new cairo</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>ba496</td>\n",
       "      <td>bed &amp; breakfast party house</td>\n",
       "      <td>desenzano del garda</td>\n",
       "      <td>italy</td>\n",
       "      <td>25015</td>\n",
       "      <td>via woal verdi 10</td>\n",
       "      <td>+39 44 33 443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>ba497</td>\n",
       "      <td>hostal la lambada</td>\n",
       "      <td>chefchaouene</td>\n",
       "      <td>morocco</td>\n",
       "      <td>91000</td>\n",
       "      <td>rue sidi ahme</td>\n",
       "      <td>+212 44-33445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>ba498</td>\n",
       "      <td>orka my villa</td>\n",
       "      <td>istanbul</td>\n",
       "      <td>turkey</td>\n",
       "      <td>48345</td>\n",
       "      <td>ovacık mahallesi, istanbul caddesi</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>ba499</td>\n",
       "      <td>golden triangle resort</td>\n",
       "      <td>chiang saen</td>\n",
       "      <td>thailand</td>\n",
       "      <td>57150</td>\n",
       "      <td>234 golden triangle, chiang saen</td>\n",
       "      <td>+66 44 445 444</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID                                  name                 city  \\\n",
       "0     ba1  westerns country inn bed & breakfast            johnscity   \n",
       "1     ba2                        natpolin hotel             istanbul   \n",
       "2     ba3                 sleep in cairo hostel                cairo   \n",
       "3     ba4                         mekking house          george town   \n",
       "4     ba5                        palazzo seneca                 isla   \n",
       "..    ...                                   ...                  ...   \n",
       "79  ba495           newcity suites & apartments                cairo   \n",
       "80  ba496           bed & breakfast party house  desenzano del garda   \n",
       "81  ba497                     hostal la lambada         chefchaouene   \n",
       "82  ba498                         orka my villa             istanbul   \n",
       "83  ba499                golden triangle resort          chiang saen   \n",
       "\n",
       "          country    zip  \\\n",
       "0   united states  86033   \n",
       "1          turkey  34110   \n",
       "2           egypt  11511   \n",
       "3        malaysia  10220   \n",
       "4           italy   6046   \n",
       "..            ...    ...   \n",
       "79          egypt  11835   \n",
       "80          italy  25015   \n",
       "81        morocco  91000   \n",
       "82         turkey  48345   \n",
       "83       thailand  57150   \n",
       "\n",
       "                                                               street  \\\n",
       "0                                                   west route 77 447   \n",
       "1   kanuk ayasofya mahallesi yeğen sok. no:3/1 sultanahmet , fatih, ,   \n",
       "2                                       11 b, ramses street, downtown   \n",
       "3                                                 11 lorong carnarvon   \n",
       "4                                              via cesare battisti, 5   \n",
       "..                                                                ...   \n",
       "79             23 arban compound infront of new aac campus- new cairo   \n",
       "80                                                  via woal verdi 10   \n",
       "81                                                      rue sidi ahme   \n",
       "82                                 ovacık mahallesi, istanbul caddesi   \n",
       "83                                   234 golden triangle, chiang saen   \n",
       "\n",
       "              phone  \n",
       "0   +1 928-345-4567  \n",
       "1                    \n",
       "2                    \n",
       "3                    \n",
       "4   +39 0712 567834  \n",
       "..              ...  \n",
       "79                   \n",
       "80    +39 44 33 443  \n",
       "81    +212 44-33445  \n",
       "82                   \n",
       "83   +66 44 445 444  \n",
       "\n",
       "[84 rows x 7 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_source2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>name</th>\n",
       "      <th>phone</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>zip</th>\n",
       "      <th>street</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a1</td>\n",
       "      <td>westerns country inn</td>\n",
       "      <td>928-345-4567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>united states of america</td>\n",
       "      <td>86033</td>\n",
       "      <td>447 west route 77 az</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a2</td>\n",
       "      <td>natpolin hostel</td>\n",
       "      <td></td>\n",
       "      <td>istanbul</td>\n",
       "      <td>turkey</td>\n",
       "      <td>34110</td>\n",
       "      <td>kanuk ayasofya mahallesi yeğen sok. no 3/1 sultanahmet fatih</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a3</td>\n",
       "      <td>sleep in! cairo hostel</td>\n",
       "      <td></td>\n",
       "      <td>cairo</td>\n",
       "      <td>egypt</td>\n",
       "      <td>99999</td>\n",
       "      <td>11 b ramsis st. marouf tower.downtown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a4</td>\n",
       "      <td>mekking house</td>\n",
       "      <td></td>\n",
       "      <td>george town</td>\n",
       "      <td>malaysia</td>\n",
       "      <td></td>\n",
       "      <td>11 lorong carnarvon 10220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a5</td>\n",
       "      <td>rolando &amp; chateaux palazzo seneca</td>\n",
       "      <td>0712 567834</td>\n",
       "      <td>isla</td>\n",
       "      <td>italy</td>\n",
       "      <td>06044</td>\n",
       "      <td>via cesare battisti 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>a495</td>\n",
       "      <td>oldcity open-hotel - suites &amp; apartments</td>\n",
       "      <td></td>\n",
       "      <td>cairo</td>\n",
       "      <td>egypt</td>\n",
       "      <td>99999</td>\n",
       "      <td>39 chark compound campus new cairo city</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>a496</td>\n",
       "      <td>live party bed &amp; breakfast</td>\n",
       "      <td></td>\n",
       "      <td>desenzano del garda</td>\n",
       "      <td>italy</td>\n",
       "      <td>25015</td>\n",
       "      <td>woal cavour 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>a497</td>\n",
       "      <td>lala hostala</td>\n",
       "      <td></td>\n",
       "      <td>chefchaouen</td>\n",
       "      <td>morocco</td>\n",
       "      <td>91000</td>\n",
       "      <td>lalla lambda 22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>a498</td>\n",
       "      <td>orka mountain villa 10</td>\n",
       "      <td></td>\n",
       "      <td>ovacik</td>\n",
       "      <td>turkey</td>\n",
       "      <td>48340</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>a499</td>\n",
       "      <td>arltanka silver triangle</td>\n",
       "      <td></td>\n",
       "      <td>golden triangle</td>\n",
       "      <td>thailand</td>\n",
       "      <td>57150</td>\n",
       "      <td>11 moo 1 chiang ikan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID                                      name         phone  \\\n",
       "0     a1                      westerns country inn  928-345-4567   \n",
       "1     a2                           natpolin hostel                 \n",
       "2     a3                    sleep in! cairo hostel                 \n",
       "3     a4                             mekking house                 \n",
       "4     a5         rolando & chateaux palazzo seneca   0712 567834   \n",
       "..   ...                                       ...           ...   \n",
       "79  a495  oldcity open-hotel - suites & apartments                 \n",
       "80  a496                live party bed & breakfast                 \n",
       "81  a497                              lala hostala                 \n",
       "82  a498                    orka mountain villa 10                 \n",
       "83  a499                  arltanka silver triangle                 \n",
       "\n",
       "                   city                   country    zip  \\\n",
       "0             johnscity  united states of america  86033   \n",
       "1              istanbul                    turkey  34110   \n",
       "2                 cairo                     egypt  99999   \n",
       "3           george town                  malaysia          \n",
       "4                  isla                     italy  06044   \n",
       "..                  ...                       ...    ...   \n",
       "79                cairo                     egypt  99999   \n",
       "80  desenzano del garda                     italy  25015   \n",
       "81          chefchaouen                   morocco  91000   \n",
       "82               ovacik                    turkey  48340   \n",
       "83      golden triangle                  thailand  57150   \n",
       "\n",
       "                                                          street  \n",
       "0                                           447 west route 77 az  \n",
       "1   kanuk ayasofya mahallesi yeğen sok. no 3/1 sultanahmet fatih  \n",
       "2                          11 b ramsis st. marouf tower.downtown  \n",
       "3                                      11 lorong carnarvon 10220  \n",
       "4                                          via cesare battisti 5  \n",
       "..                                                           ...  \n",
       "79                       39 chark compound campus new cairo city  \n",
       "80                                                 woal cavour 2  \n",
       "81                                               lalla lambda 22  \n",
       "82                                                                \n",
       "83                                          11 moo 1 chiang ikan  \n",
       "\n",
       "[84 rows x 7 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_source1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we will solve the problem of inconsistent country names by using a Python library called pycountry (https://pypi.org/project/pycountry/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pycountry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def normalize_country_to_alpha2_code(country_name):\n",
    "    \"\"\"\n",
    "    This function will receive a country name in an arbitrary format and will return a 2 letter country code for that country as per the alpha_2 country code format.\n",
    "    This is achieved by using the pycountry Python library.\n",
    "    \"\"\"\n",
    "    country_alpha2 = pycountry.countries.search_fuzzy(country_name)[0].alpha_2\n",
    "    return country_alpha2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_source1[\"country\"] = df_source1.country.apply(lambda x: normalize_country_to_alpha2_code(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>name</th>\n",
       "      <th>phone</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>zip</th>\n",
       "      <th>street</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a1</td>\n",
       "      <td>westerns country inn</td>\n",
       "      <td>928-345-4567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>US</td>\n",
       "      <td>86033</td>\n",
       "      <td>447 west route 77 az</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a2</td>\n",
       "      <td>natpolin hostel</td>\n",
       "      <td></td>\n",
       "      <td>istanbul</td>\n",
       "      <td>TR</td>\n",
       "      <td>34110</td>\n",
       "      <td>kanuk ayasofya mahallesi yeğen sok. no 3/1 sultanahmet fatih</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a3</td>\n",
       "      <td>sleep in! cairo hostel</td>\n",
       "      <td></td>\n",
       "      <td>cairo</td>\n",
       "      <td>EG</td>\n",
       "      <td>99999</td>\n",
       "      <td>11 b ramsis st. marouf tower.downtown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a4</td>\n",
       "      <td>mekking house</td>\n",
       "      <td></td>\n",
       "      <td>george town</td>\n",
       "      <td>MY</td>\n",
       "      <td></td>\n",
       "      <td>11 lorong carnarvon 10220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a5</td>\n",
       "      <td>rolando &amp; chateaux palazzo seneca</td>\n",
       "      <td>0712 567834</td>\n",
       "      <td>isla</td>\n",
       "      <td>IT</td>\n",
       "      <td>06044</td>\n",
       "      <td>via cesare battisti 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>a495</td>\n",
       "      <td>oldcity open-hotel - suites &amp; apartments</td>\n",
       "      <td></td>\n",
       "      <td>cairo</td>\n",
       "      <td>EG</td>\n",
       "      <td>99999</td>\n",
       "      <td>39 chark compound campus new cairo city</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>a496</td>\n",
       "      <td>live party bed &amp; breakfast</td>\n",
       "      <td></td>\n",
       "      <td>desenzano del garda</td>\n",
       "      <td>IT</td>\n",
       "      <td>25015</td>\n",
       "      <td>woal cavour 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>a497</td>\n",
       "      <td>lala hostala</td>\n",
       "      <td></td>\n",
       "      <td>chefchaouen</td>\n",
       "      <td>MA</td>\n",
       "      <td>91000</td>\n",
       "      <td>lalla lambda 22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>a498</td>\n",
       "      <td>orka mountain villa 10</td>\n",
       "      <td></td>\n",
       "      <td>ovacik</td>\n",
       "      <td>TR</td>\n",
       "      <td>48340</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>a499</td>\n",
       "      <td>arltanka silver triangle</td>\n",
       "      <td></td>\n",
       "      <td>golden triangle</td>\n",
       "      <td>TH</td>\n",
       "      <td>57150</td>\n",
       "      <td>11 moo 1 chiang ikan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID                                      name         phone  \\\n",
       "0     a1                      westerns country inn  928-345-4567   \n",
       "1     a2                           natpolin hostel                 \n",
       "2     a3                    sleep in! cairo hostel                 \n",
       "3     a4                             mekking house                 \n",
       "4     a5         rolando & chateaux palazzo seneca   0712 567834   \n",
       "..   ...                                       ...           ...   \n",
       "79  a495  oldcity open-hotel - suites & apartments                 \n",
       "80  a496                live party bed & breakfast                 \n",
       "81  a497                              lala hostala                 \n",
       "82  a498                    orka mountain villa 10                 \n",
       "83  a499                  arltanka silver triangle                 \n",
       "\n",
       "                   city country    zip  \\\n",
       "0             johnscity      US  86033   \n",
       "1              istanbul      TR  34110   \n",
       "2                 cairo      EG  99999   \n",
       "3           george town      MY          \n",
       "4                  isla      IT  06044   \n",
       "..                  ...     ...    ...   \n",
       "79                cairo      EG  99999   \n",
       "80  desenzano del garda      IT  25015   \n",
       "81          chefchaouen      MA  91000   \n",
       "82               ovacik      TR  48340   \n",
       "83      golden triangle      TH  57150   \n",
       "\n",
       "                                                          street  \n",
       "0                                           447 west route 77 az  \n",
       "1   kanuk ayasofya mahallesi yeğen sok. no 3/1 sultanahmet fatih  \n",
       "2                          11 b ramsis st. marouf tower.downtown  \n",
       "3                                      11 lorong carnarvon 10220  \n",
       "4                                          via cesare battisti 5  \n",
       "..                                                           ...  \n",
       "79                       39 chark compound campus new cairo city  \n",
       "80                                                 woal cavour 2  \n",
       "81                                               lalla lambda 22  \n",
       "82                                                                \n",
       "83                                          11 moo 1 chiang ikan  \n",
       "\n",
       "[84 rows x 7 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_source1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_source2[\"country\"] = df_source2.country.apply(lambda x: normalize_country_to_alpha2_code(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>zip</th>\n",
       "      <th>street</th>\n",
       "      <th>phone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ba1</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>US</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>+1 928-345-4567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ba2</td>\n",
       "      <td>natpolin hotel</td>\n",
       "      <td>istanbul</td>\n",
       "      <td>TR</td>\n",
       "      <td>34110</td>\n",
       "      <td>kanuk ayasofya mahallesi yeğen sok. no:3/1 sultanahmet , fatih, ,</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ba3</td>\n",
       "      <td>sleep in cairo hostel</td>\n",
       "      <td>cairo</td>\n",
       "      <td>EG</td>\n",
       "      <td>11511</td>\n",
       "      <td>11 b, ramses street, downtown</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ba4</td>\n",
       "      <td>mekking house</td>\n",
       "      <td>george town</td>\n",
       "      <td>MY</td>\n",
       "      <td>10220</td>\n",
       "      <td>11 lorong carnarvon</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ba5</td>\n",
       "      <td>palazzo seneca</td>\n",
       "      <td>isla</td>\n",
       "      <td>IT</td>\n",
       "      <td>6046</td>\n",
       "      <td>via cesare battisti, 5</td>\n",
       "      <td>+39 0712 567834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>ba495</td>\n",
       "      <td>newcity suites &amp; apartments</td>\n",
       "      <td>cairo</td>\n",
       "      <td>EG</td>\n",
       "      <td>11835</td>\n",
       "      <td>23 arban compound infront of new aac campus- new cairo</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>ba496</td>\n",
       "      <td>bed &amp; breakfast party house</td>\n",
       "      <td>desenzano del garda</td>\n",
       "      <td>IT</td>\n",
       "      <td>25015</td>\n",
       "      <td>via woal verdi 10</td>\n",
       "      <td>+39 44 33 443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>ba497</td>\n",
       "      <td>hostal la lambada</td>\n",
       "      <td>chefchaouene</td>\n",
       "      <td>MA</td>\n",
       "      <td>91000</td>\n",
       "      <td>rue sidi ahme</td>\n",
       "      <td>+212 44-33445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>ba498</td>\n",
       "      <td>orka my villa</td>\n",
       "      <td>istanbul</td>\n",
       "      <td>TR</td>\n",
       "      <td>48345</td>\n",
       "      <td>ovacık mahallesi, istanbul caddesi</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>ba499</td>\n",
       "      <td>golden triangle resort</td>\n",
       "      <td>chiang saen</td>\n",
       "      <td>TH</td>\n",
       "      <td>57150</td>\n",
       "      <td>234 golden triangle, chiang saen</td>\n",
       "      <td>+66 44 445 444</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID                                  name                 city country  \\\n",
       "0     ba1  westerns country inn bed & breakfast            johnscity      US   \n",
       "1     ba2                        natpolin hotel             istanbul      TR   \n",
       "2     ba3                 sleep in cairo hostel                cairo      EG   \n",
       "3     ba4                         mekking house          george town      MY   \n",
       "4     ba5                        palazzo seneca                 isla      IT   \n",
       "..    ...                                   ...                  ...     ...   \n",
       "79  ba495           newcity suites & apartments                cairo      EG   \n",
       "80  ba496           bed & breakfast party house  desenzano del garda      IT   \n",
       "81  ba497                     hostal la lambada         chefchaouene      MA   \n",
       "82  ba498                         orka my villa             istanbul      TR   \n",
       "83  ba499                golden triangle resort          chiang saen      TH   \n",
       "\n",
       "      zip                                                             street  \\\n",
       "0   86033                                                  west route 77 447   \n",
       "1   34110  kanuk ayasofya mahallesi yeğen sok. no:3/1 sultanahmet , fatih, ,   \n",
       "2   11511                                      11 b, ramses street, downtown   \n",
       "3   10220                                                11 lorong carnarvon   \n",
       "4    6046                                             via cesare battisti, 5   \n",
       "..    ...                                                                ...   \n",
       "79  11835             23 arban compound infront of new aac campus- new cairo   \n",
       "80  25015                                                  via woal verdi 10   \n",
       "81  91000                                                      rue sidi ahme   \n",
       "82  48345                                 ovacık mahallesi, istanbul caddesi   \n",
       "83  57150                                   234 golden triangle, chiang saen   \n",
       "\n",
       "              phone  \n",
       "0   +1 928-345-4567  \n",
       "1                    \n",
       "2                    \n",
       "3                    \n",
       "4   +39 0712 567834  \n",
       "..              ...  \n",
       "79                   \n",
       "80    +39 44 33 443  \n",
       "81    +212 44-33445  \n",
       "82                   \n",
       "83   +66 44 445 444  \n",
       "\n",
       "[84 rows x 7 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_source2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we will also normalize all phone numbers to not show the country code by using a Python library called python-phonenumbers (https://github.com/daviddrysdale/python-phonenumbers)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "import phonenumbers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_national_number(phone_number):\n",
    "    \"\"\"\n",
    "    Given a phone number in any format, return only the part of it that is a national number. \n",
    "    This is achieved using phonenumber Python library.\n",
    "    \"\"\"\n",
    "    if phone_number:\n",
    "        return phonenumbers.parse(phone_number, None).national_number\n",
    "    else:\n",
    "        return \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get only the national number from source 2\n",
    "df_source2[\"phone\"] = df_source2.phone.apply(lambda x: get_national_number(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>zip</th>\n",
       "      <th>street</th>\n",
       "      <th>phone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ba1</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>US</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>9283454567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ba2</td>\n",
       "      <td>natpolin hotel</td>\n",
       "      <td>istanbul</td>\n",
       "      <td>TR</td>\n",
       "      <td>34110</td>\n",
       "      <td>kanuk ayasofya mahallesi yeğen sok. no:3/1 sultanahmet , fatih, ,</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ba3</td>\n",
       "      <td>sleep in cairo hostel</td>\n",
       "      <td>cairo</td>\n",
       "      <td>EG</td>\n",
       "      <td>11511</td>\n",
       "      <td>11 b, ramses street, downtown</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ba4</td>\n",
       "      <td>mekking house</td>\n",
       "      <td>george town</td>\n",
       "      <td>MY</td>\n",
       "      <td>10220</td>\n",
       "      <td>11 lorong carnarvon</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ba5</td>\n",
       "      <td>palazzo seneca</td>\n",
       "      <td>isla</td>\n",
       "      <td>IT</td>\n",
       "      <td>6046</td>\n",
       "      <td>via cesare battisti, 5</td>\n",
       "      <td>712567834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>ba495</td>\n",
       "      <td>newcity suites &amp; apartments</td>\n",
       "      <td>cairo</td>\n",
       "      <td>EG</td>\n",
       "      <td>11835</td>\n",
       "      <td>23 arban compound infront of new aac campus- new cairo</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>ba496</td>\n",
       "      <td>bed &amp; breakfast party house</td>\n",
       "      <td>desenzano del garda</td>\n",
       "      <td>IT</td>\n",
       "      <td>25015</td>\n",
       "      <td>via woal verdi 10</td>\n",
       "      <td>4433443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>ba497</td>\n",
       "      <td>hostal la lambada</td>\n",
       "      <td>chefchaouene</td>\n",
       "      <td>MA</td>\n",
       "      <td>91000</td>\n",
       "      <td>rue sidi ahme</td>\n",
       "      <td>4433445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>ba498</td>\n",
       "      <td>orka my villa</td>\n",
       "      <td>istanbul</td>\n",
       "      <td>TR</td>\n",
       "      <td>48345</td>\n",
       "      <td>ovacık mahallesi, istanbul caddesi</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>ba499</td>\n",
       "      <td>golden triangle resort</td>\n",
       "      <td>chiang saen</td>\n",
       "      <td>TH</td>\n",
       "      <td>57150</td>\n",
       "      <td>234 golden triangle, chiang saen</td>\n",
       "      <td>44445444</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID                                  name                 city country  \\\n",
       "0     ba1  westerns country inn bed & breakfast            johnscity      US   \n",
       "1     ba2                        natpolin hotel             istanbul      TR   \n",
       "2     ba3                 sleep in cairo hostel                cairo      EG   \n",
       "3     ba4                         mekking house          george town      MY   \n",
       "4     ba5                        palazzo seneca                 isla      IT   \n",
       "..    ...                                   ...                  ...     ...   \n",
       "79  ba495           newcity suites & apartments                cairo      EG   \n",
       "80  ba496           bed & breakfast party house  desenzano del garda      IT   \n",
       "81  ba497                     hostal la lambada         chefchaouene      MA   \n",
       "82  ba498                         orka my villa             istanbul      TR   \n",
       "83  ba499                golden triangle resort          chiang saen      TH   \n",
       "\n",
       "      zip                                                             street  \\\n",
       "0   86033                                                  west route 77 447   \n",
       "1   34110  kanuk ayasofya mahallesi yeğen sok. no:3/1 sultanahmet , fatih, ,   \n",
       "2   11511                                      11 b, ramses street, downtown   \n",
       "3   10220                                                11 lorong carnarvon   \n",
       "4    6046                                             via cesare battisti, 5   \n",
       "..    ...                                                                ...   \n",
       "79  11835             23 arban compound infront of new aac campus- new cairo   \n",
       "80  25015                                                  via woal verdi 10   \n",
       "81  91000                                                      rue sidi ahme   \n",
       "82  48345                                 ovacık mahallesi, istanbul caddesi   \n",
       "83  57150                                   234 golden triangle, chiang saen   \n",
       "\n",
       "         phone  \n",
       "0   9283454567  \n",
       "1               \n",
       "2               \n",
       "3               \n",
       "4    712567834  \n",
       "..         ...  \n",
       "79              \n",
       "80     4433443  \n",
       "81     4433445  \n",
       "82              \n",
       "83    44445444  \n",
       "\n",
       "[84 rows x 7 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_source2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "# pre-process the source 1 to have only digits in the number\n",
    "df_source1[\"phone\"] = df_source1.phone.apply(lambda x: re.sub(\"[^0-9]\", \"\", str(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>name</th>\n",
       "      <th>phone</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>zip</th>\n",
       "      <th>street</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a1</td>\n",
       "      <td>westerns country inn</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>US</td>\n",
       "      <td>86033</td>\n",
       "      <td>447 west route 77 az</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a2</td>\n",
       "      <td>natpolin hostel</td>\n",
       "      <td></td>\n",
       "      <td>istanbul</td>\n",
       "      <td>TR</td>\n",
       "      <td>34110</td>\n",
       "      <td>kanuk ayasofya mahallesi yeğen sok. no 3/1 sultanahmet fatih</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a3</td>\n",
       "      <td>sleep in! cairo hostel</td>\n",
       "      <td></td>\n",
       "      <td>cairo</td>\n",
       "      <td>EG</td>\n",
       "      <td>99999</td>\n",
       "      <td>11 b ramsis st. marouf tower.downtown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a4</td>\n",
       "      <td>mekking house</td>\n",
       "      <td></td>\n",
       "      <td>george town</td>\n",
       "      <td>MY</td>\n",
       "      <td></td>\n",
       "      <td>11 lorong carnarvon 10220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a5</td>\n",
       "      <td>rolando &amp; chateaux palazzo seneca</td>\n",
       "      <td>0712567834</td>\n",
       "      <td>isla</td>\n",
       "      <td>IT</td>\n",
       "      <td>06044</td>\n",
       "      <td>via cesare battisti 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>a495</td>\n",
       "      <td>oldcity open-hotel - suites &amp; apartments</td>\n",
       "      <td></td>\n",
       "      <td>cairo</td>\n",
       "      <td>EG</td>\n",
       "      <td>99999</td>\n",
       "      <td>39 chark compound campus new cairo city</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>a496</td>\n",
       "      <td>live party bed &amp; breakfast</td>\n",
       "      <td></td>\n",
       "      <td>desenzano del garda</td>\n",
       "      <td>IT</td>\n",
       "      <td>25015</td>\n",
       "      <td>woal cavour 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>a497</td>\n",
       "      <td>lala hostala</td>\n",
       "      <td></td>\n",
       "      <td>chefchaouen</td>\n",
       "      <td>MA</td>\n",
       "      <td>91000</td>\n",
       "      <td>lalla lambda 22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>a498</td>\n",
       "      <td>orka mountain villa 10</td>\n",
       "      <td></td>\n",
       "      <td>ovacik</td>\n",
       "      <td>TR</td>\n",
       "      <td>48340</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>a499</td>\n",
       "      <td>arltanka silver triangle</td>\n",
       "      <td></td>\n",
       "      <td>golden triangle</td>\n",
       "      <td>TH</td>\n",
       "      <td>57150</td>\n",
       "      <td>11 moo 1 chiang ikan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID                                      name       phone  \\\n",
       "0     a1                      westerns country inn  9283454567   \n",
       "1     a2                           natpolin hostel               \n",
       "2     a3                    sleep in! cairo hostel               \n",
       "3     a4                             mekking house               \n",
       "4     a5         rolando & chateaux palazzo seneca  0712567834   \n",
       "..   ...                                       ...         ...   \n",
       "79  a495  oldcity open-hotel - suites & apartments               \n",
       "80  a496                live party bed & breakfast               \n",
       "81  a497                              lala hostala               \n",
       "82  a498                    orka mountain villa 10               \n",
       "83  a499                  arltanka silver triangle               \n",
       "\n",
       "                   city country    zip  \\\n",
       "0             johnscity      US  86033   \n",
       "1              istanbul      TR  34110   \n",
       "2                 cairo      EG  99999   \n",
       "3           george town      MY          \n",
       "4                  isla      IT  06044   \n",
       "..                  ...     ...    ...   \n",
       "79                cairo      EG  99999   \n",
       "80  desenzano del garda      IT  25015   \n",
       "81          chefchaouen      MA  91000   \n",
       "82               ovacik      TR  48340   \n",
       "83      golden triangle      TH  57150   \n",
       "\n",
       "                                                          street  \n",
       "0                                           447 west route 77 az  \n",
       "1   kanuk ayasofya mahallesi yeğen sok. no 3/1 sultanahmet fatih  \n",
       "2                          11 b ramsis st. marouf tower.downtown  \n",
       "3                                      11 lorong carnarvon 10220  \n",
       "4                                          via cesare battisti 5  \n",
       "..                                                           ...  \n",
       "79                       39 chark compound campus new cairo city  \n",
       "80                                                 woal cavour 2  \n",
       "81                                               lalla lambda 22  \n",
       "82                                                                \n",
       "83                                          11 moo 1 chiang ikan  \n",
       "\n",
       "[84 rows x 7 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_source1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_source1.to_csv(\"./data/deep_matcher/s1.csv\")\n",
    "# df_source2.to_csv(\"./data/deep_matcher/s2.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Candidate Generation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As discussed in the theoretical part, we don't want to compare each record in source 1 to each record in source 2, and to avoid this we can use various approaches to candidate generation of potential match pairs.\n",
    "\n",
    "In this demo, we will simply make sure to only compare hotel that are in the same country, this should considerably decrease the amount of comparisons we need to compute. In real-word applications, candidate generation is one of the most important steps of the Entity Matching algorithm. For the purpose of this demo we are only generating candidates based on the country, however, there are many ways this step can be refined. Explore the various options and approaches to candidate generation by following the tutorials provided on the documentation page of the py_entitymatching Python library (http://anhaidgroup.github.io/py_entitymatching/v0.3.x/user_manual/guides.html#stepwise-guides)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "import py_entitymatching as em"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "em.set_key(df_source1, 'ID')\n",
    "em.set_key(df_source2, 'ID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/py_entitymatching/blocker/overlap_blocker.py:258: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  l_df[l_dummy_overlap_attr] = l_df[l_overlap_attr]\n",
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/py_entitymatching/blocker/overlap_blocker.py:259: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  r_df[r_dummy_overlap_attr] = r_df[r_overlap_attr]\n",
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/py_entitymatching/blocker/overlap_blocker.py:615: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  table[overlap_attr] = values\n",
      "0% [##############################] 100% | ETA: 00:00:00\n",
      "Total time elapsed: 00:00:00\n"
     ]
    }
   ],
   "source": [
    "# instantiate overlap blocker object\n",
    "ob = em.OverlapBlocker()\n",
    "\n",
    "#combine two tables from source 1 and source 2 and generate candidate pairs based on overlapping country columns\n",
    "match_candidate_pairs_df = ob.block_tables(df_source1, df_source2, 'country', 'country', word_level=True, overlap_size=1, \n",
    "                    l_output_attrs=['name', 'phone', 'city', 'zip', 'street'], \n",
    "                    r_output_attrs=['name', 'phone', 'city', 'zip', 'street'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>ltable_ID</th>\n",
       "      <th>rtable_ID</th>\n",
       "      <th>ltable_name</th>\n",
       "      <th>ltable_phone</th>\n",
       "      <th>ltable_city</th>\n",
       "      <th>ltable_zip</th>\n",
       "      <th>ltable_street</th>\n",
       "      <th>rtable_name</th>\n",
       "      <th>rtable_phone</th>\n",
       "      <th>rtable_city</th>\n",
       "      <th>rtable_zip</th>\n",
       "      <th>rtable_street</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>a1</td>\n",
       "      <td>ba1</td>\n",
       "      <td>westerns country inn</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>447 west route 77 az</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>a6</td>\n",
       "      <td>ba1</td>\n",
       "      <td>wake up columbia gateway</td>\n",
       "      <td>4104563367</td>\n",
       "      <td>jessup</td>\n",
       "      <td>20896</td>\n",
       "      <td>8567 washington boulevard md</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>a14</td>\n",
       "      <td>ba1</td>\n",
       "      <td>vacation inn express hotel &amp; suites monar</td>\n",
       "      <td>4353451467</td>\n",
       "      <td>monar</td>\n",
       "      <td>84182</td>\n",
       "      <td>1511 north highway 176 ut</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>a15</td>\n",
       "      <td>ba1</td>\n",
       "      <td>night inn by wyndy martinsburg</td>\n",
       "      <td>3042562588</td>\n",
       "      <td>martinsburg</td>\n",
       "      <td>25266</td>\n",
       "      <td>125 viking way wv</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>a21</td>\n",
       "      <td>ba1</td>\n",
       "      <td>mighty inn &amp; suites mt dora north</td>\n",
       "      <td>3527542200</td>\n",
       "      <td>mount dora</td>\n",
       "      <td>32345</td>\n",
       "      <td>16220 us 431 west fl</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>741</th>\n",
       "      <td>741</td>\n",
       "      <td>a482</td>\n",
       "      <td>ba498</td>\n",
       "      <td>famia gammon hotel</td>\n",
       "      <td></td>\n",
       "      <td>faralya</td>\n",
       "      <td>43100</td>\n",
       "      <td>uzunyurt sokak no 77</td>\n",
       "      <td>orka my villa</td>\n",
       "      <td></td>\n",
       "      <td>istanbul</td>\n",
       "      <td>48345</td>\n",
       "      <td>ovacık mahallesi, istanbul caddesi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>742</th>\n",
       "      <td>742</td>\n",
       "      <td>a498</td>\n",
       "      <td>ba498</td>\n",
       "      <td>orka mountain villa 10</td>\n",
       "      <td></td>\n",
       "      <td>ovacik</td>\n",
       "      <td>48340</td>\n",
       "      <td></td>\n",
       "      <td>orka my villa</td>\n",
       "      <td></td>\n",
       "      <td>istanbul</td>\n",
       "      <td>48345</td>\n",
       "      <td>ovacık mahallesi, istanbul caddesi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>743</th>\n",
       "      <td>743</td>\n",
       "      <td>a484</td>\n",
       "      <td>ba499</td>\n",
       "      <td>new inside pool villa</td>\n",
       "      <td></td>\n",
       "      <td>chalong</td>\n",
       "      <td>83213</td>\n",
       "      <td>58/135 soi anusorn aa 1 chalong</td>\n",
       "      <td>golden triangle resort</td>\n",
       "      <td>44445444</td>\n",
       "      <td>chiang saen</td>\n",
       "      <td>57150</td>\n",
       "      <td>234 golden triangle, chiang saen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>744</th>\n",
       "      <td>744</td>\n",
       "      <td>a487</td>\n",
       "      <td>ba499</td>\n",
       "      <td>eastwards cozy home</td>\n",
       "      <td></td>\n",
       "      <td>chiang mai</td>\n",
       "      <td>50993</td>\n",
       "      <td>baan wasin siri maka m.2 t.nong khwai a.hang lank 23/7</td>\n",
       "      <td>golden triangle resort</td>\n",
       "      <td>44445444</td>\n",
       "      <td>chiang saen</td>\n",
       "      <td>57150</td>\n",
       "      <td>234 golden triangle, chiang saen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>745</th>\n",
       "      <td>745</td>\n",
       "      <td>a499</td>\n",
       "      <td>ba499</td>\n",
       "      <td>arltanka silver triangle</td>\n",
       "      <td></td>\n",
       "      <td>golden triangle</td>\n",
       "      <td>57150</td>\n",
       "      <td>11 moo 1 chiang ikan</td>\n",
       "      <td>golden triangle resort</td>\n",
       "      <td>44445444</td>\n",
       "      <td>chiang saen</td>\n",
       "      <td>57150</td>\n",
       "      <td>234 golden triangle, chiang saen</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>746 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     _id ltable_ID rtable_ID                                ltable_name  \\\n",
       "0      0        a1       ba1                       westerns country inn   \n",
       "1      1        a6       ba1                   wake up columbia gateway   \n",
       "2      2       a14       ba1  vacation inn express hotel & suites monar   \n",
       "3      3       a15       ba1             night inn by wyndy martinsburg   \n",
       "4      4       a21       ba1          mighty inn & suites mt dora north   \n",
       "..   ...       ...       ...                                        ...   \n",
       "741  741      a482     ba498                         famia gammon hotel   \n",
       "742  742      a498     ba498                     orka mountain villa 10   \n",
       "743  743      a484     ba499                      new inside pool villa   \n",
       "744  744      a487     ba499                        eastwards cozy home   \n",
       "745  745      a499     ba499                   arltanka silver triangle   \n",
       "\n",
       "    ltable_phone      ltable_city ltable_zip  \\\n",
       "0     9283454567        johnscity      86033   \n",
       "1     4104563367           jessup      20896   \n",
       "2     4353451467            monar      84182   \n",
       "3     3042562588      martinsburg      25266   \n",
       "4     3527542200       mount dora      32345   \n",
       "..           ...              ...        ...   \n",
       "741                       faralya      43100   \n",
       "742                        ovacik      48340   \n",
       "743                       chalong      83213   \n",
       "744                    chiang mai      50993   \n",
       "745               golden triangle      57150   \n",
       "\n",
       "                                              ltable_street  \\\n",
       "0                                      447 west route 77 az   \n",
       "1                              8567 washington boulevard md   \n",
       "2                                 1511 north highway 176 ut   \n",
       "3                                         125 viking way wv   \n",
       "4                                      16220 us 431 west fl   \n",
       "..                                                      ...   \n",
       "741                                    uzunyurt sokak no 77   \n",
       "742                                                           \n",
       "743                         58/135 soi anusorn aa 1 chalong   \n",
       "744  baan wasin siri maka m.2 t.nong khwai a.hang lank 23/7   \n",
       "745                                    11 moo 1 chiang ikan   \n",
       "\n",
       "                              rtable_name rtable_phone  rtable_city  \\\n",
       "0    westerns country inn bed & breakfast   9283454567    johnscity   \n",
       "1    westerns country inn bed & breakfast   9283454567    johnscity   \n",
       "2    westerns country inn bed & breakfast   9283454567    johnscity   \n",
       "3    westerns country inn bed & breakfast   9283454567    johnscity   \n",
       "4    westerns country inn bed & breakfast   9283454567    johnscity   \n",
       "..                                    ...          ...          ...   \n",
       "741                         orka my villa                  istanbul   \n",
       "742                         orka my villa                  istanbul   \n",
       "743                golden triangle resort     44445444  chiang saen   \n",
       "744                golden triangle resort     44445444  chiang saen   \n",
       "745                golden triangle resort     44445444  chiang saen   \n",
       "\n",
       "    rtable_zip                       rtable_street  \n",
       "0        86033                   west route 77 447  \n",
       "1        86033                   west route 77 447  \n",
       "2        86033                   west route 77 447  \n",
       "3        86033                   west route 77 447  \n",
       "4        86033                   west route 77 447  \n",
       "..         ...                                 ...  \n",
       "741      48345  ovacık mahallesi, istanbul caddesi  \n",
       "742      48345  ovacık mahallesi, istanbul caddesi  \n",
       "743      57150    234 golden triangle, chiang saen  \n",
       "744      57150    234 golden triangle, chiang saen  \n",
       "745      57150    234 golden triangle, chiang saen  \n",
       "\n",
       "[746 rows x 13 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_candidate_pairs_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By simply overlapping the two sources by country code we will need to do computaton on more than 746 thousand record pairs. This number grows quadratically with the size of the dataset, which makes selection of a right approach when doing candidate selection so important.\n",
    "\n",
    "We can filter out a few more record pairs by, for example, allowing only record pairs in which at least one word in the hotel name exists in both pairs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/py_entitymatching/blocker/overlap_blocker.py:615: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  table[overlap_attr] = values\n"
     ]
    }
   ],
   "source": [
    "strict_match_candidate_pairs_df = ob.block_candset(match_candidate_pairs_df, 'name', 'name', word_level=True, overlap_size=1, show_progress=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>ltable_ID</th>\n",
       "      <th>rtable_ID</th>\n",
       "      <th>ltable_name</th>\n",
       "      <th>ltable_phone</th>\n",
       "      <th>ltable_city</th>\n",
       "      <th>ltable_zip</th>\n",
       "      <th>ltable_street</th>\n",
       "      <th>rtable_name</th>\n",
       "      <th>rtable_phone</th>\n",
       "      <th>rtable_city</th>\n",
       "      <th>rtable_zip</th>\n",
       "      <th>rtable_street</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>a1</td>\n",
       "      <td>ba1</td>\n",
       "      <td>westerns country inn</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>447 west route 77 az</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>a14</td>\n",
       "      <td>ba1</td>\n",
       "      <td>vacation inn express hotel &amp; suites monar</td>\n",
       "      <td>4353451467</td>\n",
       "      <td>monar</td>\n",
       "      <td>84182</td>\n",
       "      <td>1511 north highway 176 ut</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>a15</td>\n",
       "      <td>ba1</td>\n",
       "      <td>night inn by wyndy martinsburg</td>\n",
       "      <td>3042562588</td>\n",
       "      <td>martinsburg</td>\n",
       "      <td>25266</td>\n",
       "      <td>125 viking way wv</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>a21</td>\n",
       "      <td>ba1</td>\n",
       "      <td>mighty inn &amp; suites mt dora north</td>\n",
       "      <td>3527542200</td>\n",
       "      <td>mount dora</td>\n",
       "      <td>32345</td>\n",
       "      <td>16220 us 431 west fl</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>a50</td>\n",
       "      <td>ba1</td>\n",
       "      <td>east inn karlston</td>\n",
       "      <td></td>\n",
       "      <td>evanston</td>\n",
       "      <td>82334</td>\n",
       "      <td>1922 harrison drive wy</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>715</th>\n",
       "      <td>715</td>\n",
       "      <td>a494</td>\n",
       "      <td>ba494</td>\n",
       "      <td>guest paradise – bedroom home</td>\n",
       "      <td></td>\n",
       "      <td>surfside beach myrtle beach</td>\n",
       "      <td>29347</td>\n",
       "      <td>22 north dogwood drive \"paradise\"</td>\n",
       "      <td>hostel paradise and home apartments</td>\n",
       "      <td></td>\n",
       "      <td>myrtle beach</td>\n",
       "      <td>29347</td>\n",
       "      <td>south ocean boulevard 2355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>719</th>\n",
       "      <td>719</td>\n",
       "      <td>a495</td>\n",
       "      <td>ba495</td>\n",
       "      <td>oldcity open-hotel - suites &amp; apartments</td>\n",
       "      <td></td>\n",
       "      <td>cairo</td>\n",
       "      <td>99999</td>\n",
       "      <td>39 chark compound campus new cairo city</td>\n",
       "      <td>newcity suites &amp; apartments</td>\n",
       "      <td></td>\n",
       "      <td>cairo</td>\n",
       "      <td>11835</td>\n",
       "      <td>23 arban compound infront of new aac campus- new cairo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>730</th>\n",
       "      <td>730</td>\n",
       "      <td>a496</td>\n",
       "      <td>ba496</td>\n",
       "      <td>live party bed &amp; breakfast</td>\n",
       "      <td></td>\n",
       "      <td>desenzano del garda</td>\n",
       "      <td>25015</td>\n",
       "      <td>woal cavour 2</td>\n",
       "      <td>bed &amp; breakfast party house</td>\n",
       "      <td>4433443</td>\n",
       "      <td>desenzano del garda</td>\n",
       "      <td>25015</td>\n",
       "      <td>via woal verdi 10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>742</th>\n",
       "      <td>742</td>\n",
       "      <td>a498</td>\n",
       "      <td>ba498</td>\n",
       "      <td>orka mountain villa 10</td>\n",
       "      <td></td>\n",
       "      <td>ovacik</td>\n",
       "      <td>48340</td>\n",
       "      <td></td>\n",
       "      <td>orka my villa</td>\n",
       "      <td></td>\n",
       "      <td>istanbul</td>\n",
       "      <td>48345</td>\n",
       "      <td>ovacık mahallesi, istanbul caddesi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>745</th>\n",
       "      <td>745</td>\n",
       "      <td>a499</td>\n",
       "      <td>ba499</td>\n",
       "      <td>arltanka silver triangle</td>\n",
       "      <td></td>\n",
       "      <td>golden triangle</td>\n",
       "      <td>57150</td>\n",
       "      <td>11 moo 1 chiang ikan</td>\n",
       "      <td>golden triangle resort</td>\n",
       "      <td>44445444</td>\n",
       "      <td>chiang saen</td>\n",
       "      <td>57150</td>\n",
       "      <td>234 golden triangle, chiang saen</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>262 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     _id ltable_ID rtable_ID                                ltable_name  \\\n",
       "0      0        a1       ba1                       westerns country inn   \n",
       "2      2       a14       ba1  vacation inn express hotel & suites monar   \n",
       "3      3       a15       ba1             night inn by wyndy martinsburg   \n",
       "4      4       a21       ba1          mighty inn & suites mt dora north   \n",
       "6      6       a50       ba1                          east inn karlston   \n",
       "..   ...       ...       ...                                        ...   \n",
       "715  715      a494     ba494              guest paradise – bedroom home   \n",
       "719  719      a495     ba495   oldcity open-hotel - suites & apartments   \n",
       "730  730      a496     ba496                 live party bed & breakfast   \n",
       "742  742      a498     ba498                     orka mountain villa 10   \n",
       "745  745      a499     ba499                   arltanka silver triangle   \n",
       "\n",
       "    ltable_phone                  ltable_city ltable_zip  \\\n",
       "0     9283454567                    johnscity      86033   \n",
       "2     4353451467                        monar      84182   \n",
       "3     3042562588                  martinsburg      25266   \n",
       "4     3527542200                   mount dora      32345   \n",
       "6                                    evanston      82334   \n",
       "..           ...                          ...        ...   \n",
       "715               surfside beach myrtle beach      29347   \n",
       "719                                     cairo      99999   \n",
       "730                       desenzano del garda      25015   \n",
       "742                                    ovacik      48340   \n",
       "745                           golden triangle      57150   \n",
       "\n",
       "                               ltable_street  \\\n",
       "0                       447 west route 77 az   \n",
       "2                  1511 north highway 176 ut   \n",
       "3                          125 viking way wv   \n",
       "4                       16220 us 431 west fl   \n",
       "6                     1922 harrison drive wy   \n",
       "..                                       ...   \n",
       "715        22 north dogwood drive \"paradise\"   \n",
       "719  39 chark compound campus new cairo city   \n",
       "730                            woal cavour 2   \n",
       "742                                            \n",
       "745                     11 moo 1 chiang ikan   \n",
       "\n",
       "                              rtable_name rtable_phone          rtable_city  \\\n",
       "0    westerns country inn bed & breakfast   9283454567            johnscity   \n",
       "2    westerns country inn bed & breakfast   9283454567            johnscity   \n",
       "3    westerns country inn bed & breakfast   9283454567            johnscity   \n",
       "4    westerns country inn bed & breakfast   9283454567            johnscity   \n",
       "6    westerns country inn bed & breakfast   9283454567            johnscity   \n",
       "..                                    ...          ...                  ...   \n",
       "715   hostel paradise and home apartments                      myrtle beach   \n",
       "719           newcity suites & apartments                             cairo   \n",
       "730           bed & breakfast party house      4433443  desenzano del garda   \n",
       "742                         orka my villa                          istanbul   \n",
       "745                golden triangle resort     44445444          chiang saen   \n",
       "\n",
       "    rtable_zip                                           rtable_street  \n",
       "0        86033                                       west route 77 447  \n",
       "2        86033                                       west route 77 447  \n",
       "3        86033                                       west route 77 447  \n",
       "4        86033                                       west route 77 447  \n",
       "6        86033                                       west route 77 447  \n",
       "..         ...                                                     ...  \n",
       "715      29347                              south ocean boulevard 2355  \n",
       "719      11835  23 arban compound infront of new aac campus- new cairo  \n",
       "730      25015                                       via woal verdi 10  \n",
       "742      48345                      ovacık mahallesi, istanbul caddesi  \n",
       "745      57150                        234 golden triangle, chiang saen  \n",
       "\n",
       "[262 rows x 13 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "strict_match_candidate_pairs_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have reduced the number of record pairs we need to compare to only around 262 pairs."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Record Pair Comparison (Threshold-based)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our next step is to compare each identifier in each potential record pair from the previous step and compute a similarity score for each column comparison between source 1 and source 2.\n",
    "\n",
    "We can simply do a binary comparison for simple columns like phone and zip code, namely to see if they are a full match or not. For more complex columns like names, street and city, we can use one of the available string comparison algorithms implemented in the textdistance Python library (https://pypi.org/project/textdistance/) or the jellyfish Python library (https://pypi.org/project/jellyfish/). In this particular demo, we will use the Jaro Winkler distance, however you should experiment with various string similarity measures and see which one yields best results for your particular data type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "import textdistance\n",
    "\n",
    "# add columns to store similarity scores for each record identifier\n",
    "def binary_comparison(column_value1, column_value2):\n",
    "    if column_value1 == column_value2:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "    \n",
    "\n",
    "def string_similarity_comparison(column_value1, column_value2):\n",
    "    return textdistance.jaro.normalized_similarity(column_value1, column_value2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n",
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \n",
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n",
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  after removing the cwd from sys.path.\n",
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/ipykernel_launcher.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"\n"
     ]
    }
   ],
   "source": [
    "strict_match_candidate_pairs_df[\"name_sim_score\"] = strict_match_candidate_pairs_df.apply(lambda x: string_similarity_comparison(x[\"ltable_name\"], x[\"rtable_name\"]), axis=1)\n",
    "strict_match_candidate_pairs_df[\"street_sim_score\"] = strict_match_candidate_pairs_df.apply(lambda x: string_similarity_comparison(x[\"ltable_street\"], x[\"rtable_street\"]), axis=1)\n",
    "strict_match_candidate_pairs_df[\"city_sim_score\"] = strict_match_candidate_pairs_df.apply(lambda x: string_similarity_comparison(x[\"ltable_city\"], x[\"rtable_city\"]), axis=1)\n",
    "strict_match_candidate_pairs_df[\"phone_sim_score\"] = strict_match_candidate_pairs_df.apply(lambda x: binary_comparison(x[\"ltable_phone\"], x[\"rtable_phone\"]), axis=1)\n",
    "strict_match_candidate_pairs_df[\"zip_sim_score\"] = strict_match_candidate_pairs_df.apply(lambda x: binary_comparison(x[\"ltable_zip\"], x[\"rtable_zip\"]), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ltable_ID</th>\n",
       "      <th>rtable_ID</th>\n",
       "      <th>ltable_name</th>\n",
       "      <th>rtable_name</th>\n",
       "      <th>name_sim_score</th>\n",
       "      <th>street_sim_score</th>\n",
       "      <th>city_sim_score</th>\n",
       "      <th>phone_sim_score</th>\n",
       "      <th>zip_sim_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a1</td>\n",
       "      <td>ba1</td>\n",
       "      <td>westerns country inn</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>0.851852</td>\n",
       "      <td>0.721895</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a14</td>\n",
       "      <td>ba1</td>\n",
       "      <td>vacation inn express hotel &amp; suites monar</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>0.654697</td>\n",
       "      <td>0.468683</td>\n",
       "      <td>0.540741</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a15</td>\n",
       "      <td>ba1</td>\n",
       "      <td>night inn by wyndy martinsburg</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>0.602381</td>\n",
       "      <td>0.450980</td>\n",
       "      <td>0.436027</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a21</td>\n",
       "      <td>ba1</td>\n",
       "      <td>mighty inn &amp; suites mt dora north</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>0.619199</td>\n",
       "      <td>0.498529</td>\n",
       "      <td>0.544444</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>a50</td>\n",
       "      <td>ba1</td>\n",
       "      <td>east inn karlston</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>0.606042</td>\n",
       "      <td>0.481411</td>\n",
       "      <td>0.569444</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>715</th>\n",
       "      <td>a494</td>\n",
       "      <td>ba494</td>\n",
       "      <td>guest paradise – bedroom home</td>\n",
       "      <td>hostel paradise and home apartments</td>\n",
       "      <td>0.744285</td>\n",
       "      <td>0.521601</td>\n",
       "      <td>0.654321</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>719</th>\n",
       "      <td>a495</td>\n",
       "      <td>ba495</td>\n",
       "      <td>oldcity open-hotel - suites &amp; apartments</td>\n",
       "      <td>newcity suites &amp; apartments</td>\n",
       "      <td>0.717142</td>\n",
       "      <td>0.686749</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>730</th>\n",
       "      <td>a496</td>\n",
       "      <td>ba496</td>\n",
       "      <td>live party bed &amp; breakfast</td>\n",
       "      <td>bed &amp; breakfast party house</td>\n",
       "      <td>0.752735</td>\n",
       "      <td>0.592425</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>742</th>\n",
       "      <td>a498</td>\n",
       "      <td>ba498</td>\n",
       "      <td>orka mountain villa 10</td>\n",
       "      <td>orka my villa</td>\n",
       "      <td>0.767288</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.430556</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>745</th>\n",
       "      <td>a499</td>\n",
       "      <td>ba499</td>\n",
       "      <td>arltanka silver triangle</td>\n",
       "      <td>golden triangle resort</td>\n",
       "      <td>0.670157</td>\n",
       "      <td>0.593452</td>\n",
       "      <td>0.537374</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>262 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ltable_ID rtable_ID                                ltable_name  \\\n",
       "0          a1       ba1                       westerns country inn   \n",
       "2         a14       ba1  vacation inn express hotel & suites monar   \n",
       "3         a15       ba1             night inn by wyndy martinsburg   \n",
       "4         a21       ba1          mighty inn & suites mt dora north   \n",
       "6         a50       ba1                          east inn karlston   \n",
       "..        ...       ...                                        ...   \n",
       "715      a494     ba494              guest paradise – bedroom home   \n",
       "719      a495     ba495   oldcity open-hotel - suites & apartments   \n",
       "730      a496     ba496                 live party bed & breakfast   \n",
       "742      a498     ba498                     orka mountain villa 10   \n",
       "745      a499     ba499                   arltanka silver triangle   \n",
       "\n",
       "                              rtable_name  name_sim_score  street_sim_score  \\\n",
       "0    westerns country inn bed & breakfast        0.851852          0.721895   \n",
       "2    westerns country inn bed & breakfast        0.654697          0.468683   \n",
       "3    westerns country inn bed & breakfast        0.602381          0.450980   \n",
       "4    westerns country inn bed & breakfast        0.619199          0.498529   \n",
       "6    westerns country inn bed & breakfast        0.606042          0.481411   \n",
       "..                                    ...             ...               ...   \n",
       "715   hostel paradise and home apartments        0.744285          0.521601   \n",
       "719           newcity suites & apartments        0.717142          0.686749   \n",
       "730           bed & breakfast party house        0.752735          0.592425   \n",
       "742                         orka my villa        0.767288          0.000000   \n",
       "745                golden triangle resort        0.670157          0.593452   \n",
       "\n",
       "     city_sim_score  phone_sim_score  zip_sim_score  \n",
       "0          1.000000                0              1  \n",
       "2          0.540741                0              0  \n",
       "3          0.436027                0              0  \n",
       "4          0.544444                0              0  \n",
       "6          0.569444                0              0  \n",
       "..              ...              ...            ...  \n",
       "715        0.654321                1              1  \n",
       "719        1.000000                1              0  \n",
       "730        1.000000                0              1  \n",
       "742        0.430556                1              0  \n",
       "745        0.537374                0              1  \n",
       "\n",
       "[262 rows x 9 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's inspect the table with the columns containing similarity score values for each record identifier pair\n",
    "strict_match_candidate_pairs_df[[\"ltable_ID\", \"rtable_ID\", \"ltable_name\", \"rtable_name\", \"name_sim_score\", \"street_sim_score\", \"city_sim_score\", \"phone_sim_score\", \"zip_sim_score\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that we have given each identifier pair score a range betwee 0 and 1. Actually, the phone number or the zip code matching is hardly as significant as a name or full street address. For this reason, we should boost the scores of each of these columns accordingly. For the purpose of the demo, we are going to use arbitrary number that we selected without any special testing. For the purpose of a production-ready system, the boosting weights should be computed using a more elaborate approach, for example a machine learning approach that defines best boosting values based on the accuracy of the final prediction on the whole gold standard."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \n",
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    }
   ],
   "source": [
    "# let's boost the scores of the more important fields\n",
    "strict_match_candidate_pairs_df[\"name_sim_score\"] = strict_match_candidate_pairs_df.name_sim_score.apply(lambda x: x*3)\n",
    "strict_match_candidate_pairs_df[\"street_sim_score\"] = strict_match_candidate_pairs_df.street_sim_score.apply(lambda x: x*2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>ltable_ID</th>\n",
       "      <th>rtable_ID</th>\n",
       "      <th>ltable_name</th>\n",
       "      <th>ltable_phone</th>\n",
       "      <th>ltable_city</th>\n",
       "      <th>ltable_zip</th>\n",
       "      <th>ltable_street</th>\n",
       "      <th>rtable_name</th>\n",
       "      <th>rtable_phone</th>\n",
       "      <th>rtable_city</th>\n",
       "      <th>rtable_zip</th>\n",
       "      <th>rtable_street</th>\n",
       "      <th>name_sim_score</th>\n",
       "      <th>street_sim_score</th>\n",
       "      <th>city_sim_score</th>\n",
       "      <th>phone_sim_score</th>\n",
       "      <th>zip_sim_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>a1</td>\n",
       "      <td>ba1</td>\n",
       "      <td>westerns country inn</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>447 west route 77 az</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>2.555556</td>\n",
       "      <td>1.443791</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>a14</td>\n",
       "      <td>ba1</td>\n",
       "      <td>vacation inn express hotel &amp; suites monar</td>\n",
       "      <td>4353451467</td>\n",
       "      <td>monar</td>\n",
       "      <td>84182</td>\n",
       "      <td>1511 north highway 176 ut</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>1.964092</td>\n",
       "      <td>0.937367</td>\n",
       "      <td>0.540741</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>a15</td>\n",
       "      <td>ba1</td>\n",
       "      <td>night inn by wyndy martinsburg</td>\n",
       "      <td>3042562588</td>\n",
       "      <td>martinsburg</td>\n",
       "      <td>25266</td>\n",
       "      <td>125 viking way wv</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>1.807143</td>\n",
       "      <td>0.901961</td>\n",
       "      <td>0.436027</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>a21</td>\n",
       "      <td>ba1</td>\n",
       "      <td>mighty inn &amp; suites mt dora north</td>\n",
       "      <td>3527542200</td>\n",
       "      <td>mount dora</td>\n",
       "      <td>32345</td>\n",
       "      <td>16220 us 431 west fl</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>1.857598</td>\n",
       "      <td>0.997059</td>\n",
       "      <td>0.544444</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>a50</td>\n",
       "      <td>ba1</td>\n",
       "      <td>east inn karlston</td>\n",
       "      <td></td>\n",
       "      <td>evanston</td>\n",
       "      <td>82334</td>\n",
       "      <td>1922 harrison drive wy</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>1.818125</td>\n",
       "      <td>0.962821</td>\n",
       "      <td>0.569444</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _id ltable_ID rtable_ID                                ltable_name  \\\n",
       "0    0        a1       ba1                       westerns country inn   \n",
       "2    2       a14       ba1  vacation inn express hotel & suites monar   \n",
       "3    3       a15       ba1             night inn by wyndy martinsburg   \n",
       "4    4       a21       ba1          mighty inn & suites mt dora north   \n",
       "6    6       a50       ba1                          east inn karlston   \n",
       "\n",
       "  ltable_phone  ltable_city ltable_zip              ltable_street  \\\n",
       "0   9283454567    johnscity      86033       447 west route 77 az   \n",
       "2   4353451467        monar      84182  1511 north highway 176 ut   \n",
       "3   3042562588  martinsburg      25266          125 viking way wv   \n",
       "4   3527542200   mount dora      32345       16220 us 431 west fl   \n",
       "6                  evanston      82334     1922 harrison drive wy   \n",
       "\n",
       "                            rtable_name rtable_phone rtable_city rtable_zip  \\\n",
       "0  westerns country inn bed & breakfast   9283454567   johnscity      86033   \n",
       "2  westerns country inn bed & breakfast   9283454567   johnscity      86033   \n",
       "3  westerns country inn bed & breakfast   9283454567   johnscity      86033   \n",
       "4  westerns country inn bed & breakfast   9283454567   johnscity      86033   \n",
       "6  westerns country inn bed & breakfast   9283454567   johnscity      86033   \n",
       "\n",
       "       rtable_street  name_sim_score  street_sim_score  city_sim_score  \\\n",
       "0  west route 77 447        2.555556          1.443791        1.000000   \n",
       "2  west route 77 447        1.964092          0.937367        0.540741   \n",
       "3  west route 77 447        1.807143          0.901961        0.436027   \n",
       "4  west route 77 447        1.857598          0.997059        0.544444   \n",
       "6  west route 77 447        1.818125          0.962821        0.569444   \n",
       "\n",
       "   phone_sim_score  zip_sim_score  \n",
       "0                0              1  \n",
       "2                0              0  \n",
       "3                0              0  \n",
       "4                0              0  \n",
       "6                0              0  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "strict_match_candidate_pairs_df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we need to sum up all the similarity scores and set a threshold at which we can regard a record pair an actual match. We can have a maximum score of 8. For the purpose of the demo, we will set the threhold at which we regard something as a match at the score of 5.5. The threshold has to be low enough to account for some records missing data for various columns, but high enough to reach a reasonable level of prediction accuracy. For the production-level solutions, make sure to compute a suitable threshold for your use case by doing additional experimentation on your data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "strict_match_candidate_pairs_df[\"total_sim_score\"] = strict_match_candidate_pairs_df[[\"name_sim_score\", \"street_sim_score\", \"city_sim_score\", \"phone_sim_score\", \"zip_sim_score\"]].sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>ltable_ID</th>\n",
       "      <th>rtable_ID</th>\n",
       "      <th>ltable_name</th>\n",
       "      <th>ltable_phone</th>\n",
       "      <th>ltable_city</th>\n",
       "      <th>ltable_zip</th>\n",
       "      <th>ltable_street</th>\n",
       "      <th>rtable_name</th>\n",
       "      <th>rtable_phone</th>\n",
       "      <th>rtable_city</th>\n",
       "      <th>rtable_zip</th>\n",
       "      <th>rtable_street</th>\n",
       "      <th>name_sim_score</th>\n",
       "      <th>street_sim_score</th>\n",
       "      <th>city_sim_score</th>\n",
       "      <th>phone_sim_score</th>\n",
       "      <th>zip_sim_score</th>\n",
       "      <th>total_sim_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>a1</td>\n",
       "      <td>ba1</td>\n",
       "      <td>westerns country inn</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>447 west route 77 az</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>2.555556</td>\n",
       "      <td>1.443791</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5.999346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>a14</td>\n",
       "      <td>ba1</td>\n",
       "      <td>vacation inn express hotel &amp; suites monar</td>\n",
       "      <td>4353451467</td>\n",
       "      <td>monar</td>\n",
       "      <td>84182</td>\n",
       "      <td>1511 north highway 176 ut</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>1.964092</td>\n",
       "      <td>0.937367</td>\n",
       "      <td>0.540741</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.442200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>a15</td>\n",
       "      <td>ba1</td>\n",
       "      <td>night inn by wyndy martinsburg</td>\n",
       "      <td>3042562588</td>\n",
       "      <td>martinsburg</td>\n",
       "      <td>25266</td>\n",
       "      <td>125 viking way wv</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>1.807143</td>\n",
       "      <td>0.901961</td>\n",
       "      <td>0.436027</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.145131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>a21</td>\n",
       "      <td>ba1</td>\n",
       "      <td>mighty inn &amp; suites mt dora north</td>\n",
       "      <td>3527542200</td>\n",
       "      <td>mount dora</td>\n",
       "      <td>32345</td>\n",
       "      <td>16220 us 431 west fl</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>1.857598</td>\n",
       "      <td>0.997059</td>\n",
       "      <td>0.544444</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.399101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>a50</td>\n",
       "      <td>ba1</td>\n",
       "      <td>east inn karlston</td>\n",
       "      <td></td>\n",
       "      <td>evanston</td>\n",
       "      <td>82334</td>\n",
       "      <td>1922 harrison drive wy</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>1.818125</td>\n",
       "      <td>0.962821</td>\n",
       "      <td>0.569444</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.350391</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _id ltable_ID rtable_ID                                ltable_name  \\\n",
       "0    0        a1       ba1                       westerns country inn   \n",
       "2    2       a14       ba1  vacation inn express hotel & suites monar   \n",
       "3    3       a15       ba1             night inn by wyndy martinsburg   \n",
       "4    4       a21       ba1          mighty inn & suites mt dora north   \n",
       "6    6       a50       ba1                          east inn karlston   \n",
       "\n",
       "  ltable_phone  ltable_city ltable_zip              ltable_street  \\\n",
       "0   9283454567    johnscity      86033       447 west route 77 az   \n",
       "2   4353451467        monar      84182  1511 north highway 176 ut   \n",
       "3   3042562588  martinsburg      25266          125 viking way wv   \n",
       "4   3527542200   mount dora      32345       16220 us 431 west fl   \n",
       "6                  evanston      82334     1922 harrison drive wy   \n",
       "\n",
       "                            rtable_name rtable_phone rtable_city rtable_zip  \\\n",
       "0  westerns country inn bed & breakfast   9283454567   johnscity      86033   \n",
       "2  westerns country inn bed & breakfast   9283454567   johnscity      86033   \n",
       "3  westerns country inn bed & breakfast   9283454567   johnscity      86033   \n",
       "4  westerns country inn bed & breakfast   9283454567   johnscity      86033   \n",
       "6  westerns country inn bed & breakfast   9283454567   johnscity      86033   \n",
       "\n",
       "       rtable_street  name_sim_score  street_sim_score  city_sim_score  \\\n",
       "0  west route 77 447        2.555556          1.443791        1.000000   \n",
       "2  west route 77 447        1.964092          0.937367        0.540741   \n",
       "3  west route 77 447        1.807143          0.901961        0.436027   \n",
       "4  west route 77 447        1.857598          0.997059        0.544444   \n",
       "6  west route 77 447        1.818125          0.962821        0.569444   \n",
       "\n",
       "   phone_sim_score  zip_sim_score  total_sim_score  \n",
       "0                0              1         5.999346  \n",
       "2                0              0         3.442200  \n",
       "3                0              0         3.145131  \n",
       "4                0              0         3.399101  \n",
       "6                0              0         3.350391  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "strict_match_candidate_pairs_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "strict_match_candidate_pairs_df = strict_match_candidate_pairs_df[strict_match_candidate_pairs_df[\"total_sim_score\"] > 5.5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>ltable_ID</th>\n",
       "      <th>rtable_ID</th>\n",
       "      <th>ltable_name</th>\n",
       "      <th>ltable_phone</th>\n",
       "      <th>ltable_city</th>\n",
       "      <th>ltable_zip</th>\n",
       "      <th>ltable_street</th>\n",
       "      <th>rtable_name</th>\n",
       "      <th>rtable_phone</th>\n",
       "      <th>rtable_city</th>\n",
       "      <th>rtable_zip</th>\n",
       "      <th>rtable_street</th>\n",
       "      <th>name_sim_score</th>\n",
       "      <th>street_sim_score</th>\n",
       "      <th>city_sim_score</th>\n",
       "      <th>phone_sim_score</th>\n",
       "      <th>zip_sim_score</th>\n",
       "      <th>total_sim_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>a1</td>\n",
       "      <td>ba1</td>\n",
       "      <td>westerns country inn</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>447 west route 77 az</td>\n",
       "      <td>westerns country inn bed &amp; breakfast</td>\n",
       "      <td>9283454567</td>\n",
       "      <td>johnscity</td>\n",
       "      <td>86033</td>\n",
       "      <td>west route 77 447</td>\n",
       "      <td>2.555556</td>\n",
       "      <td>1.443791</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5.999346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>a2</td>\n",
       "      <td>ba2</td>\n",
       "      <td>natpolin hostel</td>\n",
       "      <td></td>\n",
       "      <td>istanbul</td>\n",
       "      <td>34110</td>\n",
       "      <td>kanuk ayasofya mahallesi yeğen sok. no 3/1 sultanahmet fatih</td>\n",
       "      <td>natpolin hotel</td>\n",
       "      <td></td>\n",
       "      <td>istanbul</td>\n",
       "      <td>34110</td>\n",
       "      <td>kanuk ayasofya mahallesi yeğen sok. no:3/1 sultanahmet , fatih, ,</td>\n",
       "      <td>2.933333</td>\n",
       "      <td>1.859829</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7.793162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23</td>\n",
       "      <td>a3</td>\n",
       "      <td>ba3</td>\n",
       "      <td>sleep in! cairo hostel</td>\n",
       "      <td></td>\n",
       "      <td>cairo</td>\n",
       "      <td>99999</td>\n",
       "      <td>11 b ramsis st. marouf tower.downtown</td>\n",
       "      <td>sleep in cairo hostel</td>\n",
       "      <td></td>\n",
       "      <td>cairo</td>\n",
       "      <td>11511</td>\n",
       "      <td>11 b, ramses street, downtown</td>\n",
       "      <td>2.954545</td>\n",
       "      <td>1.505163</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>6.459709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27</td>\n",
       "      <td>a4</td>\n",
       "      <td>ba4</td>\n",
       "      <td>mekking house</td>\n",
       "      <td></td>\n",
       "      <td>george town</td>\n",
       "      <td></td>\n",
       "      <td>11 lorong carnarvon 10220</td>\n",
       "      <td>mekking house</td>\n",
       "      <td></td>\n",
       "      <td>george town</td>\n",
       "      <td>10220</td>\n",
       "      <td>11 lorong carnarvon</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.840000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>6.840000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>76</td>\n",
       "      <td>a8</td>\n",
       "      <td>ba8</td>\n",
       "      <td>hotel nakoogullari</td>\n",
       "      <td></td>\n",
       "      <td>batman</td>\n",
       "      <td>72200</td>\n",
       "      <td>ink mah. d.bakır cad. arsan haklari bulvari no 3</td>\n",
       "      <td>hotel nakooğullari</td>\n",
       "      <td></td>\n",
       "      <td>batman</td>\n",
       "      <td>72200</td>\n",
       "      <td>ink mah. d.bakır cad. arsan haklari bulvari no:3</td>\n",
       "      <td>2.888889</td>\n",
       "      <td>1.972222</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7.861111</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    _id ltable_ID rtable_ID             ltable_name ltable_phone  ltable_city  \\\n",
       "0     0        a1       ba1    westerns country inn   9283454567    johnscity   \n",
       "12   12        a2       ba2         natpolin hostel                  istanbul   \n",
       "23   23        a3       ba3  sleep in! cairo hostel                     cairo   \n",
       "27   27        a4       ba4           mekking house               george town   \n",
       "76   76        a8       ba8      hotel nakoogullari                    batman   \n",
       "\n",
       "   ltable_zip                                                 ltable_street  \\\n",
       "0       86033                                          447 west route 77 az   \n",
       "12      34110  kanuk ayasofya mahallesi yeğen sok. no 3/1 sultanahmet fatih   \n",
       "23      99999                         11 b ramsis st. marouf tower.downtown   \n",
       "27                                                11 lorong carnarvon 10220   \n",
       "76      72200              ink mah. d.bakır cad. arsan haklari bulvari no 3   \n",
       "\n",
       "                             rtable_name rtable_phone  rtable_city rtable_zip  \\\n",
       "0   westerns country inn bed & breakfast   9283454567    johnscity      86033   \n",
       "12                        natpolin hotel                  istanbul      34110   \n",
       "23                 sleep in cairo hostel                     cairo      11511   \n",
       "27                         mekking house               george town      10220   \n",
       "76                    hotel nakooğullari                    batman      72200   \n",
       "\n",
       "                                                        rtable_street  \\\n",
       "0                                                   west route 77 447   \n",
       "12  kanuk ayasofya mahallesi yeğen sok. no:3/1 sultanahmet , fatih, ,   \n",
       "23                                      11 b, ramses street, downtown   \n",
       "27                                                11 lorong carnarvon   \n",
       "76                   ink mah. d.bakır cad. arsan haklari bulvari no:3   \n",
       "\n",
       "    name_sim_score  street_sim_score  city_sim_score  phone_sim_score  \\\n",
       "0         2.555556          1.443791             1.0                0   \n",
       "12        2.933333          1.859829             1.0                1   \n",
       "23        2.954545          1.505163             1.0                1   \n",
       "27        3.000000          1.840000             1.0                1   \n",
       "76        2.888889          1.972222             1.0                1   \n",
       "\n",
       "    zip_sim_score  total_sim_score  \n",
       "0               1         5.999346  \n",
       "12              1         7.793162  \n",
       "23              0         6.459709  \n",
       "27              0         6.840000  \n",
       "76              1         7.861111  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "strict_match_candidate_pairs_df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After filtering out all record pairs that have a total similarity score lower than our set threshold, we are left with 442 record pairs. Now we also need to make sure that we do a one to one comparison and only allow the records from source 1 to be matches to one record in source 2."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's extract one to one predicted pairs and evaluate the quality based on an annotated gold standard (for the purpose of the demo, the gold standard was not split into training, testing and validation sets, however, this is the recommended way to follow for the developmenet of a production-ready system)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# extract predicted record pairs\n",
    "predicted_record_pair_list = list()\n",
    "\n",
    "strict_match_candidate_pairs_df = strict_match_candidate_pairs_df[[\"ltable_ID\", \"rtable_ID\", \"total_sim_score\"]]\n",
    "df_to_list = strict_match_candidate_pairs_df.values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nstrict_match_candidate_pairs_df2 = strict_match_candidate_pairs_df.groupby(by=[\"ltable_ID\"])\\nstrict_match_candidate_pairs_df3 = strict_match_candidate_pairs_df2.agg({\"total_sim_score\":\"max\"})\\nstrict_match_candidate_pairs_df3.reset_index(inplace=True)\\n'"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ToDo: redo with pandas: https://stackoverflow.com/questions/39918262/filter-max-values-by-row-and-columns\n",
    "\"\"\"\n",
    "strict_match_candidate_pairs_df2 = strict_match_candidate_pairs_df.groupby(by=[\"ltable_ID\"])\n",
    "strict_match_candidate_pairs_df3 = strict_match_candidate_pairs_df2.agg({\"total_sim_score\":\"max\"})\n",
    "strict_match_candidate_pairs_df3.reset_index(inplace=True)\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "52\n"
     ]
    }
   ],
   "source": [
    "# extract the unique one to one predictions based on the highest total similarity score between each pair\n",
    "from collections import defaultdict\n",
    "\n",
    "value_pair = defaultdict(list)\n",
    "for x in df_to_list:\n",
    "    value_pair[x[0]].append([x[1], x[2]])\n",
    "\n",
    "final_predictions = set()\n",
    "for x in value_pair:\n",
    "    final_predictions.add((x, sorted(value_pair[x], key=lambda x: x[1])[-1][0]))\n",
    "\n",
    "print(len(final_predictions))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Evaluate Prediction Quality (Threshold-based)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "from utils import build_annotation_mapping, evaluate_matches"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "annotation_df = pd.read_csv(\"./data/gold_standard.csv\", low_memory=False)\n",
    "annotation_df = annotation_df[[\"ID\", \"ID2\", \"match\"]].dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>ID2</th>\n",
       "      <th>match</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a1</td>\n",
       "      <td>ba1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a2</td>\n",
       "      <td>ba2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a3</td>\n",
       "      <td>ba3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a4</td>\n",
       "      <td>ba4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a5</td>\n",
       "      <td>ba5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>a495</td>\n",
       "      <td>ba495</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>a496</td>\n",
       "      <td>ba496</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>a497</td>\n",
       "      <td>ba497</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>a498</td>\n",
       "      <td>ba498</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>a499</td>\n",
       "      <td>ba499</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID    ID2  match\n",
       "0     a1    ba1      1\n",
       "1     a2    ba2      1\n",
       "2     a3    ba3      1\n",
       "3     a4    ba4      1\n",
       "4     a5    ba5      1\n",
       "..   ...    ...    ...\n",
       "79  a495  ba495      0\n",
       "80  a496  ba496      0\n",
       "81  a497  ba497      0\n",
       "82  a498  ba498      0\n",
       "83  a499  ba499      0\n",
       "\n",
       "[84 rows x 3 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annotation_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "gold_standard = build_annotation_mapping(annotation_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recall, precision, f1score = evaluate_matches(final_predictions, gold_standard)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Precision: 0.8269230769230769\n",
      "Recall: 0.7818181818181819\n",
      "F1-Score: 80.37\n"
     ]
    }
   ],
   "source": [
    "print(\"Precision:\", precision)\n",
    "print(\"Recall:\", recall)\n",
    "print(\"F1-Score:\", f1score)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using a simple threhold-based approach we have already achieved a significant level of Precision and Recall without having to rely on a large training corpus of thousands of annotated record pairs. This is one of the main adventages of such simple apporaches. Next we will shortly compare a neural-based Entity Matching system called DeepMatcher."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Record Pair Comparison (Neural-based, DeepMatcher)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A neural-based approach to Entity Matching has its pros and cons. It still required pre-processing and candidate generations teps, however, the similarity scores we had to compute for each record identifier is now a part of a fully automated process within the neural-based approach, such as DeepMatcher. The drawback of this approach is the fact that often a lot of annotated data with manually matched record pairs is required to be able to train a model that will produce quality match classification results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "import deepmatcher as dm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/torchtext/data/field.py:150: UserWarning: MatchingField class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.\n",
      "  warnings.warn('{} class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.'.format(self.__class__.__name__), UserWarning)\n",
      "\n",
      "Reading and processing data from \"./data/deep_matcher/train.csv\"\n",
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/torchtext/data/example.py:68: UserWarning: Example class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.\n",
      "  warnings.warn('Example class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.', UserWarning)\n",
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/torchtext/data/example.py:78: UserWarning: Example class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.\n",
      "  warnings.warn('Example class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.', UserWarning)\n",
      "0% [############################# ] 100% | ETA: 00:00:00\n",
      "Reading and processing data from \"./data/deep_matcher/validation.csv\"\n",
      "0% [######################  ] 100% | ETA: 00:00:00\n",
      "Reading and processing data from \"./data/deep_matcher/test.csv\"\n",
      "0% [######################  ] 100% | ETA: 00:00:00/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/torchtext/data/iterator.py:48: UserWarning: MatchingIterator class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.\n",
      "  warnings.warn('{} class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.'.format(self.__class__.__name__), UserWarning)\n",
      "\n",
      "Building vocabulary\n",
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/torchtext/data/batch.py:23: UserWarning: Batch class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.\n",
      "  warnings.warn('{} class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.'.format(self.__class__.__name__), UserWarning)\n",
      "0% [#] 100% | ETA: 00:00:00\n",
      "Total time elapsed: 00:00:00\n",
      "\n",
      "Computing principal components\n",
      "0% [#] 100% | ETA: 00:00:00\n",
      "Total time elapsed: 00:00:00\n"
     ]
    }
   ],
   "source": [
    "train, validation, test = dm.data.process(\n",
    "    path='./data/deep_matcher',\n",
    "    train='train.csv',\n",
    "    validation='validation.csv',\n",
    "    test='test.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The DeepMatcher framework for neural Entity Matching provides extensive examples on how to use it under its GitHub repository (https://github.com/anhaidgroup/deepmatcher/tree/master/examples). For this experiment we will use a Hybrid DeepMatcher model [citation, page 8]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "model = dm.MatchingModel(attr_summarizer=dm.attr_summarizers.Hybrid(word_contextualizer=dm.word_contextualizers.SelfAttention(heads=2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/torchtext/data/iterator.py:48: UserWarning: MatchingIterator class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.\n",
      "  warnings.warn('{} class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.'.format(self.__class__.__name__), UserWarning)\n",
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/torchtext/data/batch.py:23: UserWarning: Batch class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.\n",
      "  warnings.warn('{} class will be retired soon and moved to torchtext.legacy. Please see the most recent release notes for further information.'.format(self.__class__.__name__), UserWarning)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "* Number of trainable parameters: 19590008\n",
      "===>  TRAIN Epoch 1\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ivan/anaconda3/envs/entity_matching_demo/lib/python3.7/site-packages/torch/nn/functional.py:2398: UserWarning: reduction: 'mean' divides the total loss by both the batch size and the support size.'batchmean' divides only by the batch size, and aligns with the KL div math definition.'mean' will be changed to behave the same as 'batchmean' in the next major release.\n",
      "  warnings.warn(\"reduction: 'mean' divides the total loss by both the batch size and the support size.\"\n",
      "0% [█] 100% | ETA: 00:00:00\n",
      "Total time elapsed: 00:00:01\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished Epoch 1 || Run Time:    1.7 | Load Time:    0.1 || F1:  79.31 | Prec:  74.19 | Rec:  85.19 || Ex/s:  22.19\n",
      "\n",
      "===>  EVAL Epoch 1\n",
      "Finished Epoch 1 || Run Time:    0.4 | Load Time:    0.0 || F1:  75.68 | Prec:  60.87 | Rec: 100.00 || Ex/s:  52.64\n",
      "\n",
      "* Best F1: tensor(75.6757, device='cuda:0')\n",
      "Saving best model...\n",
      "Done.\n",
      "---------------------\n",
      "\n",
      "===>  TRAIN Epoch 2\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "0% [█] 100% | ETA: 00:00:00\n",
      "Total time elapsed: 00:00:01\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished Epoch 2 || Run Time:    1.4 | Load Time:    0.0 || F1:  83.08 | Prec:  71.05 | Rec: 100.00 || Ex/s:  26.68\n",
      "\n",
      "===>  EVAL Epoch 2\n",
      "Finished Epoch 2 || Run Time:    0.4 | Load Time:    0.0 || F1:  75.68 | Prec:  60.87 | Rec: 100.00 || Ex/s:  56.11\n",
      "\n",
      "---------------------\n",
      "\n",
      "===>  TRAIN Epoch 3\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "0% [█] 100% | ETA: 00:00:00\n",
      "Total time elapsed: 00:00:01\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished Epoch 3 || Run Time:    1.4 | Load Time:    0.0 || F1:  83.08 | Prec:  71.05 | Rec: 100.00 || Ex/s:  25.61\n",
      "\n",
      "===>  EVAL Epoch 3\n",
      "Finished Epoch 3 || Run Time:    0.6 | Load Time:    0.0 || F1:  75.68 | Prec:  60.87 | Rec: 100.00 || Ex/s:  38.16\n",
      "\n",
      "---------------------\n",
      "\n",
      "===>  TRAIN Epoch 4\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "0% [█] 100% | ETA: 00:00:00\n",
      "Total time elapsed: 00:00:01\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished Epoch 4 || Run Time:    1.6 | Load Time:    0.1 || F1:  83.08 | Prec:  71.05 | Rec: 100.00 || Ex/s:  23.10\n",
      "\n",
      "===>  EVAL Epoch 4\n",
      "Finished Epoch 4 || Run Time:    0.4 | Load Time:    0.0 || F1:  75.68 | Prec:  60.87 | Rec: 100.00 || Ex/s:  50.24\n",
      "\n",
      "---------------------\n",
      "\n",
      "===>  TRAIN Epoch 5\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "0% [█] 100% | ETA: 00:00:00\n",
      "Total time elapsed: 00:00:01\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished Epoch 5 || Run Time:    1.4 | Load Time:    0.0 || F1:  83.08 | Prec:  71.05 | Rec: 100.00 || Ex/s:  25.49\n",
      "\n",
      "===>  EVAL Epoch 5\n",
      "Finished Epoch 5 || Run Time:    0.4 | Load Time:    0.0 || F1:  75.68 | Prec:  60.87 | Rec: 100.00 || Ex/s:  53.28\n",
      "\n",
      "---------------------\n",
      "\n",
      "Loading best model...\n",
      "Training done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "tensor(75.6757, device='cuda:0')"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model.run_train(\n",
    "    train,\n",
    "    validation,\n",
    "    epochs=5,\n",
    "    batch_size=8,\n",
    "    best_save_path='hybrid_model.pth',\n",
    "    pos_neg_ratio=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "===>  EVAL Epoch 1\n",
      "Finished Epoch 1 || Run Time:    0.2 | Load Time:    0.0 || F1:  75.68 | Prec:  60.87 | Rec: 100.00 || Ex/s: 125.78\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "tensor(75.6757, device='cuda:0')"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model.run_eval(test)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that for the purpose of the demo, we have skipped the candidate generation step for DeepMatcher, hence Recall is at maximum."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DeepMatcher provides multiple tutotials on its usage at https://github.com/anhaidgroup/deepmatcher, continue there for more advanced examples of its usage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "entity_matching_demo",
   "language": "python",
   "name": "entity_matching_demo"
  },
  "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}