Joining by multiple columns with stringdist_join

user_13349937 0
Viewed 1321

I have two dataframes, where column x can have typos and column y is always correct. I can't figure out why joining by multiple columns with stringdist gives these pairs:

library(dplyr)
library(fuzzyjoin)
a <- data.frame(x = c("season", "season", "season", "package", "package"), y = c("1","2", "3", "1","6"))

b <- data.frame(x = c("season", "seson", "seson", "package", "pakkage"), y = c("1","2", "3", "2","6"))

c <- a %>%
  stringdist_left_join(b, by = c("x", "y"), max_dist = c(1,0))

      x.x y.x     x.y  y.y
1  season   1  season    1
2  season   1   seson    2
3  season   1   seson    3
4  season   2   seson    2
5  season   3  season    1
6  season   3   seson    2
7  season   3   seson    3
8 package   1 package    2
9 package   6    <NA> <NA>

I would like to get

      x.x y.x     x.y  y.y
1  season   1  season    1
2  season   2   seson    2
3  season   3   seson    3
4 package   1    <NA> <NA>
5 package   6 pakkage    6
left-join r join
2 Answers

We could make this work by creating a new column based on the similarity of column values in 'x' columns in both dataset and then do a left_join

library(stringdist)
library(dplyr)
a %>%
    mutate(grp = phonetic(x)) %>%
   left_join(b %>% mutate(grp = phonetic(x), y2 = y), by = c('grp', 'y')) %>% 
   select(-grp)

-output

#      x.x y     x.y   y2
#1  season 1  season    1
#2  season 2   seson    2
#3  season 3   seson    3
#4 package 1    <NA> <NA>
#5 package 6 pakkage    6

Or other option is to change the method in stringdist_left_join from its default option (osa -> Optimal string aligment, (restricted Damerau-Levenshtein distance).) to soundex (Distance based on soundex encoding)

library(fuzzyjoin)
a %>%
   stringdist_left_join(b, by = c("x", "y"), max_dist = c(1,0), 
            method = "soundex")
#      x.x y.x     x.y  y.y
#1  season   1  season    1
#2  season   2   seson    2
#3  season   3   seson    3
#4 package   1    <NA> <NA>
#5 package   6 pakkage    6

According to ?"stringdist-metrics"

For the soundex distance (method='soundex'), strings are translated to a soundex code (see phonetic for a specification). The distance between strings is 0 when they have the same soundex code, otherwise 1. Note that soundex recoding is only meaningful for characters in the ranges a-z and A-Z. A warning is emitted when non-printable or non-ascii characters are encountered.

user_37322710

cbind can reproduce your desired output.

cbind(a,b)
       x y      x y
1 season 1 season 1
2 season 2  seson 2
3 season 3  seson 3
4 season 4  seson 4
5 season 6  seson 6

EDIT

If a differs from b in the number of rows, you can try full_join from dplyr.

full_join(a,b, by = "y")
     x.x y    x.y
1 season 1 season
2 season 2  seson
3 season 3  seson
4 season 4  seson
5 season 6  seson
user_148121700
Related