Joining and Merging in R

This tutorial explains how we can join (merge) two tables in R.

Let's create two tables - 

Table I : DF1
df1 <- data.frame(ID = c(1, 2, 3, 4, 5),
                 w = c('a', 'b', 'c', 'd', 'e'),
                 x = c(1, 1, 0, 0, 1),
                 y=rnorm(5),
                 z=letters[1:5])

ID w x y z
1 a 1 -1.250974459 a
2 b 1 1.234389053 b
3 c 0 0.796469701 c
4 d 0 -0.004735964 d
5 e 1 -0.729994828 e

Table II : DF2
df2 <- data.frame(ID = c(1, 7, 3, 6, 8),
                   a = c('z', 'b', 'k', 'd', 'l'),
                   b = c(1, 2, 3, 0, 4),
                   c =rnorm(5),
                   d =letters[2:6])
ID a b c d
1 z 1 0.9367346 b
7 b 2 -2.3464766 c
3 k 3 0.8259913 d
6 d 0 -0.8663029 e
8 l 4 -0.482444 f
Inner Join
df3 = merge(df1, df2, by ="ID")
If the primary key (matching variable) do not have same name in both the tables (data frames),
df3 = merge(df1, df2, by.x ="ID", by.y="ID")
Left Join
df4 = merge(df1, df2, by ="ID", all.x = TRUE)
Right Join
df5 = merge(df1, df2, by ="ID", all.y = TRUE)
Full (Outer) Join
df6 = merge(df1, df2, by ="ID", all = TRUE)
Cross Join
df7 = merge(df1, df2, by = NULL)

With SQL Joins
library(sqldf)
df9 = sqldf('select df1.*, df2.* from df1 left join df2 on df1.ID = df2.ID')

Best Online Course : Practical Data Science using R

- Explain Advanced Algorithms in Simple English
- Live Projects & Case Studies
- Domain Knowledge
- Job Placement Assistance
- Money Back Guarantee


R Tutorials : 75 Free R Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

1 Response to "Joining and Merging in R"

Next → ← Prev