Matching strings in a column of a data frame with the strings in a column of another data frame using R or Python

Question

I am trying to match strings in a column of a data frame with the strings in a column of another data frame and map the corresponding values. The number of rows are different for both data frames

df1 = data.frame(name = c("(CKMB)Creatinine Kinase Muscle & Brain", "24 Hours Urine for Sodium", "Antistreptolysin O Titer", "Blood group O", lonic_code = c("27816-8-O", "27816-8-B", "1869-7", "33914-3")
df2 = data.frame(Testcomponents = c("creatinine", "blood", "potassium"))

Expected output

Test Components          lonic_code
creatinine                27816-8-O
 blood                      1869-7
potassium                    NA

I would really appreciate if you could help me with the code? i have a list of 342 names in a data frame for which i need to map the lonic_code values from another data frame having around 1000 names. — ajax, Jan 31 '18 at 06:52
as @Onyambu said, it is better to also show an expected output for others to help you better — akrun, Jan 31 '18 at 07:09

Florian · Answer 1 · 2018-01-31T10:01:19.670

2

Here is a possible solution. Probably not the most beautiful one, so curious to see other solution approaches.

df1 = data.frame(name = c("(CKMB)Creatinine Kinase Muscle & Brain", "24 Hours Urine for Sodium", "Antistreptolysin O Titer", "Blood group O"), lonic_code = c("27816-8-O", "27816-8-B", "1869-7", "33914-3"))
df2 = data.frame(Testcomponents = c("creatinine", "blood", "potassium"))

result = lapply(sapply(df2$Testcomponents,function(x) {
  which(sapply(df1$name,function(y) {grepl(x,y,ignore.case = T)}))}),function(z) {df1$lonic_code[z]})

df2$Ionic_code= result

Output:

  Testcomponents Ionic_code
1     creatinine          3
2          blood          4
3      potassium

edited Jan 31 '18 at 10:01

answered Jan 31 '18 at 07:05

Florian

24,425
4
49
80

Thanks Florian. I will run the code and will let you know. Thank you for your time. – ajax Jan 31 '18 at 07:24
I applied this to the datasets i have, but the lonic_code column is showing all NULL values. – ajax Jan 31 '18 at 08:51
That is strange, since it does reproduce your expected output? Is your sample data given here the same as in your real data? Are there upper case-lower case mismatches? In that case you could do `grepl(x,y,ignore.case = T`. Otherwise, please add a better [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). – Florian Jan 31 '18 at 09:07
I have edited the question adding the expected output. – ajax Jan 31 '18 at 09:54
Updated the answer, switched up df1 and df2 based on your previous example, and added the ignore.case parameter. Hope this helps! – Florian Jan 31 '18 at 10:01

score 2 · Accepted Answer · answered Jan 31 '18 at 11:19

regex_right_join could be handy in this case.

library(fuzzyjoin)
library(dplyr)

df1 %>%
  mutate(name = as.character(name)) %>%
  regex_right_join(df2 %>%
                     mutate(Testcomponents = as.character(Testcomponents)), 
                   by = c(name = "Testcomponents"), ignore_case = T) %>%
  select(Testcomponents, lonic_code)

Output is:

  Testcomponents lonic_code
1     creatinine  27816-8-O
2          blood    33914-3
3      potassium       <NA>

Sample data:

df1 <- structure(list(name = structure(1:4, .Label = c("(CKMB)Creatinine Kinase Muscle & Brain", 
"24 Hours Urine for Sodium", "Antistreptolysin O Titer", "Blood group O"
), class = "factor"), lonic_code = structure(c(3L, 2L, 1L, 4L
), .Label = c("1869-7", "27816-8-B", "27816-8-O", "33914-3"), class = "factor")), .Names = c("name", 
"lonic_code"), row.names = c(NA, -4L), class = "data.frame")

df2 <- structure(list(Testcomponents = structure(c(2L, 1L, 3L), .Label = c("blood", 
"creatinine", "potassium"), class = "factor")), .Names = "Testcomponents", row.names = c(NA, 
-3L), class = "data.frame")

However, using the above code i couldn't map some of the lonic_code values. — ajax, Feb 01 '18 at 05:42
If you could update your post with the troubling (sample) 'lonic_code' as `dput(df1)` & `dput(df2)` then we can look into it. — Prem, Feb 01 '18 at 05:50
Thank for your time! Really appreciate it! I figure out the rest!! — ajax, Feb 01 '18 at 07:26

score 1 · Answer 3 · answered Jan 31 '18 at 10:03

This is a little more code than Florian's answer, however, I think it makes up for it by being easier to read:

df1 = data.frame(Testcomponent = c("Albumin", "HDL Cholesterol",
                                   "Erythrocyte Sedimentation Rate (ESR)", "Thyroid-stimulating Hormone (TSH)"))

df2 = data.frame(Names = c("Micro Albumin", "Serum Globulin", "CMV Antibody (IgG)"), lonic_code = c("10501-5", "5196", "EKC 1"))

get.test.component <- function(component.name) {
  component <- grep(component.name, df2$Names)
  if (length(component) == 0) {
    return (NA)
  } else {
    return (as.character(df2$lonic_code[component]))
  }
}

new.ionic.codes <- Reduce(c, lapply(df1$Testcomponent, function(x) get.test.component(x)))
df1.new <- cbind(df1, new.ionic.codes)

score 1 · Answer 4 · answered Jan 31 '18 at 11:34

You can use sapply to loop over the Testcomponents:

df2$lonic_code <- sapply(tolower(df2$Testcomponents), function(x) 
                     df1$lonic_code[grep(x, tolower(df1$name), fixed = TRUE)[1L]])

df2
#  Testcomponents lonic_code
#1     creatinine  27816-8-O
#2          blood    33914-3
#3      potassium       <NA>

In case of multiple matches, this will always return only the first match.

This should be reasonably fast since it only uses a single loop and because we specify fixed = TRUE in grep. For further speed improvement you could use the stringi package's regex functions.

Matching strings in a column of a data frame with the strings in a column of another data frame using R or Python

4 Answers4