We’re in the process of merging two very large online databases, and there’s going to be a lot of manual data cleaning we’re going to have to do. For now, we’re just using R to visually put the columns next to each other and eyeball the differences.
Say one of the databases looks like this:
df<-structure(list(IRB_NO = c(NA, "0002-20", NA, "0003-21", "0005-14",
"0005-21"), Oncore_NCT_Number = c("NCT03150693", NA, "NCT05091567",
"NCT04546399", "NCT01824836", NA), Oncore_Protocol_No = c("A041501",
"NHPCC", "LS-P-GO43104", "AALL1821", "CTSU-E1Z11", "LS-P-ONC-1879"
)), row.names = c(NA, -6L), spec = structure(list(cols = list(
IRB_NO = structure(list(), class = c("collector_character",
"collector")), Oncore_NCT_Number = structure(list(), class = c("collector_character",
"collector")), Oncore_Protocol_No = structure(list(), class = c("collector_character",
"collector"))), default = structure(list(), class = c("collector_guess",
"collector")), delim = ","), class = "col_spec"), class = c("spec_tbl_df",
"tbl_df", "tbl", "data.frame"))
and the other looks like this:
df2<-structure(list(IRB_NO = c("0001-21", "0002-20", NA, NA, "0005-14",
"0005-21"), Epic_NCT_Number = c("NCT03150693", NA, "NCT05091567",
"NCT04546399", "NCT01824836", NA), Epic_Protocol_No = c("A041501",
"NHPCC", "LS-P-GO43104", "AALL1821", "CTSU-E1Z11", "LS-P-ONC-1879"
)), row.names = c(NA, -6L), spec = structure(list(cols = list(
IRB_NO = structure(list(), class = c("collector_character",
"collector")), Epic_NCT_Number = structure(list(), class = c("collector_character",
"collector")), Epic_Protocol_No = structure(list(), class = c("collector_character",
"collector"))), default = structure(list(), class = c("collector_guess",
"collector")), delim = ","), class = "col_spec"), class = c("spec_tbl_df",
"tbl_df", "tbl", "data.frame"))
And I want to join them on "IRB_No". Well when I do that like so:
Merged<-df%>%full_join(df2, by="IRB_NO")
The problem is, sure that joins them on IRB_No, but if the IRB_No was blank, it still joins them, like so:
Now, SOMETIMES thats ok. In the row I marked blue, you can tell thats probably a match because they have the same protocol_no and nct_number, but obviously a lot of the time its incorrect.
I saw the warning in R about multiple matches and handling it with "all", "any", "first", etc… but I was thinking there’s probably a better way.
Is there a way to do a "backup" column? I.e. if irb_no is blank, then try to join by nct_number?
Update based on comments:
after using na_matches = "never" I was able to help dial in my results a little more. But for instance, these two rows (which now dont match because they have blank IRB_NO’s) are pretty obviously the same study, and it’d b great if I could "squish" the rows into one row:
>Solution :
To get something like your "backup," you can compute a key in each dataframe by dplyr::coalesce()ing IRB_NO and *_NCT_Number. This will yield a key with the value of IRB_NO if not NA and *_NCT_Number if IRB_NO is NA. (And you’ll want to use na_matches = "never" as pointed out by @dufei.)
library(dplyr)
full_join(
mutate(df, key = coalesce(IRB_NO, Oncore_NCT_Number)),
mutate(df2, key = coalesce(IRB_NO, Epic_NCT_Number)),
by = "key",
na_matches = "never"
) %>%
relocate(key, Oncore_IRB_NO = IRB_NO.x, Epic_IRB_NO = IRB_NO.y)
# A tibble: 8 × 7
key Oncore_IRB_NO Epic_IRB_NO Oncore_NCT_Number Oncore_Protocol_No Epic_NCT_Number Epic_Protocol_No
<chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 NCT03150693 NA NA NCT03150693 A041501 NA NA
2 0002-20 0002-20 0002-20 NA NHPCC NA NHPCC
3 NCT05091567 NA NA NCT05091567 LS-P-GO43104 NCT05091567 LS-P-GO43104
4 0003-21 0003-21 NA NCT04546399 AALL1821 NA NA
5 0005-14 0005-14 0005-14 NCT01824836 CTSU-E1Z11 NCT01824836 CTSU-E1Z11
6 0005-21 0005-21 0005-21 NA LS-P-ONC-1879 NA LS-P-ONC-1879
7 0001-21 NA 0001-21 NA NA NCT03150693 A041501
8 NCT04546399 NA NA NA NA NCT04546399 AALL1821