Merging Data Frames in R with dplyr

Introduction to Data Frames and dplyr

Welcome aboard our enlightening journey through merging data frames using dplyr in R! In the real world, data are rarely consolidated in one location. Often, they're spread across multiple sources, waiting to be collected, organized, and analyzed. Whether we're dealing with sales data from various regions, healthcare records from a multitude of facilities, or educational scores from several institutions, joining diverse chunks of data is a routine task in any data-driven field. In this lesson, we will learn how to use this powerful tool to combine data frames, and discover various merge operations and their usage in different scenarios. With practical examples to guide you, get ready to master the art of merging data frames with R dplyr!

Basic Syntax for Merging Data Frames

We utilize the join() functions from dplyr to combine data frames. Here's a general example: R library(dplyr) merged_df <- inner_join(df1, df2, by = "common_column") library(dplyr) merged_df <- inner_join(df1, df2, by = "common_column") In these examples, the abstract variables df1 and df2 are merged based on a shared or common column. We shall look at specific examples and unpack the four types of merges: inner join, outer join, left join, and right join.

Dataset

For this lesson, we will use the following dataset, stored in two separate data frames: R df_books <- data.frame("Book_ID" = c(1, 2, 3, 4, 5), "Book_Title" = c('Gatsby', 'Mockingbird', '1984', 'Catcher', 'LOTR'), "Author_ID" = c(101, 102, 103, NA, 112), "Genre" = c('Fiction', 'Fiction', 'Fiction', 'Fiction', 'Fantasy')) # creating the DataFrame for Authors df_authors <- data.frame("Author_ID" = c(101, 102, 103, 104, 105), "Author_Name" = c('F. Fitzgerald', 'H. Lee', 'G. Orwell', 'J. Salinger', 'J. Tolkien'), "Nationality" = c('American', 'American', 'British', 'American', 'British')) df_books <- data.frame("Book_ID" = c(1, 2, 3, 4, 5), "Book_Title" = c('Gatsby', 'Mockingbird', '1984', 'Catcher', 'LOTR'), "Author_ID" = c(101, 102, 103, NA, 112), "Genre" = c('Fiction', 'Fiction', 'Fiction', 'Fiction', 'Fantasy')) # creating the DataFrame for Authors df_authors <- data.frame("Author_ID" = c(101, 102, 103, 104, 105), "Author_Name" = c('F. Fitzgerald', 'H. Lee', 'G. Orwell', 'J. Salinger', 'J. Tolkien'), "Nationality" = c('American', 'American', 'British', 'American', 'British')) Two important things to note: The author with Author_ID=112 is missing in the df_authors data frame. The book named Catcher in the df_books data frame has missing info about its author.

Inner Join

An inner join includes rows where there is a match in both data frames. The following examples show how you can perform an inner join: R # Merge the dataframes - inner merge merged_df <- inner_join(df_books, df_authors, by = "Author_ID") # With dplyr # Merge the dataframes - inner merge merged_df <- inner_join(df_books, df_authors, by = "Author_ID") # With dplyr The resultant data frame will include only rows with common Author_ID in both data frames, so books without author information will not be included. Book_ID Book_Title Author_ID Genre Author_Name Nationality 1 1 Gatsby 101 Fiction F. Fitzgerald American 2 2 Mockingbird 102 Fiction H. Lee American 3 3 1984 103 Fiction G. Orwell British Book_ID Book_Title Author_ID Genre Author_Name Nationality 1 1 Gatsby 101 Fiction F. Fitzgerald American 2 2 Mockingbird 102 Fiction H. Lee American 3 3 1984 103 Fiction G. Orwell British

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

library(dplyr)

merged_df <- inner_join(df1, df2, by = "common_column")

df_books <- data.frame(
     "Book_ID" = c(1, 2, 3, 4, 5),
     "Book_Title" = c('Gatsby', 'Mockingbird', '1984', 'Catcher', 'LOTR'),
     "Author_ID" = c(101, 102, 103, NA, 112),
     "Genre" = c('Fiction', 'Fiction', 'Fiction', 'Fiction', 'Fantasy')
 )

# creating the DataFrame for Authors
df_authors <- data.frame(
    "Author_ID" = c(101, 102, 103, 104, 105),
    "Author_Name" = c('F. Fitzgerald', 'H. Lee', 'G. Orwell', 'J. Salinger', 'J. Tolkien'),
    "Nationality" = c('American', 'American', 'British', 'American', 'British')
)

# Merge the dataframes - inner merge
merged_df <- inner_join(df_books, df_authors, by = "Author_ID") # With dplyr

  Book_ID  Book_Title Author_ID   Genre   Author_Name Nationality
1       1      Gatsby       101 Fiction F. Fitzgerald    American
2       2 Mockingbird       102 Fiction        H. Lee    American
3       3        1984       103 Fiction     G. Orwell     British

# Merge - outer merge
merged_df <- full_join(df_books, df_authors, by = "Author_ID")

# Merge - outer merge
merged_df <- full_join(df_books, df_authors, by = "Author_ID")

  Book_ID  Book_Title Author_ID   Genre   Author_Name Nationality
1       1      Gatsby       101 Fiction F. Fitzgerald    American
2       2 Mockingbird       102 Fiction        H. Lee    American
3       3        1984       103 Fiction     G. Orwell     British
4       4     Catcher        NA Fiction          <NA>        <NA>
5       5        LOTR       112 Fantasy          <NA>        <NA>
6      NA        <NA>       104    <NA>   J. Salinger    American
7      NA        <NA>       105    <NA>    J. Tolkien     British

  Book_ID  Book_Title Author_ID   Genre   Author_Name Nationality
1       1      Gatsby       101 Fiction F. Fitzgerald    American
2       2 Mockingbird       102 Fiction        H. Lee    American
3       3        1984       103 Fiction     G. Orwell     British
4       4     Catcher        NA Fiction          <NA>        <NA>
5       5        LOTR       112 Fantasy          <NA>        <NA>
6      NA        <NA>       104    <NA>   J. Salinger    American
7      NA        <NA>       105    <NA>    J. Tolkien     British

# Merge - left merge
merged_df <- left_join(df_books, df_authors, by = "Author_ID")

# Merge - left merge
merged_df <- left_join(df_books, df_authors, by = "Author_ID")

  Book_ID  Book_Title Author_ID   Genre   Author_Name Nationality
1       1      Gatsby       101 Fiction F. Fitzgerald    American
2       2 Mockingbird       102 Fiction        H. Lee    American
3       3        1984       103 Fiction     G. Orwell     British
4       4     Catcher        NA Fiction          <NA>        <NA>
5       5        LOTR       112 Fantasy          <NA>        <NA>

  Book_ID  Book_Title Author_ID   Genre   Author_Name Nationality
1       1      Gatsby       101 Fiction F. Fitzgerald    American
2       2 Mockingbird       102 Fiction        H. Lee    American
3       3        1984       103 Fiction     G. Orwell     British
4       4     Catcher        NA Fiction          <NA>        <NA>
5       5        LOTR       112 Fantasy          <NA>        <NA>

merged_df = right_join(df_books, df_authors, by = "Author_ID")

merged_df = right_join(df_books, df_authors, by = "Author_ID")

  Book_ID  Book_Title Author_ID   Genre   Author_Name Nationality
1       1      Gatsby       101 Fiction F. Fitzgerald    American
2       2 Mockingbird       102 Fiction        H. Lee    American
3       3        1984       103 Fiction     G. Orwell     British
4      NA        <NA>       104    <NA>   J. Salinger    American
5      NA        <NA>       105    <NA>    J. Tolkien     British

  Book_ID  Book_Title Author_ID   Genre   Author_Name Nationality
1       1      Gatsby       101 Fiction F. Fitzgerald    American
2       2 Mockingbird       102 Fiction        H. Lee    American
3       3        1984       103 Fiction     G. Orwell     British
4      NA        <NA>       104    <NA>   J. Salinger    American
5      NA        <NA>       105    <NA>    J. Tolkien     British

# Merge - outer merge
merged_df <- full_join(df_books, df_authors, by = "Author_ID")

  Book_ID  Book_Title Author_ID   Genre   Author_Name Nationality
1       1      Gatsby       101 Fiction F. Fitzgerald    American
2       2 Mockingbird       102 Fiction        H. Lee    American
3       3        1984       103 Fiction     G. Orwell     British
4       4     Catcher        NA Fiction          <NA>        <NA>
5       5        LOTR       112 Fantasy          <NA>        <NA>
6      NA        <NA>       104    <NA>   J. Salinger    American
7      NA        <NA>       105    <NA>    J. Tolkien     British

# Merge - left merge
merged_df <- left_join(df_books, df_authors, by = "Author_ID")

  Book_ID  Book_Title Author_ID   Genre   Author_Name Nationality
1       1      Gatsby       101 Fiction F. Fitzgerald    American
2       2 Mockingbird       102 Fiction        H. Lee    American
3       3        1984       103 Fiction     G. Orwell     British
4       4     Catcher        NA Fiction          <NA>        <NA>
5       5        LOTR       112 Fantasy          <NA>        <NA>

merged_df = right_join(df_books, df_authors, by = "Author_ID")

  Book_ID  Book_Title Author_ID   Genre   Author_Name Nationality
1       1      Gatsby       101 Fiction F. Fitzgerald    American
2       2 Mockingbird       102 Fiction        H. Lee    American
3       3        1984       103 Fiction     G. Orwell     British
4      NA        <NA>       104    <NA>   J. Salinger    American
5      NA        <NA>       105    <NA>    J. Tolkien     British

Introduction to Data Frames and dplyr

Basic Syntax for Merging Data Frames

Dataset

Inner Join

Join the 1M+ learners on CodeSignal

Outer Join

Left Join

Right Join

Lesson Summary