docs/guides/link_two_dataframes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "12cf642a",
   "metadata": {},
   "source": [
    "Link two datasets\n",
    "=================\n",
    "\n",
    "Introduction\n",
    "------------\n",
    "\n",
    "This example shows how two datasets with data about persons can be\n",
    "linked. We will try to link the data based on attributes like first\n",
    "name, surname, sex, date of birth, place and address. The data used in\n",
    "this example is part of\n",
    "[Febrl](https://sourceforge.net/projects/febrl/) and is fictitious.\n",
    "\n",
    "First, start with importing the ``recordlinkage`` module. The submodule\n",
    "``recordlinkage.datasets`` contains several datasets that can be used\n",
    "for testing. For this example, we use the Febrl datasets 4A and 4B.\n",
    "These datasets can be loaded with the function ``load_febrl4``."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "aa80dc6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import recordlinkage\n",
    "from recordlinkage.datasets import load_febrl4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b01fab49",
   "metadata": {},
   "source": [
    "The datasets are loaded with the following code. The returned datasets\n",
    "are of type ``pandas.DataFrame``. This makes it easy to manipulate the\n",
    "data if desired. For details about data manipulation with ``pandas``,\n",
    "see their comprehensive documentation http://pandas.pydata.org/."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a531c1da",
   "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>street_number</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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>rec-1070-org</th>\n",
       "      <td>michaela</td>\n",
       "      <td>neumann</td>\n",
       "      <td>8</td>\n",
       "      <td>stanley street</td>\n",
       "      <td>miami</td>\n",
       "      <td>winston hills</td>\n",
       "      <td>4223</td>\n",
       "      <td>nsw</td>\n",
       "      <td>19151111</td>\n",
       "      <td>5304218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-1016-org</th>\n",
       "      <td>courtney</td>\n",
       "      <td>painter</td>\n",
       "      <td>12</td>\n",
       "      <td>pinkerton circuit</td>\n",
       "      <td>bega flats</td>\n",
       "      <td>richlands</td>\n",
       "      <td>4560</td>\n",
       "      <td>vic</td>\n",
       "      <td>19161214</td>\n",
       "      <td>4066625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4405-org</th>\n",
       "      <td>charles</td>\n",
       "      <td>green</td>\n",
       "      <td>38</td>\n",
       "      <td>salkauskas crescent</td>\n",
       "      <td>kela</td>\n",
       "      <td>dapto</td>\n",
       "      <td>4566</td>\n",
       "      <td>nsw</td>\n",
       "      <td>19480930</td>\n",
       "      <td>4365168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-1288-org</th>\n",
       "      <td>vanessa</td>\n",
       "      <td>parr</td>\n",
       "      <td>905</td>\n",
       "      <td>macquoid place</td>\n",
       "      <td>broadbridge manor</td>\n",
       "      <td>south grafton</td>\n",
       "      <td>2135</td>\n",
       "      <td>sa</td>\n",
       "      <td>19951119</td>\n",
       "      <td>9239102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-3585-org</th>\n",
       "      <td>mikayla</td>\n",
       "      <td>malloney</td>\n",
       "      <td>37</td>\n",
       "      <td>randwick road</td>\n",
       "      <td>avalind</td>\n",
       "      <td>hoppers crossing</td>\n",
       "      <td>4552</td>\n",
       "      <td>vic</td>\n",
       "      <td>19860208</td>\n",
       "      <td>7207688</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-2153-org</th>\n",
       "      <td>annabel</td>\n",
       "      <td>grierson</td>\n",
       "      <td>97</td>\n",
       "      <td>mclachlan crescent</td>\n",
       "      <td>lantana lodge</td>\n",
       "      <td>broome</td>\n",
       "      <td>2480</td>\n",
       "      <td>nsw</td>\n",
       "      <td>19840224</td>\n",
       "      <td>7676186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-1604-org</th>\n",
       "      <td>sienna</td>\n",
       "      <td>musolino</td>\n",
       "      <td>22</td>\n",
       "      <td>smeaton circuit</td>\n",
       "      <td>pangani</td>\n",
       "      <td>mckinnon</td>\n",
       "      <td>2700</td>\n",
       "      <td>nsw</td>\n",
       "      <td>19890525</td>\n",
       "      <td>4971506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-1003-org</th>\n",
       "      <td>bradley</td>\n",
       "      <td>matthews</td>\n",
       "      <td>2</td>\n",
       "      <td>jondol place</td>\n",
       "      <td>horseshoe ck</td>\n",
       "      <td>jacobs well</td>\n",
       "      <td>7018</td>\n",
       "      <td>sa</td>\n",
       "      <td>19481122</td>\n",
       "      <td>8927667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4883-org</th>\n",
       "      <td>brodee</td>\n",
       "      <td>egan</td>\n",
       "      <td>88</td>\n",
       "      <td>axon street</td>\n",
       "      <td>greenslopes</td>\n",
       "      <td>wamberal</td>\n",
       "      <td>2067</td>\n",
       "      <td>qld</td>\n",
       "      <td>19121113</td>\n",
       "      <td>6039042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-66-org</th>\n",
       "      <td>koula</td>\n",
       "      <td>houweling</td>\n",
       "      <td>3</td>\n",
       "      <td>mileham street</td>\n",
       "      <td>old airdmillan road</td>\n",
       "      <td>williamstown</td>\n",
       "      <td>2350</td>\n",
       "      <td>nsw</td>\n",
       "      <td>19440718</td>\n",
       "      <td>6375537</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5000 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             given_name    surname street_number            address_1  \\\n",
       "rec_id                                                                  \n",
       "rec-1070-org   michaela    neumann             8       stanley street   \n",
       "rec-1016-org   courtney    painter            12    pinkerton circuit   \n",
       "rec-4405-org    charles      green            38  salkauskas crescent   \n",
       "rec-1288-org    vanessa       parr           905       macquoid place   \n",
       "rec-3585-org    mikayla   malloney            37        randwick road   \n",
       "...                 ...        ...           ...                  ...   \n",
       "rec-2153-org    annabel   grierson            97   mclachlan crescent   \n",
       "rec-1604-org     sienna   musolino            22      smeaton circuit   \n",
       "rec-1003-org    bradley   matthews             2         jondol place   \n",
       "rec-4883-org     brodee       egan            88          axon street   \n",
       "rec-66-org        koula  houweling             3       mileham street   \n",
       "\n",
       "                        address_2            suburb postcode state  \\\n",
       "rec_id                                                               \n",
       "rec-1070-org                miami     winston hills     4223   nsw   \n",
       "rec-1016-org           bega flats         richlands     4560   vic   \n",
       "rec-4405-org                 kela             dapto     4566   nsw   \n",
       "rec-1288-org    broadbridge manor     south grafton     2135    sa   \n",
       "rec-3585-org              avalind  hoppers crossing     4552   vic   \n",
       "...                           ...               ...      ...   ...   \n",
       "rec-2153-org        lantana lodge            broome     2480   nsw   \n",
       "rec-1604-org              pangani          mckinnon     2700   nsw   \n",
       "rec-1003-org         horseshoe ck       jacobs well     7018    sa   \n",
       "rec-4883-org          greenslopes          wamberal     2067   qld   \n",
       "rec-66-org    old airdmillan road      williamstown     2350   nsw   \n",
       "\n",
       "             date_of_birth soc_sec_id  \n",
       "rec_id                                 \n",
       "rec-1070-org      19151111    5304218  \n",
       "rec-1016-org      19161214    4066625  \n",
       "rec-4405-org      19480930    4365168  \n",
       "rec-1288-org      19951119    9239102  \n",
       "rec-3585-org      19860208    7207688  \n",
       "...                    ...        ...  \n",
       "rec-2153-org      19840224    7676186  \n",
       "rec-1604-org      19890525    4971506  \n",
       "rec-1003-org      19481122    8927667  \n",
       "rec-4883-org      19121113    6039042  \n",
       "rec-66-org        19440718    6375537  \n",
       "\n",
       "[5000 rows x 10 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfA, dfB = load_febrl4()\n",
    "dfA"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4169cd03",
   "metadata": {},
   "source": [
    "Make record pairs\n",
    "-----------------\n",
    "\n",
    "It is very intuitive to compare each record in DataFrame ``dfA`` with\n",
    "all records of DataFrame ``dfB``. In fact, we want to make record pairs.\n",
    "Each record pair should contain one record of ``dfA`` and one record of\n",
    "``dfB``. This process of making record pairs is also called \"indexing\".\n",
    "With the ``recordlinkage`` module, indexing is easy. First, load the\n",
    "``index.Index`` class and call the ``.full`` method. This object\n",
    "generates a full index on a ``.index(...)`` call. In case of\n",
    "deduplication of a single dataframe, one dataframe is sufficient as\n",
    "argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cc462dcd",
   "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"
     ]
    }
   ],
   "source": [
    "indexer = recordlinkage.Index()\n",
    "indexer.full()\n",
    "pairs = indexer.index(dfA, dfB)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c497e7df",
   "metadata": {},
   "source": [
    "With the method ``index``, all possible (and unique) record pairs are\n",
    "made. The method returns a ``pandas.MultiIndex``. The number of pairs is\n",
    "equal to the number of records in ``dfA`` times the number of records in\n",
    "``dfB``."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "eacecfa4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5000 5000 25000000\n"
     ]
    }
   ],
   "source": [
    "print (len(dfA), len(dfB), len(pairs))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cde2a308",
   "metadata": {},
   "source": [
    "Many of these record pairs do not belong to the same person. In case of\n",
    "one-to-one matching, the number of matches should be no more than the\n",
    "number of records in the smallest dataframe. In case of full indexing,\n",
    "``min(len(dfA), len(N_dfB))`` is much smaller than ``len(pairs)``. The\n",
    "``recordlinkage`` module has some more advanced indexing methods to\n",
    "reduce the number of record pairs. Obvious non-matches are left out of\n",
    "the index. Note that if a matching record pair is not included in the\n",
    "index, it can not be matched anymore.\n",
    "\n",
    "One of the most well known indexing methods is named *blocking*. This\n",
    "method includes only record pairs that are identical on one or more\n",
    "stored attributes of the person (or entity in general). The blocking\n",
    "method can be used in the ``recordlinkage`` module."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f5a0bccd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "77249"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indexer = recordlinkage.Index()\n",
    "indexer.block(\"given_name\")\n",
    "candidate_links = indexer.index(dfA, dfB)\n",
    "len(candidate_links)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07ed1531",
   "metadata": {},
   "source": [
    "The argument \"given\\_name\" is the blocking variable. This variable has\n",
    "to be the name of a column in ``dfA`` and ``dfB``. It is possible to\n",
    "parse a list of columns names to block on multiple variables. Blocking\n",
    "on multiple variables will reduce the number of record pairs even\n",
    "further.\n",
    "\n",
    "Another implemented indexing method is *Sorted Neighbourhood Indexing*\n",
    "(``recordlinkage.index.SortedNeighbourhood``). This method is very\n",
    "useful when there are many misspellings in the string were used for\n",
    "indexing. In fact, sorted neighbourhood indexing is a generalisation of\n",
    "blocking. See the documentation for details about sorted neighbourd\n",
    "indexing."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40d7089c",
   "metadata": {},
   "source": [
    "Compare records\n",
    "---------------\n",
    "\n",
    "Each record pair is a candidate match. To classify the candidate record\n",
    "pairs into matches and non-matches, compare the records on all\n",
    "attributes both records have in common. The ``recordlinkage`` module has\n",
    "a class named ``Compare``. This class is used to compare the records.\n",
    "The following code shows how to compare attributes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f5df923b",
   "metadata": {},
   "outputs": [],
   "source": [
    "compare_cl = recordlinkage.Compare()\n",
    "compare_cl.exact(\"given_name\", \"given_name\", 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(\"suburb\", \"suburb\", label=\"suburb\")\n",
    "compare_cl.exact(\"state\", \"state\", label=\"state\")\n",
    "compare_cl.string(\"address_1\", \"address_1\", threshold=0.85, label=\"address_1\")\n",
    "features = compare_cl.compute(candidate_links, dfA, dfB)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a5e0cff",
   "metadata": {},
   "source": [
    "The comparing of record pairs starts when the ``compute`` method is\n",
    "called. All attribute comparisons are stored in a DataFrame with\n",
    "horizontally the features and vertically the record pairs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c9048853",
   "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>suburb</th>\n",
       "      <th>state</th>\n",
       "      <th>address_1</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">rec-1070-org</th>\n",
       "      <th>rec-3024-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-2371-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4652-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4795-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-1314-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4528-org</th>\n",
       "      <th>rec-4528-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4887-org</th>\n",
       "      <th>rec-4887-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4350-org</th>\n",
       "      <th>rec-4350-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4569-org</th>\n",
       "      <th>rec-4569-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-3125-org</th>\n",
       "      <th>rec-3125-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>77249 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             given_name  surname  date_of_birth  suburb  \\\n",
       "rec_id_1     rec_id_2                                                     \n",
       "rec-1070-org rec-3024-dup-0           1      0.0              0       0   \n",
       "             rec-2371-dup-0           1      0.0              0       0   \n",
       "             rec-4652-dup-0           1      0.0              0       0   \n",
       "             rec-4795-dup-0           1      0.0              0       0   \n",
       "             rec-1314-dup-0           1      0.0              0       0   \n",
       "...                                 ...      ...            ...     ...   \n",
       "rec-4528-org rec-4528-dup-0           1      1.0              1       1   \n",
       "rec-4887-org rec-4887-dup-0           1      1.0              1       0   \n",
       "rec-4350-org rec-4350-dup-0           1      1.0              1       1   \n",
       "rec-4569-org rec-4569-dup-0           1      1.0              1       1   \n",
       "rec-3125-org rec-3125-dup-0           1      1.0              1       0   \n",
       "\n",
       "                             state  address_1  \n",
       "rec_id_1     rec_id_2                          \n",
       "rec-1070-org rec-3024-dup-0      1        0.0  \n",
       "             rec-2371-dup-0      0        0.0  \n",
       "             rec-4652-dup-0      0        0.0  \n",
       "             rec-4795-dup-0      1        0.0  \n",
       "             rec-1314-dup-0      1        0.0  \n",
       "...                            ...        ...  \n",
       "rec-4528-org rec-4528-dup-0      1        1.0  \n",
       "rec-4887-org rec-4887-dup-0      1        1.0  \n",
       "rec-4350-org rec-4350-dup-0      1        1.0  \n",
       "rec-4569-org rec-4569-dup-0      1        0.0  \n",
       "rec-3125-org rec-3125-dup-0      1        1.0  \n",
       "\n",
       "[77249 rows x 6 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b01cba37",
   "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>date_of_birth</th>\n",
       "      <th>suburb</th>\n",
       "      <th>state</th>\n",
       "      <th>address_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>77249.0</td>\n",
       "      <td>77249.000000</td>\n",
       "      <td>77249.000000</td>\n",
       "      <td>77249.000000</td>\n",
       "      <td>77249.000000</td>\n",
       "      <td>77249.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.044428</td>\n",
       "      <td>0.037929</td>\n",
       "      <td>0.032259</td>\n",
       "      <td>0.248767</td>\n",
       "      <td>0.036700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.206045</td>\n",
       "      <td>0.191027</td>\n",
       "      <td>0.176689</td>\n",
       "      <td>0.432301</td>\n",
       "      <td>0.188024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       given_name       surname  date_of_birth        suburb         state  \\\n",
       "count     77249.0  77249.000000   77249.000000  77249.000000  77249.000000   \n",
       "mean          1.0      0.044428       0.037929      0.032259      0.248767   \n",
       "std           0.0      0.206045       0.191027      0.176689      0.432301   \n",
       "min           1.0      0.000000       0.000000      0.000000      0.000000   \n",
       "25%           1.0      0.000000       0.000000      0.000000      0.000000   \n",
       "50%           1.0      0.000000       0.000000      0.000000      0.000000   \n",
       "75%           1.0      0.000000       0.000000      0.000000      0.000000   \n",
       "max           1.0      1.000000       1.000000      1.000000      1.000000   \n",
       "\n",
       "          address_1  \n",
       "count  77249.000000  \n",
       "mean       0.036700  \n",
       "std        0.188024  \n",
       "min        0.000000  \n",
       "25%        0.000000  \n",
       "50%        0.000000  \n",
       "75%        0.000000  \n",
       "max        1.000000  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4f0d758",
   "metadata": {},
   "source": [
    "The last step is to decide which records belong to the same person. In\n",
    "this example, we keep it simple:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "866388f3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6.0     1566\n",
       "5.0     1332\n",
       "4.0      343\n",
       "3.0      146\n",
       "2.0    16427\n",
       "1.0    57435\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features.sum(axis=1).value_counts().sort_index(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8d1d8027",
   "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>suburb</th>\n",
       "      <th>state</th>\n",
       "      <th>address_1</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>rec-2371-org</th>\n",
       "      <th>rec-2371-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-3024-org</th>\n",
       "      <th>rec-3024-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4652-org</th>\n",
       "      <th>rec-4652-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4795-org</th>\n",
       "      <th>rec-4795-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-1016-org</th>\n",
       "      <th>rec-1016-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4528-org</th>\n",
       "      <th>rec-4528-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4887-org</th>\n",
       "      <th>rec-4887-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4350-org</th>\n",
       "      <th>rec-4350-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-4569-org</th>\n",
       "      <th>rec-4569-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rec-3125-org</th>\n",
       "      <th>rec-3125-dup-0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3241 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             given_name  surname  date_of_birth  suburb  \\\n",
       "rec_id_1     rec_id_2                                                     \n",
       "rec-2371-org rec-2371-dup-0           1      1.0              1       1   \n",
       "rec-3024-org rec-3024-dup-0           1      1.0              1       0   \n",
       "rec-4652-org rec-4652-dup-0           1      1.0              1       0   \n",
       "rec-4795-org rec-4795-dup-0           1      1.0              1       1   \n",
       "rec-1016-org rec-1016-dup-0           1      1.0              1       1   \n",
       "...                                 ...      ...            ...     ...   \n",
       "rec-4528-org rec-4528-dup-0           1      1.0              1       1   \n",
       "rec-4887-org rec-4887-dup-0           1      1.0              1       0   \n",
       "rec-4350-org rec-4350-dup-0           1      1.0              1       1   \n",
       "rec-4569-org rec-4569-dup-0           1      1.0              1       1   \n",
       "rec-3125-org rec-3125-dup-0           1      1.0              1       0   \n",
       "\n",
       "                             state  address_1  \n",
       "rec_id_1     rec_id_2                          \n",
       "rec-2371-org rec-2371-dup-0      1        1.0  \n",
       "rec-3024-org rec-3024-dup-0      1        0.0  \n",
       "rec-4652-org rec-4652-dup-0      1        1.0  \n",
       "rec-4795-org rec-4795-dup-0      1        1.0  \n",
       "rec-1016-org rec-1016-dup-0      0        1.0  \n",
       "...                            ...        ...  \n",
       "rec-4528-org rec-4528-dup-0      1        1.0  \n",
       "rec-4887-org rec-4887-dup-0      1        1.0  \n",
       "rec-4350-org rec-4350-dup-0      1        1.0  \n",
       "rec-4569-org rec-4569-dup-0      1        0.0  \n",
       "rec-3125-org rec-3125-dup-0      1        1.0  \n",
       "\n",
       "[3241 rows x 6 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features[features.sum(axis=1) > 3]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe7e75f1",
   "metadata": {},
   "source": [
    "Full code\n",
    "---------"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "24a74dfe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3241\n"
     ]
    }
   ],
   "source": [
    "import recordlinkage\n",
    "from recordlinkage.datasets import load_febrl4\n",
    "\n",
    "dfA, dfB = load_febrl4()\n",
    "\n",
    "# Indexation step\n",
    "indexer = recordlinkage.Index()\n",
    "indexer.block(\"given_name\")\n",
    "candidate_links = indexer.index(dfA, dfB)\n",
    "\n",
    "# Comparison step\n",
    "compare_cl = recordlinkage.Compare()\n",
    "\n",
    "compare_cl.exact(\"given_name\", \"given_name\", 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(\"suburb\", \"suburb\", label=\"suburb\")\n",
    "compare_cl.exact(\"state\", \"state\", label=\"state\")\n",
    "compare_cl.string(\"address_1\", \"address_1\", threshold=0.85, label=\"address_1\")\n",
    "\n",
    "features = compare_cl.compute(candidate_links, dfA, dfB)\n",
    "\n",
    "# Classification step\n",
    "matches = features[features.sum(axis=1) > 3]\n",
    "print(len(matches))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}