Match partial numeric values in R data frame column with values in another column

user_7576640 0
Viewed 42

I have a R data frame df1 that looks like below:

Product       new_ID
Prod1         129000007
Prod2         7432309490
Prod3         1708289014
Prod4         4741602975
Prod5         906485301012

And another one, df2, which looks like:

Brand         old_ID
Brand1        13554998333
Brand2        17432309490
Brand3        14300012960
Brand4        14741602975
Brand5        2710420383988

To give some context, the data comes from two different databases where product codes (columns new_ID and old_ID respectively) are represented slightly differently. For example, Prod 2 and Brand 2 are the same with one extra digit in the old_ID column value compared to the value in new_ID. Same for Prod 4 and Brand 4. Note that all the codes in new_ID are not in old_ID.

Edit: Also note that the difference between the new_ID and old_ID value is not always that of the leading digit. Sometimes the first and last digit of an old_ID value is dropped to get new_ID value.

So I want to find all the rows in df2 which contain the products in df1 using the fields old_ID and new_ID. I could think of matching the new_ID value in old_ID using grepl. But I think that can be done only one at a time.

Is there a better way to find a match of a vector of new_ID values in the old_ID column?

match dataframe r
1 Answers

If for sure the difference is only an extra leading digit then you can just remove the leading extra digit and use merge to find matches:

df1 <- data.frame(Product = c("Prod1",  
                              "Prod2",   
                              "Prod3",   
                              "Prod4",   
                              "Prod5"),
                  new_ID = c("129000007",
                             "7432309490",
                             "1708289014",
                             "4741602975",
                             "906485301012"))
df2 <- data.frame(Brand = c("Brand1",
                            "Brand2",
                            "Brand3",
                            "Brand4",
                            "Brand5"),
                  old_ID = c("13554998333",
                             "17432309490",
                             "14300012960",
                             "14741602975",
                             "2710420383988"))
df2$new_ID <- sub("^.", "", df2$old_ID)
matches <- merge(df1, df2, by = "new_ID")

which gives:

matches
#      new_ID Product  Brand      old_ID
#1 4741602975   Prod4 Brand4 14741602975
#2 7432309490   Prod2 Brand2 17432309490

EDIT: If there s always a leading extra digit and potentially other stuff then fuzzyjoin can do the job. I added Prod6 and Brand6 with another final digit

df1 <- data.frame(Product = c("Prod1",  
                              "Prod2",   
                              "Prod3",   
                              "Prod4",   
                              "Prod5",   
                              "Prod6"),
                  new_ID = c("129000007",
                             "7432309490",
                             "1708289014",
                             "4741602975",
                             "906485301012",
                             "123456789"))
df2 <- data.frame(Brand = c("Brand1",
                            "Brand2",
                            "Brand3",
                            "Brand4",
                            "Brand5",
                            "Brand6"),
                  old_ID = c("13554998333",
                             "17432309490",
                             "14300012960",
                             "14741602975",
                             "2710420383988",
                             "11234567892"))
#comment next line if leading digit is not always added...
df2$new_ID <- sub("^.", "", df2$old_ID)

require(fuzzyjoin)

#put max_dist = 2 if not always first extra digit 
matches <- stringdist_inner_join(df1, df2, by = "new_ID", method = "lcs", max_dist = 1) 
matches
  Product   new_ID.x  Brand      old_ID   new_ID.y
1   Prod2 7432309490 Brand2 17432309490 7432309490
2   Prod4 4741602975 Brand4 14741602975 4741602975
3   Prod6  123456789 Brand6 11234567892 1234567892
user_34962540
Related