In this lesson, you'll learn how to combine information from multiple DataFrames.

Working with Multiple DataFrames

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 DataFrame from multiple smaller DataFrames, we can use the method **`pd.concat([df1, df2, df3, ...])`**.  This method only works if all of the columns are the same in all of the DataFrames.

For instance, suppose that we have two DataFrames:

##### `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 DataFrames, we can use the following command:
```py
pd.concat([df1, df2])
```
That would result in the following DataFrame:
<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 DataFrames

In our previous examples, there were always matching values when we were performing our merges.  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?

Mismatched Merges

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

Generally, the `products` and `customers` DataFrames would not have the columns `product_id` or `customer_id`.  Instead, they would both be called `id` and it would be implied that the id was the `product_id` for the `products` table and `customer_id` for the `customers` table.  They would look like this:

#### 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>

#### Products
<div class="narrative-table-container"> <div class="narrative-table-scroll">
<table>
<tr><th>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></div>

<br>
**How would this affect our merges?**

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

One way that we could address this problem is to use `.rename()` to rename the columns for our merges.  In the example below, we will rename the column `id` to `customer_id`, so that `orders` and `customers` have a common column for the merge.
```py
pd.merge(
    orders,
    customers.rename(columns={'id': 'customer_id'}))
```

Merge on Specific Columns

In addition to using `pd.merge()`, each DataFrame has its own `.merge()` method.  For instance, if you wanted to merge `orders` with `customers`, you could use:
```py
new_df = orders.merge(customers)
```
This produces the same DataFrame as if we had called `pd.merge(orders, customers)`.

We generally use this when we are joining more than two DataFrames together because we can "chain" the commands.  The following command would merge `orders` to `customers`, and then the resulting DataFrame to `products`:
```py
big_df = orders.merge(customers)\
    .merge(products)
```

Inner Merge III

This lesson introduced some methods for combining multiple DataFrames:
* Creating a DataFrame made by matching the common columns of two DataFrames is called a `merge`
* We can specify which columns should be matches by using the keyword arguments `left_on` and `right_on`
* We can combine DataFrames whose rows don't all match using `left`, `right`, and `outer` merges and the `how` keyword argument
* We can stack or concatenate DataFrames with the same columns using `pd.concat`

Review

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

Luckily, Pandas can efficiently do this for the entire table. We use the `.merge()` method.

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

We can call the `pd.merge()` method with two tables like this:
```py
new_df = pd.merge(orders, customers)
```
This will match up all of the customer information to the orders that each customer made.

Inner Merge II

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

---
### Left Merge

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 Merge*.  A *Left Merge* 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 DataFrame 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.

```py
pd.merge(company_a, company_b, how='left')
```
The result would look like this:
<br>

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

|name|email|phone|
|-|-|-|
|Sally Sparrow|sally.sparrow@gmail.com| `None` |
|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 Merge. 

---
### Right Merge
Right merge is the exact opposite of left merge. Here, the merged 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. 

```py
pd.merge(company_a, company_b, how="right")
```
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|`None`|303-456-7891|

</div>

Left and Right Merge

Outer Merge

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

If we don't want to do that, we have another option.  We could use the keywords `left_on` and `right_on` to specify which columns we want to perform the merge 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.
```py
pd.merge(
    orders,
    customers,
    left_on='customer_id',
    right_on='id')
```

If we use this syntax, we'll end up with two columns called `id`, one from the first table and one from the second.  Pandas 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 `suffixes`. We can provide a list 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:
```py
pd.merge(
    orders,
    customers,
    left_on='customer_id',
    right_on='id',
    suffixes=['_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>

Merge on Specific Columns II

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>

- `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>

- `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>

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 **merging** two DataFrames.


Inner Merge I

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 Pandas commands that help us work with data stored in multiple tables.
