0
$\begingroup$

I have two data sets, both with dog breed names. I'd like to combine the data sets somehow, but a lot of dogs have multiple names, so for instance African Hairless Dogs are also called African Abyssinian Terriers.

I think these names are too different to match by the similarity of their words, so I'm wondering what's the best way to match them.

So for instance, suppose data set 1 is

Breed                           Height (inches)
------------------------------------------------                 
African Hairless Dog            17 
Golden Retriever                20 
Labrador Retriever              22

And data set 2 is

Breed                           Weight (pounds)
------------------------------------------------               
African Abyssinian Terrier      30 
Golden Retriever                60 
Labrador Retriever              65

And I'd like a data set like the following below:

Breed                           Height (inches)           Weight (pounds)
-------------------------------------------------------------------------              
African Hairless Dog            17                        30
Golden Retriever                20                        60
Labrador Retriever              22                        65
$\endgroup$
1
$\begingroup$

Here is a solution using tidyverse:

# library
library(tidyverse)


# load test data
set1 <- tibble(Breed=c("African Hairless Dog","Golden Retriever","Labrador Retriever"), 
                    Height=c(17,20,22))


set2 <- tibble(Breed=c("Abyssinian Terriers","Golden Retriever","Labrador Retriever"),
                   Weight=c(30,60,65))


# make a lookup table (you should add here otherBreeds and Nicknames)
lookup <- tibble(Breed=c("African Hairless Dog","other Breed"),
                 Nicknames=list(c("Abyssinian Terriers","other Nickname","other Nickname"),
                                c("Nickname1","Nickname2","Nickname3")))


#prepare the lookup table                 
lookup %>% unnest(cols=c(Nicknames)) -> lookup



# apply the lookup table to your set2
set2 %>%
  left_join(lookup, by=c("Breed"="Nicknames")) %>% 
  mutate(Breed=case_when(is.na(Breed.y)~Breed, TRUE ~Breed.y)) %>% 
  select(Breed,Weight) -> set2_new

# combine your data  
left_join(set1,set2_new)
#> Joining, by = "Breed"
#> # A tibble: 3 x 3
#>   Breed                Height Weight
#>   <chr>                 <dbl>  <dbl>
#> 1 African Hairless Dog     17     30
#> 2 Golden Retriever         20     60
#> 3 Labrador Retriever       22     65

Created on 2020-04-05 by the reprex package (v0.3.0)

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.