Two by two matching between dataframes in r

Question

I need to combine two data frames (df1 and df2) by matching up two site columns of each data frame to produce a third data frame (df3).

df1 = data.frame(Site.1=c("A","A","B"),
                 Site.2=c("B","C","C"),
                 Score1=c(60,70,80))
df1

 Site.1 Site.2 Score1
1      A      B     60
2      A      C     70
3      B      C     80

df2 = data.frame(Site.1=c("B","A","A"),
                 Site.2=c("C","B","C"),
                 Score2=c(10,20,30))
df2

  Site.1 Site.2 Score2
1      B      C     10
2      A      B     20
3      A      C     30

df3 = data.frame(Site.1=c("A","A","B"),
                 Site.2=c("B","C","C"),
                 Score1=c(60,70,80),
                 Score2=c(20,30,10))
df3

  Site.1 Site.2 Score1 Score2
1      A      B     60     20
2      A      C     70     30
3      B      C     80     10

There are many similar questions about merging data.frame in SO...make some effort and use the search engine!!! — dickoa, Aug 25 '12 at 15:04

score 4 · Accepted Answer · answered Aug 25 '12 at 15:03

You want the merge function. Since your column names that you want to match on already have the same name you don't even need to do anything special. If that wasn't the case you would want to look into the by.x and by.y parameters that merge takes.

df1 = data.frame(Site.1=c("A","A","B"),Site.2=c("B","C","C"),Score1=c(60,70,80))
df2 = data.frame(Site.1=c("B","A","A"),Site.2=c("C","B","C"), Score2=c(10,20,30))
df3 = data.frame(Site.1=c("A","A","B"),Site.2=c("B","C","C"), Score1=c(60,70,80),Score2=c(20,30,10))
df3

# Merge gives you what you want
merge(df1, df2)

You can also specify columns with numbers, e.g., `by=1:2`. – IRTFM Aug 25 '12 at 20:11 — IRTFM, Aug 25 '12 at 20:11

score 0 · Answer 2 · answered Dec 02 '19 at 17:13

dplyr may be helpful here.

library(dplyr)   

df1 = data.frame(Site.1 = c("A", "A", "B"),
                 Site.2 = c("B", "C", "C"),
                 Score1 = c(60, 70, 80))


df2 = data.frame(Site.1 = c("B", "A", "A"),
                 Site.2 = c("C", "B", "C"),
                 Score2 = c(10, 20, 30))


inner_join(df1, df2)

Two by two matching between dataframes in r

2 Answers2

Linked