How to merge 2 dataframes in R?
MACHINE LEARNING RECIPES DATA CLEANING PYTHON DATA MUNGING PANDAS CHEATSHEET     ALL TAGS

How to merge 2 dataframes in R?

How to merge 2 dataframes in R?

This recipe helps you merge 2 dataframes in R

0

Recipe Objective

In formation of a database, all the data cannot be stored in one table to avoid duplicacy. To extract specific information out of the database, we merge two or more tables using a common field. The merging takes place w.r.t the concept of joins. There are 5 types of joins: ​

  1. Inner join: Returns only matching records
  2. Outer join: Returns all records including no matches in both directions
  3. Left Join: Returns all records in left dataframe and only matching records from the other
  4. Right Join : Returns all records in right dataframe and only matching records from the other
  5. Cross join: Returns all the possible combination of records in both the dataframes

To merge 2 dataframes in R, we use merge() function to do so. In this recipe, we will learn how to merge two dataframe in R using an example.

Step 1: Loading required library and creating 2 dataframes.

Taking an example of university database with 1 table to be of personal details and the other as courses enrolled

#Data manipulation library(tidyverse) # Dataframe 1: personal_details = data.frame(Student_ID = c(1:5), Name = c("Siddhi", "Jessica", "Nisarg", "Vishal", "Fredo"), Address = c("Mumbai", "Mumbai", "Pune", "Madgaon", "Nashik")) #Dataframe 2: courses = data.frame(Student_ID = c(2,3,5,8), Course = c("Chemistry", "Physics", "Computer Science", "History")) print(personal_details) print(courses)
  Student_ID    Name Address
1          1  Siddhi  Mumbai
2          2 Jessica  Mumbai
3          3  Nisarg    Pune
4          4  Vishal Madgaon
5          5   Fredo  Nashik
  Student_ID           Course
1          2        Chemistry
2          3          Physics
3          5 Computer Science
4          8          History

Merging the two dataframes

We use the common field "Student_ID" for merging the data using merge() function. ​

Syntax: merge(x , y, by = , all.x = , all.y = , all = ) ​

where: ​

  1. x = dataframe 1
  2. y = dataframe 2
  3. by = common field by which the merging takes place
  4. all, all.x, all.y = logical values which indicates the type of merge. The default value of all is FALSE(i.e. it returns only the matching rows)

1. Inner Join ​

inner = merge(x=personal_details,y=courses,by="Student_ID") inner
Student_ID    Name Address           Course
1          2 Jessica  Mumbai        Chemistry
2          3  Nisarg    Pune          Physics
3          5   Fredo  Nashik Computer Science

2. Outer Join: (condition: all = TRUE) ​

outer = merge(x=personal_details,y=courses,by="Student_ID", all = TRUE) outer
Student_ID    Name Address           Course
1          1  Siddhi  Mumbai             
2          2 Jessica  Mumbai        Chemistry
3          3  Nisarg    Pune          Physics
4          4  Vishal Madgaon             
5          5   Fredo  Nashik Computer Science
6          8                  History

3. Left Join: (Condition: all.x = TRUE) ​

left = merge(x=personal_details,y=courses,by="Student_ID", all.x = TRUE) left
Student_ID    Name Address           Course
1          1  Siddhi  Mumbai             
2          2 Jessica  Mumbai        Chemistry
3          3  Nisarg    Pune          Physics
4          4  Vishal Madgaon             
5          5   Fredo  Nashik Computer Science

4. Right Join: (Condition: all.y = TRUE) ​

right = merge(x=personal_details,y=courses,by="Student_ID", all.y = TRUE) right
Student_ID    Name Address           Course
1          2 Jessica  Mumbai        Chemistry
2          3  Nisarg    Pune          Physics
3          5   Fredo  Nashik Computer Science
4          8                  History

Relevant Projects

Perform Time series modelling using Facebook Prophet
In this project, we are going to talk about Time Series Forecasting to predict the electricity requirement for a particular house using Prophet.

Credit Card Fraud Detection as a Classification Problem
In this data science project, we will predict the credit card fraud in the transactional dataset using some of the predictive models.

Loan Eligibility Prediction using Gradient Boosting Classifier
This data science in python project predicts if a loan should be given to an applicant or not. We predict if the customer is eligible for loan based on several factors like credit score and past history.

Ensemble Machine Learning Project - All State Insurance Claims Severity Prediction
In this ensemble machine learning project, we will predict what kind of claims an insurance company will get. This is implemented in python using ensemble machine learning algorithms.

Predict Macro Economic Trends using Kaggle Financial Dataset
In this machine learning project, you will uncover the predictive value in an uncertain world by using various artificial intelligence, machine learning, advanced regression and feature transformation techniques.

Forecast Inventory demand using historical sales data in R
In this machine learning project, you will develop a machine learning model to accurately forecast inventory demand based on historical sales data.

Learn to prepare data for your next machine learning project
Text data requires special preparation before you can start using it for any machine learning project.In this ML project, you will learn about applying Machine Learning models to create classifiers and learn how to make sense of textual data.

Walmart Sales Forecasting Data Science Project
Data Science Project in R-Predict the sales for each department using historical markdown data from the Walmart dataset containing data of 45 Walmart stores.

Data Science Project - Instacart Market Basket Analysis
Data Science Project - Build a recommendation engine which will predict the products to be purchased by an Instacart consumer again.

Customer Churn Prediction Analysis using Ensemble Techniques
In this machine learning churn project, we implement a churn prediction model in python using ensemble techniques.