fuzzy join with sqldf

Question

I was expecting this code to return a data.frame with name = helicase in row 1. How can I make this type of comparison with sqldf?

require(data.table)
df <- fread('EC              name
    2.1.1.233       helicase
    4.1.3.3         phosphatase
    1.3.2.1,2.5.4.1 lipase
')
df2 <- fread('     PDB     EC         
    1uay    2.1.1
    5ad4    4.1.3.3   
')


require(sqldf)

sqldf('
select  df2.*
        , df.name
from    df2
        left join df
          on df2.EC like df.EC
')

#    PDB      EC        name
# 1 1uay   2.1.1        <NA>
# 2 5ad4 4.1.3.3 phosphatase

What is the default `drv` in your case as I get error with the code `Error in rsqlite_send_query(conn@ptr, statement) : no such column: df2.EC` — akrun, Feb 14 '18 at 15:28
Ok, I think the example is creating V1, V2 as column for df2 as there are some white space — akrun, Feb 14 '18 at 15:33

score 2 · Accepted Answer · answered Feb 14 '18 at 15:36

2

Borrowing from a similar answer here, you want to concatenate the percent signs:

sqldf("
select  df2.*
        , df.name
from    df2
        left join df
          on like('%'||df2.EC||'%', '%'||df.EC||'%')")

answered Feb 14 '18 at 15:36

David Klotz

2,401
1
7
16

fuzzy join with sqldf

1 Answers1