Combining rows using fuzzy matching of the keys in R

user_167726 0
Viewed 112

I have a data set that might contain some very similar keys - something like a row of data for each of the email address john.doe@foo.com and john.m.doe@foo.com. How can I combine similarly named keys and do an aggregate in R?

Sample input

|Email              | Subscriptions |
-------------------------------------
|john.doe@foo.com   | 10            |
|john.m.doe@foo.com | 11            |
|jane.doe@foo.com   | 20            |

Expected result

|Email              | Subscriptions |
-------------------------------------
|john.doe@foo.com   | 21            |
|jane.doe@foo.com   | 20            |

I know agrep and few other libraries can do fuzzy matching, but how do I employ it in combining rows in a data set?

cluster-analysis r
1 Answers

Here is one way to use agrep in combination with dplyr:

df <- data.frame(mail = c("john.doe@foo.com", "john.m.doe@foo.com", "jane.doe@foo.com"),
                 sub = c(10, 11, 20))

df %>% 
  rowwise() %>% 
  mutate(new = paste(agrep(mail, df$mail, max = 2, ignore.case = TRUE), collapse = ",")) %>%
  group_by(new) %>% 
  mutate(sub = sum(sub)) %>%
  slice(1) 


  mail               sub new  
  <fct>            <dbl> <chr>
1 john.doe@foo.com    21 1,2  
2 jane.doe@foo.com    20 3
user_141700970
Related