1

I have a dataframe (relationship) in R containing relationships between animals: 

animal1 | animal2 | relationship  
dog       cat       friendly
cat       dog       amicable
pig       goose     mean

I have another dataframe (animal) containing info about each animal:

id | animal | count   
1   dog       2      
2   cat       5       
3   pig       1     
4   goose     2

I want to replace the animals in the first dataframe with the values of their IDs in the second data frame, i.e.

animal1 | animal2 | relationship  
1       | 2       | friendly
2       | 1       | amicable
3       | 4       | mean

How do I do this? So far, I am able to use grepl to do this for an individual item, i.e.

which(grepl(relationship$animal1[3],animal$id)) >>>> 2

How can I apply this generally over the whole relationship data frame and replace the animal1/animal2 columns with the result?

Jaap
  • 81,064
  • 34
  • 182
  • 193
epr8n
  • 45
  • 1
  • 7

1 Answers1

1

Here is an option using tidyverse. We gather the data to 'long' format, left_join with the second dataset, change the 'animal' column with the values of 'id' and spread it to 'wide' format

library(tidyverse)
gather(df1, key, animal, animal1:animal2) %>%
        left_join(df2[-3]) %>% 
        mutate(animal = id) %>% 
        select(-id)  %>% 
        spread(key, animal) %>%
        select(names(df1))

Or another option without doing the reshaping in base R would be to loop through the first two column, do a match with the 'animal column of 'df2' and get the corresponding 'id's, assign it back to the columns of interest

df1[1:2] <- lapply(df1[1:2], function(x) df2$id[match(x, df2$animal)])
df1
#   animal1 animal2 relationship
#1       1       2     friendly
#2       2       1     amicable
#3       3       4         mean

Or the similar approach with dplyr would be

df1 %>% 
    mutate_at(vars(matches("animal")), funs(df2$id[match(., df2$animal)]))
akrun
  • 874,273
  • 37
  • 540
  • 662