The problem
fastLink and RecordLinkage packages do extremely well in matching records (rows) from database A to database B and vice-versa. The developers are working on extending from matching only 2 databases to multiple databases.
A simple example of both I gave here.
In the meantime, how would we go about matching multiple data frames? For example, I have multiple medical records of patients from clinic A, B, C, D, E, F, and I want to merge them into a single one.
A reproducible example:
dfA <-
structure(list(fname = c("Jafar", "Nemo", "Simba", "Belle", "Nala",
"Jasmine"), lname = c("Evil", "Water", "King", "Beauty", "Princess",
"Princess"), gender = c("M", "M", "M", "F", "F", "F"), dob = c(1987,
2000, 2011, 1989, 1970, 1989), city = c("Arabtown", "Atlantic",
"Sahara", "Nice", "Sahara", "Arabtown")), row.names = c(NA, -6L
), class = c("tbl_df", "tbl", "data.frame"))
dfB <-
structure(list(fname = c("Jafar Jr", "Nemo", "Simba", "Belle",
"Nala", "Jasmine"), lname = c("Evil", "Waterson", "King", "Beauty",
"Princess", "Princess of Arabtown"), gender = c("M", "M", "M",
"F", "F", "F"), dob = c(NA, 2000, 2011, NA, NA, 1989), city = c("Arabtown",
"Atlantica", "Sahara", "Nice-France", "Sahara", "Arabia")), row.names = c(NA,
-6L), class = c("tbl_df", "tbl", "data.frame"))
dfC <-
structure(list(fname = c("Jafar Jr", "Fishy", "Lion", "Belle",
"Sarabi", "Jasmine"), lname = c("Evil", "Waterpal", "King", "Beauty",
"Queen", NA), gender = c("M", "M", NA, "F", "F", "F"), dob = c(NA,
2000, 2011, NA, 1940, 1989), city = c("Arabia", NA, "Sahara",
"France", "Sahara", NA)), row.names = c(NA, -6L), class = c("tbl_df",
"tbl", "data.frame"))
dfD <-
structure(list(fname = c("Jafar Jr", "Nemo", "Simba", "Belle",
"Sarabi", "Jasmine"), lname = c("Evil", "Waterson", "King", "Beast",
"Queen", "Evil"), gender = c("M", "M", "M", "F", "F", "M"), dob = c(NA,
2000, 2011, 1989, NA, 1989), city = c("Arabtown", "Atlantica",
"Sahara", NA, "Sahara", "Arabtown")), row.names = c(NA, -6L), class = c("tbl_df",
"tbl", "data.frame"))
dfE <-
structure(list(fname = c("Jafar Jr", "Nemo", "Simba", "Belle",
"Nala", "Aladdin"), lname = c("Evil", "Pateron", NA, "Gaston",
NA, "Streetrat"), gender = c("M", NA, "M", "F", "F", "M"), dob = c(1987,
NA, NA, NA, 1970, 1989), city = c("Arabtown", "Atlantica", "Sahara",
"France", "Sahara", "Arabia")), row.names = c(NA, -6L), class = c("tbl_df",
"tbl", "data.frame"))
dfF <-
structure(list(fname = c("Jafar Jr", "Nemo", "Simba", "Belle",
"Nala", "Al"), lname = c("Evil", "Waterson", "Dead", "Beauty",
"Princess", "Streetrat"), gender = c("M", "M", NA, "F", "F",
"M"), dob = c(1987, 2000, 2011, NA, NA, 1989), city = c("Arabia",
"Atlantic", "Sahara", "Nice-France", "Sahara", "Arabia")), row.names = c(NA,
-6L), class = c("tbl_df", "tbl", "data.frame"))
Expected result :
In the end I want unique identified records :
1 Jafar Evil M 1987 Arabtown
2 Nemo Water M 2000 Atlantic
3 Simba King M 2011 Sahara
4 Belle Beauty F 1989 Nice
5 Nala Princess F 1970 Sahara
6 Jasmine Princess F 1989 Arabtown
7 Sarabi Queen F 1940 Sahara
8 Aladdin Streetrat M 1989 Arabia
Even if the result isn't as clean as above, it's alright. The goal is to find a unified record from all 6 records and belong to the same entity. Both fastLink & RecordLinkage take care of deduping (removing duplicates).
How can I develop an approach to deal with more than two databases in this scenario?
