Joining and Merging in R


R Data Science: R Programming A-Z: R For Data Science With Real Exercises!

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')
Coursera Data Science

R Tutorials : 75 Free R Tutorials

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