library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union#Inner Join (3 points) Perform an inner join between the customers and orders datasets.
customers <- tibble(
customer_id = c(1, 2, 3, 4, 5),
name = c("Alice", "Bob", "Charlie", "David", "Eve"),
city = c("New York", "Los Angeles", "Chicago", "Houston", "Phoenix")
)
orders <- tibble(
order_id = c(101, 102, 103, 104, 105, 106),
customer_id = c(1, 2, 3, 2, 6, 7),
product = c("Laptop", "Phone", "Tablet", "Desktop", "Camera", "Printer"),
amount = c(1200, 800, 300, 1500, 600, 150)
)1. Inner Join (3 points) Perform an inner join between the customers and orders datasets.
Joint <- inner_join(customers, orders, by = "customer_id")a. How many rows are in the result?
nrow(Joint)## [1] 4b. Why are some customers or orders not included in the
result?
Some customers and orders aren’t in the result because they do not have
a customer id that matches with the other table
c. Display the result
head(Joint)## # A tibble: 4 × 6
## customer_id name city order_id product amount
## <dbl> <chr> <chr> <dbl> <chr> <dbl>
## 1 1 Alice New York 101 Laptop 1200
## 2 2 Bob Los Angeles 102 Phone 800
## 3 2 Bob Los Angeles 104 Desktop 1500
## 4 3 Charlie Chicago 103 Tablet 3002. Left Join (3 points) Perform a left join with customers as the left table and orders as the right table.
left_joint <- left_join(customers, orders, by = "customer_id")a. How many rows are in the result?
nrow(left_joint)## [1] 6b. Explain why this number differs from the inner join
result.
This number differs because it’s focusing on the left table which is
customers, so it is showing all the customer data, even if their order
data isn’t avaialable
c. Display the result
head(left_joint)## # A tibble: 6 × 6
## customer_id name city order_id product amount
## <dbl> <chr> <chr> <dbl> <chr> <dbl>
## 1 1 Alice New York 101 Laptop 1200
## 2 2 Bob Los Angeles 102 Phone 800
## 3 2 Bob Los Angeles 104 Desktop 1500
## 4 3 Charlie Chicago 103 Tablet 300
## 5 4 David Houston NA <NA> NA
## 6 5 Eve Phoenix NA <NA> NA3. Right Join (3 points) Perform a right join with customers as the left table and orders as the right table.
right_joint <- right_join(customers, orders, by = "customer_id")a. How many rows are in the result?
nrow(right_joint)## [1] 6b. Which customer_ids in the result have NULL for customer
name and city? Explain why.
Customer ids 6 and 7 have no name or city because there are none
associated with this order, they are not listed in the customer table.
c. Display the result
head(right_joint)## # A tibble: 6 × 6
## customer_id name city order_id product amount
## <dbl> <chr> <chr> <dbl> <chr> <dbl>
## 1 1 Alice New York 101 Laptop 1200
## 2 2 Bob Los Angeles 102 Phone 800
## 3 2 Bob Los Angeles 104 Desktop 1500
## 4 3 Charlie Chicago 103 Tablet 300
## 5 6 <NA> <NA> 105 Camera 600
## 6 7 <NA> <NA> 106 Printer 1504. Full Join (3 points) Perform a full join between customers and orders.
full_joint <- full_join(customers, orders, by = "customer_id")a. How many rows are in the result?
nrow(full_joint)## [1] 8b. Identify any rows where there’s information from only one
table. Explain these results.
Rows 5-8 only have information from one table because they do not have
the corresponding data in the other table, the full join only displays
what they have.
c. Display the result
head(full_joint)## # A tibble: 6 × 6
## customer_id name city order_id product amount
## <dbl> <chr> <chr> <dbl> <chr> <dbl>
## 1 1 Alice New York 101 Laptop 1200
## 2 2 Bob Los Angeles 102 Phone 800
## 3 2 Bob Los Angeles 104 Desktop 1500
## 4 3 Charlie Chicago 103 Tablet 300
## 5 4 David Houston NA <NA> NA
## 6 5 Eve Phoenix NA <NA> NA5. Semi Join (3 points) Perform a semi join with customers as the left table and orders as the right table.
semi_joint <- semi_join(customers, orders, by = "customer_id")a. How many rows are in the result?
nrow (semi_joint)## [1] 3b. How does this result differ from the inner join
result?
This result differs from the inner join because it is only displaying
the customer table, as well as onyl displaying the customers on that
table that mathc with the orders table, without joining the tables
together.
c. Display the result
head(semi_joint)## # A tibble: 3 × 3
## customer_id name city
## <dbl> <chr> <chr>
## 1 1 Alice New York
## 2 2 Bob Los Angeles
## 3 3 Charlie Chicago6. Anti Join (3 points) Perform an anti join with customers as the left table and orders as the right table.
anti_joint <- anti_join(customers, orders, by = "customer_id")a. Which customers are in the result?
Customers 4 and 5 are in the result.
b. Explain what this result tells you about these
customers.
This result tells me that these are customers who did not order
something, as they have no data in the orders table.
c. Display the result
head(anti_joint)## # A tibble: 2 × 3
## customer_id name city
## <dbl> <chr> <chr>
## 1 4 David Houston
## 2 5 Eve Phoenix7. Practical Application (4 points) Imagine you’re analyzing
customer behavior.
a. Which join would you use to find all customers, including
those who haven’t placed any orders? Why? I would use full join
to find all customers, because full join gives all dta from both tables,
even those without matches in the other table.
b. Which join would you use to find only the customers who have
placed orders? Why?
I would use the inner join function to find only the customers who have
placed orders, because the inner join function only displays data that
matches in both tables, customers and orders.
c. Write the R code for both scenarios.
full_joint <- full_join(customers, orders, by = "customer_id")Joint <- inner_join(customers, orders, by = "customer_id")d. Display the result
head(full_join)##
## 1 function (x, y, by = NULL, copy = FALSE, suffix = c(".x", ".y"),
## 2 ..., keep = NULL)
## 3 {
## 4 UseMethod("full_join")
## 5 }head(inner_join)##
## 1 function (x, y, by = NULL, copy = FALSE, suffix = c(".x", ".y"),
## 2 ..., keep = NULL)
## 3 {
## 4 UseMethod("inner_join")
## 5 }