Merging Data Frames

Sean Wen

21st January 2019

Prologue

Oftertimes, the variables of the data may be scattered across different columns from different data sets. Thus, comes the task of bringing the different columns together into a single data frame.
To do this, you would need a reference or key column as the common column between the different data frames.
If you used to be a hardcore Excel user like me, you would probably agree that the VLOOKUP function is one of the most can’t-live-without function in Excel to merge different data sets.
Nevertheless, there are similar functions in R to achieve the same goal, but with some subtleties between them.

Concatanate entire data frames

This is merging data frames sideways.
To do this, use the cbind() function.
Note that the entries of reference column need to be in the same order in both data frames for the entries of the variables/measurements to match. In this example, the reference column is the Name column.
Typically, we do this when we have new variables/measurements.

# Create dataset no. 1
Name <- c("Alice", "Barbara", "Cathy", "Dunn")
Weight <- round(runif(4, 50, 90), digits=0)
Height <- round(runif(4, 150, 190), digits=0)
df1 <- data.frame(Name, Weight, Height, stringsAsFactors=FALSE)
print(df1)

##      Name Weight Height
## 1   Alice     70    155
## 2 Barbara     82    167
## 3   Cathy     50    169
## 4    Dunn     71    154

# Create dataset no. 2
Name <- c("Alice", "Barbara", "Cathy", "Dunn")
Horoscope <- c("Gemini", "Cancer", "Taurus", "Libra")
Country <- c("United Kingdom", "Korea", "Istanbul", "Indonesia")
df2 <- data.frame(Name, Horoscope, Country, stringsAsFactors=FALSE)
print(df2)

##      Name Horoscope        Country
## 1   Alice    Gemini United Kingdom
## 2 Barbara    Cancer          Korea
## 3   Cathy    Taurus       Istanbul
## 4    Dunn     Libra      Indonesia

# Concatanate data frames
df.merged <- cbind(df1, df2)
print(df.merged)

##      Name Weight Height    Name Horoscope        Country
## 1   Alice     70    155   Alice    Gemini United Kingdom
## 2 Barbara     82    167 Barbara    Cancer          Korea
## 3   Cathy     50    169   Cathy    Taurus       Istanbul
## 4    Dunn     71    154    Dunn     Libra      Indonesia

Append entire data frames

This is merging data frames vertically.
To do this, use the rbind() function.
Note that the column names (header) needs to be in the same order in both data frames for the entries to match.
Typically, we do this when we have new entries (e.g. study participants).

# Create dataset no. 1
Name <- c("Alice", "Barbara", "Cathy", "Dunn")
Horoscope <- c("Gemini", "Cancer", "Taurus", "Libra")
Country <- c("United Kingdom", "Korea", "Istanbul", "Indonesia")
df1 <- data.frame(Name, Horoscope, Country, stringsAsFactors=FALSE)
print(df1)

##      Name Horoscope        Country
## 1   Alice    Gemini United Kingdom
## 2 Barbara    Cancer          Korea
## 3   Cathy    Taurus       Istanbul
## 4    Dunn     Libra      Indonesia

# Create dataset no. 2
Name <- c("Taylor", "Ben", "Anne", "Joseph")
Horoscope <- c("Aries", "Sagittarius", "Scorpio", "Capricorn")
Country <- c("USA", "Japan", "China", "Spain")
df2 <- data.frame(Name, Horoscope, Country, stringsAsFactors=FALSE)
print(df2)

##     Name   Horoscope Country
## 1 Taylor       Aries     USA
## 2    Ben Sagittarius   Japan
## 3   Anne     Scorpio   China
## 4 Joseph   Capricorn   Spain

# Append data frames
df.merged <- rbind(df1, df2)
print(df.merged)

##      Name   Horoscope        Country
## 1   Alice      Gemini United Kingdom
## 2 Barbara      Cancer          Korea
## 3   Cathy      Taurus       Istanbul
## 4    Dunn       Libra      Indonesia
## 5  Taylor       Aries            USA
## 6     Ben Sagittarius          Japan
## 7    Anne     Scorpio          China
## 8  Joseph   Capricorn          Spain

Merging data frames

The merge() function is useful when the reference column in both data frames is not in the same order. Therefore, concatanating entire data frames can lead to mismatches.
The merge() function can be used to merge two data frames whose reference columns are not in the same order.
The by argument specifies the reference column.

# Create dataset no. 1
Name <- c("Cathy", "Alice", "Dunn", "Barbara", "Yale")
Weight <- round(runif(5, 50, 90), digits=0)
Height <- round(runif(5, 150, 190), digits=0)
df1 <- data.frame(Name, Weight, Height, stringsAsFactors=FALSE)
print(df1)

##      Name Weight Height
## 1   Cathy     55    185
## 2   Alice     61    180
## 3    Dunn     54    188
## 4 Barbara     60    159
## 5    Yale     88    180

# Create dataset no. 2
Name <- c("Barbara", "Cathy", "Alice", "Dunn", "Michael")
Horoscope <- c("Gemini", "Cancer", "Taurus", "Libra", "Aries")
Country <- c("United Kingdom", "Korea", "Istanbul", "Indonesia", "Canada")
df2 <- data.frame(Name, Horoscope, Country, stringsAsFactors=FALSE)
print(df2)

##      Name Horoscope        Country
## 1 Barbara    Gemini United Kingdom
## 2   Cathy    Cancer          Korea
## 3   Alice    Taurus       Istanbul
## 4    Dunn     Libra      Indonesia
## 5 Michael     Aries         Canada

# Merge data frame
df.merged <- merge(x=df1, y=df2, by="Name")
print(df.merged)

##      Name Weight Height Horoscope        Country
## 1   Alice     61    180    Taurus       Istanbul
## 2 Barbara     60    159    Gemini United Kingdom
## 3   Cathy     55    185    Cancer          Korea
## 4    Dunn     54    188     Libra      Indonesia

By default, the merged data frame will only consist of the overlaps of two reference column. In this case Alice, Barbara, Cathy, and Dunn. Yale and Michael got left out. ## Merging data frames
Use the all=TRUE argument to keep all entries in the reference column from both data frames.
Missing variables will be labelled as missing, i.e. NA for numeric variables and for character variables.

df.merged <- merge(x=df1, y=df2, by="Name", all=TRUE)
print(df.merged)

##      Name Weight Height Horoscope        Country
## 1   Alice     61    180    Taurus       Istanbul
## 2 Barbara     60    159    Gemini United Kingdom
## 3   Cathy     55    185    Cancer          Korea
## 4    Dunn     54    188     Libra      Indonesia
## 5 Michael     NA     NA     Aries         Canada
## 6    Yale     88    180      <NA>           <NA>

Use the all.x=TRUE argument to keep all entries in the reference column from first data frame.
Missing variables will be labelled as missing.

df.merged <- merge(x=df1, y=df2, by="Name", all.x=TRUE)
print(df.merged)

##      Name Weight Height Horoscope        Country
## 1   Alice     61    180    Taurus       Istanbul
## 2 Barbara     60    159    Gemini United Kingdom
## 3   Cathy     55    185    Cancer          Korea
## 4    Dunn     54    188     Libra      Indonesia
## 5    Yale     88    180      <NA>           <NA>

Use the all.y=TRUE argument to keep all entries in the reference column from second data frame.
Missing variables will be labelled as missing.

df.merged <- merge(x=df1, y=df2, by="Name", all.y=TRUE)
print(df.merged)

##      Name Weight Height Horoscope        Country
## 1   Alice     61    180    Taurus       Istanbul
## 2 Barbara     60    159    Gemini United Kingdom
## 3   Cathy     55    185    Cancer          Korea
## 4    Dunn     54    188     Libra      Indonesia
## 5 Michael     NA     NA     Aries         Canada

Merging data frames

What if you have duplicate entries in the second data frames? Would the merge() function bring over only one or all entries to the first data frame?

Name <- c("Cathy", "Alice", "Dunn", "Barbara", "Yale")
Weight <- round(runif(5, 50, 90), digits=0)
Height <- round(runif(5, 150, 190), digits=0)
df1 <- data.frame(Name, Weight, Height, stringsAsFactors=FALSE)
print(df1)

##      Name Weight Height
## 1   Cathy     64    162
## 2   Alice     61    158
## 3    Dunn     55    169
## 4 Barbara     69    156
## 5    Yale     60    154

# Create dataset no. 2
Name <- c("Barbara", "Barbara", "Cathy", "Alice", "Dunn", "Michael")
Colour <- c("Red", "Green", "Blue", "Yellow", "Purple", "Pink")
df2 <- data.frame(Name, Colour, stringsAsFactors=FALSE)
print(df2)

##      Name Colour
## 1 Barbara    Red
## 2 Barbara  Green
## 3   Cathy   Blue
## 4   Alice Yellow
## 5    Dunn Purple
## 6 Michael   Pink

# Merge data frame 
df.merged <- merge(x=df1, y=df2, by="Name", all.x=TRUE)
print(df.merged)

##      Name Weight Height Colour
## 1   Alice     61    158 Yellow
## 2 Barbara     69    156    Red
## 3 Barbara     69    156  Green
## 4   Cathy     64    162   Blue
## 5    Dunn     55    169 Purple
## 6    Yale     60    154   <NA>

In this case, merge() brings over both entries when duplicates are found in the reference column of the second data frame.

Merging data frames

Up to this point, we have been merging data frames with reference columns of the same name.
What if the reference columns of both data frames are of different names?
This is case, you can specify the different reference column labels using the arguments by.x and by.y to indicate the reference column name of the first and second data frame, respectively.
The resulting reference column in the merged data frame will take the name of the first data frame.

Name <- c("Cathy", "Alice", "Dunn", "Barbara", "Yale")
Weight <- round(runif(5, 50, 90), digits=0)
Height <- round(runif(5, 150, 190), digits=0)
df1 <- data.frame(Name, Weight, Height, stringsAsFactors=FALSE)
print(df1)

##      Name Weight Height
## 1   Cathy     80    177
## 2   Alice     73    169
## 3    Dunn     84    157
## 4 Barbara     73    181
## 5    Yale     70    180

# Create dataset no. 2
FirstName <- c("Barbara", "Cathy", "Alice", "Dunn", "Michael")
Colour <- c("Red", "Blue", "Yellow", "Purple", "Pink")
df2 <- data.frame(FirstName, Colour, stringsAsFactors=FALSE)
print(df2)

##   FirstName Colour
## 1   Barbara    Red
## 2     Cathy   Blue
## 3     Alice Yellow
## 4      Dunn Purple
## 5   Michael   Pink

# Merge data frame
df.merged <- merge(x=df1, y=df2, by.x="Name", by.y="FirstName", all.x=TRUE)
print(df.merged)

##      Name Weight Height Colour
## 1   Alice     73    169 Yellow
## 2 Barbara     73    181    Red
## 3   Cathy     80    177   Blue
## 4    Dunn     84    157 Purple
## 5    Yale     70    180   <NA>

Merging data frames

Looking back at the previous examples, notice the reference column of the merged data frame is reordered alphabetically? This happens to be one of my pet peeves of the merge() function.
If you would like to retain the original order of your reference column in the merged data frame, you may consider using the join() function from the plyr package.

library(plyr)

Joining data frames

Similar to the merge() function, the join() function takes in both data frames as the basic arguments.
But unlike the merge() function, the join() function by default only retain the entries present in the reference column of the first data frame.
This is similar to using the all.x=TRUE argument for the merge() function.
(Reminder: The merge() function by default retains overlapping entries from reference columns of both data frames)

Name <- c("Cathy", "Alice", "Dunn", "Barbara", "Yale")
Weight <- round(runif(5, 50, 90), digits=0)
Height <- round(runif(5, 150, 190), digits=0)
df1 <- data.frame(Name, Weight, Height, stringsAsFactors=FALSE)
print(df1)

##      Name Weight Height
## 1   Cathy     82    152
## 2   Alice     58    163
## 3    Dunn     72    150
## 4 Barbara     77    163
## 5    Yale     76    176

# Create dataset no. 2
Name <- c("Barbara", "Cathy", "Alice", "Dunn", "Michael")
Horoscope <- c("Gemini", "Cancer", "Taurus", "Libra", "Aries")
Country <- c("United Kingdom", "Korea", "Istanbul", "Indonesia", "Canada")
df2 <- data.frame(Name, Horoscope, Country, stringsAsFactors=FALSE)
print(df2)

##      Name Horoscope        Country
## 1 Barbara    Gemini United Kingdom
## 2   Cathy    Cancer          Korea
## 3   Alice    Taurus       Istanbul
## 4    Dunn     Libra      Indonesia
## 5 Michael     Aries         Canada

# Merge data frame
df.merged <- join(x=df1, y=df2, by="Name")
print(df.merged)

##      Name Weight Height Horoscope        Country
## 1   Cathy     82    152    Cancer          Korea
## 2   Alice     58    163    Taurus       Istanbul
## 3    Dunn     72    150     Libra      Indonesia
## 4 Barbara     77    163    Gemini United Kingdom
## 5    Yale     76    176      <NA>           <NA>

Joining data frames

Use the type=“full” argument to keep all entries in the reference column from both data frames.
This is similar to using the all=TRUE argument for the merge() function.
Missing variables will be labelled as missing, i.e. NA for numeric variables and for character variables.

df.merged <- join(x=df1, y=df2, by="Name", type="full")
print(df.merged)

##      Name Weight Height Horoscope        Country
## 1   Cathy     82    152    Cancer          Korea
## 2   Alice     58    163    Taurus       Istanbul
## 3    Dunn     72    150     Libra      Indonesia
## 4 Barbara     77    163    Gemini United Kingdom
## 5    Yale     76    176      <NA>           <NA>
## 6 Michael     NA     NA     Aries         Canada

Use the type=“inner” argument to keep overlapping entries in the reference column from both data frames.
This is similar to using the default setting for the merge() function.
Missing variables will be labelled as missing.

df.merged <- join(x=df1, y=df2, by="Name", type="inner")
print(df.merged)

##      Name Weight Height Horoscope        Country
## 1   Cathy     82    152    Cancer          Korea
## 2   Alice     58    163    Taurus       Istanbul
## 3    Dunn     72    150     Libra      Indonesia
## 4 Barbara     77    163    Gemini United Kingdom

Use the type=“right” argument to keep all entries in the reference column from second data frame.
This is similar to using the by.y=TRUE argument for the merge() function.
Missing variables will be labelled as missing.

df.merged <- join(x=df1, y=df2, by="Name", type="right")
print(df.merged)

##      Name Weight Height Horoscope        Country
## 1 Barbara     77    163    Gemini United Kingdom
## 2   Cathy     82    152    Cancer          Korea
## 3   Alice     58    163    Taurus       Istanbul
## 4    Dunn     72    150     Libra      Indonesia
## 5 Michael     NA     NA     Aries         Canada

Joining data frames

What if you have duplicate entries in the second data frames? Would the join() function bring over only one or all entries to the first data frame?
Similar to the merge() function, join() brings over both entries when duplicates are found in the reference column of the second data frame.

Name <- c("Cathy", "Alice", "Dunn", "Barbara", "Yale")
Weight <- round(runif(5, 50, 90), digits=0)
Height <- round(runif(5, 150, 190), digits=0)
df1 <- data.frame(Name, Weight, Height, stringsAsFactors=FALSE)
print(df1)

##      Name Weight Height
## 1   Cathy     79    182
## 2   Alice     59    174
## 3    Dunn     81    177
## 4 Barbara     57    188
## 5    Yale     78    185

# Create dataset no. 2
Name <- c("Barbara", "Barbara", "Cathy", "Alice", "Dunn", "Michael")
Colour <- c("Red", "Green", "Blue", "Yellow", "Purple", "Pink")
df2 <- data.frame(Name, Colour, stringsAsFactors=FALSE)
print(df2)

##      Name Colour
## 1 Barbara    Red
## 2 Barbara  Green
## 3   Cathy   Blue
## 4   Alice Yellow
## 5    Dunn Purple
## 6 Michael   Pink

# Merge data frames 
df.merged <- join(x=df1, y=df2, by="Name")
print(df.merged)

##      Name Weight Height Colour
## 1   Cathy     79    182   Blue
## 2   Alice     59    174 Yellow
## 3    Dunn     81    177 Purple
## 4 Barbara     57    188    Red
## 5 Barbara     57    188  Green
## 6    Yale     78    185   <NA>

Joining data frames

The join() function brings over both entries when duplicates are found in the reference column of the second data frame.
What if you only wanted the first entry to be brought over? You can use the match=“first” argument for this purpose.
And no, there is no match=“second” or match=“third” etc options.

# Merge data frames
df.merged <- join(x=df1, y=df2, by="Name", match="first")
print(df.merged)

##      Name Weight Height Colour
## 1   Cathy     79    182   Blue
## 2   Alice     59    174 Yellow
## 3    Dunn     81    177 Purple
## 4 Barbara     57    188    Red
## 5    Yale     78    185   <NA>

Joining data frames

Up to this point, we have been merging data frames with reference columns of the same name.
What if the reference columns of both data frames are of different names?
In the case of the merge() function, you can specify the different reference column labels using the arguments by.x and by.y to indicate the reference column name of the first and second data frames, respectively.
Unfortunately, there is no way around this for the join() function.
You may need to rename the reference column in one of the data frames to match the other (sad!). This happens to be one of my pet peeves of the join() function. (But hey, we can’t have everything, right?)

Summary

The cbind() function is useful when the order of the entries in the reference columns of both data frames is the same.
The merge() and join() functions are useful when the order of the entries in the reference columns of both data frames is not the same.
The merge() and join() functions share similar but different arguments.
The merge() function is particularly handy when the reference columns from both data frames have different names.
The join() function is particularly handy when you would like to maintain the order of the entries of the reference column in the merged data frame as per the first data frame.