Joining Tables in R

1.Introduction

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:

1.order_id

2.customer_id

3.customer_name

4.customer_address

5.customer_phone_number

6.product_id

7.product_description

8.product_price

9.quantity

10.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:

1.orders would contain the information necessary to describe an order: order_id, customer_id, product_id, quantity, and timestamp

2.products would contain the information to describe each product: product_id, product_description and product_price

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

Instructions

1.In notebook.Rmd, we’ve loaded in three data frames: orders, products, and customers.

Begin by inspecting orders using head(). What columns are in the data frame?

2.Now inspect products using head().

Are there any columns in common with orders?

3.Finally inspect customers using head().

Are there any columns in common with orders or products?

# load packages
library(readr)
library(dplyr)

# load orders data
orders <- read_csv("orders.csv")
customers <- read_csv("customers.csv")
products <- read_csv("products.csv")

# inspect orders, customers and products here:

head(orders)

head(customers)

head(products)

2.Joining

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

1.orders — a table with information on each transaction:

orders

2.customers — a table with customer names and contact information:

customers

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

products

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.

Instructions

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.

# define order_3_description here:
order_3_description <- 'thing-a-ma-jig'

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.

# define order_5_phone_number here:
    order_5_phone_number <- '112-358-1321'

3.Inner Join I

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 Preview: Docs Data frames are objects that store data into two dimensions of columns and rows. 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:

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

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

Instructions

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 data frames defined in the file notebook.Rmd:

(1)sales contains the monthly revenue for Cool T-Shirts Inc. It has two columns: month and revenue.

(2)targets contains the goals for monthly revenue for each month. It has two columns: month and target.

Create a new data frame sales_vs_targets which contains the inner_join() of sales and targets.

# load packages
library(readr)
library(dplyr)

# load sales and targets data
sales <- read_csv("sales.csv")
targets <- read_csv("targets.csv")

# inspect orders, customers and products
sales

targets

# define sales_vs_targets here:
sales_vs_targets <- sales %>%
  inner_join(targets)

sales_vs_targets

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

Filter sales_vs_targets to only include the rows where revenue is greater than target. Save these rows to the variable crushing_it.

# define crushing_it here:
crushing_it <- sales_vs_targets %>%
  filter(revenue > target)

crushing_it

4.Inner Join II

In addition to using inner_join() to join two 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:

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

big_df

Instructions

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 data frame called men_women, and inspect it using head().

# load men_women data here:
men_women <- read_csv("men_women_sales.csv")

# inspect men_women here:
head(men_women)

2.Join all three data frames (sales, targets, and men_women) into one big data frame called all_data. View all_data.

# define all_data here:
all_data <- sales %>%
  inner_join(targets) %>%
  inner_join(men_women)

all_data

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

Filter the rows of all_data to only include rows where:

1.revenue is greater than target

AND

2.women is greater than men

Save your answer to the variable results, and view it.

# define results here:
results <- all_data %>%
  filter(revenue > target) %>%
  filter(women > men)

results

5.Join on Specific Columns I

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:

knitr::include_graphics("C:/Users/kuoan/Desktop/R Code/Join1.png")

knitr::include_graphics("C:/Users/kuoan/Desktop/R Code/Join2.png")

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.

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

Instructions

1.The id column of products stores the same information as the product_id column of orders. Rename the id column of products to product_id. Save the updated data frame to products.

# load orders and products data
orders <- read_csv("orders1.csv")
products <- read_csv("products1.csv")

# inspect orders and products
head(orders)

head(products)

# rename the id column of products here:
products <- products %>%
  rename(product_id = id)

products

2.Join orders and products. Save the result to the variable orders_products, and view it.

# define orders_products here:
orders_products <- inner_join(orders, products)

orders_products

6.Join on Specific Columns II

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.

customers <- read_csv("customers1.csv")
orders <- read_csv("orders1.csv")
orders %>% 
  inner_join(customers,
             by = c('customer_id' = '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. R won’t let you have two columns with the same name, so it will change them to id_x and id_y.

customers <- read_csv("customers1.csv")
orders <- read_csv("orders1.csv")
customers

orders

join <- orders %>% 
  inner_join(customers,
             by = c('customer_id' = 'id'))

join

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:

join <- orders %>% 
  inner_join(customers,
             by = c('customer_id' = 'id'),
             suffix = c('_order','_customer'))

join

Instructions

1.Join the orders and products data frames using an inner_join(), with orders as the first argument and products as the second argument. Also include the by argument to indicate which columns to join on. Save your results to the variable orders_products, and view it.

# load orders and products data
orders <- read_csv("orders1.csv")
products <- read_csv("products1.csv")

# inspect orders and products
head(orders)

head(products)

# define orders_products here:
orders_products <- inner_join(orders, products, by = c('product_id' = 'id'))

orders_products

NA

2.Now join the products and orders data frames using an inner_join(), with products as the first argument and orders as the second argument. Also include the by argument to indicate which columns to join on, as well as a suffix argument c(’_product’,’_order’). Save your results to the variable products_orders, and view it.

# define products_orders here:
products_orders <- inner_join(products, orders, by = c('id' = 'product_id'), suffix = c('_product','_order'))

products_orders

7.Mismatched Joins

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?

Instructions

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 notebook.Rmd, you’ll find two data frames: orders and products. Inspect these data frames using head().

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

# load orders and products data
orders <- read_csv("orders2.csv")
products <- read_csv("products2.csv")

# inspect orders and products here:
head(orders)

head(products)

2.Join the orders and products data frames with inner_join() and save the resulting data frame to the variable orders_products. View orders_products.

What happened to order_id 3?

# define orders_products here:
orders_products <- inner_join(orders, products)
orders_products

8.Full Join

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

This type of join (where we only include matching rows) is called an inner join. There are other types of joins 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 <- read_csv("company_a.csv")
company_b <- read_csv("company_b.csv")

company_a

company_b

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 a Full Join. A Full Join would include all rows from both tables, even if they don’t match. Any missing values are filled in with NA.

full_joined_dfs <- company_a %>%
  full_join(company_b)

full_joined_dfs

Instructions

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

Combine the inventories of Store A and Store B using a full join. Save the results to the variable store_a_b_full.

# load store_a and store_b data
store_a <- read_csv("store_a.csv")
store_b <- read_csv("store_b.csv")

# inspect store_a and store_b
store_a

store_b

# define store_a_b_full here:
store_a_b_full <- full_join(store_a, store_b)

store_a_b_full

9.Left and Right Joins

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.

left_joined_df <- company_a %>%
  left_join(company_b)

The result would look like this:

left_joined_df

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.

right_joined_df <- company_a %>%
  right_join(company_b)

The result would look like this:

right_joined_df

Instrctions

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 join, combine store_a to store_b and save the results to left_a_b.

The items with NA in left_a_b are carried by Store A, but not by Store B.

# define left_a_b here:
left_a_b <- store_a %>%
  left_join(store_b)

left_a_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 data frames but in the reverse order (i.e., store_b followed by store_a) and save the results to the variable left_b_a.

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

What do you notice about these two data frames?

How are they different?

How are they the same?

# define left_b_a here:
left_b_a <- store_b %>%
  left_join(store_a)

left_b_a

10.Concatenate Data Frames

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 <- read_csv("df1.csv")
df2 <- read_csv("df2.csv")

df1

df2

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

concatenated_dfs <- df1 %>%
  bind_rows(df2)

That would result in the following data frame:

concatenated_dfs

Instructions

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

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

Create their new menu by concatenating the two data frames into a data frame called menu.

# load bakery and ice_cream data
bakery <- read_csv('bakery.csv')
ice_cream <- read_csv('ice_cream.csv')

# inspect bakery and ice_cream
head(bakery)

head(ice_cream)

# define menu here:
menu <- bind_rows(bakery, ice_cream)

menu

11.Review

This lesson introduced some methods for combining multiple data frames:

1.Creating a data frame made by matching the common columns of two is called a join

2.We can specify which columns should be matched by using the by argument

3.We can combine data frames whose rows don’t all match using left, right, and full joins

4.We can stack or concatenate data frames with the same columns using bind_rows()

Instructions

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

(1)visits contains information on all visits to their landing page

(2)checkouts contains all users who began to checkout on their website

Use head() to inspect each data frame.

# load visits and checkouts data
visits <- read_csv('visits.csv')
checkouts <- read_csv('checkouts.csv')

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 inner_join to combine visits and checkouts and save it to the variable v_to_c. View v_to_c.

# inspect visits and checkouts here:
head(visits)

head(checkouts)

# define v_to_c here:

v_to_c <- inner_join(visits, checkouts)

v_to_c

3.In order to calculate the time between visiting and checking out, define a column of v_to_c called time by pasting the following code into notebook.Rmd:

# define avg_time_to_check here:
v_to_c <- v_to_c %>% 
  mutate(time = checkout_time - visit_time)
v_to_c

4.To get the average time to checkout, paste the following code into notebook.Rmd:

avg_time_to_check <- v_to_c %>% 
  summarize(mean_time = mean(time))

avg_time_to_check