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# Dataset 1: Customers
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")
)
# Dataset 2: Orders
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. # a. How many rows are in the result? # b. Why are some customers or orders not included in the result? # c. Display the result
question1 <- inner_join (customers, orders)## Joining with `by = join_by(customer_id)`a. How many rows are in the result?
4 rows. The data has 4 rows
b.Why are some customers or orders not included in the result?
Inner join returns where there is a match on both tables. The customer data only has 3 customers in the order table where one customer has 2 orders. Only a match on 3 customers and 1 customer has 2 orders, making 4 rows.
c. Display the result
head(question1)## # 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 300#2. Left Join (3 points) Perform a left join with customers as the left table and orders as the right table. # a. How many rows are in the result? # b. Explain why this number differs from the inner join result. # c. Display the result
question2 <- left_join(customers, orders)## Joining with `by = join_by(customer_id)`a. How many rows are in the result?
6 rows. The data has 6rows
b. Explain why this number differs from the inner join result.
Left join prints every customer_id even if there is no order_id, product, or amount. Inner join only printed customer_ids if they had an order_id as well.
c. Display the result
head(question2)## # 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> NAquestion3 <- right_join(customers, orders)## Joining with `by = join_by(customer_id)`a. How many rows are in the result?
6 rows. The data has 6 rows.
b. Which customer_ids in the result have NULL for customer name and city? Explain why.
customer_ids 6 and 7 have NULL because there were no results for customer name and city.
c. Display the result
head(question3)## # 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 150#4. Full Join (3 points) Perform a full join between customers and orders. #a. How many rows are in the result? #b. Identify any rows where there’s information from only one table. Explain these results. #c. Display the result
question4 <- full_join(customers, orders)## Joining with `by = join_by(customer_id)`a. How many rows are in the result?
8 rows. The data has 8 rows.
b. Identify any rows where there’s information from only one table. Explain these results.
Rows 5,6,7, and 8 are missing information. Rows 5 and 6 are missing order_id, product, and amount. Rows 7 and 8 are missing name and city. They are msising information from the table.
c. Display the result
head(question4)## # 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> NA#5. Semi Join (3 points) Perform a semi join with customers as the left table and orders as the right table. #a. How many rows are in the result? #b. How does this result differ from the inner join result? #c. Display the result
question5 <- semi_join(customers, orders)## Joining with `by = join_by(customer_id)`a. How many rows are in the result?
3 rows. The data has 3 rows.
b. How does this result differ from the inner join result?
This result is missing a row with the number 2. In inner join it has two rows with the customer_id as 2. This result only has 1,2,and 3 has customer_ids. It doesn’t repeat ID numbers.
c. Display the result
head(question5)## # A tibble: 3 × 3
## customer_id name city
## <dbl> <chr> <chr>
## 1 1 Alice New York
## 2 2 Bob Los Angeles
## 3 3 Charlie Chicago#6. Anti Join (3 points) Perform an anti join with customers as the left table and orders as the right table. #a. Which customers are in the result? #b. Explain what this result tells you about these customers. #c. Display the result
question6 <- anti_join(customers, orders)## Joining with `by = join_by(customer_id)`a. Which customers are in the result?
David and Eve.
b. Explain what this result tells you about these customers.
This result tells us the customer’s ID number, name, and city.
c. Display the result
head(question6)## # A tibble: 2 × 3
## customer_id name city
## <dbl> <chr> <chr>
## 1 4 David Houston
## 2 5 Eve Phoenix#7. 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? #b. Which join would you use to find only the customers who have placed orders? Why? #c. Write the R code for both scenarios. #d. Display the result
a. Which join would you use to find all customers, including those who haven’t placed any orders? Why?
Full Join will show all customers including those with no orders. It shows all customers even if they only had information from one table.
b. Which join would you use to find only the customers who have placed orders? Why?
I would use right join because it shows all placed orders using (customers , orders).
c. Write the R code for both scenarios.
question7a <- full_join(customers, orders)## Joining with `by = join_by(customer_id)`question7b <- right_join(customers, orders)## Joining with `by = join_by(customer_id)`d. Display the result.
head(question7a)## # 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> NAhead(question7b)## # 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 150