Partial Matching two data frames having a common column(by words) in R/Python

982 views Asked by At

I have two dataframes as csv files where df1 has more rows than df2:

Df1

Name                         Count
xxx yyyyyy bbb cccc           15
fffdd 444 ggg                 20
kkbbb ccc dd 29p              5
22 cc pbc2 kmn3 b23 efgh      4
ccccccccc sss qqqq            2

Df2

Name
xxx yyyyyy bbb cccc
ccccccccc sss qqqq pppc
22 cc pbc2 kmn3 b23,efgh

I want to do partial matching(approximate/fuzzy matching) by matching either first two/three words. Basically the output will be like this:

Output:

Name                       Count
xxx yyyyyy bbb cccc         15
22 cc pbc2 kmn3 b23 efgh    4
ccccccccc sss qqqq          2

By trying exact matching, I'm missing some of the rows. I tried with agrep in R but somehow its not working and fuzzy matching is quite slow. Please suggest me a way to do this in R or python. Any help is appreciated!

1

There are 1 answers

10
Sven Hohenstein On BEST ANSWER

In R, you can use agrep for fuzzy matching. You can use the max.distance parameter to set the maximum distance allowed for a match.

DF1[sapply(DF2$Name, agrep, DF1$Name, max.distance = 0.2), ]

#                       Name Count
# 1      xxx yyyyyy bbb cccc    15
# 5       ccccccccc sss qqqq     2
# 4 22 cc pbc2 kmn3 b23 efgh     4

The data:

DF1 <- read.table(text = "Name                         Count
'xxx yyyyyy bbb cccc'           15
'fffdd 444 ggg '                20
'kkbbb ccc dd 29p'              5
'22 cc pbc2 kmn3 b23 efgh'      4
'ccccccccc sss qqqq'           2", header = TRUE, stringsAsFactors = FALSE)

DF2 <- read.table(text = "Name
'xxx yyyyyy bbb cccc'
'ccccccccc sss qqqq pppc'
'22 cc pbc2 kmn3 b23,efgh'", header = TRUE, stringsAsFactors = FALSE)