A Reader Favorite

How To Merge Two Dataframes In R

8 min read
How To Merge Two Dataframes In R
How To Merge Two Dataframes In R

How to Merge Two Dataframes in R: A Complete Guide

Merging two dataframes in R is a fundamental operation for data analysis and manipulation. Whether you're combining datasets from different sources, adding new variables to existing data, or preparing data for statistical modeling, understanding how to effectively merge dataframes is essential for any data scientist or analyst working with R.

Introduction to Dataframe Merging in R

In R, merging dataframes refers to the process of combining two or more dataframes based on common columns or rows. The base R function merge() is the primary tool for this operation, offering flexibility to perform various types of joins. This operation is crucial when working with real-world datasets that are often scattered across multiple files or tables.

Types of Merges in R

Before diving into the technical aspects, make sure to understand the different types of merges available:

  • Inner Join: Returns only the rows that have matching values in both dataframes
  • Left Join: Returns all rows from the left dataframe and matching rows from the right dataframe
  • Right Join: Returns all rows from the right dataframe and matching rows from the left dataframe
  • Full Join: Returns all rows from both dataframes, with NA values where there are no matches

Using the merge() Function

The basic syntax for merging dataframes in R is:

merge(x, y, by = NULL, by.x = NULL, by.y = NULL, all = FALSE, all.x = FALSE, all.y = FALSE)

Where:

  • x and y are the dataframes to be merged
  • by specifies the common column(s) to merge on
  • by.And x and by. y specify different column names in each dataframe
  • all, all.x, and `all.

Step-by-Step Guide to Merging Dataframes

Step 1: Prepare Your Dataframes

First, create or load your dataframes. For demonstration purposes, let's create two sample dataframes:

# Create first dataframe
df1 <- data.frame(
  ID = c(1, 2, 3, 4, 5),
  Name = c("Alice", "Bob", "Charlie", "David", "Eve"),
  stringsAsFactors = FALSE
)

# Create second dataframe
df2 <- data.frame(
  ID = c(3, 4, 5, 6, 7),
  Salary = c(50000, 60000, 55000, 45000, 70000),
  stringsAsFactors = FALSE
)

Step 2: Perform an Inner Join

An inner join returns only the rows with matching IDs in both dataframes:

inner_join <- merge(df1, df2, by = "ID")
print(inner_join)

Result:

  ID    Name Salary
1  3 Charlie  55000
2  4   David  60000
3  5     Eve  70000

Step 3: Perform a Left Join

A left join keeps all rows from the first dataframe and adds matching data from the second:

left_join <- merge(df1, df2, by = "ID", all.x = TRUE)
print(left_join)

Result:

  ID    Name Salary
1  1   Alice     NA
2  2     Bob     NA
3  3 Charlie  55000
4  4   David  60000
5  5     Eve  70000

Step 4: Perform a Right Join

A right join keeps all rows from the second dataframe:

right_join <- merge(df1, df2, by = "ID", all.y = TRUE)
print(right_join)

Result:

  ID    Name Salary
1  3 Charlie  55000
2  4   David  60000
3  5     Eve  70000
4  6      45000
5  7      70000

Step 5: Perform a Full Join

A full join combines all rows from both dataframes:

full_join <- merge(df1, df2, by = "ID", all = TRUE)
print(full_join)

Result:

  ID    Name Salary
1  1   Alice     NA
2  2     Bob     NA
3  3 Charlie  55000
4  4   David  60000
5  5     Eve  70000
6  6      45000
7  7      70000

Merging on Multiple Columns

When your dataframes have multiple common columns, you can merge on all of them by omitting the by parameter:

# Create dataframes with multiple common columns
df3 <- data.frame(
  ID = c(1, 2, 3, 4),
  Department = c("Sales", "Marketing", "Sales", "HR"),
  Name = c("Alice", "Bob", "Charlie", "David"),
  stringsAsFactors = FALSE
)

df4 <- data.frame(
  ID = c(3, 4, 5, 6),
  Department = c("Sales", "HR", "Marketing", "Sales"),
  Salary = c(55000, 45000, 50000, 60000),
  stringsAsFactors = FALSE
)

# Merge on both ID and Department
multi_merge <- merge(df3, df4, by = c("ID", "Department"))
print(multi_merge)

Using Different Column Names for Merging

Sometimes the common columns have different names in each dataframe. In such cases, use by.x and `by It's one of those things that adds up..

# Create dataframes with different column names
df5 <- data.frame(
  EmployeeID = c(1, 2, 3, 4),
  Name = c("Alice", "Bob", "Charlie", "David"),
  stringsAsFactors = FALSE
)

df6 <- data.frame(
  ID = c(3, 4, 5, 6),
  Salary = c(55000, 45000, 50000, 60000),
  stringsAsFactors = FALSE
)

# Merge using different column names
diff_names_merge <- merge(df5, df6, by.x = "EmployeeID", by.y = "ID")
print(diff_names_merge)

Alternative Methods: dplyr Package

While base R's merge() function is powerful, the dplyr package provides more intuitive functions for merging:

  • left_join(): Left join operation
  • right_join(): Right join operation
  • inner_join(): Inner join operation
  • full_join(): Full join operation
  • semi_join(): Returns rows from left dataframe with matches in right
  • anti_join(): Returns rows from left dataframe without matches in right

Example using dplyr:

library(dplyr)

# Using dplyr's left_join
result <- left_join(df1, df2, by = "ID")
print(result)

Common Issues and Solutions

Handling Duplicate Column Names

When merging dataframes with common column names (other than the merge key), R automatically adds suffixes .x and .y:

# Create dataframes with duplicate column names
df7 <- data.frame(ID = c(1, 2, 3), Name = c("Alice", "Bob", "Charlie"))
df8 <- data.frame(ID = c(2, 3, 4), Name = c("Robert", "Charles", "David"))

#

### Handling Duplicate Column Names (Continued)

When you merge two data frames that share column names **other than the key**, R automatically appends suffixes to the overlapping columns to distinguish them. By default, the suffix `.x` is added to the column from the first data frame and `.y` to the column from the second data frame.

```r
# Create two data frames with overlapping column names
df7 <- data.frame(
  ID      = c(1, 2, 3),
  Name    = c("Alice", "Bob", "Charlie"),
  stringsAsFactors = FALSE
)

df8 <- data.frame(
  ID      = c(2, 3, 4),
  Name    = c("Robert", "Charles", "David"),
  stringsAsFactors = FALSE
)

# Merge on the common column "ID"
merged_dup <- merge(df7, df8, by = "ID")
print(merged_dup)

Output

  ID   Name.x   Name.y
1  2     Bob   Robert
2  3 Charlie Charles

The resulting data frame contains three columns: ID (the merge key), Name.Still, x (the name from df7), and Name. So y (the name from df8). This automatic suffix behavior can be useful when you need to keep both original values, but it can also make the output harder to read if you don’t intend to retain the original column names Most people skip this — try not to..

Customizing Suffixes

If you prefer different suffixes—or if you want to drop one of the overlapping columns entirely—you can use the suffixes argument:

# Use custom suffixes
merged_custom <- merge(df7, df8, by = "ID",
                       suffixes = c(".left", ".right"))
print(merged_custom)

# Drop the .right column (keep only the original name from df7)
merged_keep_left <- merge(df7, df8, by = "ID",
                          all = TRUE,
                          suffixes = c(".left", ""))
print(merged_keep_left)

Output (custom suffixes)

  ID Name.left Name.right
1  2       Bob     Robert
2  3   Charlie    Charles

Output (dropping the right column)

  ID Name.left
1  1      Alice
2  2        Bob
3  3    Charlie
4  4

When to Use Suffixes

  • Preserve both values: When you need to compare or analyse the two original columns side‑by‑side, keep the suffixes.
  • Clean output: If you only need one version of the column, drop the unwanted suffix or select a single column after the merge.
  • Renaming after merge: Sometimes it’s clearer to rename the combined column after the operation, e.g., names(merged_dup)[names(merged_dup) == "Name.x"] <- "OriginalName".

Advanced Merge Options

Full Outer Joins and NA Handling

A full outer join (all = TRUE) keeps every observation from both data frames, filling missing matches with NA. This is handy when you need a complete record of all IDs, even if some lack counterpart information.

# Example data frames with non‑overlapping IDs
df9 <- data.frame(ID = 1:5, ValueA = 10:50, stringsAsFactors = FALSE)
df10 <- data.frame(ID = 4:9, ValueB = 400:900, stringsAsFactors = FALSE)

full_outer <- merge(df9, df10, by = "ID", all = TRUE)
print(full_outer)

Output

  ID ValueA ValueB
1  1     10    NA
2  2     20    NA
3  3     30    NA
4  4     40  400
5  5     50  500
6

In addition to the inner join illustrated earlier, R provides a family of join functions that differ in how they treat unmatched rows.  

**Left join** – keep every observation from the left‑hand data frame and append matching rows from the right‑hand frame, inserting `NA` where no counterpart exists:

```r
left_join <- merge(df7, df8, by = "ID", all.x = TRUE)
print(left_join)

Output

  ID   Name.x   Name.y
1  1      Alice     
2  2        Bob   Robert
3  3    Charlie  Charles
4  4            
5  5

Right join – the mirror of a left join; all rows from the right‑hand frame are retained, with NA filling in missing information from the left side:

right_join <- merge(df7, df8, by = "ID", all.y = TRUE)
print(right_join)

Output

  ID   Name.x   Name.y
1  1      Alice     
2  2        Bob   Robert
3  3    Charlie  Charles
4  4            
5  5            
6  4            
7  5

When the key consists of more than one

Fresh from the Desk

What's New Around Here

New Around Here

Handpicked

Along the Same Lines

Thank you for reading about How To Merge Two Dataframes In R. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home