Find matches between unique pairs of two dataframe

问题:

I have two dataframes dat1 and dat2 and I would like to find matches between the first two columns of the two dataframes and join the values contained in each dataframe for unique pairs.

dat1<-data.frame(V1 = c("home","fire","sofa","kitchen"), 
                V2 = c("cat","water","TV","knife"), V3 = c('date1','date2','date3','date4'))

       V1    V2    V3
1    home   cat date1
2    fire water date2
3    sofa    TV date3
4 kitchen knife date4

dat2<-data.frame(V1 = c("home","water","sofa","knife"), 
                 V2 = c("cat","fire","TV","kitchen"), V3 = c('col1','col2','col3','col4'))

 V1      V2   V3
1  home     cat col1
2 water    fire col2
3  sofa      TV col3
4 knife kitchen col4

The required result would be:

 V1      V2    V3   V4
1  home     cat date1 col1
2 water    fire date2 col2
3  sofa      TV date3 col3
4 knife kitchen date4 col4

Any idea on how to do it in R?

回答1:

You can create a new column which contains the words in the same order and join based off that column:

library(dplyr)

dat2 %>% 
  mutate_if(is.factor, as.character) %>% 
  mutate(x = ifelse(V1 > V2, paste(V1, V2), paste(V2, V1))) %>% 
  inner_join(
    dat1 %>% 
      mutate_if(is.factor, as.character) %>% 
      mutate(x = ifelse(V1 > V2, paste(V1, V2), paste(V2, V1))) %>% 
      select(-V1, -V2),
    by = "x"
  ) %>% 
  select(V1, V2, V3 = V3.y, V4 = V3.x)
#     V1      V2    V3   V4
#1  home     cat date1 col1
#2 water    fire date2 col2
#3  sofa      TV date3 col3
#4 knife kitchen date4 col4