Merging Datasets in R

Adding datasets horizontally

When you have datasets representing the same set of observations, you can concatenate such datasets horizontally. This time, keeping the rows of your dataset, you can add more columns to it. In such cases, you should check that the order of the observations are the same. If your datasets have a different amount of rows, or they have the same number of rows, but the rows are ordered inconsistently, you can pair one set of columns with the other set in a way that doesn't make sense.

Let's extend the example above for an example. Suppose you have two data files, one containing the car make and number of unique models offered, and another containing the car make and total sales:

Number of unique models offered

Total sales

It's important to note that if you have the same observation across multiple datasets and you concatenate them horizontally using cbind() , you'll end up with redundant columns in your table. And though the two datasets contain related information, ordering of rows matter!

In the console below, call cbind() on models and sales and print out the result:

You should have gotten something like this:

Models & Sales

Do you see the problem here? This data is not tidy!

According to the principles of tidy data taught in this foundational course, each observation of a dataset should be represented in a unique row. And what if you had information only on some of the rows in one dataset and wanted to add information only for those you have more information on? Put another way, what if you wanted to add more columns from one dataset to another, but these datasets don't have the same number of observations?

Primary Key and Foreign Keys

The first step when looking to combine datasets is to look for the primary key of your dataset. The primary key is the column or set of columns that uniquely identifies each observation in your dataset. In the example with car makes, the number of unique models offered, and total sales, the primary key of your datasets is the make column.

Now, we can perform joins, the standard way to merge datasets into a single table.

Types of Joins

There are many types of joins. You can learn how to augment columns from one dataset with columns from another with mutating joins, how to filter one dataset against another with filtering joins, and how to sift through datasets with set operations in the Joining Data in R with dplyr course. Below are some of the most common.

left_join(x, y): returns all rows from x, and all columns from x and y. Rows in x with no match in y will have NA values in the new columns. If there are multiple matches between x and y, all combinations of the matches are returned.

inner_join(x, y): returns all rows from x where there are matching values in y, and all columns from x and y. If there are multiple matches between x and y, all combinations of the matches are returned.

full_join(x, y): returns all rows and all columns from both x and y. Where there are not matching values, the function returns NA for the one missing.

The joins mentioned above are examples of mutating joins since they combine variables from two datasets.

Missing Keys

Suppose you have two datasets. The first dataset is called size and contains the names of people and their shirt size:

 > size
name size
1  Tom    M
2  Dan   XL
3 Keil    S

The second dataset is called color and contains the people's surnames, shirt color preferences, and stores some information in the row.names attribute:

 > color
     surname color
Tom     Jeon  <NA>
Dan    Smith  Dark
Bob McLadden Light

Notice what could go wrong here? Two-table joins can get complicated when there are missing keys or duplicate keys. In this example, R's data frames store important information in the row.names attribute. When this is the case, you won't be able to access the key with a join function, as join functions can only access columns of the data frame.

The trick to easily fix this problem is to use the rownames_to_column() function from the tibble package. It returns a copy of your dataset with the row names added to the data as a column. The first argument to rownames_to_column() is your data frame object and the second argument is a string specifying the name of the column you want to add.

Try exploring in the console below. Datasets size and color are pre-loaded on your workspace.

Final Thoughts

In the real world, data may come split across many datasets, across many different formats. Because R is designed to work with single tables of data, manipulating and combining datasets into a single table is an essential skill. Complete the Importing & Cleaning Data with R skill track and learn to parse and combine data in any format. Check out DataCamp's R Data Import tutorial. Happy learning!