How to Perform Inner Join in R
When working with multiple datasets in R, combining them based on common keys or variables is often necessary to derive meaningful insights. Inner join is one of the fundamental operations in data manipulation that allows you to merge datasets based on matching values. In this article, we will explore the inner join operation in R Programming Language.
Table of Content
- merge() function from base R
- Inner Join using merge()
- Inner Join on Multiple Columns
- Using dplyr to Perform Inner Join in R
- Inner Join on a Single Column
- Inner Join on Multiple Columns
- Conclusion
There are two main two types of methods available:
- merge() function from base R
- inner_join() function from dplyr
The inner join operation in R can be carried out employing either the merge() function which is base R’s or the inner_join() function from dplyr. Here’s a detailed explanation of the syntax for both approaches:
merge() function from base R
The merge() function in base R is a powerful tool for combining two data frames by columns when they have one or more common variables (similar to SQL joins).
Synatx: merged_df <- merge(x = dataframe1, y = dataframe2, by = “common_key”, all = FALSE)
Where:
- x and y: These parameters give accounts for the input dataframes to be merged. x stands for the left dataframe and y indicates the right side of the dataframe.
- by: By setting this parameter, the user can control which column(s) or variable(s) in the input dataframes will act as key(s) to perform the merge operation.
- all: The presence or absence of this parameter is optional.
Let’s explane the inner join operation with a practical example:
# Create two example data frames
df1 <- data.frame(ID = c(1, 2, 3, 4),
Name = c("Johny", "Ali", "Boby", "Emilya"))
df1
df2 <- data.frame(ID = c(2, 3, 4, 5),
Age = c(30, 25, 40, 35),
Gender = c("Male", "Female", "Male", "Female"))
df2
Output:
ID Name
1 1 Johny
2 2 Ali
3 3 Boby
4 4 Emilya
ID Age Gender
1 2 30 Male
2 3 25 Female
3 4 40 Male
4 5 35 Female
Inner Join using merge()
Here we are going to preform inner join with single columns i.e ID.
# Perform an inner join on the "ID" column
merged_df <- merge(df1, df2, by = "ID", all = FALSE)
# Output the merged data frame
print(merged_df)
Output:
ID Name Age Gender
1 2 Ali 30 Male
2 3 Boby 25 Female
3 4 Emilya 40 Male
This will produce a data frame containing only the rows where the “ID” column matches in both data frames.
Inner Join on Multiple Columns
Here we are going to preform inner join with multiple columns i.e ID.
# Create two example data frames
df1 <- data.frame(ID = c(1, 2, 3, 4),
Name = c("Johny", "Ali", "Boby", "Emilya"))
df2 <- data.frame(ID = c(2, 3, 4, 5),
Name = c("Ali", "Boby", "Emilya",'Jhonathan'),
Age = c(30, 25, 40, 35),
Gender = c("Male", "Female", "Male", "Female"))
# Perform an inner join on the "ID" and "Name" columns
merged_df <- merge(df1, df2, by = c("ID", "Name"), all = FALSE)
# Output the merged data frame
print(merged_df)
Output:
ID Name Age Gender
1 2 Ali 30 Male
2 3 Boby 25 Female
3 4 Emilya 40 Male
This will merge the data frames based on both “ID” and “Name” columns. Adjust the all parameter as needed to perform other types of joins.
Using dplyr to Perform Inner Join in R
By using dplyr package we perform Inner join in R. Here are the basic syntax for Perform Inner Join in R.
Syntax: merged_df <- inner_join(dataframe1, dataframe2, by = “common_key”)
Where,
- dataframe1 and dataframe2: These parameters specify the input dataframes to be merged.
- by: This parameter explains the column(s) or variable(s) in the dataframes being passed in as the keys for merging.
Inner Join on a Single Column
# Load the dplyr package
library(dplyr)
# Create two example data frames
df1 <- data.frame(ID = c(1, 2, 3, 4),
Name = c("John", "Alice", "Bob", "Emily"))
df1
df2 <- data.frame(ID = c(2, 3, 4, 5),
Age = c(30, 25, 40, 35),
Gender = c("Male", "Female", "Male", "Female"))
df2
# Perform a simple inner join on the "ID" column using dplyr
merged_df_simple <- inner_join(df1, df2, by = "ID")
# Output the merged data frame
print(merged_df_simple)
Output:
ID Name
1 1 John
2 2 Alice
3 3 Bob
4 4 Emily
ID Age Gender
1 2 30 Male
2 3 25 Female
3 4 40 Male
4 5 35 Female
ID Name Age Gender
1 2 Alice 30 Male
2 3 Bob 25 Female
3 4 Emily 40 Male
In this we join the dataframes by ID column.
Inner Join on Multiple Columns
# Load the dplyr package
library(dplyr)
# Create two example data frames with overlapping and non-overlapping columns
df1 <- data.frame(ID = c(1, 2, 3, 4),
Name = c("John", "Alice", "Bob", "Emily"),
Age = c(30, 25, 40, 35))
df1
df2 <- data.frame(ID = c(2, 3, 4, 5),
Name = c("Alice", "Bob", "Emily", "Eve"),
Age = c(25, 40, 35, 45), # Adjusted Age values for matches
Gender = c("Male", "Female", "Male", "Female"))
df2
# Perform an inner join on multiple columns using dplyr
merged_df_multiple <- inner_join(df1, df2, by = c("ID", "Name", "Age"))
# Output the merged data frame
print(merged_df_multiple)
Output:
ID Name Age
1 1 John 30
2 2 Alice 25
3 3 Bob 40
4 4 Emily 35
ID Name Age Gender
1 2 Alice 25 Male
2 3 Bob 40 Female
3 4 Emily 35 Male
4 5 Eve 45 Female
ID Name Age Gender
1 2 Alice 25 Male
2 3 Bob 40 Female
3 4 Emily 35 Male
In this we performed inner join on the “ID”, “Name”, and “Age” columns simultaneously. Adjust the by parameter accordingly to match the columns you want to join on.
Conclusion
Inner join is a critical feature of R-based analytics that we can use to append datasets by common key fields or variables. With knowledge of its syntax, applications, and limitations as well as best practices you would be able to incorporate data that comes from different sources, analyze it and extract important information. Whatever data analysis you’re carrying out, whether it’s building your predictive models, conducting business intelligence or simply in exploratory data analysis, the proper use of inner join in R is vital for effective and insightful data manipulation.
Inner Join in R – FAQs
What is an inner join, and how does it differ from other types of joins?
An inner join is just one of the join operations present in R, and it combines rows from two data sets based on the identical values of particular columns. Differently from outer joins (full, left, or right), which involve unmatched rows either from one or both datasets; inner join keeps only the records where corresponding data elements from both datasets have matching keys.
Can I perform an inner join on multiple columns?
Absolutely, you can select the ‘columns’ that will work as key for the ‘inner join’ function in the database. Just give a vector of column names you need to make a merge through the by parameter or inner_join() function.
What happens if there are duplicate keys in one or both datasets?
In inner join, when repeat information of among one or two sets in the dataset, rows which will be duplicated in merged dataset will exist. The keys pairs from both maze I, maze II shall result in as many rows as they are.
How does an inner join handle missing values in the key columns?
Inner join learning discards rows with missing key cluster columns. If one dataset have/s missing values in the primary column but it is the other set of data doesn’t, only those rows that have/has missing values won’t be included in the final outcome.
What should I do if I want to keep unmatched rows from one or both datasets?
In case you need to keep unmerged rows from the first or second data set, outer join (full, left, or right) can be done in order to exclude the innermost rows. This means that the whole line from one of the datasets, or even both datasets, will be present even in the case when there is no of matching.