CODEWITHSUNDEEP
rstring-matching
Ian Hargreaves
Ian Hargreaves

Reputation: 35

R: "vlookup" based on partial string matches in R

I have two data frames:

1,

             NAME
1         SMALL H
2          ZITT M
3         SMITH E
4       GLANZEL W
5        HUANG MH
6         THIJS B

and 2,

name                                    address
SIBLEY B                                SOME ADDRESS 1
STEWART C;KOCH A                        SOME ADDRESS 2
HILL GM;LEE A;SMITH E                   SOME ADDRESS 3
DAVIS L                                 SOME ADDRESS 4
MERCIER K;SMITH E;GIBBONE A             SOME ADDRESS 5
DAVIDSON S;BEKIARI A                    SOME ADDRESS 6

I want to be able to match the NAME within the 1st table to instances where there is a string match with name in the 2nd table and then add the data from the ADDRESS column, a little like vlookup. It must also deal with multiple instances of the same name. In the example above, the name SMITH E (different people) would provide a match, giving the below result:

             NAME    ADDRESS 1        ADDRESS 2
1         SMALL H
2          ZITT M
3         SMITH E    SOME ADDRESS 5   SOME ADDRESS 3
4       GLANZEL W
5        HUANG MH
6         THIJS B

Upvotes: 1

Views: 409

Answers (1)

JBGruber
JBGruber

Reputation: 12420

Here is a tidyverse solution. I first clean the second table by splitting up the entries into individual names. The we can use left_join to match the entries:

library(tidyverse)
df2_clean <- df2 %>% 
  mutate(name = str_split(name, ";")) %>% 
  unnest(name)

df1 %>% 
  left_join(df2_clean, by = c("NAME" = "name"))
#>        NAME        address
#> 1   SMALL H           <NA>
#> 2    ZITT M           <NA>
#> 3   SMITH E SOME ADDRESS 3
#> 4   SMITH E SOME ADDRESS 5
#> 5 GLANZEL W           <NA>
#> 6  HUANG MH           <NA>
#> 7   THIJS B           <NA>

If you really want, you can get the two addresses of Smith into two columns but I would suggest to stick to the long format here:

df1 %>% 
  left_join(df2_clean, by = c("NAME" = "name")) %>%
  group_by(NAME) %>% 
  mutate(add_c = row_number()) %>% 
  pivot_wider(id_cols = NAME, names_from = add_c, names_prefix = "address_", values_from = address)
#> # A tibble: 6 x 3
#> # Groups:   NAME [6]
#>   NAME      address_1      address_2     
#>   <chr>     <chr>          <chr>         
#> 1 SMALL H   <NA>           <NA>          
#> 2 ZITT M    <NA>           <NA>          
#> 3 SMITH E   SOME ADDRESS 3 SOME ADDRESS 5
#> 4 GLANZEL W <NA>           <NA>          
#> 5 HUANG MH  <NA>           <NA>          
#> 6 THIJS B   <NA>           <NA>

data

df1 <- read.delim(text = "NAME
SMALL H
ZITT M
SMITH E
GLANZEL W
HUANG MH
THIJS B", stringsAsFactors = FALSE)

df2 <- read.delim(text = "name,address
SIBLEY B,SOME ADDRESS 1
STEWART C;KOCH A,SOME ADDRESS 2
HILL GM;LEE A;SMITH E,SOME ADDRESS 3
DAVIS L,SOME ADDRESS 4
MERCIER K;SMITH E;GIBBONE A,SOME ADDRESS 5
DAVIDSON S;BEKIARI A,SOME ADDRESS 6", sep = ",", stringsAsFactors = FALSE)

Upvotes: 1

Related Questions