In this lesson, you'll learn how to combine information from multiple data frames in R with dplyr.

Joining Tables in R

It is easy to do this kind of matching for one row, but hard to do it for multiple rows. 

Luckily, dplyr can efficiently do this for the entire table using the `inner_join()` method.

The **`inner_join()`** method looks for columns that are common between two data frames and then looks for rows where those columns' values are the same.  It then combines the matching rows into a single row in a new table.


We can call the `inner_join()` method with two data frames like this:

```r
joined_df <- orders %>%
  inner_join(customers)
```

This will match up all of the customer information to the orders that each customer made.

Inner Join I

Suppose we have the following three tables that describe our eCommerce business:
- `orders` &mdash; a table with information on each transaction:

<div class="narrative-table-container">

<table>
<tr><th>order_id</th><th>customer_id</th><th>product_id</th><th>quantity</th><th>timestamp</th></tr>
<tr><td>1</td><td>2</td><td>3</td><td>1</td><td>2017-01-01</td></tr>
<tr><td>2</td><td>2</td><td>2</td><td>3</td><td>2017-01-01</td></tr>
<tr><td>3</td><td>3</td><td>1</td><td>1</td><td>2017-01-01</td></tr>
<tr><td>4</td><td>3</td><td>2</td><td>2</td><td>2017-02-01</td></tr>
<tr><td>5</td><td>3</td><td>3</td><td>3</td><td>2017-02-01</td></tr>
<tr><td>6</td><td>1</td><td>4</td><td>2</td><td>2017-03-01</td></tr>
<tr><td>7</td><td>1</td><td>1</td><td>1</td><td>2017-02-02</td></tr>
<tr><td>8</td><td>1</td><td>4</td><td>1</td><td>2017-02-02</td></tr>
</table>

</div>
<br>

- `customers` &mdash; a table with customer names and contact information:

<div class="narrative-table-container">

<table>
<tr><th>customer_id</th><th>customer_name</th><th>address</th><th>phone_number</th></tr>
<tr><td>1</td><td>John Smith</td><td>123 Main St.</td><td>212-123-4567</td></tr>
<tr><td>2</td><td>Jane Doe</td><td>456 Park Ave.</td><td>949-867-5309</td></tr>
<tr><td>3</td><td>Joe Schmo</td><td>798 Broadway</td><td>112-358-1321</td></tr>
</table>

</div>
<br>


- `products` &mdash; a table with product IDs, descriptions, and prices:

<div class="narrative-table-container">

<table>
<tr><th>product_id</th><th>description</th><th>price</th></tr>
<tr><td>1</td><td>thing-a-ma-jig</td><td>5</td></tr>
<tr><td>2</td><td>whatcha-ma-call-it</td><td>10</td></tr>
<tr><td>3</td><td>doo-hickey</td><td>7</td></tr>
<tr><td>4</td><td>gizmo</td><td>3</td></tr>
</table>

</div>
<br>

If we just look at the `orders` table, we can't really tell what's happened in each order.  However, if we refer to the other tables, we can get a more complete picture.

Let's examine the order with an `order_id` of `1`.  It was purchased by Customer 2.  To find out the customer's name, we look at the `customers` table and look for the item with a `customer_id` value of `2`.  We can see that Customer 2's name is Jane Doe and that she lives at 456 Park Ave. 

Doing this kind of matching is called **joining** two data frames.


Joining

In order to efficiently store data, we often spread related information across multiple tables.

For instance, imagine that we own an e-commerce business and we want to track the products that have been ordered from our website.

We could have one table with all of the following information:
- `order_id`
- `customer_id`
- `customer_name`
- `customer_address`
- `customer_phone_number`
- `product_id`
- `product_description`
- `product_price`
- `quantity`
- `timestamp`

However, a lot of this information would be repeated.  If the same customer makes multiple orders, that customer's name, address, and phone number will be reported multiple times.  If the same product is ordered by multiple customers, then the product price and description will be repeated.  This will make our orders table big and unmanageable.

So instead, we can split our data into three tables:

* `orders` would contain the information necessary to describe an order: `order_id`, `customer_id`, `product_id`, `quantity`, and `timestamp`
* `products` would contain the information to describe each product: `product_id`, `product_description` and `product_price`
* `customers` would contain the information for each customer: `customer_id`, `customer_name`, `customer_address`, and `customer_phone_number`

In this lesson, we will learn the dplyr commands that help us work with data stored in multiple tables.


Introduction

In addition to using `inner_join()` to join two data frames together, we can use the pipe `%>%` to join multiple data frames together at once. The following command would join `orders` with `customers`, and then join the resulting data frame with `products`:

```r
big_df <- orders %>%
  inner_join(customers) %>%
  inner_join(products)
```

Inner Join II

In the previous example, the `inner_join()` function "knew" how to combine tables based on the columns that were the same between two tables.  For instance, `orders` and `customers` both had a column called `customer_id`.  This won't always be true when we want to perform a join.

Generally, the `orders` data frame would not have the column `order_id` and the `customers` data frame would not have the column `customer_id`.  Instead, they would both have a column `id` and it would be implied that the id was the `order_id` for the `orders` table and the `customer_id` for the `customers` table.  They would look like this:

#### Orders

<div class="narrative-table-container"> <div class="narrative-table-scroll">

<table>
<tr><th>id</th><th>customer_id</th><th>product_id</th><th>quantity</th><th>timestamp</th></tr>
<tr><td>1</td><td>2</td><td>3</td><td>1</td><td>2017-01-01</td></tr>
<tr><td>2</td><td>2</td><td>2</td><td>3</td><td>2017-01-01</td></tr>
<tr><td>3</td><td>3</td><td>1</td><td>1</td><td>2017-01-01</td></tr>
<tr><td>4</td><td>3</td><td>2</td><td>2</td><td>2017-02-01</td></tr>
<tr><td>5</td><td>3</td><td>3</td><td>3</td><td>2017-02-01</td></tr>
<tr><td>6</td><td>1</td><td>4</td><td>2</td><td>2017-03-01</td></tr>
<tr><td>7</td><td>1</td><td>1</td><td>1</td><td>2017-02-02</td></tr>
<tr><td>8</td><td>1</td><td>4</td><td>1</td><td>2017-02-02</td></tr>
</table>

</div>
</div>
<br>

#### Customers

<div class="narrative-table-container"> <div class="narrative-table-scroll">
<table>
<tr><th>id</th><th>customer_name</th><th>address</th><th>phone_number</th></tr>
<tr><td>1</td><td>John Smith</td><td>123 Main St.</td><td>212-123-4567</td></tr>
<tr><td>2</td><td>Jane Doe</td><td>456 Park Ave.</td><td>949-867-5309</td></tr>
<tr><td>3</td><td>Joe Schmo</td><td>798 Broadway</td><td>112-358-1321</td></tr>
</table>

</div>
</div>
<br>

**How would this affect our joins?**

Because the `id` columns would mean something different in each table, our default joins would be wrong.  

One way that we could address this problem is to use the dplyr function `rename()` to rename the columns for our joins.  In the example below, we will rename the column `id` in the `customers` data frame to `customer_id`, so that `orders` and `customers` now have a common column to join on.

```r
customers <- customers %>%
  rename(customer_id = id)
inner_join(orders, customers)
```

Join on Specific Columns I

Full Join

Let's return to the join of Company A and Company B.

---
### Left Join

Suppose we want to identify which customers are missing phone information.  We would want a list of all customers who have `email`, but don't have `phone`.

We could get this by performing a *Left Join*.  A *Left Join* includes all rows from the first (left) table, but only rows from the second (right) table that match the first table.

For this command, the order of the arguments matters.  If the first data frame is `company_a` and we do a left join, we'll only end up with rows that appear in `company_a`.

By listing `company_a` first, we get all customers from Company A, and only customers from Company B who are *also* customers of Company A.

```r
left_joined_df <- company_a %>%
  left_join(company_b)
```

The result would look like this:
<br>

<div class="narrative-table-container">

|name|email|phone|
|-|-|-|
|Sally Sparrow|sally.sparrow@gmail.com| `NA` |
|Peter Grant|pgrant@yahoo.com|212-345-6789|
|Leslie May|leslie_may@gmail.com|626-987-6543|

</div>

Now let's say we want a list of all customers who have `phone` but no `email`. We can do this by performing a Right Join. 

---
### Right Join

A Right Join is the exact opposite of left join. Here, the joined table will include all rows from the second (right) table, but only rows from the first (left) table that match the second table.   

By listing `company_a` first and `company_b` second, we get all customers from Company B, and only customers from Company A who are also customers of Company B. 

```r
right_joined_df <- company_a %>%
  right_join(company_b)
```

The result would look like this: 
<br>

<div class="narrative-table-container"> 

|name|email|phone|
|-|-|-|
|Peter Grant|pgrant@yahoo.com|212-345-6789|
|Leslie May|leslie_may@gmail.com|626-987-6543|
|Aaron Burr|`NA`|303-456-7891|

</div>

Left and Right Joins

This lesson introduced some methods for combining multiple data frames:
* Creating a data frame made by matching the common columns of two data frames is called a `join`
* We can specify which columns should be matched by using the `by` argument
* We can combine data frames whose rows don't all match using `left`, `right`, and `full` joins
* We can stack or concatenate data frames with the same columns using `bind_rows()`

Review

In the previous exercise, we learned how to use `rename()` to join two data frames whose columns don't match.

A better option, however, exists.  We can add the `by` argument when calling `inner_join()` to specify which columns we want to join on.  In the example below, the "left" table is the one that comes first (`orders`), and the "right" table is the one that comes second (`customers`).  This syntax says that we should match the `customer_id` from orders to the `id` in `customers`.

```r
orders %>% 
  inner_join(customers,
             by = c('customer_id' = 'id'))
```

The resulting data frame will look like this:

<div class="narrative-table-container"> <div class="narrative-table-scroll">

|id|customer_id|product_id|quantity|timestamp|customer_name|address|phone_number|
|-|-|-|-|-|-|-|-|
|1|2|3|1|2017-01-01 00:00:00|Jane Doe|456 Park Ave|949-867-5309|
|2|2|2|3|2017-01-01 00:00:00|Jane Doe|456 Park Ave|949-867-5309|
|3|3|1|1|2017-01-01 00:00:00|Joe Schmo|789 Broadway|112-358-1321|
|4|3|2|2|2016-02-01 00:00:00|Joe Schmo|789 Broadway|112-358-1321|
|5|3|3|3|2017-02-01 00:00:00|Joe Schmo|789 Broadway|112-358-1321|
|6|1|4|2|2017-03-01 00:00:00|John Smith|123 Main St.|212-123-4567|
|7|1|1|1|2017-02-02 00:00:00|John Smith|123 Main St.|212-123-4567|
|8|1|4|1|2017-02-02 00:00:00|John Smith|123 Main St.|212-123-4567|

</div></div>

If we use this syntax, we'll end up with two columns called `id`, one from the first table and one from the second.  R won't let you have two columns with the same name, so it will change them to `id_x` and `id_y`.

It will look like this:

<div class="narrative-table-container"> <div class="narrative-table-scroll">

|id_x|customer_id|product_id|quantity|timestamp|id_y|customer_name|address|phone_number|
|-|-|-|-|-|-|-|-|-|
|1|2|3|1|2017-01-01 00:00:00|2|Jane Doe|456 Park Ave|949-867-5309|
|2|2|2|3|2017-01-01 00:00:00|2|Jane Doe|456 Park Ave|949-867-5309|
|3|3|1|1|2017-01-01 00:00:00|3|Joe Schmo|789 Broadway|112-358-1321|
|4|3|2|2|2016-02-01 00:00:00|3|Joe Schmo|789 Broadway|112-358-1321|
|5|3|3|3|2017-02-01 00:00:00|3|Joe Schmo|789 Broadway|112-358-1321|
|6|1|4|2|2017-03-01 00:00:00|1|John Smith|123 Main St.|212-123-4567|
|7|1|1|1|2017-02-02 00:00:00|1|John Smith|123 Main St.|212-123-4567|
|8|1|4|1|2017-02-02 00:00:00|1|John Smith|123 Main St.|212-123-4567|


</div></div>

The new column names `id_x` and `id_y` aren't very helpful for us when we read the table. We can help make them more useful by using the keyword `suffix`. We can provide a vector of suffixes to use instead of "_x" and "_y".

For example, we could use the following code to make the suffixes reflect the table names:

```r
orders %>% 
  inner_join(customers,
             by = c('customer_id' = 'id'),
             suffix = c('_order','_customer'))
```

The resulting table would look like this:

<div class="narrative-table-container"> <div class="narrative-table-scroll">

|id_order|customer_id|product_id|quantity|timestamp|id_customer|customer_name|address|phone_number|
|-|-|-|-|-|-|-|-|-|
|1|2|3|1|2017-01-01 00:00:00|2|Jane Doe|456 Park Ave|949-867-5309|
|2|2|2|3|2017-01-01 00:00:00|2|Jane Doe|456 Park Ave|949-867-5309|
|3|3|1|1|2017-01-01 00:00:00|3|Joe Schmo|789 Broadway|112-358-1321|
|4|3|2|2|2016-02-01 00:00:00|3|Joe Schmo|789 Broadway|112-358-1321|
|5|3|3|3|2017-02-01 00:00:00|3|Joe Schmo|789 Broadway|112-358-1321|
|6|1|4|2|2017-03-01 00:00:00|1|John Smith|123 Main St.|212-123-4567|
|7|1|1|1|2017-02-02 00:00:00|1|John Smith|123 Main St.|212-123-4567|
|8|1|4|1|2017-02-02 00:00:00|1|John Smith|123 Main St.|212-123-4567|
</div>
</div>

Join on Specific Columns II

Sometimes, a dataset is broken into multiple tables.  For instance, data is often split into multiple CSV files so that each download is smaller.

When we need to reconstruct a single data frame from multiple smaller data frames, we can use the dplyr **`bind_rows()`** method.  This method only works if all of the columns are the same in all of the data frames.

For instance, suppose that we have two data frames:

##### `df1`
<div class="narrative-table-container"> 
<table>
<tr><th>name</th><th>email</th></tr>
<tr><td>Katja Obinger</td><td>k.obinger@gmail.com</td></tr>
<tr><td>Alison Hendrix</td><td>alisonH@yahoo.com</td></tr>
<tr><td>Cosima Niehaus</td><td>cosi.niehaus@gmail.com</td></tr>
<tr><td>Rachel Duncan</td><td>rachelduncan@hotmail.com</td></tr>
</table>
</div>


##### `df2`
<div class="narrative-table-container">
<table>
<tr><th>name</th><th>email</th></tr>
<tr><td>Jean Gray</td><td>jgray@netscape.net</td></tr>
<tr><td>Scott Summers</td><td>ssummers@gmail.com</td></tr>
<tr><td>Kitty Pryde</td><td>kitkat@gmail.com</td></tr>
<tr><td>Charles Xavier</td><td>cxavier@hotmail.com</td></tr>
</table>
</div>

If we want to combine these two data frames, we can use the following command:

```r
concatenated_dfs <- df1 %>%
  bind_rows(df2)
```
That would result in the following data frame:
<div class="narrative-table-container"> 
<table>
<tr><th>name</th><th>email</th></tr>
<tr><td>Katja Obinger</td><td>k.obinger@gmail.com</td></tr>
<tr><td>Alison Hendrix</td><td>alisonH@yahoo.com</td></tr>
<tr><td>Cosima Niehaus</td><td>cosi.niehaus@gmail.com</td></tr>
<tr><td>Rachel Duncan</td><td>rachelduncan@hotmail.com</td></tr>
<tr><td>Jean Gray</td><td>jgray@netscape.net</td></tr>
<tr><td>Scott Summers</td><td>ssummers@gmail.com</td></tr>
<tr><td>Kitty Pryde</td><td>kitkat@gmail.com</td></tr>
<tr><td>Charles Xavier</td><td>cxavier@hotmail.com</td></tr>
</table>
</div>



Concatenate Data Frames

In our previous examples, there were always matching values when we were performing our joins.  What happens when that isn't true?

Let's imagine that our `products` table is out of date and is missing the newest product: Product 5.  What happens when someone orders it?