How to re-use the fuzzy-matching (for address) code built with apply/sapply, in a data.frame's groupby setup i.e. match within groups?

user_4778964 0
Viewed 39

I'm trying to group a list of addresses for a bunch of individuals—an individual can have more than 1 address mapped to him—while addresses are captured in the system with all manual inconsistencies e.g. typo (or) additional info/title in some versions of same address.

library(tidyverse)
df <- tibble(
  individuals = c(1, 1, 1, 1, 2, 2),
  addresses = c(
    'king st toronto',
    'queen st',
    'king toronto',
    'broadway st',
    'broadway ave',
    'attn: broadway ave'
  )
)

It doesn't matter which one of an address' variation I'm choosing finally, but all that is required is, group/recognize them as ONE same address, say, in a new column.

I used Levenshtein edit distance, along with baseR's apply and sapply as shown below to do fuzzy matching, and then map to 1 unique address(in fuzzy sense) per individual (here I picked the variation with fewer characters but any one representation is okay).

matches <-
  sapply(df[['addresses']], function(pattern)
    agrepl(pattern, df[['addresses']], max.distance = 0.3))

apply(matches , 1, function(arg)
  df[['addresses']][arg][which.min(nchar(df[['addresses']][arg]))])

This code works as stand-alone for 1 group, but I'm not able to generalize it to entire data.frame with multiple groups, say in a dplyr/groupby setup. I tried using plyr:ddply(data.frame, .(groupby_var), <FUNCTION>) but ran into error 'Error in apply() dim(X) must have a positive length'.

Expected Output:

individuals addresses
1 king toronto
1 queen st
1 king toronto
1 broadway st
2 broadway ave
2 broadway ave
dplyr sapply r
1 Answers

Here's one option that might help you out; I create a new column of all addresses that match the address given within a certain distance by pasting together the similar addresses. If you only want one address then you can subset the string up to the comma-space separator. Then you can group by the combined name and do your normal tidyverse functions on each group of addresses, or group by individual and paste together all the possible addresses for them.

Interestingly, the Levenshtein edit distance is non-symmetrical so you may run into problems with it. For example, "attn: broadway ave" is within 0.3 of "broadway st" but "broadway st" is NOT within 0.3 of "attn: broadway ave". I bumped up your distance to 0.5 in the example below but you'll likely run into this problem elsewhere.

df %>%
  rowwise() %>%
  mutate(agrep_matches=list(agrep(addresses, df$addresses, max.distance = 0.5))) %>%
  mutate(other_names=paste(df$addresses[unlist(agrep_matches)], collapse = ", "))

# A tibble: 6 x 4
# Rowwise: 
  individuals addresses          agrep_matches other_names                                  
        <dbl> <chr>              <list>        <chr>                                        
1           1 king st toronto    <int [2]>     king st toronto, king toronto                
2           1 queen st           <int [1]>     queen st                                     
3           1 king toronto       <int [2]>     king st toronto, king toronto                
4           1 broadway st        <int [3]>     broadway st, broadway ave, attn: broadway ave
5           2 broadway ave       <int [3]>     broadway st, broadway ave, attn: broadway ave
6           2 attn: broadway ave <int [3]>     broadway st, broadway ave, attn: broadway ave
user_78354350
Related