Introduction:Multiple DataFrames

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.

1.In script.py, we’ve loaded in three DataFrames: orders, products, and customers.

Start by inspecting orders using the following code:

print(orders)

2.Now inspect products using the following code:

print(products)

3.Now inspect customers using the following code:

print(customers)

import pandas as pd

orders = pd.read_csv('orders.csv')

products = pd.read_csv('products.csv')

customers = pd.read_csv('customers.csv')
print(orders)
print(products)
print(customers)

Inner Merge I

Suppose we have the following three tables that describe our eCommerce business:

• orders — a table with information on each transaction:

order_id	customer_id	product_id	quantity	timestamp
1	2	3	1	2017-01-01
2	2	2	3	2017-01-01
3	3	1	1	2017-01-01
4	3	2	2	2017-02-01
5	3	3	3	2017-02-01
6	1	4	2	2017-03-01
7	1	1	1	2017-02-02
8	1	4	1	2017-02-02

• products — a table with product IDs, descriptions, and prices:

product_id	description	price
1	thing-a-ma-jig	5
2	whatcha-ma-call-it	10
3	doo-hickey	7
4	gizmo	3

• customers — a table with customer names and contact information:

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

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.

1.Examine the orders and products tables.

What is the description of the product that was ordered in Order 3?

Give your answer as a string assigned to the variable order_3_description.

2.Examine the orders and customers tables.

What is the phone_number of the customer in Order 5?

Give your answer as a string assigned to the variable order_5_phone_number.

import codecademylib3
import pandas as pd

orders = pd.read_csv('orders.csv')

products = pd.read_csv('products.csv')

customers = pd.read_csv('customers.csv')

print(orders)
print(products)
print(customers)

order_3_description = 'thing-a-ma-jig'
order_5_phone_number = '112-358-1321'

Inner Merge II

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:

new_df = pd.merge(orders, customers)

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

1.You are an analyst at Cool T-Shirts Inc. You are going to help them analyze some of their sales data.

There are two DataFrames defined in the file script.py:

• sales contains the monthly revenue for Cool T-Shirts Inc. It has two columns: month and revenue.
• targets contains the goals for monthly revenue for each month. It has two columns: month and target.

Create a new DataFrame sales_vs_targets which contains the merge of sales and targets.

2.Display sales_vs_targets using print.

3.Cool T-Shirts Inc. wants to know the months when they crushed their targets.

Select the rows from sales_vs_targets where revenue is greater than target. Save these rows to the variable crushing_it.

import codecademylib3
import pandas as pd

sales = pd.read_csv('sales.csv')
print(sales)
targets = pd.read_csv('targets.csv')
print(targets)
# q1
sales_vs_targets = pd.merge(sales,targets)

# q2
print(sales_vs_targets)

# q3
crushing_it = sales_vs_targets[sales_vs_targets.revenue > sales_vs_targets.target]

Inner Merge III

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:

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:

big_df = orders.merge(customers).merge(products)

1.We have some more data from Cool T-Shirts Inc. The number of men’s and women’s t-shirts sold per month is in a file called men_women_sales.csv. Load this data into a DataFrame called men_women.

2.Merge all three DataFrames (sales, targets, and men_women) into one big DataFrame called all_data.

3.Display all_data using print.

4.Cool T-Shirts Inc. thinks that they have more revenue in months where they sell more women’s t-shirts.

Select the rows of all_data where:

• revenue is greater than target

AND

• women is greater than men

Save your answer to the variable results.

import codecademylib3
import pandas as pd

sales = pd.read_csv('sales.csv')
print(sales)
targets = pd.read_csv('targets.csv')
print(targets)
#q1
men_women = pd.read_csv('men_women_sales.csv')
#q2
all_data = sales.merge(targets).merge(men_women)
#q3
print(all_data)
#q4
results = all_data[(all_data.revenue > all_data.target)&(all_data.women > all_data.men)]

Merge on Specific Columns

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

id	customer_name	address	phone_number
1	John Smith	123 Main St.	212-123-4567
2	Jane Doe	456 Park Ave.	949-867-5309
3	Joe Schmo	798 Broadway	112-358-1321

Products

id	description	price
1	thing-a-ma-jig	5
2	whatcha-ma-call-it	10
3	doo-hickey	7
4	gizmo	3

**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.

pd.merge(
    orders,
    customers.rename(columns={'id': 'customer_id'}))

1.Merge orders and products using .rename(). Save your results to the variable orders_products.

2.Display orders_products using print.

import codecademylib3
import pandas as pd

orders = pd.read_csv('orders.csv')
print(orders)
products = pd.read_csv('products.csv')
print(products)

#q1
orders_products = pd.merge(orders,products.rename(columns={'id':'product_id'}))
#q2
print(orders_products)

Merge on Specific Columns II

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.

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:

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

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:

pd.merge(
    orders,
    customers,
    left_on='customer_id',
    right_on='id',
    suffixes=['_order', '_customer']
)

The resulting table would look like this:

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

1.Merge orders and products using left_on and right_on. Use the suffixes _orders and _products. Save your results to the variable orders_products.

2.Display orders_products using print.

import codecademylib3
import pandas as pd

orders = pd.read_csv('orders.csv')
print(orders)
products = pd.read_csv('products.csv')
print(products)
#q1
orders_products = pd.merge(
  orders,
  products,
  left_on = 'product_id',
  right_on = 'id',
  suffixes = ['_orders','_products']
)
#q2
print(orders_products)

Mismatched Merges

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?

1.We’ve just released a new product with product_id equal to 5. People are ordering this product, but we haven’t updated the products table.

In script.py, you’ll find two DataFrames: products and orders. Inspect these DataFrames using print.

Notice that the third order in orders is for the mysterious new product, but that there is no product_id 5 in products.

2.Merge orders and products and save it to the variable merged_df.

Inspect merged_df using:

print(merged_df)

What happened to order_id 3?

import codecademylib3
import pandas as pd

orders = pd.read_csv('orders.csv')
products = pd.read_csv('products.csv')
#q1
print(orders)
print(products)
#q2
merged_df = pd.merge(orders,products)
print(merged_df)

Outer Merge

In the previous exercise, we saw that when we merge two DataFrames whose rows don’t match perfectly, we lose the unmatched rows.

This type of merge (where we only include matching rows) is called an inner merge. There are other types of merges that we can use when we want to keep information from the unmatched rows.

Suppose that two companies, Company A and Company B have just merged. They each have a list of customers, but they keep slightly different data. Company A has each customer’s name and email. Company B has each customer’s name and phone number. They have some customers in common, but some are different.

company_a

name	email
Sally Sparrow	sally.sparrow@gmail.com
Peter Grant	pgrant@yahoo.com
Leslie May	leslie_may@gmail.com

---

company_b

name	phone
Peter Grant	212-345-6789
Leslie May	626-987-6543
Aaron Burr	303-456-7891

---

If we wanted to combine the data from both companies without losing the customers who are missing from one of the tables, we could use an Outer Join. An Outer Join would include all rows from both tables, even if they don’t match. Any missing values are filled in with None or nan (which stands for “Not a Number”).

pd.merge(company_a, company_b, how='outer')

The resulting table would look like this:

name	email	phone
Sally Sparrow	sally.sparrow@gmail.com	nan
Peter Grant	pgrant@yahoo.com	212-345-6789
Leslie May	leslie_may@gmail.com	626-987-6543
Aaron Burr	nan	303-456-7891

1.There are two hardware stores in town: Store A and Store B. Store A’s inventory is in DataFrame store_a and Store B’s inventory is in DataFrame store_b. They have decided to merge into one big Super Store!

Combine the inventories of Store A and Store B using an outer merge. Save the results to the variable store_a_b_outer.

2.Display store_a_b_outer using print.

Which values are nan or None?

What does that mean?

import codecademylib3
import pandas as pd

store_a = pd.read_csv('store_a.csv')
print(store_a)
store_b = pd.read_csv('store_b.csv')
print(store_b)

#q1
store_a_b_outer = pd.merge(store_a,store_b,how='outer')
#q2
print(store_a_b_outer)

Left and Right Merge

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.

pd.merge(company_a, company_b, how='left')

The result would look like this:

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

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.

pd.merge(company_a, company_b, how="right")

The result would look like this:

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

1.Let’s return to the two hardware stores, Store A and Store B. They’re not quite sure if they want to merge into a big Super Store just yet.

Store A wants to find out what products they carry that Store B does not carry. Using a left merge, combine store_a to store_b and save the results to store_a_b_left.

The items with null in store_b_inventory are carried by Store A, but not Store B.

2.Now, Store B wants to find out what products they carry that Store A does not carry. Use a left join, to combine the two DataFrames but in the reverse order (i.e., store_b followed by store_a) and save the results to the variable store_b_a_left.

Which items are not carried by Store A, but are carried by Store B?

3.Paste the following code into script.py:

print(store_a_b_left)
print(store_b_a_left)

What do you notice about these two DataFrames?

How are they different?

How are they the same?

import codecademylib3
import pandas as pd

store_a = pd.read_csv('store_a.csv')
print(store_a)
store_b = pd.read_csv('store_b.csv')
print(store_b)

store_a_b_left = pd.merge(store_a,store_b,how='left')
print(store_a_b_left)

store_b_a_left = pd.merge(store_a,store_b,how='right')
print(store_b_a_left)

Concatenate 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

name	email
Katja Obinger	k.obinger@gmail.com
Alison Hendrix	alisonH@yahoo.com
Cosima Niehaus	cosi.niehaus@gmail.com
Rachel Duncan	rachelduncan@hotmail.com

df2

name	email
Jean Gray	jgray@netscape.net
Scott Summers	ssummers@gmail.com
Kitty Pryde	kitkat@gmail.com
Charles Xavier	cxavier@hotmail.com

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

pd.concat([df1, df2])

That would result in the following DataFrame:

name	email
Katja Obinger	k.obinger@gmail.com
Alison Hendrix	alisonH@yahoo.com
Cosima Niehaus	cosi.niehaus@gmail.com
Rachel Duncan	rachelduncan@hotmail.com
Jean Gray	jgray@netscape.net
Scott Summers	ssummers@gmail.com
Kitty Pryde	kitkat@gmail.com
Charles Xavier	cxavier@hotmail.com

1.An ice cream parlor and a bakery have decided to merge.

The bakery’s menu is stored in the DataFrame bakery, and the ice cream parlor’s menu is stored in DataFrame ice_cream.

Create their new menu by concatenating the two DataFrames into a DataFrame called menu.

2.Display menu using print.

import codecademylib3
import pandas as pd

bakery = pd.read_csv('bakery.csv')
print(bakery)
ice_cream = pd.read_csv('ice_cream.csv')
print(ice_cream)
#q1
menu = pd.concat([bakery, ice_cream])
#q2
print(menu)

Review

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

 

1.Cool T-Shirts Inc. just created a website for ordering their products. They want you to analyze two datasets for them:

• visits contains information on all visits to their landing page
• checkouts contains all users who began to checkout on their website

Use print to inspect each DataFrame.

2.We want to know the amount of time from a user’s initial visit to the website to when they start to check out.

Use merge to combine visits and checkouts and save it to the variable v_to_c.

3.In order to calculate the time between visiting and checking out, define a column of v_to_c called time that calculates the difference between checkout_time and visit_time for every row.

4.Use .mean() to calculate the average time to checkout and print that value to the terminal.

import codecademylib3
import pandas as pd

visits = pd.read_csv('visits.csv',
                        parse_dates=[1])
checkouts = pd.read_csv('checkouts.csv',
                        parse_dates=[1])
#q1
print(visits)
print(checkouts)
#q2
v_to_c = pd.merge(visits,checkouts)
#q3
v_to_c['time'] = v_to_c.checkout_time - v_to_c.visit_time
print(v_to_c)
#q4
print(v_to_c.time.mean())