Lookup string with substring key R

user_272594 0
Viewed 298

I have a long list of strings, that share substrings. The list comes from event stream data, so there are tens of thousands of rows, but I'll simplify for this example; Pets:

+--------------------------------+
|              Pets              |
+--------------------------------+
| "one calico cat that's smart"  |
| "German Shepard dog"           |
| "A Chameleon that is a Lizard" |
| "a cute tabby cat"             |
| "the fish guppy"               |
| "Lizard Gecko"                 |
| "German Shepard dog"           |
| "Budgie Bird"                  |
| "Canary Bird in a coal mine"   |
| "a chihuahua dog"              |
+--------------------------------+
dput output: structure(list(Pets = structure(c(8L, 6L, 1L, 3L, 9L, 7L, 6L, 4L, 5L, 2L),.Label = c("A Chameleon that is a Lizard", "a chihuahua dog", "a cute tabby cat", "Budgie Bird", "Canary Bird in a coal mine", "German Shepard dog", "Lizard Gecko", "one calico cat that's smart", "the fish guppy"), class = "factor")), .Names = "Pets", row.names = c(NA,  -10L), class = "data.frame")

I want to add information based on the generic type of pet (dog, cat, etc) and I have a key table that holds this information:

+----------+----------------+
|   key    | classification |
+----------+----------------+
| "dog"    | "canine"       |
| "cat"    | "feline"       |
| "lizard" | "reptile"      |
| "bird"   | "avian"        |
| "fish"   | "fish"         |
+----------+----------------+
dput output: structure(list(key = structure(c(3L, 2L, 5L, 1L, 4L), .Label = c("bird", "cat", "dog", "fish", "lizard"), class = "factor"), classification = structure(c(2L, 3L, 5L, 1L, 4L), .Label = c("avian", "canine", "feline", "fish", "reptile"), class = "factor")), .Names = c("key", "classification"), row.names = c(NA, -5L), class = "data.frame")

How do I use the "long string" from the Pets table to find relevant classification in the key table? The issue is, my lookup string contains the substring that is found in the key table.

I'm started by using grepl like this:

key[grepl(pets[1,1], key[ , 2]), ]

But this won't work because "calico cat" isn't in the key list, though "cat" is. The result I'm looking for would be "feline".

(Note: I can't simply switch things around because, in my own code, this sits within an apply function and loops through each row in the data. so, instead of pets[1,1] it's pets[n,1] In the end I intend to cbind the results onto the event stream data to do further analysis.)

I'm having trouble wrapping my head around how to do this. Any advice?

substring r
3 Answers

You can use the package fuzzyjoin to do these sorts of things very easily.

Here you can use regex_left_join, which works just like a normal left join (eg dplyr::left_join), except that that the criteria for rwos being a match is determined by regular expression matching like stringr::str_detect

library(tibble)
library(fuzzyjoin)

pets <- tribble(
                            ~pets,
   "one calico cat that\'s smart",
             "German Shepard dog",
   "A Chameleon that is a Lizard",
               "a cute tabby cat",
                 "the fish guppy",
                   "Lizard Gecko",
             "German Shepard dog",
                    "Budgie Bird",
     "Canary Bird in a coal mine",
                "a chihuahua dog"
)

key <- tribble(
       ~key, ~classification,
      "dog",        "canine",
      "cat",        "feline",
   "lizard",       "reptile",
     "bird",         "avian",
     "fish",          "fish"
)

regex_left_join(pets, key, by = c("pets" = "key"), ignore_case = TRUE)

#> # A tibble: 10 x 3
#>                            pets    key classification
#>                           <chr>  <chr>          <chr>
#>  1  one calico cat that's smart    cat         feline
#>  2           German Shepard dog    dog         canine
#>  3 A Chameleon that is a Lizard lizard        reptile
#>  4             a cute tabby cat    cat         feline
#>  5               the fish guppy   fish           fish
#>  6                 Lizard Gecko lizard        reptile
#>  7           German Shepard dog    dog         canine
#>  8                  Budgie Bird   bird          avian
#>  9   Canary Bird in a coal mine   bird          avian
#> 10              a chihuahua dog    dog         canine
user_70512390

You can construct the list of keys for each Pet and then look them up in the table

Pattern = paste(KeyTable$key, collapse="|")
Pattern = paste0(".*(", Pattern, ").*")
Type = tolower(sub(Pattern, "\\1", ignore.case=TRUE, Pets))
KeyTable$classification[match(Type, KeyTable$key)]
 [1] "feline"  "canine"  "reptile" "feline"  "feline"  "canine"  "fish"   
 [8] "reptile" "canine"  "avian"   "avian"   "canine"

Data

KeyTable = read.table(text="key classification 
dog  canine
cat  feline   
lizard reptile
bird  avian    
fish  fish", 
header=TRUE, stringsAsFactors=FALSE)

Pets  = c("calico cat",
"Shepard dog"  ,
"Chameleon Lizard", 
"calico cat",
"tabby cat",
"chihuahua dog",
"guppy fish",
"Gecko Lizard",
"Shepard dog",
"Budgie Bird",
"Canary Bird" ,
"chihuahua dog")
user_47526750

Here's another method using hashmap:

library(hashmap)

hash_table = hashmap(Lookup$key, Lookup$classification)

Pets %>%
  separate_rows(Pets, sep = " ") %>%
  mutate(class = hash_table[[tolower(Pets)]]) %>%
  na.omit() %>%
  select(Key = Pets, class) %>%
  bind_cols(Pets, .)

Result:

> hash_table
## (character) => (character)
##      [fish] => [fish]     
##      [bird] => [avian]    
##    [lizard] => [reptile]  
##       [cat] => [feline]   
##       [dog] => [canine] 

                           Pets    Key   class
1   one calico cat that's smart    cat  feline
2            German Shepard dog    dog  canine
3  A Chameleon that is a Lizard Lizard reptile
4              a cute tabby cat    cat  feline
5                the fish guppy   fish    fish
6                  Lizard Gecko Lizard reptile
7            German Shepard dog    dog  canine
8                   Budgie Bird   Bird   avian
9    Canary Bird in a coal mine   Bird   avian
10              a chihuahua dog    dog  canine

Data:

Pets = structure(list(Pets = c("one calico cat that's smart", "German Shepard dog", 
                               "A Chameleon that is a Lizard", "a cute tabby cat", "the fish guppy", 
                               "Lizard Gecko", "German Shepard dog", "Budgie Bird", "Canary Bird in a coal mine", 
                               "a chihuahua dog")), .Names = "Pets", row.names = c(NA, -10L), class = "data.frame")


Lookup = structure(list(key = c("dog", "cat", "lizard", "bird", "fish"), 
                        classification = c("canine", "feline", "reptile", "avian", 
                      "fish")), class = "data.frame", .Names = c("key", "classification"
                      ), row.names = c(NA, -5L))
user_51506290
Related