model_create_h50k.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from splink.datasets import splink_datasets\n",
    "from splink.duckdb.linker import DuckDBLinker\n",
    "from splink.duckdb.blocking_rule_library import block_on\n",
    "import splink.duckdb.comparison_template_library as ctl\n",
    "import splink.duckdb.comparison_library as cl\n",
    "\n",
    "df = splink_datasets.historical_50k"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Probability two random records match is estimated to be  0.000136.\n",
      "This means that amongst all possible pairwise record comparisons, one in 7,362.31 are expected to match.  With 1,279,041,753 total possible comparisons, we expect a total of around 173,728.33 matching pairs\n",
      "----- Estimating u probabilities using random sampling -----\n",
      "\n",
      "Estimated u probabilities using random sampling\n",
      "\n",
      "Your model is not yet fully trained. Missing estimates for:\n",
      "    - first_name (no m values are trained).\n",
      "    - surname (no m values are trained).\n",
      "    - dob (no m values are trained).\n",
      "    - postcode_fake (no m values are trained).\n",
      "    - birth_place (no m values are trained).\n",
      "    - occupation (no m values are trained).\n",
      "\n",
      "----- Starting EM training session -----\n",
      "\n",
      "Estimating the m probabilities of the model by blocking on:\n",
      "(l.\"first_name\" = r.\"first_name\") AND (l.\"surname\" = r.\"surname\")\n",
      "\n",
      "Parameter estimates will be made for the following comparison(s):\n",
      "    - dob\n",
      "    - postcode_fake\n",
      "    - birth_place\n",
      "    - occupation\n",
      "\n",
      "Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules: \n",
      "    - first_name\n",
      "    - surname\n",
      "\n",
      "Iteration 1: Largest change in params was -0.519 in probability_two_random_records_match\n",
      "Iteration 2: Largest change in params was -0.0429 in the m_probability of birth_place, level `All other comparisons`\n",
      "Iteration 3: Largest change in params was -0.0179 in the m_probability of birth_place, level `All other comparisons`\n",
      "Iteration 4: Largest change in params was 0.00699 in the m_probability of birth_place, level `Exact match`\n",
      "\n",
      "EM converged after 4 iterations\n",
      "\n",
      "Your model is not yet fully trained. Missing estimates for:\n",
      "    - first_name (no m values are trained).\n",
      "    - surname (no m values are trained).\n",
      "\n",
      "----- Starting EM training session -----\n",
      "\n",
      "Estimating the m probabilities of the model by blocking on:\n",
      "l.\"dob\" = r.\"dob\"\n",
      "\n",
      "Parameter estimates will be made for the following comparison(s):\n",
      "    - first_name\n",
      "    - surname\n",
      "    - postcode_fake\n",
      "    - birth_place\n",
      "    - occupation\n",
      "\n",
      "Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules: \n",
      "    - dob\n",
      "\n",
      "Iteration 1: Largest change in params was -0.363 in the m_probability of first_name, level `Exact match first_name`\n",
      "Iteration 2: Largest change in params was 0.0428 in the m_probability of first_name, level `All other comparisons`\n",
      "Iteration 3: Largest change in params was 0.00816 in the m_probability of surname, level `All other comparisons`\n",
      "\n",
      "EM converged after 3 iterations\n",
      "\n",
      "Your model is fully trained. All comparisons have at least one estimate for their m and u values\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'link_type': 'dedupe_only',\n",
       " 'blocking_rules_to_generate_predictions': [{'blocking_rule': '(l.\"first_name\" = r.\"first_name\") AND (l.\"surname\" = r.\"surname\")',\n",
       "   'sql_dialect': 'duckdb'},\n",
       "  {'blocking_rule': '(l.\"surname\" = r.\"surname\") AND (l.\"dob\" = r.\"dob\")',\n",
       "   'sql_dialect': 'duckdb'},\n",
       "  {'blocking_rule': '(l.\"first_name\" = r.\"first_name\") AND (l.\"dob\" = r.\"dob\")',\n",
       "   'sql_dialect': 'duckdb'},\n",
       "  {'blocking_rule': '(l.\"postcode_fake\" = r.\"postcode_fake\") AND (l.\"first_name\" = r.\"first_name\")',\n",
       "   'sql_dialect': 'duckdb'}],\n",
       " 'comparisons': [{'output_column_name': 'first_name',\n",
       "   'comparison_levels': [{'sql_condition': '\"first_name_l\" IS NULL OR \"first_name_r\" IS NULL',\n",
       "     'label_for_charts': 'Null',\n",
       "     'is_null_level': True},\n",
       "    {'sql_condition': '\"first_name_l\" = \"first_name_r\"',\n",
       "     'label_for_charts': 'Exact match first_name',\n",
       "     'm_probability': 0.5441309704828743,\n",
       "     'u_probability': 0.012224508462954514,\n",
       "     'tf_adjustment_column': 'first_name',\n",
       "     'tf_adjustment_weight': 1.0},\n",
       "    {'sql_condition': 'damerau_levenshtein(\"first_name_l\", \"first_name_r\") <= 1',\n",
       "     'label_for_charts': 'Damerau_levenshtein <= 1',\n",
       "     'm_probability': 0.08597202475282924,\n",
       "     'u_probability': 0.002895025362570828},\n",
       "    {'sql_condition': 'jaro_winkler_similarity(\"first_name_l\", \"first_name_r\") >= 0.9',\n",
       "     'label_for_charts': 'Jaro_winkler_similarity >= 0.9',\n",
       "     'm_probability': 0.036910574203147924,\n",
       "     'u_probability': 0.002137531421674203},\n",
       "    {'sql_condition': 'jaro_winkler_similarity(\"first_name_l\", \"first_name_r\") >= 0.8',\n",
       "     'label_for_charts': 'Jaro_winkler_similarity >= 0.8',\n",
       "     'm_probability': 0.08947871678465857,\n",
       "     'u_probability': 0.004985060274390353},\n",
       "    {'sql_condition': 'ELSE',\n",
       "     'label_for_charts': 'All other comparisons',\n",
       "     'm_probability': 0.24350771377649003,\n",
       "     'u_probability': 0.9777578744784101}],\n",
       "   'comparison_description': 'Exact match vs. First_Name within levenshtein threshold 1 vs. First_Name within damerau-levenshtein threshold 1 vs. First_Name within jaro_winkler thresholds 0.9, 0.8 vs. anything else'},\n",
       "  {'output_column_name': 'surname',\n",
       "   'comparison_levels': [{'sql_condition': '\"surname_l\" IS NULL OR \"surname_r\" IS NULL',\n",
       "     'label_for_charts': 'Null',\n",
       "     'is_null_level': True},\n",
       "    {'sql_condition': '\"surname_l\" = \"surname_r\"',\n",
       "     'label_for_charts': 'Exact match surname',\n",
       "     'm_probability': 0.7702354573591275,\n",
       "     'u_probability': 0.000650928449422447,\n",
       "     'tf_adjustment_column': 'surname',\n",
       "     'tf_adjustment_weight': 1.0},\n",
       "    {'sql_condition': 'damerau_levenshtein(\"surname_l\", \"surname_r\") <= 1',\n",
       "     'label_for_charts': 'Damerau_levenshtein <= 1',\n",
       "     'm_probability': 0.12636442095622685,\n",
       "     'u_probability': 0.0003716318004094365},\n",
       "    {'sql_condition': 'jaro_winkler_similarity(\"surname_l\", \"surname_r\") >= 0.9',\n",
       "     'label_for_charts': 'Jaro_winkler_similarity >= 0.9',\n",
       "     'm_probability': 0.010494933613633142,\n",
       "     'u_probability': 0.0002797548879777571},\n",
       "    {'sql_condition': 'jaro_winkler_similarity(\"surname_l\", \"surname_r\") >= 0.8',\n",
       "     'label_for_charts': 'Jaro_winkler_similarity >= 0.8',\n",
       "     'm_probability': 0.014385423969344663,\n",
       "     'u_probability': 0.0030090261620080946},\n",
       "    {'sql_condition': 'ELSE',\n",
       "     'label_for_charts': 'All other comparisons',\n",
       "     'm_probability': 0.07851976410166804,\n",
       "     'u_probability': 0.9956886587001823}],\n",
       "   'comparison_description': 'Exact match vs. Surname within levenshtein threshold 1 vs. Surname within damerau-levenshtein threshold 1 vs. Surname within jaro_winkler thresholds 0.9, 0.8 vs. anything else'},\n",
       "  {'output_column_name': 'dob',\n",
       "   'comparison_levels': [{'sql_condition': '\\n        try_strptime(\"dob_l\", \\'%Y-%m-%d\\')\\n     IS NULL OR \\n        try_strptime(\"dob_r\", \\'%Y-%m-%d\\')\\n     IS NULL',\n",
       "     'label_for_charts': 'Null',\n",
       "     'is_null_level': True},\n",
       "    {'sql_condition': '\"dob_l\" = \"dob_r\"',\n",
       "     'label_for_charts': 'Exact match',\n",
       "     'm_probability': 0.6879913183078774,\n",
       "     'u_probability': 0.0024273489114530217},\n",
       "    {'sql_condition': 'damerau_levenshtein(\"dob_l\", \"dob_r\") <= 1',\n",
       "     'label_for_charts': 'Damerau_levenshtein <= 1',\n",
       "     'm_probability': 0.27639408965983203,\n",
       "     'u_probability': 0.025766860284662796},\n",
       "    {'sql_condition': '\\n            abs(date_diff(\\'month\\',\\n                strptime(\"dob_l\", \\'%Y-%m-%d\\'),\\n                strptime(\"dob_r\", \\'%Y-%m-%d\\'))\\n                ) <= 1\\n        ',\n",
       "     'label_for_charts': 'Within 1 month',\n",
       "     'm_probability': 0.0031756211995425145,\n",
       "     'u_probability': 0.0037749301645013966},\n",
       "    {'sql_condition': '\\n            abs(date_diff(\\'year\\',\\n                strptime(\"dob_l\", \\'%Y-%m-%d\\'),\\n                strptime(\"dob_r\", \\'%Y-%m-%d\\'))\\n                ) <= 1\\n        ',\n",
       "     'label_for_charts': 'Within 1 year',\n",
       "     'm_probability': 0.007451940207640657,\n",
       "     'u_probability': 0.046537844189243116},\n",
       "    {'sql_condition': '\\n            abs(date_diff(\\'year\\',\\n                strptime(\"dob_l\", \\'%Y-%m-%d\\'),\\n                strptime(\"dob_r\", \\'%Y-%m-%d\\'))\\n                ) <= 10\\n        ',\n",
       "     'label_for_charts': 'Within 10 years',\n",
       "     'm_probability': 0.016859886889761786,\n",
       "     'u_probability': 0.22140167605196648},\n",
       "    {'sql_condition': 'ELSE',\n",
       "     'label_for_charts': 'All other comparisons',\n",
       "     'm_probability': 0.008127143735345621,\n",
       "     'u_probability': 0.7000913403981732}],\n",
       "   'comparison_description': 'Exact match vs. Dob within damerau-levenshtein threshold 1 vs. Dates within the following thresholds Month(s): 1, Year(s): 1, Year(s): 10 vs. anything else'},\n",
       "  {'output_column_name': 'postcode_fake',\n",
       "   'comparison_levels': [{'sql_condition': '\"postcode_fake_l\" IS NULL OR \"postcode_fake_r\" IS NULL',\n",
       "     'label_for_charts': 'Null',\n",
       "     'is_null_level': True},\n",
       "    {'sql_condition': 'lower(\"postcode_fake_l\") = lower(\"postcode_fake_r\")',\n",
       "     'label_for_charts': 'Exact match postcode_fake',\n",
       "     'm_probability': 0.6786618521290907,\n",
       "     'u_probability': 0.00014099506799252162},\n",
       "    {'sql_condition': '\\n        regexp_extract(lower(\"postcode_fake_l\"), \\'^[A-Za-z]{1,2}[0-9][A-Za-z0-9]? [0-9]\\')\\n     = \\n        regexp_extract(lower(\"postcode_fake_r\"), \\'^[A-Za-z]{1,2}[0-9][A-Za-z0-9]? [0-9]\\')\\n    ',\n",
       "     'label_for_charts': 'Exact match Postcode Sector',\n",
       "     'm_probability': 0.09454214756974498,\n",
       "     'u_probability': 0.00039541283512569396},\n",
       "    {'sql_condition': '\\n        regexp_extract(lower(\"postcode_fake_l\"), \\'^[A-Za-z]{1,2}[0-9][A-Za-z0-9]?\\')\\n     = \\n        regexp_extract(lower(\"postcode_fake_r\"), \\'^[A-Za-z]{1,2}[0-9][A-Za-z0-9]?\\')\\n    ',\n",
       "     'label_for_charts': 'Exact match Postcode District',\n",
       "     'm_probability': 0.04245024745889525,\n",
       "     'u_probability': 0.0004890962247473917},\n",
       "    {'sql_condition': '\\n        regexp_extract(lower(\"postcode_fake_l\"), \\'^[A-Za-z]{1,2}\\')\\n     = \\n        regexp_extract(lower(\"postcode_fake_r\"), \\'^[A-Za-z]{1,2}\\')\\n    ',\n",
       "     'label_for_charts': 'Exact match Postcode Area',\n",
       "     'm_probability': 0.09818471400853485,\n",
       "     'u_probability': 0.010970356256938133},\n",
       "    {'sql_condition': 'ELSE',\n",
       "     'label_for_charts': 'All other comparisons',\n",
       "     'm_probability': 0.08616103883373426,\n",
       "     'u_probability': 0.9880041396151963}],\n",
       "   'comparison_description': 'Exact match on full postcode vs. exact match on sector vs. exact match on district vs. exact match on area vs. all other comparisons'},\n",
       "  {'output_column_name': 'birth_place',\n",
       "   'comparison_levels': [{'sql_condition': '\"birth_place_l\" IS NULL OR \"birth_place_r\" IS NULL',\n",
       "     'label_for_charts': 'Null',\n",
       "     'is_null_level': True},\n",
       "    {'sql_condition': '\"birth_place_l\" = \"birth_place_r\"',\n",
       "     'label_for_charts': 'Exact match',\n",
       "     'm_probability': 0.8411885596523894,\n",
       "     'u_probability': 0.004255555132464759,\n",
       "     'tf_adjustment_column': 'birth_place',\n",
       "     'tf_adjustment_weight': 1.0},\n",
       "    {'sql_condition': 'ELSE',\n",
       "     'label_for_charts': 'All other comparisons',\n",
       "     'm_probability': 0.1588114403476107,\n",
       "     'u_probability': 0.9957444448675352}],\n",
       "   'comparison_description': 'Exact match vs. anything else'},\n",
       "  {'output_column_name': 'occupation',\n",
       "   'comparison_levels': [{'sql_condition': '\"occupation_l\" IS NULL OR \"occupation_r\" IS NULL',\n",
       "     'label_for_charts': 'Null',\n",
       "     'is_null_level': True},\n",
       "    {'sql_condition': '\"occupation_l\" = \"occupation_r\"',\n",
       "     'label_for_charts': 'Exact match',\n",
       "     'm_probability': 0.8964431408109956,\n",
       "     'u_probability': 0.04052388472636166,\n",
       "     'tf_adjustment_column': 'occupation',\n",
       "     'tf_adjustment_weight': 1.0},\n",
       "    {'sql_condition': 'ELSE',\n",
       "     'label_for_charts': 'All other comparisons',\n",
       "     'm_probability': 0.10355685918900442,\n",
       "     'u_probability': 0.9594761152736383}],\n",
       "   'comparison_description': 'Exact match vs. anything else'}],\n",
       " 'retain_matching_columns': True,\n",
       " 'retain_intermediate_calculation_columns': True,\n",
       " 'max_iterations': 10,\n",
       " 'em_convergence': 0.01,\n",
       " 'sql_dialect': 'duckdb',\n",
       " 'linker_uid': 'p47ggjq1',\n",
       " 'probability_two_random_records_match': 0.00013582694460587586}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "settings = {\n",
    "    \"link_type\": \"dedupe_only\",\n",
    "    \"blocking_rules_to_generate_predictions\": [\n",
    "        block_on([\"first_name\", \"surname\"]),\n",
    "        block_on([\"surname\", \"dob\"]),\n",
    "        block_on([\"first_name\", \"dob\"]),\n",
    "        block_on([\"postcode_fake\", \"first_name\"]),\n",
    "    ],\n",
    "    \"comparisons\": [\n",
    "        ctl.name_comparison(\"first_name\", term_frequency_adjustments=True),\n",
    "        ctl.name_comparison(\"surname\", term_frequency_adjustments=True),\n",
    "        ctl.date_comparison(\"dob\", cast_strings_to_date=True, invalid_dates_as_null=True),\n",
    "        ctl.postcode_comparison(\"postcode_fake\"),\n",
    "        cl.exact_match(\"birth_place\", term_frequency_adjustments=True),\n",
    "        cl.exact_match(\"occupation\",  term_frequency_adjustments=True),\n",
    "    ],\n",
    "    \"retain_matching_columns\": True,\n",
    "    \"retain_intermediate_calculation_columns\": True,\n",
    "    \"max_iterations\": 10,\n",
    "    \"em_convergence\": 0.01\n",
    "}\n",
    "\n",
    "linker = DuckDBLinker(df, settings)\n",
    "\n",
    "linker.estimate_probability_two_random_records_match(\n",
    "    [\n",
    "        \"l.first_name = r.first_name and l.surname = r.surname and l.dob = r.dob\",\n",
    "        \"substr(l.first_name,1,2) = substr(r.first_name,1,2) and l.surname = r.surname and substr(l.postcode_fake,1,2) = substr(r.postcode_fake,1,2)\",\n",
    "        \"l.dob = r.dob and l.postcode_fake = r.postcode_fake\",\n",
    "    ],\n",
    "    recall=0.6,\n",
    ")\n",
    "\n",
    "linker.estimate_u_using_random_sampling(max_pairs=5e6)\n",
    "\n",
    "training_blocking_rule = block_on([\"first_name\", \"surname\"])\n",
    "training_session_names = linker.estimate_parameters_using_expectation_maximisation(training_blocking_rule)\n",
    "\n",
    "training_blocking_rule = block_on(\"dob\")\n",
    "training_session_dob = linker.estimate_parameters_using_expectation_maximisation(training_blocking_rule)\n",
    "\n",
    "linker.save_model_to_json(\"model_h50k.json\", overwrite=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}