Introduction to Patient Matching.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to Patient matching\n",
    "This notebook introduces key concepts of patient matching while demonstrating those concepts using python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*********************"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## About this notebook\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* This notebook provides an introduction and demonstration of the patient matching process.This tool is intended for anyone who wants a better understand the matching process with a practical example. This noteboook uses syntehtic data but can also run with your own datasets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "* This tool uses the  **Python Record Linkage Toolkit** to demonstrate the patient matching process.The **Python Record Linkage Toolkit** is a library to link records in or between data sources. The toolkit provides\n",
    "most of the tools needed for record linkage and deduplication. \n",
    "\n",
    "    * Link github: [Python Record Linkage Toolkit](https://github.com/J535D165/recordlinkage)\n",
    "    * Link Documentation: [Documentation : Python Record Linkage Toolkit](https://recordlinkage.readthedocs.io/en/latest/index.html#)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*  To use this notebook with your dataset you need to replace both dataset :deduplication and the gold standard file with your own data file. However the fields in your data files should be exact same as the ones in the notebook, if not you can still for each step comment the code corresponding to the fields not available."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What is patient matching?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Data matching: The process to identify records that refer to the same real-world entity within one or across several databases.\n",
    "    * When applied on one database, this process is called deduplication.\n",
    "    * Also known as entity resolution, object identification, duplicate detection.\n",
    "\n",
    "\n",
    "**Patient matching**: Comparing data from multiple sources to identify records that represent the same patient."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Python Record Linkage Toolkit"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "* The **Python Record Linkage Toolkit** is a library to link records in or between data sources. The toolkit provides\n",
    "most of the tools needed for record linkage and deduplication. \n",
    "* The package contains indexing methods, functions to compare records and classifiers. \n",
    "* The project is inspired by the Freely Extensible Biomedical Record Linkage (FEBRL,Christen et al. 2002, 2004) project but add extensive use of data manipulation tools like pandas and numpy and machine learning tools like scikit-learn.\n",
    "* Link github: [Python Record Linkage Toolkit](https://github.com/J535D165/recordlinkage)\n",
    "* Link Documentation: [Documentation : Python Record Linkage Toolkit](https://recordlinkage.readthedocs.io/en/latest/index.html#)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Patient matching process (deduplication)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "* Record linkage is a complex process and requires the user to understand, up to a certain degree, many technical details.\n",
    "* The process consists of 5 steps : **Pre-processing**, **Indexing/Blocking**, **Comparison**,**Classification**, **Evaluation**\n",
    "* It is important to understand the process because each step influence the matching results\n",
    "* For example understanding blocking strategy is crucial because affecting performance and matching results\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![record linkage process](./docs/matching_process_dedup.PNG \"record linkage\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "import recordlinkage  as rl\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import warnings\n",
    "from recordlinkage.index import Block\n",
    "from recordlinkage.datasets import load_febrl4,load_febrl3,load_febrl2,load_febrl1\n",
    "from recordlinkage.preprocessing import phonetic\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Importing dataset to deduplicate\n",
    "Before we start with the deduplication process first we import the data in notebook. We assume the data has been cleaned and mapped to be imported in the format below :\n",
    "\n",
    "| Variable| Description | Format |\n",
    "| --- | --- | --- |\n",
    "| rec_id | Internal ID in your data | |\n",
    "| given_name | patient first name | |\n",
    "| surname | patient last name | |\n",
    "| date_of_birth | patient date of birth | YYYYMMDD  |\n",
    "| address_1 | patient address first part | |\n",
    "| address_2 | patient address second part | |\n",
    "| suburb | patient address surburb | |\n",
    "| postcode | patient postal code | |\n",
    "| state | patient address state | |\n",
    "| soc_soc_id | social security number | |\n",
    "\n",
    "In this notebook we use  synthetic dataset from The Python Record Linkage Toolkit (PRLT). The PRLT contains several open public synthetic datasets. The package is distributed with a  four synthetic datasets. For this project we will use The Freely Extensible Biomedical Record Linkage (Febrl) dataset 3 . **Dataset 3 (FEBRL3) contains 5000 records (2000 originals and 3000 duplicates)**.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of records: 5000\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>given_name</th>\n",
       "      <th>surname</th>\n",
       "      <th>address_1</th>\n",
       "      <th>address_2</th>\n",
       "      <th>suburb</th>\n",
       "      <th>postcode</th>\n",
       "      <th>state</th>\n",
       "      <th>date_of_birth</th>\n",
       "      <th>soc_sec_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>rec-1307-dup-2</th>\n",
       "      <td>campbell</td>\n",
       "      <td>abbey</td>\n",
       "      <td>adair street</td>\n",
       "      <td>arcadai</td>\n",
       "      <td>wantirna</td>\n",
       "      <td>6051</td>\n",
       "      <td>sa</td>\n",
       "      <td>19211108.0</td>\n",
       "      <td>1327917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-515-dup-3</th>\n",
       "      <td>tyna</td>\n",
       "      <td>abea</td>\n",
       "      <td>haines street</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ballarat</td>\n",
       "      <td>2303</td>\n",
       "      <td>nsw</td>\n",
       "      <td>19370412.0</td>\n",
       "      <td>5824612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-515-dup-0</th>\n",
       "      <td>tynna</td>\n",
       "      <td>abera</td>\n",
       "      <td>haines street</td>\n",
       "      <td>eureka</td>\n",
       "      <td>ballarat</td>\n",
       "      <td>2303</td>\n",
       "      <td>nws</td>\n",
       "      <td>19370412.0</td>\n",
       "      <td>5824612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-515-dup-1</th>\n",
       "      <td>tynan</td>\n",
       "      <td>abera</td>\n",
       "      <td>hainesstreet</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ballarat</td>\n",
       "      <td>2303</td>\n",
       "      <td>nws</td>\n",
       "      <td>19370412.0</td>\n",
       "      <td>6873174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-515-dup-2</th>\n",
       "      <td>tynan</td>\n",
       "      <td>abera</td>\n",
       "      <td>haines street</td>\n",
       "      <td>st john of go d hospital</td>\n",
       "      <td>ballarat</td>\n",
       "      <td>2033</td>\n",
       "      <td>nsw</td>\n",
       "      <td>19370412.0</td>\n",
       "      <td>5821612</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               given_name surname      address_1                 address_2  \\\n",
       "rec_id                                                                       \n",
       "rec-1307-dup-2   campbell   abbey   adair street                   arcadai   \n",
       "rec-515-dup-3        tyna    abea  haines street                       NaN   \n",
       "rec-515-dup-0       tynna   abera  haines street                    eureka   \n",
       "rec-515-dup-1       tynan   abera   hainesstreet                       NaN   \n",
       "rec-515-dup-2       tynan   abera  haines street  st john of go d hospital   \n",
       "\n",
       "                  suburb  postcode state  date_of_birth  soc_sec_id  \n",
       "rec_id                                                               \n",
       "rec-1307-dup-2  wantirna      6051    sa     19211108.0     1327917  \n",
       "rec-515-dup-3   ballarat      2303   nsw     19370412.0     5824612  \n",
       "rec-515-dup-0   ballarat      2303   nws     19370412.0     5824612  \n",
       "rec-515-dup-1   ballarat      2303   nws     19370412.0     6873174  \n",
       "rec-515-dup-2   ballarat      2033   nsw     19370412.0     5821612  "
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# file to deduplicate\n",
    "IMPORT_FILE_TO_DEDUPLICATE = './data/dataset_febrl3.csv'\n",
    "df_a = pd.read_csv(IMPORT_FILE_TO_DEDUPLICATE)\n",
    "df_a = df_a.set_index('rec_id')\n",
    "\n",
    "print(\"Total number of records:\", len(df_a))\n",
    "df_a.sort_values('surname').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For more info : [Synthetic datasets](https://recordlinkage.readthedocs.io/en/latest/ref-datasets.html \"Title\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pre-Processing and Standardization"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The main task of data cleaning and standardization is the conversion of the raw input data into well defined, consistent forms, as well as the resolution of inconsistencies in the way information is represented and encoded.(Christen 2012)\n",
    "* Cleaning patient names,  addresses and hospital names\n",
    "* Parsing  patient names, addresses and phone number\n",
    "* Formatting and coding dates (e.g. date of birth, date of ART initiation)\n",
    "\n",
    "We have split the date of birth in 3 columns for more easy comparison, also we have calculated the metaphone of the given name and surname respectively.\n",
    "\n",
    "**Metaphone** is a phonetic encoding algorithm used to encode the way words an syllable are pronounces to help **reduce minor typographical error**.The output of a phonetic algorithm is an intentionally approximate phonetic representation of the word. With application still limited to English words Metaphone is an improvement on the Soundex algorihtm .\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "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>given_name</th>\n",
       "      <th>surname</th>\n",
       "      <th>address_1</th>\n",
       "      <th>address_2</th>\n",
       "      <th>suburb</th>\n",
       "      <th>postcode</th>\n",
       "      <th>state</th>\n",
       "      <th>date_of_birth</th>\n",
       "      <th>soc_sec_id</th>\n",
       "      <th>YearB</th>\n",
       "      <th>MonthB</th>\n",
       "      <th>DayB</th>\n",
       "      <th>metaphone_given_name</th>\n",
       "      <th>metaphone_surname</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>rec-1307-dup-2</th>\n",
       "      <td>campbell</td>\n",
       "      <td>abbey</td>\n",
       "      <td>adair street</td>\n",
       "      <td>arcadai</td>\n",
       "      <td>wantirna</td>\n",
       "      <td>6051</td>\n",
       "      <td>sa</td>\n",
       "      <td>19211108.0</td>\n",
       "      <td>1327917</td>\n",
       "      <td>1921</td>\n",
       "      <td>10</td>\n",
       "      <td>08.0</td>\n",
       "      <td>C514</td>\n",
       "      <td>AB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-515-dup-3</th>\n",
       "      <td>tyna</td>\n",
       "      <td>abea</td>\n",
       "      <td>haines street</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ballarat</td>\n",
       "      <td>2303</td>\n",
       "      <td>nsw</td>\n",
       "      <td>19370412.0</td>\n",
       "      <td>5824612</td>\n",
       "      <td>1937</td>\n",
       "      <td>41</td>\n",
       "      <td>12.0</td>\n",
       "      <td>T500</td>\n",
       "      <td>AB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-515-dup-0</th>\n",
       "      <td>tynna</td>\n",
       "      <td>abera</td>\n",
       "      <td>haines street</td>\n",
       "      <td>eureka</td>\n",
       "      <td>ballarat</td>\n",
       "      <td>2303</td>\n",
       "      <td>nws</td>\n",
       "      <td>19370412.0</td>\n",
       "      <td>5824612</td>\n",
       "      <td>1937</td>\n",
       "      <td>41</td>\n",
       "      <td>12.0</td>\n",
       "      <td>T500</td>\n",
       "      <td>ABR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-515-dup-1</th>\n",
       "      <td>tynan</td>\n",
       "      <td>abera</td>\n",
       "      <td>hainesstreet</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ballarat</td>\n",
       "      <td>2303</td>\n",
       "      <td>nws</td>\n",
       "      <td>19370412.0</td>\n",
       "      <td>6873174</td>\n",
       "      <td>1937</td>\n",
       "      <td>41</td>\n",
       "      <td>12.0</td>\n",
       "      <td>T550</td>\n",
       "      <td>ABR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-515-dup-2</th>\n",
       "      <td>tynan</td>\n",
       "      <td>abera</td>\n",
       "      <td>haines street</td>\n",
       "      <td>st john of go d hospital</td>\n",
       "      <td>ballarat</td>\n",
       "      <td>2033</td>\n",
       "      <td>nsw</td>\n",
       "      <td>19370412.0</td>\n",
       "      <td>5821612</td>\n",
       "      <td>1937</td>\n",
       "      <td>41</td>\n",
       "      <td>12.0</td>\n",
       "      <td>T550</td>\n",
       "      <td>ABR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               given_name surname      address_1                 address_2  \\\n",
       "rec_id                                                                       \n",
       "rec-1307-dup-2   campbell   abbey   adair street                   arcadai   \n",
       "rec-515-dup-3        tyna    abea  haines street                       NaN   \n",
       "rec-515-dup-0       tynna   abera  haines street                    eureka   \n",
       "rec-515-dup-1       tynan   abera   hainesstreet                       NaN   \n",
       "rec-515-dup-2       tynan   abera  haines street  st john of go d hospital   \n",
       "\n",
       "                  suburb  postcode state date_of_birth  soc_sec_id YearB  \\\n",
       "rec_id                                                                     \n",
       "rec-1307-dup-2  wantirna      6051    sa    19211108.0     1327917  1921   \n",
       "rec-515-dup-3   ballarat      2303   nsw    19370412.0     5824612  1937   \n",
       "rec-515-dup-0   ballarat      2303   nws    19370412.0     5824612  1937   \n",
       "rec-515-dup-1   ballarat      2303   nws    19370412.0     6873174  1937   \n",
       "rec-515-dup-2   ballarat      2033   nsw    19370412.0     5821612  1937   \n",
       "\n",
       "               MonthB  DayB metaphone_given_name metaphone_surname  \n",
       "rec_id                                                              \n",
       "rec-1307-dup-2     10  08.0                 C514                AB  \n",
       "rec-515-dup-3      41  12.0                 T500                AB  \n",
       "rec-515-dup-0      41  12.0                 T500               ABR  \n",
       "rec-515-dup-1      41  12.0                 T550               ABR  \n",
       "rec-515-dup-2      41  12.0                 T550               ABR  "
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_a['date_of_birth'] = df_a['date_of_birth'].astype(str)\n",
    "df_a['YearB'] = df_a['date_of_birth'].str[:4].astype(str)\n",
    "df_a['MonthB'] = df_a['date_of_birth'].str[5:7].astype(str)\n",
    "df_a['DayB'] = df_a['date_of_birth'].str[6:].astype(str)\n",
    "\n",
    "df_a['metaphone_given_name'] = phonetic(df_a['given_name'], method='soundex')\n",
    "df_a['metaphone_surname'] = phonetic(df_a['surname'], method='metaphone')\n",
    "df_a.sort_values('surname').head() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Blocking "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The second step of the process called **blocking or indexing** try to reduce the number of records we need to compare. The idea is instead of comparing all records of the dataset between themselves we want to compare only the records that are most likely to be matched. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Blocking Key"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Phonetic encoding"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Phonetic encoding convert a string name into a code according to how it is pronounced. This help reduce minor typographical errors.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Soundex\n",
      "**************************************************\n",
      "mayer: M600\n",
      "meyer : M600\n",
      "myer : M600\n",
      "mayar : M600\n",
      "mayor : M600\n",
      "maier : M600\n",
      "Metaphone\n",
      "**************************************************\n",
      "mayer: MYR\n",
      "meyer : MYR\n",
      "myer : MYR\n",
      "mayar : MYR\n",
      "mayor : MYR\n",
      "maier : MR\n"
     ]
    }
   ],
   "source": [
    "from jellyfish import soundex, metaphone\n",
    "\n",
    "print('Soundex')\n",
    "print('*'*50)\n",
    "print(\"mayer:\",soundex('mayer'))\n",
    "print(\"meyer :\",soundex('meyer'))\n",
    "print(\"myer :\",soundex('myer'))\n",
    "print(\"mayar :\",soundex('mayar'))\n",
    "print(\"mayor :\",soundex('mayor'))\n",
    "print(\"maier :\",soundex('maier'))\n",
    "\n",
    "\n",
    "print('Metaphone')\n",
    "print('*'*50)\n",
    "print(\"mayer:\",metaphone('mayer'))\n",
    "print(\"meyer :\",metaphone('meyer'))\n",
    "print(\"myer :\",metaphone('myer'))\n",
    "print(\"mayar :\",metaphone('mayar'))\n",
    "print(\"mayor :\",metaphone('mayor'))\n",
    "print(\"maier :\",metaphone('maier'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**In PRLT Phonetic encoding possible options are “soundex”, “nysiis”, “metaphone” or “match_rating”.**\n",
    "\n",
    "**Others not included : double-metaphne, phonix , phonex, OCNA, Fuzzy soundex (Christen 2012)**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Full Indexing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Comparing each record to all other records pairs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WARNING:recordlinkage:indexing - performance warning - A full index can result in large number of record pairs.\n",
      "Number of candidate record pairs 12497500\n"
     ]
    }
   ],
   "source": [
    "indexer = rl.Index()\n",
    "indexer.full()\n",
    "candidate_record_pairs = indexer.index(df_a)\n",
    "print(\"Number of candidate record pairs\", len(candidate_record_pairs))\n",
    "#candidate_record_pairs.to_frame(index=False)\n",
    "\n",
    "#(5000*5000-5000)/2 = 12 497 500"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Blocking Key"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Example 1) Only compare records with the same last name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of candidate record pairs : 37255\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>rec_id_1</th>\n",
       "      <th>rec_id_2</th>\n",
       "      <th>given_name_1</th>\n",
       "      <th>surname_1</th>\n",
       "      <th>date_of_birth_1</th>\n",
       "      <th>given_name_2</th>\n",
       "      <th>surname_2</th>\n",
       "      <th>date_of_birth_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rec-1191-dup-3</td>\n",
       "      <td>rec-1496-org</td>\n",
       "      <td>loan</td>\n",
       "      <td>green</td>\n",
       "      <td>19440514.0</td>\n",
       "      <td>mitchell</td>\n",
       "      <td>green</td>\n",
       "      <td>19560409.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rec-1076-org</td>\n",
       "      <td>rec-1496-org</td>\n",
       "      <td>sarah</td>\n",
       "      <td>green</td>\n",
       "      <td>19140908.0</td>\n",
       "      <td>mitchell</td>\n",
       "      <td>green</td>\n",
       "      <td>19560409.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>rec-1076-org</td>\n",
       "      <td>rec-1191-dup-3</td>\n",
       "      <td>sarah</td>\n",
       "      <td>green</td>\n",
       "      <td>19140908.0</td>\n",
       "      <td>loan</td>\n",
       "      <td>green</td>\n",
       "      <td>19440514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>rec-1297-org</td>\n",
       "      <td>rec-1496-org</td>\n",
       "      <td>emiily</td>\n",
       "      <td>green</td>\n",
       "      <td>19380625.0</td>\n",
       "      <td>mitchell</td>\n",
       "      <td>green</td>\n",
       "      <td>19560409.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>rec-1297-org</td>\n",
       "      <td>rec-1191-dup-3</td>\n",
       "      <td>emiily</td>\n",
       "      <td>green</td>\n",
       "      <td>19380625.0</td>\n",
       "      <td>loan</td>\n",
       "      <td>green</td>\n",
       "      <td>19440514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>rec-1297-org</td>\n",
       "      <td>rec-1076-org</td>\n",
       "      <td>emiily</td>\n",
       "      <td>green</td>\n",
       "      <td>19380625.0</td>\n",
       "      <td>sarah</td>\n",
       "      <td>green</td>\n",
       "      <td>19140908.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>rec-1357-org</td>\n",
       "      <td>rec-1496-org</td>\n",
       "      <td>jordan</td>\n",
       "      <td>green</td>\n",
       "      <td>nan</td>\n",
       "      <td>mitchell</td>\n",
       "      <td>green</td>\n",
       "      <td>19560409.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>rec-1357-org</td>\n",
       "      <td>rec-1191-dup-3</td>\n",
       "      <td>jordan</td>\n",
       "      <td>green</td>\n",
       "      <td>nan</td>\n",
       "      <td>loan</td>\n",
       "      <td>green</td>\n",
       "      <td>19440514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>rec-1357-org</td>\n",
       "      <td>rec-1076-org</td>\n",
       "      <td>jordan</td>\n",
       "      <td>green</td>\n",
       "      <td>nan</td>\n",
       "      <td>sarah</td>\n",
       "      <td>green</td>\n",
       "      <td>19140908.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>rec-1357-org</td>\n",
       "      <td>rec-1297-org</td>\n",
       "      <td>jordan</td>\n",
       "      <td>green</td>\n",
       "      <td>nan</td>\n",
       "      <td>emiily</td>\n",
       "      <td>green</td>\n",
       "      <td>19380625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>rec-759-org</td>\n",
       "      <td>rec-1496-org</td>\n",
       "      <td>samuel</td>\n",
       "      <td>green</td>\n",
       "      <td>19020601.0</td>\n",
       "      <td>mitchell</td>\n",
       "      <td>green</td>\n",
       "      <td>19560409.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>rec-759-org</td>\n",
       "      <td>rec-1191-dup-3</td>\n",
       "      <td>samuel</td>\n",
       "      <td>green</td>\n",
       "      <td>19020601.0</td>\n",
       "      <td>loan</td>\n",
       "      <td>green</td>\n",
       "      <td>19440514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>rec-759-org</td>\n",
       "      <td>rec-1076-org</td>\n",
       "      <td>samuel</td>\n",
       "      <td>green</td>\n",
       "      <td>19020601.0</td>\n",
       "      <td>sarah</td>\n",
       "      <td>green</td>\n",
       "      <td>19140908.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>rec-759-org</td>\n",
       "      <td>rec-1297-org</td>\n",
       "      <td>samuel</td>\n",
       "      <td>green</td>\n",
       "      <td>19020601.0</td>\n",
       "      <td>emiily</td>\n",
       "      <td>green</td>\n",
       "      <td>19380625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>rec-759-org</td>\n",
       "      <td>rec-1357-org</td>\n",
       "      <td>samuel</td>\n",
       "      <td>green</td>\n",
       "      <td>19020601.0</td>\n",
       "      <td>jordan</td>\n",
       "      <td>green</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>rec-1004-org</td>\n",
       "      <td>rec-1496-org</td>\n",
       "      <td>tayla</td>\n",
       "      <td>green</td>\n",
       "      <td>19310715.0</td>\n",
       "      <td>mitchell</td>\n",
       "      <td>green</td>\n",
       "      <td>19560409.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>rec-1004-org</td>\n",
       "      <td>rec-1191-dup-3</td>\n",
       "      <td>tayla</td>\n",
       "      <td>green</td>\n",
       "      <td>19310715.0</td>\n",
       "      <td>loan</td>\n",
       "      <td>green</td>\n",
       "      <td>19440514.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>rec-1004-org</td>\n",
       "      <td>rec-1076-org</td>\n",
       "      <td>tayla</td>\n",
       "      <td>green</td>\n",
       "      <td>19310715.0</td>\n",
       "      <td>sarah</td>\n",
       "      <td>green</td>\n",
       "      <td>19140908.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>rec-1004-org</td>\n",
       "      <td>rec-1297-org</td>\n",
       "      <td>tayla</td>\n",
       "      <td>green</td>\n",
       "      <td>19310715.0</td>\n",
       "      <td>emiily</td>\n",
       "      <td>green</td>\n",
       "      <td>19380625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>rec-1004-org</td>\n",
       "      <td>rec-1357-org</td>\n",
       "      <td>tayla</td>\n",
       "      <td>green</td>\n",
       "      <td>19310715.0</td>\n",
       "      <td>jordan</td>\n",
       "      <td>green</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          rec_id_1        rec_id_2 given_name_1 surname_1 date_of_birth_1  \\\n",
       "0   rec-1191-dup-3    rec-1496-org         loan     green      19440514.0   \n",
       "1     rec-1076-org    rec-1496-org        sarah     green      19140908.0   \n",
       "2     rec-1076-org  rec-1191-dup-3        sarah     green      19140908.0   \n",
       "3     rec-1297-org    rec-1496-org       emiily     green      19380625.0   \n",
       "4     rec-1297-org  rec-1191-dup-3       emiily     green      19380625.0   \n",
       "5     rec-1297-org    rec-1076-org       emiily     green      19380625.0   \n",
       "6     rec-1357-org    rec-1496-org       jordan     green             nan   \n",
       "7     rec-1357-org  rec-1191-dup-3       jordan     green             nan   \n",
       "8     rec-1357-org    rec-1076-org       jordan     green             nan   \n",
       "9     rec-1357-org    rec-1297-org       jordan     green             nan   \n",
       "10     rec-759-org    rec-1496-org       samuel     green      19020601.0   \n",
       "11     rec-759-org  rec-1191-dup-3       samuel     green      19020601.0   \n",
       "12     rec-759-org    rec-1076-org       samuel     green      19020601.0   \n",
       "13     rec-759-org    rec-1297-org       samuel     green      19020601.0   \n",
       "14     rec-759-org    rec-1357-org       samuel     green      19020601.0   \n",
       "15    rec-1004-org    rec-1496-org        tayla     green      19310715.0   \n",
       "16    rec-1004-org  rec-1191-dup-3        tayla     green      19310715.0   \n",
       "17    rec-1004-org    rec-1076-org        tayla     green      19310715.0   \n",
       "18    rec-1004-org    rec-1297-org        tayla     green      19310715.0   \n",
       "19    rec-1004-org    rec-1357-org        tayla     green      19310715.0   \n",
       "\n",
       "   given_name_2 surname_2 date_of_birth_2  \n",
       "0      mitchell     green      19560409.0  \n",
       "1      mitchell     green      19560409.0  \n",
       "2          loan     green      19440514.0  \n",
       "3      mitchell     green      19560409.0  \n",
       "4          loan     green      19440514.0  \n",
       "5         sarah     green      19140908.0  \n",
       "6      mitchell     green      19560409.0  \n",
       "7          loan     green      19440514.0  \n",
       "8         sarah     green      19140908.0  \n",
       "9        emiily     green      19380625.0  \n",
       "10     mitchell     green      19560409.0  \n",
       "11         loan     green      19440514.0  \n",
       "12        sarah     green      19140908.0  \n",
       "13       emiily     green      19380625.0  \n",
       "14       jordan     green             nan  \n",
       "15     mitchell     green      19560409.0  \n",
       "16         loan     green      19440514.0  \n",
       "17        sarah     green      19140908.0  \n",
       "18       emiily     green      19380625.0  \n",
       "19       jordan     green             nan  "
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indexer = rl.Index()\n",
    "indexer.add(Block(['surname']))\n",
    "candidate_record_pairs = indexer.index(df_a)\n",
    "print(\"Number of candidate record pairs :\", len(candidate_record_pairs))\n",
    "#candidate_record_pairs.to_frame(index=False)\n",
    "df_a_1 = df_a[['given_name','surname','date_of_birth']]\n",
    "\n",
    "df_c = candidate_record_pairs.to_frame(index=False)\n",
    "df_c.join(df_a_1,on=['rec_id_1']).join(df_a_1,on=['rec_id_2'], lsuffix='_1', rsuffix='_2').head(20)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Example 2) Only compare records with the same  metaphone of last name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of candidate record pairs : 45346\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>rec_id_1</th>\n",
       "      <th>rec_id_2</th>\n",
       "      <th>given_name_1</th>\n",
       "      <th>surname_1</th>\n",
       "      <th>date_of_birth_1</th>\n",
       "      <th>metaphone_surname_1</th>\n",
       "      <th>given_name_2</th>\n",
       "      <th>surname_2</th>\n",
       "      <th>date_of_birth_2</th>\n",
       "      <th>metaphone_surname_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rec-1191-dup-3</td>\n",
       "      <td>rec-1496-org</td>\n",
       "      <td>loan</td>\n",
       "      <td>green</td>\n",
       "      <td>19440514.0</td>\n",
       "      <td>KRN</td>\n",
       "      <td>mitchell</td>\n",
       "      <td>green</td>\n",
       "      <td>19560409.0</td>\n",
       "      <td>KRN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rec-1076-dup-1</td>\n",
       "      <td>rec-1496-org</td>\n",
       "      <td>sarah</td>\n",
       "      <td>greenw</td>\n",
       "      <td>19140908.0</td>\n",
       "      <td>KRN</td>\n",
       "      <td>mitchell</td>\n",
       "      <td>green</td>\n",
       "      <td>19560409.0</td>\n",
       "      <td>KRN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>rec-1076-dup-1</td>\n",
       "      <td>rec-1191-dup-3</td>\n",
       "      <td>sarah</td>\n",
       "      <td>greenw</td>\n",
       "      <td>19140908.0</td>\n",
       "      <td>KRN</td>\n",
       "      <td>loan</td>\n",
       "      <td>green</td>\n",
       "      <td>19440514.0</td>\n",
       "      <td>KRN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>rec-686-org</td>\n",
       "      <td>rec-1496-org</td>\n",
       "      <td>toby</td>\n",
       "      <td>garran</td>\n",
       "      <td>19520817.0</td>\n",
       "      <td>KRN</td>\n",
       "      <td>mitchell</td>\n",
       "      <td>green</td>\n",
       "      <td>19560409.0</td>\n",
       "      <td>KRN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>rec-686-org</td>\n",
       "      <td>rec-1191-dup-3</td>\n",
       "      <td>toby</td>\n",
       "      <td>garran</td>\n",
       "      <td>19520817.0</td>\n",
       "      <td>KRN</td>\n",
       "      <td>loan</td>\n",
       "      <td>green</td>\n",
       "      <td>19440514.0</td>\n",
       "      <td>KRN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         rec_id_1        rec_id_2 given_name_1 surname_1 date_of_birth_1  \\\n",
       "0  rec-1191-dup-3    rec-1496-org         loan     green      19440514.0   \n",
       "1  rec-1076-dup-1    rec-1496-org        sarah    greenw      19140908.0   \n",
       "2  rec-1076-dup-1  rec-1191-dup-3        sarah    greenw      19140908.0   \n",
       "3     rec-686-org    rec-1496-org         toby    garran      19520817.0   \n",
       "4     rec-686-org  rec-1191-dup-3         toby    garran      19520817.0   \n",
       "\n",
       "  metaphone_surname_1 given_name_2 surname_2 date_of_birth_2  \\\n",
       "0                 KRN     mitchell     green      19560409.0   \n",
       "1                 KRN     mitchell     green      19560409.0   \n",
       "2                 KRN         loan     green      19440514.0   \n",
       "3                 KRN     mitchell     green      19560409.0   \n",
       "4                 KRN         loan     green      19440514.0   \n",
       "\n",
       "  metaphone_surname_2  \n",
       "0                 KRN  \n",
       "1                 KRN  \n",
       "2                 KRN  \n",
       "3                 KRN  \n",
       "4                 KRN  "
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indexer = rl.Index()\n",
    "indexer.add(Block(['metaphone_surname']))\n",
    "candidate_record_pairs = indexer.index(df_a)\n",
    "print(\"Number of candidate record pairs :\", len(candidate_record_pairs))\n",
    "#candidate_record_pairs.to_frame(index=False)\n",
    "df_a_1 = df_a[['given_name','surname','date_of_birth','metaphone_surname']]\n",
    "\n",
    "df_c = candidate_record_pairs.to_frame(index=False)\n",
    "df_c.join(df_a_1,on=['rec_id_1']).join(df_a_1,on=['rec_id_2'], lsuffix='_1', rsuffix='_2').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As example you can decide to compare only patiend with the same : first name, last name and date of birth. This combination of fields is called a **  blocking key**. Using  a blocking key provide a reduce set of record pairs. In this notebook we use multiple blocking keys and consider the  ** union  ** of all the results set of candidate record pairs to evaluate for matching in the next steps.\n",
    "\n",
    "In other terms we compare patient with :\n",
    "1. (the same **'metaphone_given_name','metaphone_surname','date_of_birth'**) UNION \n",
    "2. (the same **'metaphone_surname','DayB'**) UNION \n",
    "3. (the same **'metaphone_surname','MonthB'**) UNION \n",
    "4. (the same **date_of_birth**) UNION \n",
    "5. (the same **'metaphone_given_name','YearB'**)\n",
    "\n",
    "\n",
    "Please note the use of the **metaphone** algorithm here instead of the exact value.This takes into account typrographic errors in the names and provide a wider range of candidiate record pairs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of candidate record pairs : 11126\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>rec_id_1</th>\n",
       "      <th>rec_id_2</th>\n",
       "      <th>given_name_1</th>\n",
       "      <th>surname_1</th>\n",
       "      <th>date_of_birth_1</th>\n",
       "      <th>metaphone_surname_1</th>\n",
       "      <th>metaphone_given_name_1</th>\n",
       "      <th>soc_sec_id_1</th>\n",
       "      <th>given_name_2</th>\n",
       "      <th>surname_2</th>\n",
       "      <th>date_of_birth_2</th>\n",
       "      <th>metaphone_surname_2</th>\n",
       "      <th>metaphone_given_name_2</th>\n",
       "      <th>soc_sec_id_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rec-0-org</td>\n",
       "      <td>rec-1050-dup-0</td>\n",
       "      <td>jinni</td>\n",
       "      <td>dreyer</td>\n",
       "      <td>19420127.0</td>\n",
       "      <td>TRYR</td>\n",
       "      <td>J500</td>\n",
       "      <td>3787407</td>\n",
       "      <td>jenna</td>\n",
       "      <td>pasalic</td>\n",
       "      <td>19541124.0</td>\n",
       "      <td>PSLK</td>\n",
       "      <td>J500</td>\n",
       "      <td>7730932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rec-0-org</td>\n",
       "      <td>rec-1050-dup-1</td>\n",
       "      <td>jinni</td>\n",
       "      <td>dreyer</td>\n",
       "      <td>19420127.0</td>\n",
       "      <td>TRYR</td>\n",
       "      <td>J500</td>\n",
       "      <td>3787407</td>\n",
       "      <td>jenna</td>\n",
       "      <td>paslic</td>\n",
       "      <td>19541124.0</td>\n",
       "      <td>PSLK</td>\n",
       "      <td>J500</td>\n",
       "      <td>7730932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>rec-0-org</td>\n",
       "      <td>rec-1050-org</td>\n",
       "      <td>jinni</td>\n",
       "      <td>dreyer</td>\n",
       "      <td>19420127.0</td>\n",
       "      <td>TRYR</td>\n",
       "      <td>J500</td>\n",
       "      <td>3787407</td>\n",
       "      <td>jenna</td>\n",
       "      <td>pasalic</td>\n",
       "      <td>19541124.0</td>\n",
       "      <td>PSLK</td>\n",
       "      <td>J500</td>\n",
       "      <td>7730932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>rec-0-org</td>\n",
       "      <td>rec-1540-dup-1</td>\n",
       "      <td>jinni</td>\n",
       "      <td>dreyer</td>\n",
       "      <td>19420127.0</td>\n",
       "      <td>TRYR</td>\n",
       "      <td>J500</td>\n",
       "      <td>3787407</td>\n",
       "      <td>john</td>\n",
       "      <td>benger</td>\n",
       "      <td>19710126.0</td>\n",
       "      <td>BNJR</td>\n",
       "      <td>J500</td>\n",
       "      <td>5651019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>rec-1-org</td>\n",
       "      <td>rec-1643-org</td>\n",
       "      <td>jonah</td>\n",
       "      <td>browne</td>\n",
       "      <td>19250413.0</td>\n",
       "      <td>BRN</td>\n",
       "      <td>J500</td>\n",
       "      <td>8328406</td>\n",
       "      <td>joanna</td>\n",
       "      <td>dooley</td>\n",
       "      <td>19930513.0</td>\n",
       "      <td>TL</td>\n",
       "      <td>J500</td>\n",
       "      <td>4843681</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    rec_id_1        rec_id_2 given_name_1 surname_1 date_of_birth_1  \\\n",
       "0  rec-0-org  rec-1050-dup-0        jinni    dreyer      19420127.0   \n",
       "1  rec-0-org  rec-1050-dup-1        jinni    dreyer      19420127.0   \n",
       "2  rec-0-org    rec-1050-org        jinni    dreyer      19420127.0   \n",
       "3  rec-0-org  rec-1540-dup-1        jinni    dreyer      19420127.0   \n",
       "4  rec-1-org    rec-1643-org        jonah    browne      19250413.0   \n",
       "\n",
       "  metaphone_surname_1 metaphone_given_name_1  soc_sec_id_1 given_name_2  \\\n",
       "0                TRYR                   J500       3787407        jenna   \n",
       "1                TRYR                   J500       3787407        jenna   \n",
       "2                TRYR                   J500       3787407        jenna   \n",
       "3                TRYR                   J500       3787407         john   \n",
       "4                 BRN                   J500       8328406       joanna   \n",
       "\n",
       "  surname_2 date_of_birth_2 metaphone_surname_2 metaphone_given_name_2  \\\n",
       "0   pasalic      19541124.0                PSLK                   J500   \n",
       "1    paslic      19541124.0                PSLK                   J500   \n",
       "2   pasalic      19541124.0                PSLK                   J500   \n",
       "3    benger      19710126.0                BNJR                   J500   \n",
       "4    dooley      19930513.0                  TL                   J500   \n",
       "\n",
       "   soc_sec_id_2  \n",
       "0       7730932  \n",
       "1       7730932  \n",
       "2       7730932  \n",
       "3       5651019  \n",
       "4       4843681  "
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indexer = rl.Index()\n",
    "\n",
    "# describe each step in the union\n",
    "\n",
    "# soundex firstname, methapone surname, exact date of birth\n",
    "indexer.add(Block(['metaphone_given_name','metaphone_surname','date_of_birth']))\n",
    "# soundex firstname , day of birth\n",
    "indexer.add(Block(['metaphone_given_name','DayB']))\n",
    "#soundex firstname , month of birth\n",
    "indexer.add(Block(['metaphone_given_name','MonthB']))\n",
    "# metaphone surname, year of birth \n",
    "indexer.add(Block(['metaphone_surname','YearB']))\n",
    "# ssn\n",
    "indexer.add(Block(['soc_sec_id']))\n",
    "\n",
    "\n",
    "candidate_record_pairs = indexer.index(df_a)\n",
    "print(\"Number of candidate record pairs :\", len(candidate_record_pairs))\n",
    "#candidate_record_pairs.to_frame(index=False)\n",
    "df_a_1 = df_a[['given_name','surname','date_of_birth','metaphone_surname','metaphone_given_name','soc_sec_id']]\n",
    "\n",
    "df_c = candidate_record_pairs.to_frame(index=False)\n",
    "df_c.join(df_a_1,on=['rec_id_1']).join(df_a_1,on=['rec_id_2'], lsuffix='_1', rsuffix='_2').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Comparison"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Identifying the similarity between records pairs to create a comparison vectors. \n",
    "\n",
    "The previous step provided us a list of record pairs. In this step we compare the corresponding fields of each record pair using string distance algorithm."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Comparison function** :\n",
    "A function that has input two atrtributes values (string,number, dates, time,complex objects) and calculate the similarity between these two values. The comparison can be exact or  allow for approximate similarity.An exact returns 0 or 1 and approximate returns a normalized value between zero and 1."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### String Comparison function or String distance algorithm\n",
    "\n",
    "  - Edit distance\n",
    "  - Jaccard distance\n",
    "  - SequenceMatcher\n",
    "  - Levenshtein\n",
    "  - Jaro\n",
    "  - Jaro-Winkler\n",
    "  - Jaccard similarity\n",
    "  - SoftTf-Idf\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "*****************jaro_distance***********************\n",
      "mayer vs meyer : 0.8666666666666667\n",
      "mayer vs myer : 0.9333333333333332\n",
      "mayer vs mayar : 0.8666666666666667\n",
      "mayer vs mayor : 0.8666666666666667\n",
      "mayer vs maier : 0.8666666666666667\n",
      "*****************jaro_winkler***********************\n",
      "mayer vs meyer : 0.88\n",
      "mayer vs myer : 0.94\n",
      "mayer vs mayar : 0.9066666666666667\n",
      "mayer vs mayor : 0.9066666666666667\n",
      "mayer vs maier : 0.8933333333333333\n",
      "*************levenshtein_distance ************************\n",
      "mayer vs meyer : 1\n",
      "mayer vs myer : 1\n",
      "mayer vs mayar : 1\n",
      "mayer vs mayor : 1\n",
      "mayer vs maier : 1\n"
     ]
    }
   ],
   "source": [
    "from jellyfish import jaro_winkler,levenshtein_distance,hamming_distance,jaro_distance\n",
    "\n",
    "print(\"*****************jaro_distance***********************\")\n",
    "print(\"mayer vs meyer :\",jaro_distance('mayer','meyer'))\n",
    "print(\"mayer vs myer :\",jaro_distance('mayer','myer'))\n",
    "print(\"mayer vs mayar :\",jaro_distance('mayer','mayar'))\n",
    "print(\"mayer vs mayor :\",jaro_distance('mayer','mayor'))\n",
    "print(\"mayer vs maier :\",jaro_distance('mayer','maier'))\n",
    "\n",
    "\n",
    "print(\"*****************jaro_winkler***********************\")\n",
    "print(\"mayer vs meyer :\",jaro_winkler('mayer','meyer'))\n",
    "print(\"mayer vs myer :\",jaro_winkler('mayer','myer'))\n",
    "print(\"mayer vs mayar :\",jaro_winkler('mayer','mayar'))\n",
    "print(\"mayer vs mayor :\",jaro_winkler('mayer','mayor'))\n",
    "print(\"mayer vs maier :\",jaro_winkler('mayer','maier'))\n",
    "\n",
    "\n",
    "print(\"*************levenshtein_distance ************************\")\n",
    "print(\"mayer vs meyer :\",levenshtein_distance('mayer','meyer'))\n",
    "print(\"mayer vs myer :\",levenshtein_distance('mayer','myer'))\n",
    "print(\"mayer vs mayar :\",levenshtein_distance('mayer','mayar'))\n",
    "print(\"mayer vs mayor :\",levenshtein_distance('mayer','mayor'))\n",
    "print(\"mayer vs maier :\",levenshtein_distance('mayer','maier'))\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Python Record Linkage Toolkit implemented algorithms are: **‘jaro’**,**’jarowinkler’**, **‘levenshtein’**, **‘damerau_levenshtein’**, **‘qgram’** or **‘cosine’**. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Complex aggreement pattern"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create a complex aggreement pattern,for each record pair we compare the corresponding fields using a string comparison or string distance function\n",
    " \n",
    "| Variable| comparison function |\n",
    "| --- | --- |\n",
    "| given_name | jarowinkler| \n",
    "| surname | jarowinkler |\n",
    "| date_of_birth | exact matching |\n",
    "| soc_sec_id | exact matching|\n",
    "| address_1 | levenshtein |\n",
    "| address_2 | levenshtein |\n",
    "| suburb | levenshtein |\n",
    "| postcode | exact matching |\n",
    "| state | exact matching |\n",
    "\n",
    "The output of the comparison is the **comparison vector** that will be used for classification."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of candidate record pairs : 11126\n"
     ]
    }
   ],
   "source": [
    "compare_cl = rl.Compare()\n",
    "compare_cl.string('given_name', 'given_name', method='jarowinkler', label='given_name')\n",
    "compare_cl.string('surname', 'surname', method='jarowinkler',label='surname')\n",
    "compare_cl.exact('date_of_birth', 'date_of_birth', label='date_of_birth')\n",
    "compare_cl.exact('soc_sec_id', 'soc_sec_id', label='soc_sec_id')\n",
    "compare_cl.string('address_1', 'address_1', method ='levenshtein' , label='address_1')\n",
    "compare_cl.string('address_2', 'address_2', method ='levenshtein' , label='address_2')\n",
    "compare_cl.string('suburb', 'suburb', method ='levenshtein', label='suburb')\n",
    "compare_cl.exact('postcode', 'postcode', label='postcode')\n",
    "compare_cl.exact('state', 'state', label='state')\n",
    "\n",
    "features = compare_cl.compute(candidate_record_pairs, df_a)\n",
    "features.head()\n",
    "print(\"Number of candidate record pairs :\", len(features))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Simple (Binary) aggreement pattern"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " To continue our example we will use a simple aggreement pattern.\n",
    " We will use a threshold for the string comparison function - if the **score >0.85** we say there's agreeement (1) if not there's disagreement (0).\n",
    " \n",
    "| Variable| comparison function | threshold |\n",
    "| --- | --- |---|\n",
    "| given_name | jarowinkler| 0.85 |\n",
    "| surname | jarowinkler | 0.85\n",
    "| date_of_birth | exact matching |\n",
    "| soc_sec_id | exact matching||\n",
    "| address_1 | levenshtein | 0.85|\n",
    "| address_2 | levenshtein | 0.85 |\n",
    "| suburb | levenshtein | 0.85 |\n",
    "| postcode | exact matching | |\n",
    "| state | exact matching | |\n",
    "\n",
    "The output of the comparison is the **comparison vector** that will be used for classification."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "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></th>\n",
       "      <th>given_name</th>\n",
       "      <th>surname</th>\n",
       "      <th>date_of_birth</th>\n",
       "      <th>soc_sec_id</th>\n",
       "      <th>address_1</th>\n",
       "      <th>address_2</th>\n",
       "      <th>suburb</th>\n",
       "      <th>postcode</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec_id_1</th>\n",
       "      <th>rec_id_2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">rec-0-org</th>\n",
       "      <th>rec-1050-dup-0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-1050-dup-1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-1050-org</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-1540-dup-1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-1-org</th>\n",
       "      <th>rec-1643-org</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          given_name  surname  date_of_birth  soc_sec_id  \\\n",
       "rec_id_1  rec_id_2                                                         \n",
       "rec-0-org rec-1050-dup-0         0.0      0.0              0           0   \n",
       "          rec-1050-dup-1         0.0      0.0              0           0   \n",
       "          rec-1050-org           0.0      0.0              0           0   \n",
       "          rec-1540-dup-1         0.0      0.0              0           0   \n",
       "rec-1-org rec-1643-org           0.0      0.0              0           0   \n",
       "\n",
       "                          address_1  address_2  suburb  postcode  state  \n",
       "rec_id_1  rec_id_2                                                       \n",
       "rec-0-org rec-1050-dup-0        0.0        0.0     0.0         0      0  \n",
       "          rec-1050-dup-1        0.0        0.0     0.0         0      0  \n",
       "          rec-1050-org          0.0        0.0     0.0         0      0  \n",
       "          rec-1540-dup-1        0.0        0.0     0.0         0      1  \n",
       "rec-1-org rec-1643-org          0.0        0.0     0.0         0      0  "
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "compare_cl = rl.Compare()\n",
    "compare_cl.string('given_name', 'given_name', method='jarowinkler', threshold = 0.85, label='given_name')\n",
    "compare_cl.string('surname', 'surname', method='jarowinkler',threshold = 0.85, label='surname')\n",
    "compare_cl.exact('date_of_birth', 'date_of_birth', label='date_of_birth')\n",
    "compare_cl.exact('soc_sec_id', 'soc_sec_id', label='soc_sec_id')\n",
    "compare_cl.string('address_1', 'address_1', method ='levenshtein' ,threshold = 0.85, label='address_1')\n",
    "compare_cl.string('address_2', 'address_2', method ='levenshtein' ,threshold = 0.85, label='address_2')\n",
    "compare_cl.string('suburb', 'suburb', method ='levenshtein' ,threshold = 0.85, label='suburb')\n",
    "compare_cl.exact('postcode', 'postcode', label='postcode')\n",
    "compare_cl.exact('state', 'state', label='state')\n",
    "\n",
    "features = compare_cl.compute(candidate_record_pairs, df_a)\n",
    "features.head()\n",
    "#print(\"Number of candidate record pairs :\", len(features))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Classification"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on comparison results, this step uses a classification algorithm to classify candidate records pairs in: matches, non-matches or potential matches.\n",
    "\n",
    "Probabilistic matching is based on a probability model that designates record pairs as matches, possible matches, or non-matches based on calculation of linkage scores and application of decision rules about these scores to define true matches. \n",
    "\n",
    "Options:\n",
    " - Simple Scoring\n",
    " - Rules\n",
    " - Probabilistic matching\n",
    " - Supervised learning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### An example of simple score-based or threshold-based matching"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The simplest way to classisfy  candidate records pairs into two classes matches and non-matches is to sum the similarity values in their comparison vector into a single total similarity value and apply a similarity **threshold**  to decide in which class a record pair belongs.\n",
    "\n",
    "This is what we did in the example below, adding a column **simsum**, we select a **threshold = 4**. All record pairs with s similarity sum greater or equal to 4 are considered as matches.\n",
    "\n",
    "Reference:\n",
    "* Christen, Peter. 2012. Data matching: concepts and techniques for record linkage, entity resolution, and duplicate detection. Springer Science & Business Media."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of match record pairs : 6287\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></th>\n",
       "      <th>given_name</th>\n",
       "      <th>surname</th>\n",
       "      <th>date_of_birth</th>\n",
       "      <th>soc_sec_id</th>\n",
       "      <th>address_1</th>\n",
       "      <th>address_2</th>\n",
       "      <th>suburb</th>\n",
       "      <th>postcode</th>\n",
       "      <th>state</th>\n",
       "      <th>simsum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec_id_1</th>\n",
       "      <th>rec_id_2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>rec-10-dup-0</th>\n",
       "      <th>rec-10-dup-2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">rec-10-dup-1</th>\n",
       "      <th>rec-10-dup-0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-10-dup-2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">rec-10-org</th>\n",
       "      <th>rec-10-dup-0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-10-dup-1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-10-dup-2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">rec-100-dup-0</th>\n",
       "      <th>rec-100-dup-1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-100-dup-3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-100-dup-4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-100-dup-1</th>\n",
       "      <th>rec-100-dup-4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">rec-100-dup-2</th>\n",
       "      <th>rec-100-dup-0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-100-dup-1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-100-dup-3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-100-dup-4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-100-org</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">rec-100-dup-3</th>\n",
       "      <th>rec-100-dup-1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-100-dup-4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">rec-100-org</th>\n",
       "      <th>rec-100-dup-0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-100-dup-1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-100-dup-3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             given_name  surname  date_of_birth  soc_sec_id  \\\n",
       "rec_id_1      rec_id_2                                                        \n",
       "rec-10-dup-0  rec-10-dup-2          0.0      0.0              1           1   \n",
       "rec-10-dup-1  rec-10-dup-0          0.0      0.0              1           1   \n",
       "              rec-10-dup-2          1.0      1.0              1           1   \n",
       "rec-10-org    rec-10-dup-0          0.0      0.0              1           1   \n",
       "              rec-10-dup-1          1.0      1.0              1           1   \n",
       "              rec-10-dup-2          1.0      1.0              1           1   \n",
       "rec-100-dup-0 rec-100-dup-1         1.0      1.0              1           1   \n",
       "              rec-100-dup-3         1.0      1.0              1           1   \n",
       "              rec-100-dup-4         1.0      1.0              0           0   \n",
       "rec-100-dup-1 rec-100-dup-4         1.0      1.0              0           0   \n",
       "rec-100-dup-2 rec-100-dup-0         1.0      1.0              0           1   \n",
       "              rec-100-dup-1         1.0      1.0              0           1   \n",
       "              rec-100-dup-3         1.0      1.0              0           1   \n",
       "              rec-100-dup-4         1.0      1.0              0           0   \n",
       "              rec-100-org           1.0      1.0              0           1   \n",
       "rec-100-dup-3 rec-100-dup-1         1.0      1.0              1           1   \n",
       "              rec-100-dup-4         1.0      1.0              0           0   \n",
       "rec-100-org   rec-100-dup-0         1.0      1.0              1           1   \n",
       "              rec-100-dup-1         1.0      1.0              1           1   \n",
       "              rec-100-dup-3         1.0      1.0              1           1   \n",
       "\n",
       "                             address_1  address_2  suburb  postcode  state  \\\n",
       "rec_id_1      rec_id_2                                                       \n",
       "rec-10-dup-0  rec-10-dup-2         0.0        1.0     1.0         0      1   \n",
       "rec-10-dup-1  rec-10-dup-0         0.0        1.0     1.0         0      1   \n",
       "              rec-10-dup-2         0.0        1.0     1.0         1      1   \n",
       "rec-10-org    rec-10-dup-0         1.0        1.0     1.0         0      1   \n",
       "              rec-10-dup-1         0.0        1.0     1.0         1      1   \n",
       "              rec-10-dup-2         0.0        1.0     1.0         1      1   \n",
       "rec-100-dup-0 rec-100-dup-1        0.0        1.0     1.0         1      1   \n",
       "              rec-100-dup-3        0.0        1.0     1.0         1      1   \n",
       "              rec-100-dup-4        0.0        0.0     1.0         1      1   \n",
       "rec-100-dup-1 rec-100-dup-4        0.0        0.0     1.0         1      1   \n",
       "rec-100-dup-2 rec-100-dup-0        0.0        1.0     1.0         1      1   \n",
       "              rec-100-dup-1        1.0        1.0     1.0         1      1   \n",
       "              rec-100-dup-3        1.0        1.0     1.0         1      1   \n",
       "              rec-100-dup-4        0.0        0.0     1.0         1      1   \n",
       "              rec-100-org          1.0        1.0     1.0         1      1   \n",
       "rec-100-dup-3 rec-100-dup-1        0.0        1.0     1.0         1      1   \n",
       "              rec-100-dup-4        0.0        0.0     1.0         1      1   \n",
       "rec-100-org   rec-100-dup-0        0.0        1.0     1.0         1      1   \n",
       "              rec-100-dup-1        1.0        1.0     1.0         1      1   \n",
       "              rec-100-dup-3        1.0        1.0     1.0         1      1   \n",
       "\n",
       "                             simsum  \n",
       "rec_id_1      rec_id_2               \n",
       "rec-10-dup-0  rec-10-dup-2      5.0  \n",
       "rec-10-dup-1  rec-10-dup-0      5.0  \n",
       "              rec-10-dup-2      8.0  \n",
       "rec-10-org    rec-10-dup-0      6.0  \n",
       "              rec-10-dup-1      8.0  \n",
       "              rec-10-dup-2      8.0  \n",
       "rec-100-dup-0 rec-100-dup-1     8.0  \n",
       "              rec-100-dup-3     8.0  \n",
       "              rec-100-dup-4     5.0  \n",
       "rec-100-dup-1 rec-100-dup-4     5.0  \n",
       "rec-100-dup-2 rec-100-dup-0     7.0  \n",
       "              rec-100-dup-1     8.0  \n",
       "              rec-100-dup-3     8.0  \n",
       "              rec-100-dup-4     5.0  \n",
       "              rec-100-org       8.0  \n",
       "rec-100-dup-3 rec-100-dup-1     8.0  \n",
       "              rec-100-dup-4     5.0  \n",
       "rec-100-org   rec-100-dup-0     8.0  \n",
       "              rec-100-dup-1     9.0  \n",
       "              rec-100-dup-3     9.0  "
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_f = features.copy()\n",
    "df_f['simsum'] = features.sum(axis=1)\n",
    "\n",
    "threshold = 4 \n",
    "\n",
    "#threshold or score based classification\n",
    "matches = df_f[df_f['simsum'] >=threshold]\n",
    "print(\"Number of match record pairs :\", len(matches))\n",
    "matches.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Evaluation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Comparing match results with the known ground truth or gold standard to mesaure the performance of the matching process.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Gold standard \n",
    "The main objective of evaluation techniques is to achieve **high matching quality** in order to assess  the quality of the matched  data for a certain project ground-truth data also known as gold standard is are required.\n",
    "\n",
    "There are several approches of how ground-thruth data can be generated. In this notebook the gold standard data was generated as part of the synthetic data used for matching."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "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></th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec_id_1</th>\n",
       "      <th>rec_id_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>rec-552-dup-1</th>\n",
       "      <th>rec-552-dup-3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">rec-552-dup-0</th>\n",
       "      <th>rec-552-dup-3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-552-dup-1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">rec-552-org</th>\n",
       "      <th>rec-552-dup-3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-552-dup-1</th>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: [(rec-552-dup-1, rec-552-dup-3), (rec-552-dup-0, rec-552-dup-3), (rec-552-dup-0, rec-552-dup-1), (rec-552-org, rec-552-dup-3), (rec-552-org, rec-552-dup-1)]"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# gold_ standard or known truth\n",
    "IMPORT_FILE_GOLD_STANDARD = './data/dataset_febrl3_true_links.csv'\n",
    "df_true_links = pd.read_csv(IMPORT_FILE_GOLD_STANDARD)\n",
    "df_true_links.set_index(['rec_id_1','rec_id_2'],inplace=True)\n",
    "df_true_links.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Confusion matrix"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assuming you have the ground-truth (i.e true match status of record pairs) and matching results from a project you can assigned each record pairs into one of the following categories :\n",
    "\n",
    "* **False Positives (FP)**: Also referred to as a Type I error, refers to a classification error by the matching algorithm where a record pair is marked as a match but in reality the two records refer to two distinct patients.\n",
    "\n",
    "\n",
    "* **True Positives (TP)**: Refers to the correct classification by the matching algorithm of two patient records as a match when both records refer to the same person.\n",
    "\n",
    "\n",
    "* **False Negatives (FN)**: Also referred to as a Type II error, refers to a classification error by the matching algorithm where the two patient records are marked as referring to two distinct patients but in reality the two records refer to the same person.\n",
    "\n",
    "\n",
    "* **True Negatives (TN)**: Refers to the correct classification by the matching algorithm of two patient records as a non-match when the two records refer to two different patients\n",
    "\n",
    "This is represented in the table below known as the **confusion matrix**:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![confusion matrix](./docs/confusion_matrix.png \"record linkage\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[6287,  251],\n",
       "       [   0, 4588]])"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matrix  = rl.confusion_matrix(df_true_links, matches, len(features))\n",
    "matrix"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Evaluation metrics\n",
    "They are commonly used to evaluate the performance of matching algorithms and configuration changes to those algorithms. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Precision** :\n",
    "A measure of the fraction of pairs that the matching algorithm classified accurately as matches. It is also called the positive predictor value and can be used along with the sensitivity to jointly evaluate the performance of a matching algorithm.\n",
    "\n",
    "\\begin{align*}\n",
    "  Precision &= \\frac{TP}{TP+FP}\\\\\n",
    "\\end{align*}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rl.precision(df_true_links, matches)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Recall** :\n",
    "Also referred to as **sensistivity**, is a measure of the capability of the matching algorithm to correctly classify two records that refer to the same patient as true matches. It is calculated as the ratio of the number of true positives divided by the sum of true positives and false negatives.\n",
    "\n",
    "\\begin{align*}\n",
    "  Recall &= \\frac{TP}{TP+FN}\\\\\n",
    "\\end{align*}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9616090547568064"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rl.recall(df_true_links, matches)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**F-Score** :\n",
    "Represents the harmonic mean of the Precision, and Recall, which is not influenced by the large number of true non matches.\n",
    "\n",
    "\\begin{align*}\n",
    "  Fscore &= \\frac{2 x Precision x Recall}{Precision+Recall}\\\\\n",
    "\\end{align*}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9804288499025341"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The F-score for this classification is\n",
    "rl.fscore(df_true_links, matches)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## References"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* de Bruin, Jonathan. 2017. “Record Linkage. Python library. Version 0.8.1.” https://recordlinkage.readthedocs.io/.\n",
    "\n",
    "* Herzog, Thomas N, Fritz J Scheuren and William E Winkler. 2007. Data quality and record linkage techniques. Vol. 1 Springer.\n",
    "\n",
    "* Fellegi, Ivan P and Alan B Sunter. 1969. “A theory for record linkage.” Journal of the American Statistical Association 64(328):1183–1210.\n",
    "\n",
    "* Christen, Peter. 2012. Data matching: concepts and techniques for record linkage, entity resolution, and duplicate detection. Springer Science & Business Media."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}