Limiting the amount of fuzzy string comparisons by comparing by subgroup

Question

I have two datasets as follows:

DT1 <- structure(list(Province = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 
2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3), Year = c(2000, 
2000, 2000, 2001, 2001, 2001, 2002, 2002, 2002, 2000, 2000, 2000, 
2001, 2001, 2001, 2002, 2002, 2002, 2000, 2000, 2000, 2001, 2001, 
2001, 2002, 2002, 2002), Municipality = c("Something", "Anything", 
"Nothing", "Something", "Anything", "Nothing", "Something", "Anything", 
"Nothing", "Something", "Anything", "Nothing", "Something", "Anything", 
"Nothing", "Something", "Anything", "Nothing", "Something", "Anything", 
"Nothing", "Something", "Anything", "Nothing", "Something", "Anything", 
"Nothing"), Values = c(0.59, 0.58, 0.66, 0.53, 0.94, 0.2, 0.86, 
0.85, 0.99, 0.59, 0.58, 0.66, 0.53, 0.94, 0.2, 0.86, 0.85, 0.99, 
0.59, 0.58, 0.66, 0.53, 0.94, 0.2, 0.86, 0.85, 0.99)), row.names = c(NA, 
-27L), class = c("tbl_df", "tbl", "data.frame"))

DT2 <- structure(list(Province = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 
2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3), Year = c(2000, 
2000, 2000, 2001, 2001, 2001, 2002, 2002, 2002, 2000, 2000, 2000, 
2001, 2001, 2001, 2002, 2002, 2002, 2000, 2000, 2000, 2001, 2001, 
2001, 2002, 2002, 2002), Municipality = c("Some", "Anything", 
"Nothing", "Someth.", "Anything", "Not", "Something", "Anything", 
"None", "Some", "Anything", "Nothing", "Someth.", "Anything", 
"Not", "Something", "Anything", "None", "Some", "Anything", "Nothing", 
"Someth.", "Anything", "Not", "Something", "Anything", "None"
), `Other Values` = c(0.41, 0.42, 0.34, 0.47, 0.0600000000000001, 
0.8, 0.14, 0.15, 0.01, 0.41, 0.42, 0.34, 0.47, 0.0600000000000001, 
0.8, 0.14, 0.15, 0.01, 0.41, 0.42, 0.34, 0.47, 0.0600000000000001, 
0.8, 0.14, 0.15, 0.01)), row.names = c(NA, -27L), class = c("tbl_df", 
"tbl", "data.frame"))

I am trying to match them as follows, suggested in this link, by Arthur Yip.

library(fuzzyjoin); library(dplyr);
stringdist_join(DT1, DT2, 
                by = "Municipality",
                mode = "left",
                ignore_case = TRUE, 
                method = "jw", 
                max_dist = 10, 
                distance_col = "dist") %>%
  group_by(Municipality.x) %>%
  top_n(1, -dist)

The issue is that the code completely fries my computer, so I would like to split up the code into groups to limit the amount of string comparisons. I tried:

library(fuzzyjoin); library(dplyr);
stringdist_join(DT1, DT2, 
                by = c("Municipality","Year", "State"),
                mode = "left",
                ignore_case = TRUE, 
                method = "jw", 
                max_dist = 10, 
                distance_col = "dist") %>%
  group_by(Municipality.x) %>%
  top_n(1, -dist)

stringdist_join(DT1, DT2, 
                by = "Municipality",
                mode = "left",
                ignore_case = TRUE, 
                method = "jw", 
                max_dist = 10, 
                distance_col = "dist") %>%
  group_by(Municipality, Year, Province) %>%
  top_n(1, -dist)

But both give me the following respective errors:

Error: All columns in a tibble must be vectors.
x Column `col` is NULL.
Run `rlang::last_error()` to see where the error occurred.

And:

Error: Must group by variables found in `.data`.
* Column `Municipality` is not found.
* Column `Year` is not found.
* Column `Province` is not found.
Run `rlang::last_error()` to see where the error occurred.

What would be the proper way to do this?

Arthur Yip · Accepted Answer · 2020-12-08T20:20:25.793

You were on the right track - just a few typos/bugs and you need to finish changing/replacing the column names.

Also, in your first one, you will need to figure out how you want to pick the "best match" based on Municipality.dist, Province.dist, and Year.dist.

Maybe the second one works better if you get the years and provinces sorted out first.


DT1 <- structure(list(Province = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3), Year = c(2000, 2000, 2000, 2001, 2001, 2001, 2002, 2002, 2002, 2000, 2000, 2000, 2001, 2001, 2001, 2002, 2002, 2002, 2000, 2000, 2000, 2001, 2001, 2001, 2002, 2002, 2002), Municipality = c("Something", "Anything", "Nothing", "Something", "Anything", "Nothing", "Something", "Anything", "Nothing", "Something", "Anything", "Nothing", "Something", "Anything", "Nothing", "Something", "Anything", "Nothing", "Something", "Anything", "Nothing", "Something", "Anything", "Nothing", "Something", "Anything", "Nothing"), Values = c(0.59, 0.58, 0.66, 0.53, 0.94, 0.2, 0.86, 0.85, 0.99, 0.59, 0.58, 0.66, 0.53, 0.94, 0.2, 0.86, 0.85, 0.99, 0.59, 0.58, 0.66, 0.53, 0.94, 0.2, 0.86, 0.85, 0.99)), row.names = c(NA, -27L), class = c("tbl_df", "tbl", "data.frame"))

DT2 <- structure(list(Province = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3), Year = c(2000, 2000, 2000, 2001, 2001, 2001, 2002, 2002, 2002, 2000, 2000, 2000, 2001, 2001, 2001, 2002, 2002, 2002, 2000, 2000, 2000, 2001, 2001, 2001, 2002, 2002, 2002), Municipality = c("Some", "Anything", "Nothing", "Someth.", "Anything", "Not", "Something", "Anything", "None", "Some", "Anything", "Nothing", "Someth.", "Anything", "Not", "Something", "Anything", "None", "Some", "Anything", "Nothing", "Someth.", "Anything", "Not", "Something", "Anything", "None"), `Other Values` = c(0.41, 0.42, 0.34, 0.47, 0.0600000000000001, 0.8, 0.14, 0.15, 0.01, 0.41, 0.42, 0.34, 0.47, 0.0600000000000001, 0.8, 0.14, 0.15, 0.01, 0.41, 0.42, 0.34, 0.47, 0.0600000000000001, 0.8, 0.14, 0.15, 0.01)), row.names = c(NA, -27L), class = c("tbl_df", "tbl", "data.frame"))

library(fuzzyjoin); library(dplyr);

stringdist_join(DT1, DT2, 
                by = c("Municipality", "Year", "Province"),
                mode = "left",
                ignore_case = TRUE, 
                method = "jw", 
                max_dist = 10, 
                distance_col = "dist") %>%
    group_by(Municipality.x) %>%
    slice_min(Municipality.dist)
#> # A tibble: 135 x 12
#> # Groups:   Municipality.x [3]
#>    Province.x Year.x Municipality.x Values Province.y Year.y Municipality.y
#>         <dbl>  <dbl> <chr>           <dbl>      <dbl>  <dbl> <chr>         
#>  1          1   2000 Anything        0.580          1   2000 Anything      
#>  2          1   2000 Anything        0.580          1   2001 Anything      
#>  3          1   2000 Anything        0.580          1   2002 Anything      
#>  4          1   2000 Anything        0.580          2   2000 Anything      
#>  5          1   2000 Anything        0.580          2   2001 Anything      
#>  6          1   2000 Anything        0.580          2   2002 Anything      
#>  7          1   2000 Anything        0.580          3   2000 Anything      
#>  8          1   2000 Anything        0.580          3   2001 Anything      
#>  9          1   2000 Anything        0.580          3   2002 Anything      
#> 10          1   2001 Anything        0.94           1   2000 Anything      
#> # ... with 125 more rows, and 5 more variables: `Other Values` <dbl>,
#> #   Municipality.dist <dbl>, Province.dist <dbl>, Year.dist <dbl>, dist <lgl>

stringdist_join(DT1, DT2, 
                by = "Municipality",
                mode = "left",
                ignore_case = TRUE, 
                method = "jw", 
                max_dist = 10, 
                distance_col = "dist") %>%
    group_by(Municipality.x, Year.x, Province.x) %>%
    slice_min(dist)
#> # A tibble: 135 x 9
#> # Groups:   Municipality.x, Year.x, Province.x [27]
#>    Province.x Year.x Municipality.x Values Province.y Year.y Municipality.y
#>         <dbl>  <dbl> <chr>           <dbl>      <dbl>  <dbl> <chr>         
#>  1          1   2000 Anything        0.580          1   2000 Anything      
#>  2          1   2000 Anything        0.580          1   2001 Anything      
#>  3          1   2000 Anything        0.580          1   2002 Anything      
#>  4          1   2000 Anything        0.580          2   2000 Anything      
#>  5          1   2000 Anything        0.580          2   2001 Anything      
#>  6          1   2000 Anything        0.580          2   2002 Anything      
#>  7          1   2000 Anything        0.580          3   2000 Anything      
#>  8          1   2000 Anything        0.580          3   2001 Anything      
#>  9          1   2000 Anything        0.580          3   2002 Anything      
#> 10          2   2000 Anything        0.580          1   2000 Anything      
#> # ... with 125 more rows, and 2 more variables: `Other Values` <dbl>,
#> #   dist <dbl>

Created on 2020-12-07 by the reprex package (v0.3.0)

Thank you very much! It works! I still ran into some memory issues at first. I finally split up both data frames lists by province and used your code in a for-loop. — Tom, Dec 08 '20 at 14:13
Hi Arthur, I have a weird issue with this solution. I was wondering if you perhaps had any idea why this happens. In my actual data, I get that the year of `DT1`, is matched with a different year of `DT2`, where that should be the same year. I thought the grouping would prevent this, but apparently I am missing something. I played around with both `by=` and `group_by`, without the desired result (I end up with an empty list). — Tom, Dec 08 '20 at 18:28
if you use the by in the stringdist_join, then you will get a result that tries to match all years by string distance. This will give you year.dist = 1 for a 2010 <-> 2019 or a 2000 <-> 2020 match etc since only one character change is needed. You could try to do exact joins before using fuzzy join, or try to use fuzzy join multi_match_by where you can specify == for year and stringdist for municipality and province — Arthur Yip, Dec 08 '20 at 19:05
my solutions here may also help: https://stackoverflow.com/questions/44383510/passing-arguments-into-multiple-match-fun-functions-in-r-fuzzyjoinfuzzy-join https://stackoverflow.com/questions/56009863/how-to-fuzzy-join-based-on-multiple-columns-and-conditions/64439813#64439813 https://stackoverflow.com/questions/58442426/how-do-i-do-one-fuzzy-and-one-exact-match-in-a-dataframe/64440492#64440492 https://stackoverflow.com/questions/64321242/fuzzy-and-exact-match-of-two-databases/64440221#64440221 — Arthur Yip, Dec 08 '20 at 19:58
and in this one, you can see you can apply top_n or slice_min twice to choose the best matches according to your criteria https://stackoverflow.com/questions/42749447/r-fuzzy-string-match-to-return-specific-column-based-on-matched-string/44382189#44382189 — Arthur Yip, Dec 08 '20 at 20:15

Limiting the amount of fuzzy string comparisons by comparing by subgroup

1 Answers1