[R-Forge #2186] joins on arbitrary functions #625

Submitted by: Sasha Goodman; Assigned to: Nobody; R-Forge link

I created a discussion here:

http://stackoverflow.com/questions/11900168/could-data-table-do-joins-on-arbitrary-functions-i-e-fuzzy-soft-joins

Here is cut and paste text :
The following is hypothetical. Lets say I have a string similarity function that produces a boolean result (string_sim), and another that determines if the distance between two lat/lon coordinates is below a threshold (geo_dist)

I decide to merge on these conditions using a fuzzy join:

merge(LHS, RHS, by=string_sim(LHS$string, RHS$string) & geo_dist(LHS$lat, LHS$lon, RHS$lat,RHS$lon))
Under the hood, data.table would need to compare the cartesian product...every row to every other asymmetrically. That could easily be a gigantic number, in the trillions with medium sized datasets. Therefore, it might also need to use a divide-and-conquer strategy when sending data to the comparison functions, utilizing multiple processes with under 2 billion cells given to each to avoid integer limits. Essentially, it would need to MAP sections of the vector to pieces and then send them to the function (this is starting to sound like map-reduce)

Let's say the user wises up and wants to save time by iteratively applying join commands, starting with the least costly to run such as an equality condition.

merge(LHS, RHS, by=list(LHS$first_initial == RHS$first_initial, string_sim(LHS$string, RHS$string) & geo_dist(LHS$lat, LHS$lon, RHS$lat,RHS$lon)))
I would like a feature like this. Its taken me some time, but I've put together some code that does this using data.table, and the package could come with something like this in the future