Deduplication using Pyspark
Linking in SparkΒΆ
from pyspark import SparkConf, SparkContext
from pyspark.sql import SparkSession
from splink.backends.spark import similarity_jar_location
conf = SparkConf()
# This parallelism setting is only suitable for a small toy example
conf.set("spark.driver.memory", "12g")
conf.set("spark.default.parallelism", "8")
conf.set("spark.sql.codegen.wholeStage", "false")
# Add custom similarity functions, which are bundled with Splink
# documented here: https://github.com/moj-analytical-services/splink_scalaudfs
path = similarity_jar_location()
conf.set("spark.jars", path)
sc = SparkContext.getOrCreate(conf=conf)
spark = SparkSession(sc)
spark.sparkContext.setCheckpointDir("./tmp_checkpoints")
24/07/13 19:50:47 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
from splink import splink_datasets
pandas_df = splink_datasets.fake_1000
df = spark.createDataFrame(pandas_df)
import splink.comparison_library as cl
from splink import Linker, SettingsCreator, SparkAPI, block_on
settings = SettingsCreator(
link_type="dedupe_only",
comparisons=[
cl.NameComparison("first_name"),
cl.NameComparison("surname"),
cl.LevenshteinAtThresholds(
"dob"
),
cl.ExactMatch("city").configure(term_frequency_adjustments=True),
cl.EmailComparison("email"),
],
blocking_rules_to_generate_predictions=[
block_on("first_name"),
"l.surname = r.surname", # alternatively, you can write BRs in their SQL form
],
retain_intermediate_calculation_columns=True,
em_convergence=0.01,
)
linker = Linker(df, settings, db_api=SparkAPI(spark_session=spark))
deterministic_rules = [
"l.first_name = r.first_name and levenshtein(r.dob, l.dob) <= 1",
"l.surname = r.surname and levenshtein(r.dob, l.dob) <= 1",
"l.first_name = r.first_name and levenshtein(r.surname, l.surname) <= 2",
"l.email = r.email",
]
linker.training.estimate_probability_two_random_records_match(deterministic_rules, recall=0.6)
Probability two random records match is estimated to be 0.0806.
This means that amongst all possible pairwise record comparisons, one in 12.41 are expected to match. With 499,500 total possible comparisons, we expect a total of around 40,246.67 matching pairs
linker.training.estimate_u_using_random_sampling(max_pairs=5e5)
----- Estimating u probabilities using random sampling -----
Estimated u probabilities using random sampling
Your model is not yet fully trained. Missing estimates for:
- first_name (no m values are trained).
- surname (no m values are trained).
- dob (no m values are trained).
- city (no m values are trained).
- email (no m values are trained).
training_blocking_rule = "l.first_name = r.first_name and l.surname = r.surname"
training_session_fname_sname = (
linker.training.estimate_parameters_using_expectation_maximisation(training_blocking_rule)
)
training_blocking_rule = "l.dob = r.dob"
training_session_dob = linker.training.estimate_parameters_using_expectation_maximisation(
training_blocking_rule
)
----- Starting EM training session -----
Estimating the m probabilities of the model by blocking on:
l.first_name = r.first_name and l.surname = r.surname
Parameter estimates will be made for the following comparison(s):
- dob
- city
- email
Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules:
- first_name
- surname
Iteration 1: Largest change in params was -0.709 in probability_two_random_records_match
Iteration 2: Largest change in params was 0.0573 in the m_probability of email, level `All other comparisons`
Iteration 3: Largest change in params was 0.0215 in the m_probability of email, level `All other comparisons`
Iteration 4: Largest change in params was 0.00888 in the m_probability of email, level `All other comparisons`
EM converged after 4 iterations
Your model is not yet fully trained. Missing estimates for:
- first_name (no m values are trained).
- surname (no m values are trained).
----- Starting EM training session -----
Estimating the m probabilities of the model by blocking on:
l.dob = r.dob
Parameter estimates will be made for the following comparison(s):
- first_name
- surname
- city
- email
Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules:
- dob
WARNING:
Level Jaro-Winkler >0.88 on username on comparison email not observed in dataset, unable to train m value
Iteration 1: Largest change in params was -0.548 in the m_probability of surname, level `Exact match on surname`
Iteration 2: Largest change in params was 0.129 in probability_two_random_records_match
Iteration 3: Largest change in params was 0.0313 in probability_two_random_records_match
Iteration 4: Largest change in params was 0.0128 in probability_two_random_records_match
Iteration 5: Largest change in params was 0.00651 in probability_two_random_records_match
EM converged after 5 iterations
m probability not trained for email - Jaro-Winkler >0.88 on username (comparison vector value: 1). This usually means the comparison level was never observed in the training data.
Your model is fully trained. All comparisons have at least one estimate for their m and u values
results = linker.inference.predict(threshold_match_probability=0.9)
Blocking time: 4.65 seconds
Predict time: 82.92 seconds
spark_df = results.as_spark_dataframe().show()
+------------------+------------------+-----------+-----------+------------+------------+----------------+---------------+---------------+------------------+--------------------+---------+---------+-------------+------------+------------+-------------------+------------------+----------+----------+---------+------------------+----------+----------+----------+---------+---------+------------------+------------------+--------------------+--------------------+-----------+----------+----------+-------------------+-------------------+---------+
| match_weight| match_probability|unique_id_l|unique_id_r|first_name_l|first_name_r|gamma_first_name|tf_first_name_l|tf_first_name_r| bf_first_name|bf_tf_adj_first_name|surname_l|surname_r|gamma_surname|tf_surname_l|tf_surname_r| bf_surname| bf_tf_adj_surname| dob_l| dob_r|gamma_dob| bf_dob| city_l| city_r|gamma_city|tf_city_l|tf_city_r| bf_city| bf_tf_adj_city| email_l| email_r|gamma_email|tf_email_l|tf_email_r| bf_email| bf_tf_adj_email|match_key|
+------------------+------------------+-----------+-----------+------------+------------+----------------+---------------+---------------+------------------+--------------------+---------+---------+-------------+------------+------------+-------------------+------------------+----------+----------+---------+------------------+----------+----------+----------+---------+---------+------------------+------------------+--------------------+--------------------+-----------+----------+----------+-------------------+-------------------+---------+
|15.131885475840011|0.9999721492762709| 51| 56| Jayden| Jayden| 4| 0.008| 0.008|11.371009132404957| 4.0525525525525525| Bennett| Bennett| 4| 0.006| 0.006| 9.113630950205666| 5.981981981981981|2017-01-11|2017-02-10| 1|14.373012181955707| Swansea| Swansea| 1| 0.013| 0.013|5.8704874944935215| 5.481481481481482| NaN| jb88@king.com| 0| 0.211| 0.004|0.35260600559686806| 1.0| 0|
| 7.86514930254232|0.9957293356289956| 575| 577| Jessica| Jessica| 4| 0.011| 0.011|11.371009132404957| 2.9473109473109473| Owen| NaN| 0| 0.006| 0.181|0.45554364195240765| 1.0|1974-11-17|1974-11-17| 3|220.92747883214062| NaN| NaN| 1| 0.187| 0.187|5.8704874944935215|0.3810655575361458| NaN|jessica.owen@elli...| 0| 0.211| 0.002|0.35260600559686806| 1.0| 0|
| 5.951711022429932|0.9841000517299358| 171| 174| NaN| Leah| 0| 0.169| 0.002|0.4452000905514796| 1.0| Russell| Russell| 4| 0.01| 0.01| 9.113630950205666| 3.589189189189189|2011-06-08|2012-07-09| 0|0.2607755750325071| London| London| 1| 0.173| 0.173|5.8704874944935215|0.4119032327124813|leahrussell@charl...|leahrussell@charl...| 4| 0.005| 0.005| 8.411105418567649| 9.143943943943944| 1|
|21.650093935297473|0.9999996961409438| 518| 519| Amelia| Amlelia| 2| 0.009| 0.001| 47.10808446952784| 1.0| Morgan| Morgan| 4| 0.012| 0.012| 9.113630950205666|2.9909909909909906|2011-05-26|2011-05-26| 3|220.92747883214062| Swindno| Swindon| 0| 0.001| 0.01|0.6263033203299755| 1.0|amelia.morgan92@d...|amelia.morgan92@d...| 3| 0.004| 0.001| 211.35554441198767| 1.0| 1|
|11.456207518049865|0.9996442185022277| 752| 754| Jaes| NaN| 0| 0.001| 0.169|0.4452000905514796| 1.0| NaN| NaN| 4| 0.181| 0.181| 9.113630950205666|0.1982977452590712|1972-07-20|1971-07-20| 2| 84.28155355946456| NaN| NaN| 1| 0.187| 0.187|5.8704874944935215|0.3810655575361458| j.c@white.org| j.c@whige.wort| 3| 0.002| 0.001| 211.35554441198767| 1.0| 1|
|24.387299048327478|0.9999999544286963| 760| 761| Henry| Henry| 4| 0.009| 0.009|11.371009132404957| 3.602268935602269| Day| Day| 4| 0.004| 0.004| 9.113630950205666| 8.972972972972972|2002-09-15|2002-08-18| 1|14.373012181955707| Leeds| Leeds| 1| 0.017| 0.017|5.8704874944935215| 4.191721132897603|hday48@thomas-car...|hday48@thomas-car...| 3| 0.003| 0.001| 211.35554441198767| 1.0| 0|
|12.076660303346712|0.9997685471829967| 920| 922| Evi| Evie| 3| 0.001| 0.007| 61.79623639995749| 1.0| Jones| Jones| 4| 0.023| 0.023| 9.113630950205666|1.5605170387779081|2012-06-19|2002-07-22| 0|0.2607755750325071| NaN| NaN| 1| 0.187| 0.187|5.8704874944935215|0.3810655575361458|eviejones@brewer-...|eviejones@brewer-...| 4| 0.004| 0.004| 8.411105418567649| 11.42992992992993| 1|
| 4.002786788974079|0.9412833223288347| 171| 175| NaN| Lheah| 0| 0.169| 0.001|0.4452000905514796| 1.0| Russell| Russell| 4| 0.01| 0.01| 9.113630950205666| 3.589189189189189|2011-06-08|2011-07-10| 0|0.2607755750325071| London| Londoon| 0| 0.173| 0.002|0.6263033203299755| 1.0|leahrussell@charl...|leahrussell@charl...| 4| 0.005| 0.005| 8.411105418567649| 9.143943943943944| 1|
|19.936162812706836|0.9999990031804153| 851| 853| Mhichael| Michael| 2| 0.001| 0.006| 47.10808446952784| 1.0| NaN| NaN| 4| 0.181| 0.181| 9.113630950205666|0.1982977452590712|2000-04-03|2000-04-03| 3|220.92747883214062| London| London| 1| 0.173| 0.173|5.8704874944935215|0.4119032327124813| m.w@cannon.com| m@w.cannon.com| 2| 0.002| 0.001| 251.69908796212906| 1.0| 1|
| 21.33290823458872|0.9999996214227064| 400| 402| James| James| 4| 0.013| 0.013|11.371009132404957| 2.4938784938784937| Dixon| Dixon| 4| 0.009| 0.009| 9.113630950205666| 3.987987987987988|1991-04-12|1991-04-12| 3|220.92747883214062| NaN| Loodnon| 0| 0.187| 0.001|0.6263033203299755| 1.0|james.d@merritot-...|james.d@merritt-s...| 3| 0.001| 0.005| 211.35554441198767| 1.0| 0|
|22.169132705637786|0.9999997879560012| 81| 84| Ryan| Ryan| 4| 0.005| 0.005|11.371009132404957| 6.484084084084084| Cole| Cole| 4| 0.005| 0.005| 9.113630950205666| 7.178378378378378|1987-05-27|1988-05-27| 2| 84.28155355946456| NaN| Bristol| 0| 0.187| 0.016|0.6263033203299755| 1.0|r.cole1@ramirez-a...|r.cole1@ramtrez-a...| 3| 0.005| 0.001| 211.35554441198767| 1.0| 0|
|6.1486678498977065|0.9861008615160808| 652| 654| NaN| NaN| 4| 0.169| 0.169|11.371009132404957| 0.19183680722142257| Roberts| NaN| 0| 0.006| 0.181|0.45554364195240765| 1.0|1990-10-26|1990-10-26| 3|220.92747883214062|Birmingham|Birmingham| 1| 0.04| 0.04|5.8704874944935215|1.7814814814814814| NaN|droberts73@taylor...| 0| 0.211| 0.003|0.35260600559686806| 1.0| 0|
|17.935398542824068|0.9999960106207738| 582| 584| ilivOa| Olivia| 1| 0.001| 0.014| 3.944098136204933| 1.0| Edwards| Edwards| 4| 0.008| 0.008| 9.113630950205666| 4.486486486486486|1988-12-27|1988-12-27| 3|220.92747883214062| Dudley| Duudley| 0| 0.006| 0.001|0.6263033203299755| 1.0| oe56@lopez.net| oe56@lopez.net| 4| 0.003| 0.003| 8.411105418567649| 15.239906573239907| 1|
|21.036204363210302|0.9999995349803662| 978| 981| Jessica| Jessica| 4| 0.011| 0.011|11.371009132404957| 2.9473109473109473| Miller| Miiller| 3| 0.004| 0.001| 82.56312210691897| 1.0|2001-05-23|2001-05-23| 3|220.92747883214062| NaN| Coventry| 0| 0.187| 0.021|0.6263033203299755| 1.0|jessica.miller@jo...|jessica.miller@jo...| 4| 0.006| 0.006| 8.411105418567649| 7.619953286619953| 0|
|13.095432674729635|0.9998857562788657| 684| 686| Rosie| Rosie| 4| 0.005| 0.005|11.371009132404957| 6.484084084084084| Johnstn| Johnston| 3| 0.001| 0.002| 82.56312210691897| 1.0|1979-12-23|1978-11-23| 1|14.373012181955707| NaN| Sheffield| 0| 0.187| 0.007|0.6263033203299755| 1.0| NaN| NaN| 4| 0.211| 0.211| 8.411105418567649|0.21668113611241574| 0|
|25.252698357543103|0.9999999749861632| 279| 280| Lola| Lola| 4| 0.008| 0.008|11.371009132404957| 4.0525525525525525| Taylor| Taylor| 4| 0.014| 0.014| 9.113630950205666|2.5637065637065635|2017-11-20|2016-11-20| 2| 84.28155355946456| Aberdeen| Aberdeen| 1| 0.016| 0.016|5.8704874944935215| 4.453703703703703|lolat86@bishop-gi...|lolat86@bishop-gi...| 4| 0.002| 0.002| 8.411105418567649| 22.85985985985986| 0|
| 9.711807138722323|0.9988089303569408| 42| 43| Theodore| Theodore| 4| 0.01| 0.01|11.371009132404957| 3.242042042042042| Morris| Morris| 4| 0.004| 0.004| 9.113630950205666| 8.972972972972972|1978-09-18|1978-08-19| 1|14.373012181955707|Birgmhniam|Birmingham| 0| 0.001| 0.04|0.6263033203299755| 1.0| NaN|t.m39@brooks-sawy...| 0| 0.211| 0.005|0.35260600559686806| 1.0| 0|
| 5.951711022429932|0.9841000517299358| 173| 174| NaN| Leah| 0| 0.169| 0.002|0.4452000905514796| 1.0| Russell| Russell| 4| 0.01| 0.01| 9.113630950205666| 3.589189189189189|2011-06-08|2012-07-09| 0|0.2607755750325071| London| London| 1| 0.173| 0.173|5.8704874944935215|0.4119032327124813|leahrussell@charl...|leahrussell@charl...| 4| 0.005| 0.005| 8.411105418567649| 9.143943943943944| 1|
| 23.43211696288854|0.9999999116452517| 88| 89| Lexi| Lexi| 4| 0.003| 0.003|11.371009132404957| 10.806806806806806| NaN| NaN| 4| 0.181| 0.181| 9.113630950205666|0.1982977452590712|1994-09-02|1994-09-02| 3|220.92747883214062|Birmingham|Birmingham| 1| 0.04| 0.04|5.8704874944935215|1.7814814814814814|l.gordon34cfren@h...|l.gordon34@french...| 2| 0.001| 0.002| 251.69908796212906| 1.0| 0|
|7.1659948250873144|0.9930847652376709| 391| 393| Isaac| Isaac| 4| 0.005| 0.005|11.371009132404957| 6.484084084084084| NaN| James| 0| 0.181| 0.007|0.45554364195240765| 1.0|1991-05-06|1991-05-06| 3|220.92747883214062| Lodon| London| 0| 0.008| 0.173|0.6263033203299755| 1.0|isaac.james@smich...| NaN| 0| 0.001| 0.211|0.35260600559686806| 1.0| 0|
+------------------+------------------+-----------+-----------+------------+------------+----------------+---------------+---------------+------------------+--------------------+---------+---------+-------------+------------+------------+-------------------+------------------+----------+----------+---------+------------------+----------+----------+----------+---------+---------+------------------+------------------+--------------------+--------------------+-----------+----------+----------+-------------------+-------------------+---------+
only showing top 20 rows