CODEWITHSUNDEEP
r
ScoutingForJay
ScoutingForJay

Reputation: 109

Matching 2 DF's by 2 columns R

If I have 2 DF's with the same values in, but in different orders can I create a 3rd DF with values of the 2nd DF matching the first?

Ex: 

HomeTeam <- c("Liverpool", "Southampton", "Arsenal", "Everton", "Chelsea")
AwayTeam <- c("Burnley", "Brighton", "Man United", "Tottenham", "Man City")

df1 <- tibble(HomeTeam, AwayTeam)

HomeTeam <- c("Southampton", "Arsenal", "Chelsea", "Liverpool", "Everton")
AwayTeam <- c("Brighton", "Man United", "Man City", "Burnley", "Tottenham")


df2 <- tibble(HomeTeam, AwayTeam)


HomeTeam <- c("Liverpool", "Southampton", "Arsenal", "Everton", "Chelsea")
AwayTeam <- c("Burnley", "Brighton", "Man United", "Tottenham", "Man City")

df3 <- tibble(HomeTeam, AwayTeam)

Both DF's consist of thousands of rows of data, yet out of order. Thanks.

Upvotes: 1

Views: 60

Answers (2)

akrun
akrun

Reputation: 887118

We could use match to get the index for arrangeing the rows

library(dplyr)
df2 %>% 
    arrange(match(HomeTeam, df1$HomeTeam), match(AwayTeam, df1$AwayTeam))

Also, if there are more columns, it may be better to paste those columns and do a single match

library(stringr)
df2 %>%
     arrange(match(str_c(HomeTeam, AwayTeam), str_c(df1$HomeTeam, df1$AwayTeam)))

NOTE: For big datasets, match would be potentially faster than a join as we are extracting a single column or vector from the other dataset to be used in the match

Or another option is data.table join which would be fast as well

library(data.table)
setDT(df2)[setDT(df1), on = .(HomeTeam, AwayTeam)]
#     HomeTeam   AwayTeam
#1:   Liverpool    Burnley
#2: Southampton   Brighton
#3:     Arsenal Man United
#4:     Everton  Tottenham
#5:     Chelsea   Man City

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 388982

We can left_join/inner_join df1 with df2 to get df2 data in same order as df1. 

dplyr::left_join(df1, df2, by = c("HomeTeam", "AwayTeam"))

# A tibble: 5 x 2
#  HomeTeam    AwayTeam  
#  <chr>       <chr>     
#1 Liverpool   Burnley   
#2 Southampton Brighton  
#3 Arsenal     Man United
#4 Everton     Tottenham 
#5 Chelsea     Man City

Upvotes: 1

Related Questions