Multistage deterministic linkage in R"

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

Introduction

Linking multiple datasets to consolidate information is a common task in research, particularly for those involving the use of "big data". Deterministic record linkage is the simplest and most common method of record linkage however, its accuracy relies on data quality. Too many incorrect or missing values will often provide an unacceptable number of false matches or mismatches.

This function aims to provide a simple, multistage and flexible implementation of deterministic record linkage which tries to improve the linkage of datasets with missing or incorrect group identifiers e.g. customer, patient or admission codes. In such instances, alternative identifiers like dates, names, height or other attributes are used in a specified order of preference.

Uses

Each argument in record_group() controls separate aspects of the linkage process. Different combinations of options can be used to link datasets in a variety of ways. Examples of these include;

Implementation

Record linkage is done in stages. A match at each stage is considered more relevant than matches at subsequent stages. Matching records are assigned a unique group ID. The group ID is essentially a record ID (sn) of one of the matching records. Therefore, using a familiar record ID (sn) allows you to link the results back to the original dataset.

Each stage has a matching criteria (column). These are passed to the function as column names. The argument supports quasiquotation.

sub_criteria are additional matching conditions (columns) that can be paired with each criteria. This is provided as a list of named vectors. Each named vector contains a set of column names. When a sub_criteria is used, records will only be assigned a group ID when they match on the criteria, and at least one named column in each sub_criteria.

Each sub_criteria should be paired with a corresponding criteria. To do this, the vector name for each sub_criteria should be - "s" followed by the corresponding criteria number e.g. "s1" or "s4". When a criteria is paired to more than one sub_criteria, include a suffix after the criteria number e.g. "s1a", "s1b" and "s1c" (See examples). Any sub_criteria not paired with a criteria will be ignored. The sub_criteria argument does not support quasiquotation.

At each stage, the function prints the number of records that have been assigned a group ID and how many groups have only one record. NOTE; to_s4 and to_s4() changes the function's output from a data.frame (current default) to pid objects. pid objects will be the default output in the next release.

library(diyar); library(dplyr)
data(patient_list); 
dbs <- patient_list[c("forename","surname","sex")]; dbs

# 1 stage <- Matching surname only
dbs$pids_a <-record_group(dbs, criteria = surname, to_s4 = TRUE)

# 2 stage - Matching surname, then matching sex
dbs$pids_b <- record_group(dbs, criteria = c(surname, sex), display = FALSE, to_s4 = TRUE)

dbs
# Note that exact matching is case sensitive. See range matching.

Record matching

The choice and ordering of column names in criteria and sub_criteria determines the relevance of matches. This flexibility allows you to link records in different ways. However, you should always consider the most practical combination to get more true matches than false one.

For example, in patient_list above, linking on surnames and then sex (pid_b) leads to a different result compared to when linking on sex before surnames (pid_c) (See Record group expansion). In pid_b, a match on the individual's surname was considered more relevant than a match on their sex. The opposite can be done like in pid_c below.

dbs$pids_c <- record_group(dbs, criteria =  c(sex, surname), display = FALSE, to_s4 = TRUE)

dbs

Both result are logically correct considering the criteria used however, pids_b is not the most practical option given the dataset. For instance, records 3 and 6 which have been grouped together could actually be cousins not the same individual. A better combination would be forename at stage 1, followed by surname and sex at stage 2. See pid_d below;

dbs_2 <- patient_list; dbs_2

dbs_2$cri_2 <- paste(dbs_2$surname, dbs_2$sex,sep="-")
dbs_2$pid_d <- record_group(dbs_2, rd_id, c(forename, cri_2), display = FALSE, to_s4 = TRUE)

dbs_2

As mentioned earlier, at each stage of record linkage, a sub_criteria can be used for additional matching conditions. Just like criteria, any column in the dataset can be used as a sub_criteria.

Below are examples of record linkage using different combinations of the same criteria and sub_criteria

library(tidyr)
data(Opes); Opes

# 1 stage linkage
  # stage 1 - name AND (department OR hair_colour OR date_of_birth)
Opes$pids_a <- record_group(Opes, criteria = name, 
                            sub_criteria = list(
                              "s1a"=c("department","hair_colour","date_of_birth")),
                            display = FALSE, to_s4 = TRUE)

Opes[c("name","department","hair_colour","date_of_birth","pids_a")]

# 1 stage linkage 
  # stage 1 - name AND ((department OR hair_colour) AND (date_of_birth)) 
Opes$pids_b <- record_group(Opes, criteria = name, 
                            sub_criteria = list(
                              "s1a"=c("department","hair_colour"),
                              "s1b"=c("date_of_birth")),
                            display = FALSE, to_s4 = TRUE)

Opes[c("name","department","hair_colour","date_of_birth","pids_b")]

# 1 stage linkage 
  # stage 1 - name AND ((department OR hair_colour) AND (dd-mm OR dd-yyyy OR mm-yyyy))
Opes$pids_c <- record_group(Opes, criteria = name, 
                            sub_criteria = list(
                              "s1a"=c("department","hair_colour"),
                              "s1b"=c("db_pt1","db_pt2","db_pt3")),
                            display = FALSE, to_s4 =TRUE)

Opes[c("name","department","hair_colour","date_of_birth","pids_c")]

# 1 stage linkage 
  # stage 1 - name AND ((department)  AND (hair_colour) AND (dd-mm OR dd-yyyy OR mm-yyyy))
Opes$pids_d <- record_group(Opes, criteria =name, 
               sub_criteria = list(
                 "s1a"=c("department"),
                 "s1c"=c("hair_colour"),
                 "s1b"=c("db_pt1","db_pt2","db_pt3")),  
               display = FALSE, to_s4 = TRUE)

Opes[c("name","department","hair_colour","date_of_birth","pids_d")]

Note that using sub_criteria costs additional processing time, so it should be avoided when not needed. For example, the two implementations below (pids_e and pids_f) will lead to the same outcome but pids_f will take less time.

# 1 stage linkage 
  # stage 1 - name AND ((department)  AND (hair_colour) AND (date_of_birth))
Opes$pids_e <- record_group(Opes, criteri = name, 
                            sub_criteria = list(
                              "s1a"=c("department"), 
                              "s1b"=c("hair_colour"), 
                              "s1c"=c("date_of_birth")),
                            display = TRUE, to_s4 = TRUE)

Opes$cri <- paste(Opes$name, Opes$date_of_birth, Opes$department, Opes$hair_colour, sep="-")

# 1 stage linkage 
  # stage 1 - name AND department AND hair_colour AND date_of_birth
Opes$pids_f <- record_group(Opes, criteria = cri,  display = TRUE, to_s4 =TRUE)

Opes[c("name","department","hair_colour","date_of_birth","pids_e","pids_f")]

Range matching

Records can be matched in two ways - exact matches as in the examples above, or range matching. In range matching, records are matched if the value in one record are within a range of values in another. To do this, convert each value to the required range using number_line(). Then assign the actual value to the gid slot of the resulting number_line object. The number_line object can then be used as a criteria or sub_criteria. number_line objects are considered a match if they overlap. See the examples below.

library(lubridate)
Opes_c <- Opes["date_of_birth"]
Opes_c

# Match on date of birth + 2 years
Opes_c$date_of_birth <- dmy(Opes_c$date_of_birth)
Opes_c$range_a <- expand_number_line(as.number_line(Opes_c$date_of_birth), period(2, "years"), "end")
Opes_c$range_a@gid <- as.numeric(Opes_c$date_of_birth)

Opes_c$pids_a <- record_group(Opes_c, criteria = range_a, to_s4 =TRUE)

Opes_c[c("date_of_birth","range_a","pids_a")]

# Match on age +/- 5 years
Opes_c$age <- as.numeric(round((Sys.Date() - Opes_c$date_of_birth)/365.5)) # approximate age
Opes_c$range_b <- expand_number_line(as.number_line(Opes_c$age), 5, "both")
Opes_c$range_b@gid <- Opes_c$age

Opes_c$pids_b <- record_group(Opes_c, criteria = range_b, to_s4 =TRUE)

Opes_c[c("age","range_b","pids_b")]

Record group expansion

At each stage of record linkage, records are either assigned a new group ID or inherit an existing one. The following scenario explain how these happen;

Existing group IDs can be inherited but will not be overwritten. This is because groups formed at earlier stages are considered more "certain" than those formed at subsequent stages. Therefore, it's worth reiterating that record_group() expects the criteria to be listed in order of decreasing relevance.

The example below with patient_list demonstrates this behaviour.

data(patient_list_2); patient_list_2

patient_list_2$pids_a <- record_group(patient_list_2, rd_id, c(forename, surname, sex), to_s4 = TRUE)

patient_list_2

Handling missing values

Records with missing values for a particular criteria are excluded from that stage of record linkage. If a record has missing values for every listed criteria, it's assigned a unique group ID at the end of the process.

It's common for databases to use specific characters or numbers to represent missing or unknown data e.g. N/A, Nil, 01/01/1100, 111111 etc. These pseudo-missing values will need to be re-coded to one of the two recognised by record_group() - NA or an empty string (""). If this is not done, the function will assume the pseudo-missing values are valid values and therefore group them together. This can cause a continuous cascade of false matches as seen below.

patient_list_2$forename <- ifelse(patient_list_2$rd_id %in% 1:3, "Nil", patient_list_2$forename)
# 2 stage linkage
    # Stage 1 - forename
    # Stage 2 - surname

patient_list_2$pids_b <- record_group(patient_list_2, criteria = c(forename, surname), 
                                      display = FALSE, to_s4 =TRUE)

patient_list_2[c("forename","surname","pids_b")]

In the example above, records 1-3 are assigned a single group ID even though record 3 is clearly a different individual. This issue can be addressed by recoding "Nil" to NA or "".

# Using NA as the proxy for missing value
patient_list_2 <- mutate(patient_list_2,forename = ifelse(forename=="Nil",NA,forename))

patient_list_2$pids_d <- record_group(patient_list_2, rd_id, c(forename, surname), 
                                      display = FALSE, to_s4 = TRUE)

# Using "" as the proxy for missing value
patient_list_2 <- mutate(patient_list_2,forename = ifelse(is.na(forename),"",forename))  

patient_list_2$pids_e <- record_group(patient_list_2, rd_id, c(forename, surname), 
                                      display = FALSE, to_s4 = TRUE)

patient_list_2[c("forename","surname","pids_d","pids_e")]

Records 3 and 4 seem to be the same individual but are not grouped together because the surnames are not an exact match. A common approach to addressing this is to use a less exact but reasonable proxies of the matching critetria. For example, using surname initials or surname soundex instead of the actual surname.

library(phonics)

patient_list_2$soundex <- soundex(patient_list_2$surname)

patient_list_2$pids_e <- record_group(patient_list_2, rd_id, c(forename, soundex), 
                                      display = FALSE, to_s4 = TRUE)

patient_list_2[c("forename","surname","soundex","pids_d","pids_e")]

Conclusion

As a general rule, the more unique a criteria, the earlier it should be listed in criteria. Also, the set and ordering of criteria is a personal choice but should also be a practical one for any given dataset. For example, when linking a vehicular database with no existing identifier, vehicle colour alone is less practical than colour and brand name, which in turn is less practical than colour, brand name, make and model. However colour, brand name, make and model and 10 other parameters might be too strict and may need to be relaxed. On the other hand, the dataset could be so small that vehicle colour alone is a sufficient criteria. record_group() aims to minimize false mismatches due to random data entry errors or missing values. The choice and ordering of criteria and sub_criteria should balance the availability of alternative identifiers with their practicality as a group identifier.



Try the diyar package in your browser

Any scripts or data that you put into this service are public.

diyar documentation built on Dec. 9, 2019, 1:06 a.m.