Section 44 Merge Data: base::merge

44.1 Function merge

# S3 method for class 'data.frame'
merge(x, y, by = intersect(names(x), names(y)),
      by.x = by, by.y = by, 
      all = FALSE, all.x = all, all.y = all,
      sort = TRUE, suffixes = c(".x",".y"),
      incomparables = NULL, ...)

44.2 SQL equivalent of merge

Argument	SQL
all = FALSE	INNER (NATURAL) JOIN
all.x = TRUE	LEFT (OUTER) JOIN
all.y = TRUE	RIGHT (OUTER) JOIN
all = TRUE	FULL (OUTER) JOIN

44.3 Data

set.seed(123)

# DF1
ID <- paste0('S',1:5)
Sex <- sample(x = c('M','F'), size = length(ID), replace = TRUE)
Area <- c('A1','A1','A2','A3', NA)
DF1 <- data.frame(ID=ID, Sex=Sex, Area=Area, stringsAsFactors=FALSE)

# DF2
Area <- c('A1','A2','A3','A4')
Epi <- c('Da','Db','Dc','D4')
DF2 <- data.frame(Area=Area, Epi=Epi, stringsAsFactors=FALSE)

44.3.1 DF1

44.3.2 DF2

44.4 Inner Join

# Inner Join

m1aDF <- merge(x=DF1, y=DF2, by.x='Area', by.y='Area', all=FALSE)

m1bDF <- merge(x=DF1, y=DF2, by='Area', all=FALSE)

44.5 Left Join

# Left Join

m2DF <- merge(x=DF1, y=DF2, by.x='Area', by.y='Area', all.x=TRUE)

44.6 Right Join

# Right Join

m3DF <- merge(x=DF1, y=DF2, by.x='Area', by.y='Area', all.y=TRUE)

44.7 Full Join

# Full Join

m4DF <- merge(x=DF1, y=DF2, by.x='Area', by.y='Area', all=TRUE)