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')
Related Posts
About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 8 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Telecom and Human Resource.

3 Responses to "Joining and Merging in R"
  1. I have two files having different number of rows and column. I am trying to apply merge function because i was applying vlookup in excel. Still I am not getting the desired result in merge function which I was getting in vlookup function in excel. Please suggest me the solution. Also find the code which I am trying to run:

    file1$colname<-merge(file1,file2, by = "Symbol")

    ReplyDelete
    Replies
    1. Try this :

      file1$colname<-merge(file1,file2, by = "Symbol", all.x = TRUE)

      Delete

Next → ← Prev
Love this Post? Spread the Word!
Share